MySQL Bugs: #12574: rbr of "after insert trigger" causes stack on slave mysqld process.

Bug #12574	rbr of "after insert trigger" causes stack on slave mysqld process.
Submitted:	14 Aug 2005 16:40	Modified:	26 Oct 2005 17:55
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.0-wl1012	OS:	Linux (Linux)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
Was trying ot create a trigger test from bug #12408 for rbr. I was having issues of the tables not being created on the slave for myisam, so I decided to take out the primary key of one of the create table statements to see if it would then be created on the slave. I found the following stack trace in the slave error log:

0x818b424 handle_segfault + 452
0x401855cd _end + 934026141
0x81c4006 _Z11lock_tablesP3THDP13st_table_listj + 678
0x820b25c _ZN14Rows_log_event10exec_eventEP17st_relay_log_info + 204
0x8285c3f _Z20exec_relay_log_eventP3THDP17st_relay_log_info + 575
0x82839a8 handle_slave_sql + 1048
0x4017f6de _end + 934001838
0x402fe6c7 _end + 935570583

How to repeat:
# Begin clean up test section
connection master;
--disable_warnings
--error 0,1360
DROP TRIGGER test.t2_ai;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
--enable_warnings

# test section 1,  Taken from bug #12408

CREATE TABLE test.t2 (value CHAR(30),domain_id INT, mailaccount_id INT, program CHAR(30),keey CHAR(30));

CREATE TABLE test.t1 (id INT,domain CHAR(30),PRIMARY KEY(id));

delimiter |;
CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t2 ms, test.t1 d  SET ms.value='No' WHERE ms.domain_id = (SELECT id FROM test.t1 WHERE domain='example.com') AND ms.mailaccount_id IS NULL  AND ms.program='spamfilter' AND ms.keey='scan_incoming'|
delimiter ;|

INSERT INTO test.t1 VALUES (1, 'example.com'),(2, 'mysql.com'),(3, 'earthmotherwear.com'), (4, 'yahoo.com');

select * from test.t1;
show binlog events;
connection slave;
use test;
show tables;
#select * from test.t1;
connection master;

INSERT INTO test.t2 VALUES ('Yes', 1, NULL, 'spamfilter',
'scan_incoming');

DELETE FROM test.t1 WHERE id = 1;

select * from test.t1;
select * from test.t2;
connection slave;
#select * from test.t1;
#select * from test.t2;
connection master;
show binlog events;

# cleanup

DROP TRIGGER test.t2_ai;
DROP TABLE test.t1;
DROP TABLE test.t2;

New test case produces same results:
 0x40250da1 in kill () from /lib/libc.so.6
#1  0x40181f4a in pthread_kill () from /lib/libpthread.so.0
#2  0x0828fd2d in write_core (sig=11) at stacktrace.c:220
#3  0x0818b4ed in handle_segfault (sig=11) at mysqld.cc:2064
#4  0x401855cd in __pthread_sighandler () from /lib/libpthread.so.0
#5  <signal handler called>
#6  lock_tables (thd=0x872f338, tables=0x87460b8, count=0) at table.h:486
#7  0x0872b078 in ?? ()
#8  0x0820b25c in Rows_log_event::exec_event(st_relay_log_info*) (
    this=0x872f338, rli=0x873d67c) at log_event.cc:5064
#9  0x08285c3f in exec_relay_log_event (thd=0x8775510, rli=0x873d67c)
    at slave.cc:2960
#10 0x082839a8 in handle_slave_sql (arg=0x8737498) at slave.cc:3508
#11 0x4017f6de in pthread_start_thread () from /lib/libpthread.so.0

#############################################################################
# Original Author: Jonathan Miller                                          #
# Original Date: Aug/14/2005                                                #
#############################################################################

# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

#-- disable_query_log
#-- disable_result_log

# Begin clean up test section
connection master;
--disable_warnings
--error 0,1360
DROP TRIGGER test.t1_bi;
--error 0,1360
DROP TRIGGER test.t2_ai;
--error 0,1360
DROP TRIGGER test.t1_bu;
--error 0,1360
DROP TRIGGER test.t1_au;
--error 0,1360
DROP TRIGGER test.t1_bd;
--error 0,1360
DROP TRIGGER test.t1_ad;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
--enable_warnings

# test section 1

CREATE TABLE test.t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));

