MySQL Bugs: #74433: Slave 5.5/5.6 crash while using MRG table,auto

Bug #74433	Slave 5.5/5.6 crash while using MRG table,auto_increment,inner join and triggers
Submitted:	17 Oct 2014 23:32	Modified:	21 Oct 2014 9:25
Reporter:	Philippe Bidault	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	MySQL 5.6 and 5.5, 5.1.73, 5.6.22	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, INNER JOIN, merge table, Slave crash, triggers

Description:
Hello,

When replicating from a master 5.1.73 to 5.6.21, I can observe a crash of the slave if we use a combination of Merge tables with auto increment on the first column and a delete inner join which engage 2 triggers.

Here the error logs of the crash :

22:48:15 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=151
thread_count=3
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 67600 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xffffffff8a400468
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 = ffffffff8efcdce8 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x34)[0x852ae24]
/usr/sbin/mysqld(handle_fatal_signal+0x48c)[0x827cc5c]
[0x966400]
/usr/sbin/mysqld(_ZNK9table_def15compatible_withEP3THDP14Relay_log_infoP5TABLEPS5_P11st_mem_root+0xe1)[0x84e2ec1]
/usr/sbin/mysqld(_ZN14Rows_log_event14do_apply_eventEPK14Relay_log_info+0xa64)[0x84bb524]
/usr/sbin/mysqld(_ZN9Log_event11apply_eventEP14Relay_log_info+0x69)[0x84c4669]
/usr/sbin/mysqld(_Z26apply_event_and_update_posPP9Log_eventP3THDP14Relay_log_info+0x2e6)[0x84fa266]
/usr/sbin/mysqld[0x84fc32c]
/usr/sbin/mysqld(handle_slave_sql+0xd37)[0x84fe2f7]
/usr/sbin/mysqld(pfs_spawn_thread+0x171)[0x85872b1]
/lib/libpthread.so.0(+0x6b39)[0xfacb39]
/lib/libc.so.6(clone+0x5e)[0x222d6e]

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): 3
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.
141018 00:48:16 mysqld_safe Number of processes running now: 0
141018 00:48:16 mysqld_safe mysqld restarted

How to repeat:
On the slave 5.1, with binlog_format=mixed (Does not work with RBR)

#########################################
## Create database db1
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;

USE db1;

