What a mess… After postponing this for many months because I knew what awaited me, I double-upgraded an Ubuntu 18.04 server today, and aside from MySQL breaking in some spectacular ways (corrupt redo log for one), the Roundcube package was broken, primarily due to MySQL.

In MySQL 8, ‘system’ is now a reserved keyword; a change that in my opinion was quite risky, because many people use that word. This resulted in the Roundcube Debian migration scripts failing, because the word ‘system’ was used without back-ticks around it. 

mysql said: ERROR 1064 (42000) at line 15: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system SET value='2018021600' WHERE name='roundcube-version'' at line 1.

These scripts are actually part of the .deb file, to be executed by dbconfig.

I reported the bug, but there was no interest in fixing it. Today I posted my work-around there too. Look there for the SQL files I used. I can’t upload them to this blog.

I had more problems, so I wanted to lay out what I did to fix it.

Ubuntu 20.04 installs this deb. In it are the SQL migration scripts in ‘usr/share/dbconfig-common/data/roundcube/upgrade/mysql’. I extacted it with ‘dpkg –extract’ and I took out ‘1.4.1-1’ and removed everything above the line that failed (because in MySQL the migration is not atomic…) and ran it like:

mysql roundcube < 1.4.1-1__starting_from_first_failed_statement.sql

Then on the next upgrade, I had to do the same with the deb for 1.5.

This one was more problematic, because the migration steps contained a change to case insensitive collation. This breaks if you have multiple users with different collation:

ERROR 1062 (23000) at line 33: Duplicate entry 'Info@example.nl-mail.example.nl' for key 'users.username'

I had to remove the users in question, from the table ‘users’ and ‘identities’, and continued running the script from the offending line (because again, DDL is not atomic in MySQL).

To be honest, I didn’t check if it cascaded foreign keys, like users’ address books and such… It was primarily ancient users, so I didn’t care.

A weird observation, is that the ‘system’ table is actually empty. That “SET value=’2018021600′” was a no-op. Now I wonder if I should add that row…?

The ‘system’ problem is likely to happen again on the next upgrade. At least I’m prepared now.