Transitioning from RT 3.2.2 to RT 4.0.1 - problem updating the Schema

Dear RT-Users,

I have an old server running:
Red Hat Linux 3.2.3
MySQL 4.0.23-standard
Perl 5.8.3
Apache 2.0.46
RT 3.2.2

Trying to move the database to a new server running:
Ubuntu Server 11.04
MySQL 5.1.54-1ubuntu4
Perl 5.10.1
Apache2 2.2.17
RT 4.0.1

I followed the guide at http://requesttracker.wikia.com/wiki/MigrateToNewServer, with some changes because I’m going from / to different versions of software.

  1. On the RT3.2.2 server I ran:
    Mysqldump --user=root --password={password} --opt --skip-lock-tables --single-transaction --databases rt3 > /home/mysql/rt3.sql
    (tried --default-character-set=binary, but got the error “Character set ‘binary’ is not a compiled character set”, and we don’t have, need, or use binary attachments).
  2. FTPd the rt3.sql dump to the RT4.0.1 server.
  3. On the RT4.0.1 server, stopped Apache2.
  4. Dropped the rt3 database: make dropdb
  5. Imported the dump file: mysql --user=root --password={password} < rt3.sql
  6. Ran mysql -p and made sure the database “rt3” and the tables appeared, and they did.
  7. Ran the schema updates, with 3.2.2 as the starting version, and 3.7.87 as the ending version (as stated in the documentation): /opt/rt4/sbin/rt-setup-database \ --prompt-for-dba-password --action upgrade
  8. Get the following error when performing the schema updates:

Processing 3.3.0
Now populating database schema.
DBD::mysql::st execute failed: Table ‘ObjectCustomFieldValues’ already exists at /opt/rt4/sbin/…/lib/RT/Handle.pm line 517.

The schema update process stops there, and does not continue. I read about possibly needing to upgrade my MySQL installation on the Red Hat server from 4.0 to at least 4.1, but I’m not even sure I can find 4.1, and I read of some issues involving character set changes between versions. Not sure about compatibility between later versions of MySQL and RT3.2.2. The Red Hat server is a production server, so I sure don’t want to break it. I checked the tables after loading the RT3 dump into the new server and DEFAULT CHARSET=latin1 on those tables, so I believe that is correct.

Has anyone else run into a similar problem updating the schema, and do you have any advice or recommendations?

Also, is the current schema version stored anywhere in the RT database, so I can manually check the current schema version?

Thank you very much for any advice / guidance you can provide,

–John

[snip]

  1. FTPd the rt3.sql dump to the RT4.0.1 server.
  2. On the RT4.0.1 server, stopped Apache2.
  3. Dropped the rt3 database: make dropdb
  4. Imported the dump file: mysql --user=root --password={password} < rt3.sql

rt3.sql has NO instructions that create database. I suspect that you
didn’t drop DB, dropped different DB and something like that. SQL dump
produced by mysql has code tha drops a table right before creating a
new one and filling it with data, but this only works for tables that
are in the dump. Tables that are not in the dump stay in the DB you
restore dump into. Error quoted below in this message supports this
theory.

Connect to mysql with the shell and make sure dbs are really dropped
or issue drop database bla-bla; right from the mysq shell. Instead
of creating database manually use sbin/rt-setup-database:

sbin/rt-setup-database --action create --…other options

For mysql you can as well run acl action:

sbin/rt-setup-database --action acl --datadir etc/ --…other options

Above commands create a new empty DB according to RT’s config, but
without any tables and data. Restore dump into this DB.

  1. Ran mysql -p and made sure the database “rt3” and the tables appeared, and they did.
  2. Ran the schema updates, with 3.2.2 as the starting version, and 3.7.87 as the ending version (as stated in the documentation): /opt/rt4/sbin/rt-setup-database \ --prompt-for-dba-password --action upgrade
  3. Get the following error when performing the schema updates:

Processing 3.3.0
Now populating database schema.
DBD::mysql::st execute failed: Table ‘ObjectCustomFieldValues’ already exists at /opt/rt4/sbin/…/lib/RT/Handle.pm line 517.

Best regards, Ruslan.