MySql replication from 5.1 master to 5.6 slave keep crashing

Question

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 '[email protected]: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

Answer 1

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

Answer 2

The reason Manny Calaverra was able to replicate from 5.5 to 5.6, but you are not able to replicate from 5.1 to 5.6 is because of versioning support as listed in the documentation for MySQL. The dev team only retains complete backward master/slave compatibility between the current version of MySQL, and the most recent minor version. So if there are no effective workarounds out there, it is because that is a feature that simply is not supported by the database application.

Source: Replication Compatibility Between MySQL Versions

MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a source running MySQL 5.6 to a replica running MySQL 5.7, from a source running MySQL 5.7 to a replica running MySQL 8.0, and so on. However, you may encounter difficulties when replicating from an older source to a newer replica if the source uses statements or relies on behavior no longer supported in the version of MySQL used on the replica. For example, foreign key names longer than 64 characters are no longer supported from MySQL 8.0.

If you can do it, one method that has been done was to create a middleman server. I have seen postings where people described successfully creating (specifically) a MySQL 5.1 server replicating to a MySQL 5.5 server then to a MySQL 5.6 server.