MariaDB 10.7 to 10.5 replication issue with DROP DATABASE

Question

Originally posted on StackOverflow

I am helping a client migrate from a MariaDB 10.7 on Windows setup to a MariaDB 10.5 cluster on Debian 11. The Windows server is called win13, the Debian machines are db13 and db14. The current replication chain is win13 -> db14 -> db13. At some point the customer wants to change his application to access db14, at which point we'll change the replication to a primary - primary setup db13 <-> db14.

All databases are replicated including the mysql system database.

Now, in general the replication chain is working as expected. However, whenever a DROP DATABASE is issued on win13, db13 stops replication with these errors:

2024-07-11  7:28:09 28437 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(, found type varchar(384).
2024-07-11  7:28:09 28437 [ERROR] Slave SQL: Query caused different errors on master and slave.     Error on master: message (format)='Cannot load from %s.%s. The table is probably corrupted' error code=1728 ; Error on slave: actual message='no error', error code=0. Default database: 'bak_20240405'. Query: 'DROP DATABASE `bak_20240405`', Gtid 0-1-567223777, Internal MariaDB error code: 0
2024-07-11  7:28:09 28437 [Warning] Slave: Cannot load from mysql.proc. The table is probably corrupted Error_code: 1728
2024-07-11  7:28:09 28437 [Warning] Slave: Failed to open mysql.event Error_code: 1545
2024-07-11  7:28:09 28437 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000015' position 716995452
2024-07-11  7:28:09 28437 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.000015' at position 716995452
2024-07-11  7:28:09 28437 [Note] master was db14:3306

However, db14 continues to operate normally without showing errors and also the DROP DATABASE is apparently executed on db13 dispite the error message. A simple SQL_SLAVE_SKIP_COUNTER=1 fixes the issue on db13. mariadbcheck -e does not show any issues on both db13 and db14. Also, other statements like CREATE DATABASE do not cause issues.

I find that behaviour quite surprising. If at all, I would have expected the issue to show up on db14. Also, varchar(184) is a more relaxed column definition than char(141), so I wouldn't have had foreseen trouble here.

Q: Would it be sufficient to exclude systemdb from replication and changing the table definitions of mysql.proc and mysql.event to the 10.5 versions?

My other option would be to install the MariaDB 10.11 backport on db14 and db13

Answer 1

Incorrect definition of table mysql.event

This kind of error normally indicates that the DB that is throwing the error has been upgraded and not had mysql_upgrade run on it. The rest of the error I'd consider a distraction - at least to start with until confirming that this isn't the cause.

See https://dev.mysql.com/doc/refman/8.0/en/upgrading-what-is-upgraded.html for details on what mysql_upgrade does.

Another possible option, but I can't see how you could have managed it, is that you've set the mysql database to be replicated and when the drop is executed the mysql.event table replication happens and the structure has changed between versions so the change can't be applied on the replica.