Database upgrade for mySQL fails for 2020020101.sql

[oliverrahner]

I tried upgrading from 9a8f4cb (MySQL schema 2019092900) to 0fbd184 (MySQL schema 2020020101) today.

The updates for 2019092900 and 2020020100 went smooth.
But in 2020020101, running the statement
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
fails with
ERROR 1834 (HY000) at line 2: Cannot delete rows from table which is parent in a foreign key constraint 'user_id_fk_cache' of table 'cache'

I am using MariaDB 10.1.29 but already changed the settings needed for big indexes (as mentioned in #7212).

[alecpl]

Strange, the query is not supposed to remove any rows. Maybe MySQL/MariaDB bug.

[oliverrahner]

Thanks for the feedback, I'll investigate further then and let you know about the outcome.

[oliverrahner]

So, it turns out there are several problems with the intended updates.

1. when changing charsets on columns contained in indexes, a table rebuild can be trigerred, which seems to evaluate to deleting and reinserting all rows, thus leading to the above metioned error; MariaDB seems to have changed its behaviour over time between 10.1 and 10.4
2. changing the column users.username to utf8mb4_unicode_ci (was utf8-bin) means we switched from case sensitive to case insensitive which will lead to problems if people typed their usernames in different casing (as was the case on my server)
3. with legacy installations, ROW_FORMAT for tables should be changed to be able to create oversized indexes (as mentioned in MySQL: Specified key was too long; max key length is 767 bytes #7212) from the old default Compact to Dynamic

1 and 3 can be fixed by a modified SQL script.
For 1, we first drop all foreign key constraints and re-add them afterwards.
For 3, we just change the row format.

2 can't be fixed automatically in a generic way, only by reverting back to the previously used case sensitivity via collation utf8mb4_bin

ALTER TABLE cache DROP FOREIGN KEY user_id_fk_cache;
ALTER TABLE cache_index DROP FOREIGN KEY user_id_fk_cache_index;
ALTER TABLE cache_messages DROP FOREIGN KEY user_id_fk_cache_messages;
ALTER TABLE cache_thread DROP FOREIGN KEY user_id_fk_cache_thread;
ALTER TABLE collected_contacts DROP FOREIGN KEY user_id_fk_collected_contacts;
ALTER TABLE contactgroups DROP FOREIGN KEY user_id_fk_contactgroups;
ALTER TABLE contacts DROP FOREIGN KEY user_id_fk_contacts;
ALTER TABLE dictionary DROP FOREIGN KEY user_id_fk_dictionary;
ALTER TABLE filestore DROP FOREIGN KEY user_id_fk_filestore;
ALTER TABLE identities DROP FOREIGN KEY user_id_fk_identities;
ALTER TABLE searches DROP FOREIGN KEY user_id_fk_searches;

ALTER TABLE `session` ROW_FORMAT=DYNAMIC;
ALTER TABLE `users` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_shared` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_index` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_thread` ROW_FORMAT=DYNAMIC;
ALTER TABLE `cache_messages` ROW_FORMAT=DYNAMIC;
ALTER TABLE `contacts` ROW_FORMAT=DYNAMIC;
ALTER TABLE `contactgroups` ROW_FORMAT=DYNAMIC;
ALTER TABLE `identities` ROW_FORMAT=DYNAMIC;
ALTER TABLE `dictionary` ROW_FORMAT=DYNAMIC;
ALTER TABLE `searches` ROW_FORMAT=DYNAMIC;
ALTER TABLE `filestore` ROW_FORMAT=DYNAMIC;
ALTER TABLE `system` ROW_FORMAT=DYNAMIC;

ALTER TABLE `session` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `cache` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `cache_shared` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `cache_index` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `cache_thread` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `cache_messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `contacts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `contactgroups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `identities` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `dictionary` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `searches` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `filestore` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `system` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;



ALTER TABLE `cache`
  ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_index`
  ADD CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_messages`
  ADD CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `cache_thread`
  ADD CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `collected_contacts`
  ADD CONSTRAINT `user_id_fk_collected_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `contactgroups`
  ADD CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `contacts`
  ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dictionary`
  ADD CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `filestore`
  ADD CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `identities`
  ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `searches`
  ADD CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

[alecpl]

You say that innodb_large_prefix=true, innodb_file_format=Barracuda is not needed if we change row_format? Is row_format change force "rebuilding" of tables? I'm hesitant to that part of the proposed change.

Point 2. has been fixed.

[johndoh]

ALTER TABLE `users` ALTER `username` varchar(128) BINARY NOT NULL

results in:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'varchar(128) BINARY NOT NULL' at line 1

Tested on MariaDB, 10.3.22-MariaDB-0+deb10u1

[alecpl]

Fixed above error.

[oliverrahner]

You say that innodb_large_prefix=true, innodb_file_format=Barracuda is not needed if we change row_format? Is row_format change force "rebuilding" of tables? I'm hesitant to that part of the proposed change.

No, you still need inndob_large_prefix=true. But for actually being able to use them you also need to change the row format.

Quote from the MariaDB docs:

In MariaDB 10.1 and before, the latter two row formats are only supported if the InnoDB file format is Barracuda. Therefore, the innodb_file_format system variable must be set to Barracuda to use these row formats in those versions.

The row_format will already be DYNAMIC for all new installations because that's the default for MariaDB starting with 10.2.2. As you already chose (implicitly) not to support MariaDB and MySQL versions that do not support large prefixes, there is no downside to making this change. Apart from the process taking a while, that is.

[alecpl]

Applied suggested changes.