Kevin Morse
Kevin Morse

Reputation: 286

COLLATE differences between SQL dumps from primary and replica MySQL servers

I have two identical servers running Apache, MySQL 8.0.35, and PHP 7.4. The servers have one-way asynchronous MySQL replication setup using GTID-based replication. One server is the primary and the other is a replica.

Recently the primary server lost connectivity for a few hours due to a power outage so I switched over to the replica. After the outage I swapped replication roles between the two servers and they became synchronized again.

In order to convince myself that all the data was intact, I dumped the data on both servers and compared the SQL files.

What I found was thousands of differences between the CREATE TABLE statements and no differences with any of the INSERT INTO statements.

All the replica statements containing text columns had e.g. CHARACTER SET ascii COLLATE ascii_general_ci whereas the same statements on the primary had CHARACTER SET ascii with no COLLATE specified.

Using find and replace a few times allowed me to insert the missing COLLATE statements for the three character sets in use (ascii, utfmb3, and utfmb4) so I could prove the servers were synchronized but I don't understand why the dumps were different?

As a follow-up question, is there an easy way to update the primary so it contains the missing COLLATE statements? I never specified the collation that the replica seems to be using so it must have gotten default values somehow.

Upvotes: 0

Views: 45

Answers (0)

Related Questions