Comments - Replication slave loses connection and does not recover

4 years, 6 months ago Ian Light

Symptom: Starting the Slave manually causes replication to initially succeed (for a minute or two), but the Slave subsequently stops after reporting a failure to connect to the Master, similar to the error below:

2018-09-17 16:03:12 13 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193
2018-09-17 16:04:12 13 [ERROR] Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)

Double Checks: Confirm that both Master and Slave are using the same character sets. This may not be obvious if you are replicating between a much older version of MySql (let's say 5.1) and a current MariaDB 10.3.X instance. The older instance of MySql may not be aware of the utf8mb4 versus utf8 issue. utf8mb4 may be the default in newer MySql and MariaDB instances, whereas utf8 may be the default in older versions.

Remediation Example: If replicating between a Master with a much older version of MySql, and a Slave using a newer MariaDB (or MySql) instance, in your my.ini (or equivalent), make sure that you comment out the utf8mb4, and explicitly set the utf8 options on the Slave.

i.e.comment out the use of utf8mb4 on the slave IF the server is using utf8
# character-set-server=utf8mb4
# collation-server=utf8mb4_general_ci

If replicating between newer servers, please just confirm that the character sets in use are the same on Master and Slave (in this instance, it's preferable for both servers to use utf8mb4.

The key is to make sure that the Slave is configured to use the same character set that the Master is using.

Post-Mortem Considerations:Consider using utf8mb4 on both Master and Slave.
If this is not possible due to version constraints, then start the process of designing and scheduling a testing program to facilitate an upgrade of the Master to a newer version (preferably MariaDB).

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.