1

Rarely, but intermittently, MariaDB 5.5 fails to replicate the timezone tables onto a completely new replica (instance creation is all automated, so there's no room for missing/changed steps). We never change these internal tables, so how can this happen?

Example result (formatted for readability):

$ mysql -e 'SHOW SLAVE STATUS\G'
[…]
           Master_Log_File: bin-log.000011
       Read_Master_Log_Pos: 5124
            Relay_Log_File: relay-log.000002
             Relay_Log_Pos: 527
     Relay_Master_Log_File: bin-log.000010
          Slave_IO_Running: Yes
         Slave_SQL_Running: No
                Last_Errno: 1062
                Last_Error: Error 'Duplicate entry '339--1956609120' for key 
'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO 
time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) […]
1

I am not aware of any automatic method inserting data to those tables? Maybe you are trying to load data to a table that already contains them? Which OS are you? Maybe the operating system is doing it without telling you. Maybe someone setup a cron doing on the master:

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

https://mariadb.com/kb/en/mariadb/mysql_tzinfo_to_sql/

You can put a replication filter for now to avoid the issues, but then research the master's binaty log to understand who and where that is being inserted, and why. If those are legitimate, or it is automatized, make sure it is executed without writing to the binary log (SET SESSION sql_Log_bin=0)

2
  • Updated the question to reflect your questions. And nope, we haven't set up any crazy crons.
    – l0b0
    Mar 14 '17 at 19:22
  • 1
    The answer may be that Galera really does not want to deal with replicating MyISAM. So do it like RSU -- apply the patch to each node without replicating.
    – Rick James
    May 25 '17 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.