Mysql to Postgres Problems

RT Users
#1

Hello list!

I used to use RT-3.4.1 on MySQL and I�m trying to migrate to RT-3.6.1 on
PostgreSQL 8.1.
I�m having some problems with the database import process.

I get the same errors many times:
ERROR: invalid byte sequence for encoding “UTF8”:
and
invalid input syntax for integer: “”
Can I just change the value to NULL where the value is empty?

I have noticed that most of my data was imported…

First of all I begin a new RT 3.6.1 installation, then I follow these
instructions (Request Tracker Wiki).
That is: Dump from mysql is done:
mysqldump -u user -p mydatabase > dumpfile.sql
I edit this dumpfile leaving it only with INSERT INTO lines and removing
everything related to the sessions table.
Then on my new installation, I access the DB and DELETE FROM each table.
After that, I do:
psql -h localhost -f dumpfile.sql newdb

After that I run the make upgrade process.

I hope you can help me out.
Thanks

Mario Gomide

#2

[…]
I considered migrating a couple weeks ago. I wrote a tutorial[1] about
[…]

1 - http://www.safernet.org.br/twiki/bin/view/Colaborar/RT_MySQL2Pg

Gilmar Santos Jr

#3

Hello Mario,

I considered migrating a couple weeks ago. I wrote a tutorial[1] about
it and a Perl script to help with the process. I’ve just added a few
instructions at RT wiki.

We have almost 400.000 tickets and had serious performance problems. Pg
performed much worse than MySQL, even after performance tunning
configurations. After increasing innodb_buffer_poll_size (MySQL’s
configuration directive) the performance problem was gone.

I tested with RT 3.4.5, but i think it will work fine on 3.6, since
there were no schema changes.

ps.: create database on postgre with encoding latin1.

Good luck,
Gilmar Santos Jr

Mario Gomide escreveu:

#4

Hi there!

I�ve successfully migrated the MySQL database to Postgres.
But still having some problems.
When I finish the import process I access the RT web interface. At the
homepage (RT at a Glance), I get:
Can’t call method “Content” on an undefined value at
/usr/local/rt3/share/html/Elements/MyRT line 75.

I�ve already solved this problem by running the make upgrade scripts as
follows:
/path/to/rt/sbin/rt-setup-database --dba root --prompt-for-dba-password
–action insert --datadir etc/upgrade/3.5.1
And the previous problem was gone!

Now, when I run the upgrade script I get the following error 3 times:
Error: Internal Error: Couldn’t execute the query 'INSERT INTO
Attributes (ContentType, Creator, ObjectId, LastUpdatedBy, Created,
Content, LastUpdated, Description, Name, ObjectType) VALUES (?, ?, ?, ?,
?, ?, ?, ?, ?, ?)'ERROR: permission denied for sequence attributes_id_seq

Any ideas?

Thanks a lot!!
Mario Gomide

Gilmar Santos Jr escreveu:

#5

Mario Gomide escreveu:

[…]

Now, when I run the upgrade script I get the following error 3 times:
Error: Internal Error: Couldn’t execute the query 'INSERT INTO
Attributes (ContentType, Creator, ObjectId, LastUpdatedBy, Created,
Content, LastUpdated, Description, Name, ObjectType) VALUES (?, ?, ?,
?, ?, ?, ?, ?, ?, ?)'ERROR: permission denied for sequence
attributes_id_seq

Any ideas?

Verify the owner of sequence objects in Pg…

Gilmar Santos Jr

#6

I verified the permissions (like the ERROR message suggested), and
change it around a bit, but I got nothing.

I also tried to force writting on the Attributes Table these values…

id, name, description, content, contenttype, objecttype, objectid,
creator, created, lastupdatedby, lastupdated
1, Search - My Tickets, [_1] highest priority tickets I own, ENCODED
HASH, storable, RT::System, 1, 1, “date and time”, 1, "date and time"
2, Search - Unowned Tickets, [_1] newest unowned tickets, ENCODED HASH,
storable, RT::System, 1, 1, “date and time”, 1, "date and time"
3, HomepageSettings, HomepageSettings, ENCODED HASH, storable,
RT::System, 1, 1, “date and time”, 1, “date and time”
… checking the sequences, permissions etc.

And still get the homepage errors: Can’t call method “Content” on an
undefined value at /usr/local/rt3/share/html/Elements/MyRT line 75.

The data is there! Why can�t RT read the DB correctly?

Thank you very much!
Mario Gomide