DB upgrade to 5.0 failed - AttachmentIndex key

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?

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.

I believe that is the default table for full text search

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:

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.

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

2 Likes