MySql replication from 5.1 master to 5.6 slave keep crashing

crashMySQLreplication

I recently updated one of the replication slaves to mysql 5.6.
After importing a recent dump from the master, I run mysql_upgrade with no errors.

the database deamon started correctly with no error.

the problem began when I tried to start the slave; the slave's engine crashed almost immediately, in went on to a crash/restart endless loop.

this is what I've tried already

  • upgrade the slave from 5.1 to 5.5 (had no problems with replication at 5.5) and then up to 5.6 (crash)

  • change both master and slave binary log to row format (binlog-format=ROW in the config file)

  • resting both master and slave

my configuration is

  • both serves are hosted on a virtual machine (one for each)
  • running CentOS 6.5
  • the VM has 4 cpu's and 8 GB of ram

Here is the log dump:

2014-11-13 14:03:07 1022 [Note] Slave I/O thread: connected to master
'replicator@10.209.1.147:3306',replication started in log
'mysql-bin.000069' at position 31248706
2014-11-13 14:03:07 1022
[Warning] Slave SQL: If a crash happens this configuration does not
guarantee that the relay log info will be consistent, Error_code:
0
2014-11-13 14:03:07 1022 [Note] Slave SQL thread initialized,
starting replication in log 'mysql-bin.000069' at position 3940, relay
log '/data/relay-bin.000003' position: 4103
2014-11-13 14:03:07
1022 [Warning] Slave I/O: Notifying master by SET
@master_binlog_checksum= @@global.binlog_checksum failed with error:
Unknown system variable 'binlog_checksum', Error_code: 1193
2014-11-13 14:03:07 1022 [Warning] Slave I/O: Unknown system variable
'SERVER_UUID' on master. A probable cause is that the variable is not
supported on the master (version: 5.1.71-log), even though it is on
the slave (version: 5.6.21-log), Error_code: 1193
2014-11-13
14:03:07 1022 [ERROR] Error reading packet from server: Could not find
first log file name in binary log index file ( server_errno=1236)
2014-11-13 14:03:07 1022 [ERROR] Slave I/O: Got fatal error 1236 from
master when reading data from binary log: 'Could not find first log
file name in binary log index file', Error_code: 1236
2014-11-13
14:03:07 1022 [Note] Slave I/O thread exiting, read up to log
'mysql-bin.000069', position 31248706
2014-11-13 14:03:07 1022
[ERROR] Slave SQL: Could not execute Write_rows_v1 event on table
tpportal.player_game_plays; Duplicate entry '5890402' for key
'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the
event's master log mysql-bin.000069, end_log_pos 5048, Error_code:
1062
2014-11-13 14:03:07 1022 [Warning] Slave: Duplicate entry
'5890402' for key 'PRIMARY' Error_code: 1062
2014-11-13 14:03:07
1022 [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.000069' position 3940
14:03:07 UTC –
mysqld got signal 11 ;
This could be because you hit a bug. It is
also possible that this binary
or one of the libraries it was
linked against is corrupt, improperly built,
or misconfigured.
This error can also be caused by malfunctioning hardware.
We will
try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something
is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=100
thread_count=2
connection_count=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
47962 K bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fc53c000990
Attempting backtrace. You can use
the following information to find out
where mysqld died. If you
see no messages after this, something went
terribly wrong…
stack_bottom = 7fc547ffe7e0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8dbbb5]
/usr/sbin/mysqld(handle_fatal_signal+0x494)[0x665f24]
/lib64/libpthread.so.0(+0xf710)[0x7fc695957710]
/usr/sbin/mysqld(_Z10free_blobsP5TABLE+0x13)[0x76c103]
/usr/sbin/mysqld(_ZN14Relay_log_info20clear_tables_to_lockEv+0x31)[0x8b5e91]
/usr/sbin/mysqld(_ZN14Relay_log_info15cleanup_contextEP3THDb+0x84)[0x8b6064]
/usr/sbin/mysqld(handle_slave_sql+0x1e6)[0x8b1ea6]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xb00b1a]
/lib64/libpthread.so.0(+0x79d1)[0x7fc69594f9d1]
/lib64/libc.so.6(clone+0x6d)[0x7fc6946a0b6d]

Trying to get
some variables.
Some pointers may be invalid and cause the dump to
abort.
Query (0): is an invalid pointer
Connection ID (thread
ID): 4
Status: NOT_KILLED

The manual page at
http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the
crash.
141113 14:03:07 mysqld_safe Number of processes running
now: 0
141113 14:03:07 mysqld_safe mysqld restarted
2014-11-13
14:03:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use –explicit_defaults_for_timestamp server option
(see documentation for more details).
2014-11-13 14:03:08 1091
[Note] Plugin 'FEDERATED' is disabled.
2014-11-13 14:03:08 1091
[Note] InnoDB: Using atomics to ref count buffer pool pages
2014-11-13 14:03:08 1091 [Note] InnoDB: The InnoDB memory heap is
disabled
2014-11-13 14:03:08 1091 [Note] InnoDB: Mutexes and
rw_locks use GCC atomic builtins
2014-11-13 14:03:08 1091 [Note]
InnoDB: Memory barrier is not used
2014-11-13 14:03:08 1091 [Note]
InnoDB: Compressed tables use zlib 1.2.3
2014-11-13 14:03:08 1091
[Note] InnoDB: Using Linux native AIO
2014-11-13 14:03:08 1091
[Note] InnoDB: Using CPU crc32 instructions
2014-11-13 14:03:08
1091 [Note] InnoDB: Initializing buffer pool, size = 4.0G
2014-11-13 14:03:08 1091 [Note] InnoDB: Completed initialization of
buffer pool
2014-11-13 14:03:08 1091 [Note] InnoDB: Highest
supported file format is Barracuda.
2014-11-13 14:03:08 1091
[Note] InnoDB: The log sequence numbers 115242901774 and 115242901774
in ibdata files do not match the log sequence number 115242901784 in
the ib_logfiles!
2014-11-13 14:03:08 1091 [Note] InnoDB: Database
was not shutdown normally!
2014-11-13 14:03:08 1091 [Note] InnoDB:
Starting crash recovery.
2014-11-13 14:03:08 1091 [Note] InnoDB:
Reading tablespace information from the .ibd files…
2014-11-13
14:03:08 1091 [Note] InnoDB: Restoring possible half-written data
pages
2014-11-13 14:03:08 1091 [Note] InnoDB: from the doublewrite
buffer…
InnoDB: Last MySQL binlog file position 0 1405, file
name mysql-bin.000010
2014-11-13 14:03:09 1091 [Note] InnoDB: 128
rollback segment(s) are active.
2014-11-13 14:03:09 1091 [Note]
InnoDB: Waiting for purge to start
2014-11-13 14:03:09 1091 [Note]
InnoDB: 5.6.21 started; log sequence number 115242901784
2014-11-13 14:03:09 1091 [Note] Recovering after a crash using
/data/mysql-bin
2014-11-13 14:03:09 1091 [Note] Starting crash
recovery…
2014-11-13 14:03:09 1091 [Note] Crash recovery
finished.
2014-11-13 14:03:09 1091 [Note] Server hostname
(bind-address): '*'; port: 3306

Best Answer

Your master is not compatible with the 5.6 slave. binlog_checksum is only available on 5.6, and so is UUID. I have successfully replicated servers between 5.5 and 5.6. Perhaps you can check if these options can be disabled.

2014-11-13 14:03:07 1022 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 2014-11-13 14:03:07 1022 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.1.71-log), even though it is on the slave (version: 5.6.21-log), Error_code: 1193