delimiter |;
CREATE TRIGGER test.t1_bi BEFORE INSERT ON test.t1 FOR EACH ROW UPDATE test.t1 SET b1=100110011 and y=YEAR(NOW())|
#CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW BEGIN
#    INSERT INTO test.t2 VALUES(NULL,11111111,'Testing MySQL databases is a dream job come true', 'Theonlyjobbetteristestingdarkbeerprague',1234567891.1234,12.51,0,1965,NOW());
#    UPDATE test.t1 SET f = ROUND(f);
#END|
delimiter ;|

INSERT INTO test.t1 VALUES(NULL,00001111,'Testing MySQL databases is a cool ', 'Must make it good for the customer',987654321.4321,15.21,0,1965,NOW());
INSERT INTO test.t2 VALUES(NULL,00001111,'Testing MySQL databases is a cool ', 'Must make it good for the customer',987654321.4321,15.21,0,1965,NOW());

select * from test.t1;
select * from test.t2;
show binlog events;
connection slave;
use test;
show tables;
sleep 6;
select * from test.t1;
select * from test.t2;
connection master;

# cleanup
--disable_warnings
--error 0,1360
DROP TRIGGER test.t1_bi;
--error 0,1360
DROP TRIGGER test.t2_ai;
--error 0,1360
#DROP TRIGGER test.t1_bu;
--error 0,1360
#DROP TRIGGER test.t1_au;
--error 0,1360
#DROP TRIGGER test.t1_bd;
--error 0,1360
#DROP TRIGGER test.t1_ad;
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;
--enable_warnings

Before update seems to have same issue:
#0  0x40250da1 in kill () from /lib/libc.so.6
#1  0x40181f4a in pthread_kill () from /lib/libpthread.so.0
#2  0x0828fd2d in write_core (sig=11) at stacktrace.c:220
#3  0x0818b4ed in handle_segfault (sig=11) at mysqld.cc:2064
#4  0x401855cd in __pthread_sighandler () from /lib/libpthread.so.0
#5  <signal handler called>
#6  lock_tables (thd=0x413006c0, tables=0x41302630, count=0) at table.h:486
#7  0x41302d20 in ?? ()
#8  0x0820b25c in Rows_log_event::exec_event(st_relay_log_info*) (
    this=0x413006c0, rli=0x873d67c) at log_event.cc:5064
#9  0x08285c3f in exec_relay_log_event (thd=0x8775510, rli=0x873d67c)
    at slave.cc:2960
#10 0x082839a8 in handle_slave_sql (arg=0x8737498) at slave.cc:3508
#11 0x4017f6de in pthread_start_thread () from /lib/libpthread.so.0

CREATE TABLE test.t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));

delimiter |;
CREATE TRIGGER test.t1_bu BEFORE UPDATE on test.t1 FOR EACH ROW BEGIN
   UPDATE test.t2 SET y = '2000';
   INSERT INTO test.t1 VALUES(NULL,1,'Testing MySQL databases before update ', 'Insert should work',621.43, 0105.21,0,1974,NOW());
END|
delimiter ;|

INSERT INTO test.t1 VALUES(NULL,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer',654321.4321,15.21,0,1965,NOW());
INSERT INTO test.t2 VALUES(NULL,0,'Testing MySQL databases is a cool ', 'MySQL Customers ROCK!',654321.4321,115.24521,0,1965,NOW());

select * from test.t1;
select * from test.t2;
connection slave;
use test;
show tables;
sleep 9;
select * from test.t1;
select * from test.t2;
connection master;

UPDATE test.t1 SET b1 = 0 WHERE b1 = 1;

Here is an after update :-(
#0  0x40250da1 in kill () from /lib/libc.so.6
#1  0x40181f4a in pthread_kill () from /lib/libpthread.so.0
#2  0x0828fd2d in write_core (sig=11) at stacktrace.c:220
#3  0x0818b4ed in handle_segfault (sig=11) at mysqld.cc:2064
#4  0x401855cd in __pthread_sighandler () from /lib/libpthread.so.0
#5  <signal handler called>
#6  lock_tables (thd=0x41505750, tables=0x41500bc8, count=0) at table.h:486
#7  0x415012b8 in ?? ()
#8  0x0820b25c in Rows_log_event::exec_event(st_relay_log_info*) (
    this=0x41505750, rli=0x873d67c) at log_event.cc:5064
#9  0x08285c3f in exec_relay_log_event (thd=0x8775510, rli=0x873d67c)
    at slave.cc:2960
#10 0x082839a8 in handle_slave_sql (arg=0x8737498) at slave.cc:3508
#11 0x4017f6de in pthread_start_thread () from /lib/libpthread.so.0

CREATE TABLE test.t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));