## Create MyISAM Merge table and su child t2 -> t1
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `c1` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `c2` text NOT NULL,
  PRIMARY KEY (`c1`)
)  ENGINE=MyISAM;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c1` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `c2` text NOT NULL,
  PRIMARY KEY (`c1`)
)  ENGINE=MRG_MyISAM UNION=(`t2`);

## Create MyISAM Merge table and su child t4 -> t3
DROP TABLE IF EXISTS `t4`;
CREATE TABLE `t4` (
  `c1` int(1) NOT NULL
)  ENGINE=MyISAM;

DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `c1` int(1) NOT NULL
)  ENGINE=MRG_MyISAM UNION=(`t4`);

## Trigger creation
DROP TABLE IF EXISTS `t1_deleted`;
CREATE TABLE `t1_deleted` (
  `c1` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `c2` text NOT NULL,
  PRIMARY KEY (`c1`)
)  ENGINE=MyISAM;

CREATE DEFINER=CURRENT_USER TRIGGER t1_deleted
BEFORE DELETE ON db1.t1
FOR EACH ROW 
INSERT INTO db1.t1_deleted SELECT * FROM db1.t1 WHERE c1 = old.c1;

## Trigger creation
DROP TABLE IF EXISTS `t3_deleted`;
CREATE TABLE `t3_deleted` (
  `c1` int(1) NOT NULL
)  ENGINE=MyISAM;

CREATE DEFINER=CURRENT_USER TRIGGER t3_deleted
BEFORE DELETE ON db1.t3
FOR EACH ROW 
INSERT INTO db1.t3_deleted SELECT * FROM db1.t3 WHERE c1 = old.c1;
############################################

Make these 2 INSERTS :

INSERT INTO `db1`.`t4` SET c1=1;
INSERT INTO `db1`.`t2` SET c1=1, c2="BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BONJOUR BO";

The second one is long ...... this is normal, one letter less and the bug can't be reproduced.

And to finish :
use db1; delete from t1,t3 using t1 INNER JOIN t3 where t1.c1=1 and t3.c1=1;

This will crash your MySQL 5.6 slave.

Hello Philippe,

Thank you for the report.
Verified as described.

Thanks,
Umesh

// Master 5.1.73, Slave 5.6.22

Slave
======

(gdb) bt
#0  0x0000003bf260c8ac in pthread_kill () from /lib64/libpthread.so.0
#1  0x00000000006968bc in handle_fatal_signal (sig=11) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/signal_handler.cc:230
#2  <signal handler called>
#3  0x00000000008c2f27 in field_metadata (this=0x7f0b200095c8, thd=0x7f0b20000990, rli=0x332d5e0, table=0x7f0b2001e3e0, conv_table_var=0x7f0b3a516420, mem_root=0x7f0b20015fe8)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/rpl_utility.h:327
#4  table_def::compatible_with (this=0x7f0b200095c8, thd=0x7f0b20000990, rli=0x332d5e0, table=0x7f0b2001e3e0, conv_table_var=0x7f0b3a516420, mem_root=0x7f0b20015fe8)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/rpl_utility.cc:887
#5  0x00000000008a0cd3 in Rows_log_event::do_apply_event (this=0x7f0b20015ee0, rli=0x332d5e0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/log_event.cc:11144
#6  0x00000000008a8b08 in Log_event::apply_event (this=0x7f0b20015ee0, rli=0x332d5e0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/log_event.cc:3030
#7  0x00000000008d6ffb in apply_event_and_update_pos (ptr_ev=0x7f0b3a5166b8, thd=0x7f0b20000990, rli=0x332d5e0)
    at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/rpl_slave.cc:3501
#8  0x00000000008d8cf2 in exec_relay_log_event (thd=0x7f0b20000990, rli=0x332d5e0) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/rpl_slave.cc:3997
#9  0x00000000008da749 in handle_slave_sql (arg=<value optimized out>) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/sql/rpl_slave.cc:5815
#10 0x0000000000b1fdca in pfs_spawn_thread (arg=0x7f0b28012b30) at /export/home/pb2/build/sb_0-13146752-1409933888.09/mysqlcom-pro-5.6.22/storage/perfschema/pfs.cc:1860
#11 0x0000003bf26079d1 in start_thread () from /lib64/libpthread.so.0
#12 0x0000003bf22e886d in clone () from /lib64/libc.so.6

// How to repeat

- Setup replication between Master(5.1.73) and Slave(5.6.22)
- Master>Execute test case
- Slave> Observe slave crashes

Conf used:

#Master
[mysqld]
user = root
pid-file = /tmp/74433M/74433M.pid
socket = /tmp/74433M/74433M.sock
port = 3306
basedir = /data/ushastry/server/mysql-5.1.73
datadir = /tmp/74433M
tmpdir = /tmp
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 1

binlog_format=mixed

#Slave

[mysqld]
user = root
pid-file = /tmp/74433S/74433S.pid
socket = /tmp/74433S/74433S.sock
port = 3307
basedir = /data/ushastry/server/mysql-advanced-5.6.22
datadir = /tmp/74433S
tmpdir = /tmp
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
server-id = 2

Also, see related Bug #69574

Hello,

I perhaps have a hint regarding this issue.

I can reproduce the bug with Master 5.1.73 -> Slave 5.5.3 but not with slave 5.5.2 nor 5.5.1

And from what I can see in the MySQL documentation, the behavior of the multi-delete statements has been modified :
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

"Incompatible change: In MySQL 5.5.3, several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases."

Coincidence or negative side effect ?

Philippe.