Skip to main content
Database Administrators

Return to Question

edited body
Source Link
Marcus
Marcus
  • 101
  • 2

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.1011 backport on db14 and db13

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.10 backport on db14 and db13

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

Source Link
Marcus
Marcus
  • 101
  • 2

MariaDB 10.7 to 10.5 replication issue with DROP DATABASE

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.10 backport on db14 and db13