1

I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but after resetting slave I was greeted with more issues causing the SQL thread to abort. The following includes the first example right after I've fixed the mysql table and executed reset slave.

141006  8:53:08 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.020292' at position 54986, relay log '/var/log/mysql/relay.010967' position: 38174
141006  8:53:08 [ERROR] Slave SQL: Error 'Duplicate column name 'character_set_client'' on query. Default database: 'mysql'. Query: 'ALTER TABLE proc ADD character_set_client
                      char(32) collate utf8_bin DEFAULT NULL
                      AFTER comment', Error_code: 1060
141006  8:53:08 [Warning] Slave: Duplicate column name 'character_set_client' Error_code: 1060
141006  8:53:08 [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.020292' position 54986
141006  8:53:08 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'mysql-bin.020293' at position 2146
141006  8:54:03 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141006  8:54:03 [Note] Slave I/O thread killed while reading event
141006  8:54:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.020293', position 2146
141006  8:56:45 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/relay.000001' position: 4
141006  8:56:45 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'FIRST' at position 4
141006  8:56:45 [ERROR] Slave SQL: Error 'Duplicate entry '323812407' for key 'PRIMARY'' on query. Default database: 'im_production'. Query: 'INSERT INTO messages
                         (timestamp, seq, conversation_id, sender_id, body)
                         VALUES (1410872583925, 194935,
                           (SELECT id
                           FROM conversations
                           WHERE owner_id=123456
                           AND with_user_id=1245678), 3069096, 'lol')', Error_code: 1062
141006  8:56:45 [Warning] Slave: Duplicate entry '323812407' for key 'PRIMARY' Error_code: 1062
141006  8:56:45 [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.019466' position 1568
141006  8:59:23 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.019500', position 72441800

At this point I panicked and immediately brought production down and began transferring tablespace files from master to slave.

After looking at these logs and doing some more research, I'm starting to think that was very silly of me. I'm seeing that after I fixed the original replication issue, I executed reset slave. That seems to have reset the slave's log position back. It went from using mysql-bin 2020292 at position 54986 to trying to start the I/O thread at mysql-bin.019500 at position 72441800. It now seems like the correct thing to do would have been to reset master to the last known good position before replication broke again, instead of panicking and recovering all of our tablespace data.

Thoughts? Was as I as silly as I'm thinking I am now?

1 Answer 1

1

Because you ran the mysql_upgrade on both master and slave, the ALTER TABLE was happening twice on slave side. The changes on master was propagated to slave, trying to add columns which you already added with manual mysql_upgrade.

I don't think reset slave has anything to with the problem, unless you were looking to skip the entire block of statements done by mysql_upgrade, which IMO is very dangerous.

2
  • The problem more specifically was after ALTER TABLE broke replication. I fixed it, ran reset slave, and got several dozen more replication errors. However, looking back at the logs I realized that the positions seemed odd. They were all positions on binlogs that were much older than the last one.
    – Eric R.
    Commented Oct 7, 2014 at 21:28
  • @Eric R. I think not running it with --skip-write-binlog was the cause. RESET SLAVE was a second mistake that altered the replication pointer and corrupted your slave data. GTID would have (hopefully) prevented this second error.
    – jynus
    Commented Oct 8, 2014 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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