RT Upgrade fails to 4.5.2

RT Users
1

We are trying to upgrade our RT to 5.0.1 but it fails at

Processing 4.5.2
Now populating database schema.

[3991371] [Thu Dec 9 22:51:10 2021] [critical]: DBD::mysql::st execute failed: Specified key was too long; max key length is 1000 bytes at /root/rt-5.0.1/sbin/…/lib/RT/Handle.pm line 551. (/root/rt-5.0.1/sbin/…/lib/RT.pm:409)
DBD::mysql::st execute failed: Specified key was too long; max key length is 1000 bytes at /root/rt-5.0.1/sbin/…/lib/RT/Handle.pm line 551.
make: *** [Makefile:393: upgrade-database] Error 11

We are running on RHEL8 with MySQL 5.7.34.

I am not a database expert and cannot figure out how to get past this step.

Any recommendation would be greatly appreciated.

2

If you look in etc/upgrade/4.5.2/schema.mysql you can see the commands that are being run. Can you run each step individually to see which table is causing the problem?

3

Thanks for the tip. I am looking into the etc/upgrade/4.5.2/schema.mysql today. I’m not a mysql expert but I think I can try to step through the upgrade one step at a time. I will post an update once I have something.

4

That was easier than I thought. I got 2 errors.

mysql> ALTER TABLE Attachments CONVERT TO CHARACTER SET utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
mysql> ALTER TABLE Queues CONVERT TO CHARACTER SET utf8mb4;

mysql> ALTER TABLE Tickets CONVERT TO CHARACTER SET utf8mb4;
ERROR 1292 (22007): Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘Due’ at row 2104

I have figured out the invalid date issue and will manually fix those. There were 17 bad dates.
Any suggestions as how to fix Specified key was too long; max key length is 1000 bytes in Attachments?

5

I found one possible solution which suggests converting the engine from MyISAM to InnoDB. I attempted the following:

mysql> ALTER TABLE Attachments ENGINE=InnoDB;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This worked:

mysql> set innodb_lock_wait_timeout=500;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE Attachments ENGINE=InnoDB;
Query OK, 102279 rows affected (31 min 10.90 sec)
Records: 102279 Duplicates: 0 Warnings: 0

7

Fixed it…

mysql> ALTER TABLE Attachments CONVERT TO CHARACTER SET utf8mb4;
Query OK, 102279 rows affected (47 min 2.17 sec)
Records: 102279 Duplicates: 0 Warnings: 0