DB upgrade to 5.0 failed - AttachmentIndex key

RT Users
1

I am trying to upgrade 4.4.3 to 5.0. I use MySQL. I got this error from rt-setup-database:

[26621] [Wed Jan 20 04:31:50 2021] [warning]: Couldn’t run SQL query: ALTER TABLE AttachmentsIndex MODIFY id BIGINT NOT NULL AUTO_INCREMENT, CONVERT TO CHARACTER SET utf8mb4: Specified key was too long; max key length is 3072 bytes at ./etc/upgrade/4.5.8/content line 19. (./etc/upgrade/4.5.8/content:19)

What should I do about this?

2

Is the table AttachmentsIndex a local, custom table? Neither our old 4.4.3 version of RT or our current 5.0 instances seem to have a table called that.

3

I believe that is the default table for full text search

4

Searching on that error I see some threads like this:

In RT 4.4 the ‘query’ column for the fulltext index was:
query VARCHAR(3072) NOT NULL, so thats where the index size is coming from.

You can also check this config out:
https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

5

OK it looks like I have an ancient (and broken) sphinx fulltext indexing setup, whose table is upsetting the upgrade script. I will fix the indexing on RT4 and run the upgrade again.

6

Removed full text setup on RT4, ran the upgrade again, set up full text indexing again and all is good.

2 Likes