delimiter |;
CREATE TRIGGER test.t2_au AFTER UPDATE on test.t2 FOR EACH ROW BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a DECIMAL(10,4);
  DECLARE b FLOAT;
  DECLARE num MEDIUMINT;
  DECLARE cur1 CURSOR FOR SELECT t2.id, t2.d, t2.f FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO num, a, b;
     IF NOT done THEN
       UPDATE test.t2 SET total =(a*b) WHERE ID = num;
     END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
END|

delimiter ;|

INSERT INTO test.t1 VALUES(NULL,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer',654321.4321,15.21,0,1965,NOW());
INSERT INTO test.t2 VALUES(NULL,0,'Testing MySQL databases is a cool ', 'MySQL Customers ROCK!',654321.4321,1.24521,0,YEAR(NOW()),NOW());

select * from test.t1;
select * from test.t2;
connection slave;
use test;
show tables;
sleep 9;
select * from test.t1;
select * from test.t2;
connection master;

UPDATE test.t1 SET b1 = 0 WHERE b1 = 1;
INSERT INTO test.t2 VALUES(NULL,1,'This is an after update test.', 'If this works, total will not be zero on the master or slave',1.4321,5.221,0,YEAR(NOW()),NOW());
UPDATE test.t2 SET b1 = 0 WHERE b1 = 1;

select * from test.t1;
select * from test.t2;
connection slave;
use test;
show tables;
sleep 9;
select * from test.t1;
select * from test.t2;
connection master;

show binlog events;

Another test case:
(gdb) bt
#0  0x40250da1 in kill () from /lib/libc.so.6
#1  0x40181f4a in pthread_kill () from /lib/libpthread.so.0
#2  0x0828fd2d in write_core (sig=11) at stacktrace.c:220
#3  0x0818b4ed in handle_segfault (sig=11) at mysqld.cc:2064
#4  0x401855cd in __pthread_sighandler () from /lib/libpthread.so.0
#5  <signal handler called>
#6  lock_tables (thd=0x4131ab50, tables=0x4130da40, count=0) at table.h:486
#7  0x4131ccc0 in ?? ()
#8  0x0820b25c in Rows_log_event::exec_event(st_relay_log_info*) (
    this=0x4131ab50, rli=0x873d67c) at log_event.cc:5064
#9  0x08285c3f in exec_relay_log_event (thd=0x41300590, rli=0x873d67c)
    at slave.cc:2960
#10 0x082839a8 in handle_slave_sql (arg=0x8737498) at slave.cc:3508
#11 0x4017f6de in pthread_start_thread () from /lib/libpthread.so.0
*****************************************************
./t/rpl_row_NOW.test
#############################################################################
# Original Author: Jonathan Miller                                          #
# Original Date: Aug/18/2005                                                #
#############################################################################
#TEST: Taken and modfied from http://bugs.mysql.com/bug.php?id=12480        #
#############################################################################

# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc

# Begin clean up test section
connection master;
--disable_warnings
DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;

# Begin test section 1
CREATE TABLE test.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT,
                      a TIMESTAMP DEFAULT '2005-05-05 01:01:01',
                      b TIMESTAMP DEFAULT '2005-05-05 01:01:01',
                      PRIMARY KEY(n));
delimiter |;
CREATE FUNCTION test.f1() RETURNS TIMESTAMP
BEGIN
  DECLARE v1 INT DEFAULT 300;
  WHILE v1 > 0 DO
     SET v1 = v1 - 1;
  END WHILE;
  RETURN NOW();
END|
delimiter ;|

INSERT INTO test.t1 VALUES(NULL,NOW(),test.f1());

delimiter |;
CREATE TRIGGER test.trig1 BEFORE INSERT ON test.t1
FOR EACH ROW BEGIN
  SET new.b = test.f1();
END|
delimiter ;|

INSERT INTO test.t1 SET n = NULL, a = now();

SELECT  * FROM test.t1;
connection slave;
sleep 3;
SELECT * FROM test.t1;
connection master;

#show binlog events;
# lets cleanup

DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;

Test do not produce cores at this point.