Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What do you think is the reason for the duplicated key errors in our MySQL 5.1.61 replication slave, after the slave machine crashed? It's not corrupted redo logs.

Details follows:

We have configured MySQL master-master replication (that is, two MySQL servers, each one is a master and slave to the other one). Recently one of the servers crashed. When it started, MySQL did crash recovery, but encountered a unique key error, and replication was thereafter broken. (We fixed the issue by overwriting that broken master with a --master-data dump from the other master.)

I've checked the redo logs and it seems they didn't get corrupted (which might otherwise happen if MySQL 5.1 crashes). In fact the only error message after the crash and before the first duplicate key error was:

Error in Log_event::read_log_event(): 'read error', data_len: 142, event_type: 2

What does that error mean? (I've googled but not found out)

What things, apart from corrupted redo logs, might cause duplicated key errors after a MySQL replication slave crash? And what do you think happened in our case?

Here is the log file:

140320  1:28:52  InnoDB: Database was not shut down normally!   <--- because the whole server crashed
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1 1812207104
InnoDB: Doing recovery: scanned up to log sequence number 1 1817449984
InnoDB: Doing recovery: scanned up to log sequence number 1 1822692864
...
InnoDB: Doing recovery: scanned up to log sequence number 1 1880364544
InnoDB: Doing recovery: scanned up to log sequence number 1 1885607424
InnoDB: Doing recovery: scanned up to log sequence number 1 1887119996
140320  1:28:55  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 43016494, file name ./mysql-bin.000057
140320  1:29:08  InnoDB: Started; log sequence number 1 1887119996
140320  1:29:08 [Note] Recovering after a crash using mysql-bin
140320  1:29:09 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 142, event_type: 2    <--- don't know what this means
140320  1:29:09 [Note] Starting crash recovery...
140320  1:29:09 [Note] Crash recovery finished.
140320  1:29:09 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000071' at position 25986056, relay log './mysql-relay-bin.001761' position: 25986201
140320  1:29:09 [Note] Event Scheduler: Loaded 0 events
140320  1:29:09 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.61-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
140320  1:29:09 [Note] Slave I/O thread: connected to master 'replication@dtcp-cmsadmn01:3306',replication started in log 'mysql-bin.000071' at position 25986617
140320  1:29:09 [ERROR] Slave SQL: Error 'Duplicate entry '722217' for key 'PRIMARY'' on query. Default database: 'concrete5'. Query: 'insert into Logs (logType, logText, logIsInternal, logUserID) values ('debug', 'Get &quot;tin-metals&quot; page structure\n', 0, '73')', Error_code: 1062     <---  this error breaks master-master replication
140320  1:29:09 [Warning] Slave: Duplicate entry '722217' for key 'PRIMARY' Error_code: 1062
140320  1:29:09 [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.000071' position 26020404
share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.