Bug #36443 Server crashes when executing insert when insert trigger on table
Submitted: 30 Apr 2008 22:14 Modified: 28 Jul 2008 18:08
Reporter: Gordon Shannon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.1.24-rc-community-log, 5.1.26 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any
Tags: crash trigger, pushbuild, sporadic, test failure

[30 Apr 2008 22:14] Gordon Shannon
Description:
After upgrading a 5.0.51a database to 5.1.24 with mysql_upgrade, following happened.

1. During mysql_upgrade, got the warning
   Warning  : Triggers for table `ci_sentiment_db`.`dw_topic_dim` have no creation context
status   : OK

2. Dropped and recreated the trigger just in case.

3. When a row is inserted into the table, the server crashes and autorecovers.

4. If I drop the insert trigger, the insert works without error.

Contents of error log:
/usr/sbin/mysqld(print_stacktrace+0x1e)[0x6cd03e]
/usr/sbin/mysqld(handle_segfault+0x320)[0x5b8610]
/lib64/libpthread.so.0[0x314e80de70]
/usr/sbin/mysqld(_ZN3THD27restore_sub_statement_stateEP19Sub_statement_state+0x61)[0x5a69a1]
/usr/sbin/mysqld(_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0xe4)[0x6ea8d4]
/usr/sbin/mysqld(_Z36fill_record_n_invoke_before_triggersP3THDR4ListI4ItemES4_bP19Table_triggers_list14trg_event_type+0xd9)[0x5f7279]
/usr/sbin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x800)[0x62df90]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x29a4)[0x5c4d44]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1b4)[0x5c78a4]
/usr/sbin/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Relay_log_infoPKcj+0x38b)[0x662f9b]
/usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_infob+0x13d)[0x6bd91d]
/usr/sbin/mysqld(handle_slave_sql+0x6e7)[0x6c1f97]
/lib64/libpthread.so.0[0x314e8062f7]
/lib64/libc.so.6(clone+0x6d)[0x314dcce85d]
080430 16:50:44 - 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=8388600
read_buffer_size=2097152
max_used_connections=2
max_threads=200
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2058199 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x35e11a20
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...
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x35e48b14 = INSERT INTO `dw_topic_dim` (`name`, `posting_quartile`, `cusip`, `exchange_id`, `industry`, `symbol`, `start_date_id`, `type`, `active_flag`, `exchange`, `market_cap`, `etf_flag`, `auto_cat`, `sector`) VALUES('Chocolate Slavery', NULL, '', NULL, NULL, 'CHOCOLATESLAVER', 20080430, 'Theme', 'T', NULL, NULL, NULL, 1, '')
thd->thread_id=383
thd->killed=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.
080430 16:50:44 mysqld_safe Number of processes running now: 0
080430 16:50:44 mysqld_safe mysqld restarted
InnoDB: Log scan progressed past the checkpoint lsn 1159 1609320313
080430 16:50:51  InnoDB: Database was not shut down normally!
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 1159 1614562816
[...]
InnoDB: Doing recovery: scanned up to log sequence number 1159 2352772735
080430 16:53:39  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: In a MySQL replication slave the last master binlog file
InnoDB: position 0 304, file name mysql-bin.000518 
InnoDB: Last MySQL binlog file position 0 354642998, file name ./mysql-bin.000005
080430 17:06:08  InnoDB: Started; log sequence number 1159 2352772735
080430 17:06:08 [Note] Recovering after a crash using mysql-bin
080430 17:06:10 [Note] Starting crash recovery...
080430 17:06:10 [Note] Crash recovery finished.
080430 17:06:10 [Note] Event Scheduler: Loaded 0 events
080430 17:06:10 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.24-rc-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
080430 17:43:54 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000518' at position 864246105, relay log './relay-bin.000012' position: 360959689

How to repeat:

1. Upgrade 5.0 database to 5.1
2. Execute insert into table with insert trigger
[30 Apr 2008 22:17] Gordon Shannon
Trigger creation file

Attachment: trig.sql (application/octet-stream, text), 832 bytes.

[1 May 2008 1:44] MySQL Verification Team
Thank you for the bug report. Could you please provide the create table
statement too?. Thanks in advance.
[1 May 2008 14:40] Gordon Shannon
SQL_MODE: STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO

CREATE TABLE `dw_topic_dim` (
  `dw_topic_dim_id` int(10) unsigned NOT NULL auto_increment,
  `symbol` varchar(15) NOT NULL default '',
  `name` varchar(100) default NULL,
  `sector` varchar(100) NOT NULL default '',
  `industry` varchar(100) default NULL,
  `cusip` varchar(10) default NULL,
  `etf_flag` char(1) default NULL,
  `active_flag` char(1) default NULL,
  `posting_quartile` int(3) default NULL,
  `start_date_id` int(8) default NULL,
  `market_cap` enum('val1', 'val2', 'val3') default NULL,
  `auto_cat` tinyint(4) unsigned default '1',
  `type` varchar(255) default NULL,
  `exchange` varchar(10) default NULL,
  `exchange_id` int(11) default NULL,
  PRIMARY KEY  (`dw_topic_dim_id`),
  UNIQUE KEY `symbol` (`symbol`),
  UNIQUE KEY `active_topic_idx` (`dw_topic_dim_id`,`active_flag`),
  UNIQUE KEY `active_flag_topic_idx` (`active_flag`,`dw_topic_dim_id`),
  KEY `sector` (`sector`(3)),
  KEY `type_symbox_idx` (`type`(10),`symbol`),
  KEY `fk_topic_exchange` (`exchange_id`),
  CONSTRAINT `fk_topic_exchange` FOREIGN KEY (`exchange_id`) REFERENCES `exchanges` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `exchanges` (
  `id` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
[1 May 2008 14:43] Gordon Shannon
Forgot --- I changed the enum values in the create table statement for this posting. "val1", etc are just placeholders for the real values.
[23 May 2008 2:25] Mark Hobson
I receive the same error with a trigger on a table, but it seems to only happen through the replication client.  I can run the identical insert statement from the console and the trigger handles it perfectly.

/usr/sbin/mysqld(print_stacktrace+0x1e)[0x6cd03e]
/usr/sbin/mysqld(handle_segfault+0x320)[0x5b8610]
/lib64/libpthread.so.0[0x327480de70]
/usr/sbin/mysqld(_ZN3THD27restore_sub_statement_stateEP19Sub_statement_state+0x61)[0x5a69a1]
/usr/sbin/mysqld(_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0xe4)[0x6ea8d4]
/usr/sbin/mysqld(_Z12write_recordP3THDP8st_tableP12st_copy_info+0x18a)[0x62a6da]
/usr/sbin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x9cc)[0x62e15c]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x29a4)[0x5c4d44]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1b4)[0x5c78a4]
/usr/sbin/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Relay_log_infoPKcj+0x38b)[0x662f9b]
/usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_infob+0x13d)[0x6bd91d]
/usr/sbin/mysqld(handle_slave_sql+0x6e7)[0x6c1f97]
/lib64/libpthread.so.0[0x32748062f7]
/lib64/libc.so.6(clone+0x6d)[0x32738ce85d]
080522 21:16:26 - 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=67108864
read_buffer_size=33554432
max_used_connections=2
max_threads=200
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 13174743 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x13e16270
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...
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x143662a8 = INSERT INTO `lead`
			SET
				`created_time` = CURRENT_TIMESTAMP,
				`modified` = CURRENT_TIMESTAMP,

thd->thread_id=4
thd->killed=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.

================
To repeat

1) setup tables and triggers.  I am using 2 databases, but I've had the same problem on a single db.

CREATE TABLE `db1`.`lead` (
  `lead_id` int(11) NOT NULL AUTO_INCREMENT DEFAULT '0',
  `created_time` DATETIME NOT NULL,
  `modified` TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `db2`.`trigger_test` (
  `lead_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DELIMITER //
CREATE TRIGGER db1.trigger_test_insert
AFTER INSERT ON db1.lead
FOR EACH ROW
BEGIN
INSERT INTO db2.trigger_test SET lead_id=NEW.lead_id;
END;//
DELIMITER ;

2) Turn on replication 
mysql> start slave;

3) Add a record to db1.lead from the master and wait for it to replicate to the slave.
[10 Jun 2008 23:13] Mark Hobson
I have tested an identical trigger on the following scenarios:

master    slave (with trigger)
==============================
5.0.45 -> 5.0.45 - pass
5.1.24 -> 5.1.24 - pass
5.0.45 -> 5.1.24 - fail with crash
5.0.45 -> 5.1.24 on new hardware with a new snapshot - fail with crash

So it seems that there is a problem with the slave thread with a trigger running on mysql 5.1 that connects to a master running on mysql 5.0. 

The crash summary is the same every time:

/usr/sbin/mysqld(print_stacktrace+0x1e)[0x6cd03e]
/usr/sbin/mysqld(handle_segfault+0x320)[0x5b8610]
/lib64/libpthread.so.0[0x342480de70]
/usr/sbin/mysqld(_ZN3THD27restore_sub_statement_stateEP19Sub_statement_state+0x61)[0x5a69a1]
/usr/sbin/mysqld(_ZN19Table_triggers_list16process_triggersEP3THD14trg_event_type20trg_action_time_typeb+0xe4)[0x6ea8d4]
/usr/sbin/mysqld(_Z12write_recordP3THDP8st_tableP12st_copy_info+0x18a)[0x62a6da]
/usr/sbin/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x9cc)[0x62e15c]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x29a4)[0x5c4d44]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1b4)[0x5c78a4]
/usr/sbin/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Relay_log_infoPKcj+0x38b)[0x662f9b]
/usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3THDP14Relay_log_infob+0x13d)[0x6bd91d]
/usr/sbin/mysqld(handle_slave_sql+0x6e7)[0x6c1f97]
/lib64/libpthread.so.0[0x34248062f7]
/lib64/libc.so.6(clone+0x6d)[0x34234ce85d]
080610 12:04:28 - 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=67108864
read_buffer_size=33554432
max_used_connections=1
max_threads=200
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 13174743 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3d732490
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...
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2ab2a8020318  is invalid pointer
thd->thread_id=3
thd->killed=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.
080610 12:04:29 mysqld_safe mysqld from pid file /var/lib/mysql/adnreportingndbd1-fn2.pid ended

mysql crashes and mysqld_safe will restart a new instance of mysql.
[11 Jun 2008 5:18] MySQL Verification Team
looks like bug #37093
[11 Jun 2008 9:26] MySQL Verification Team
i repeated this crash between 5.0.45 and 5.1.24. stack trace attached.

Attachment: bug36443_stack_trace.txt (text/plain), 2.30 KiB.

[11 Jun 2008 11:15] MySQL Verification Team
here's the testcase
-------------------
i used 5.0.51a master and 5.1.26 master.
make sure the datadir on both master and slave is completely new, and no replication files or data files are there.  then start them:

master:
mysqld --console --skip-grant-tables --skip-name-resolve --port=3307  --server-id=2

slave:
mysqld-nt --console --log-bin --server-id=1 --skip-grant-tables --skip-name-resolve

##### on master:
create table `t1` (`id` int(10) unsigned not null auto_increment primary key) engine=innodb;
create trigger `trg` before insert on `t1` for each row begin end;

##### on slave:
change master to master_host='127.0.0.1', master_port=3306, master_user='root', master_password='';
start slave;
show slave status\G

#### on master :
insert into `t1` values ();

#slave crashes.
[11 Jun 2008 11:25] MySQL Verification Team
small correction to above startup:

slave:
mysqld --console --skip-grant-tables --skip-name-resolve --port=3307  --server-id=2

master:
mysqld-nt --console --log-bin --server-id=1 --skip-grant-tables --skip-name-resolve
[11 Jun 2008 12:05] MySQL Verification Team
tested on linux also. crash and glibc errors

Attachment: bug36443_stack_trace_5.1.26_linux.txt (text/plain), 4.37 KiB.

[11 Jun 2008 12:08] MySQL Verification Team
bug #37093 was marked as a duplicate of this
[17 Jun 2008 13:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47998

2656 Andrei Elkin	2008-06-17
      correcting the scope of declarion for a being appened to Discrete_interval item.
[17 Jun 2008 13:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47999

2656 Andrei Elkin	2008-06-17
      Bug #36443 Server crashes when executing insert when insert trigger on table
      
      The crash appeared to be a result of allocating an instance of Discrete_interval 
      automatically whereas it meant to be dynamical.
      
      Fixed with correcting allocation; added simulation code that forces those fixes of bug@33029
      that targeted at master-and-slave having incompatible bug33029-prone versions.
[17 Jun 2008 15:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48016

2656 Andrei Elkin	2008-06-17
      Bug #36443 Server crashes when executing insert when insert trigger on table
            
      The crash appeared to be a result of allocating an instance of Discrete_interval 
      automatically whereas it meant to be dynamical.
            
      Fixed with correcting the allocation introduced by bug@33029;
      added simulation code that forces executing those fixes of bug@33029 that targeted
      at master-and-slave having incompatible bug33029-prone versions.
[19 Jun 2008 9:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48150

2656 Andrei Elkin	2008-06-19
      Bug#36443 Server crashes when executing insert when insert trigger on table
                  
      The crash appeared to be a result of allocating an instance of Discrete_interval 
      automatically that that was referred in out-of-declaration scope.
                  
      Fixed with correcting to use shallow copying backing up and restoring scheme of
      auto_inc_intervals_forced introduced by bug#33029;
      added simulation code that forces executing those fixes of the former bug that targeted
      at master-and-slave having incompatible bug#33029-prone versions.
[19 Jun 2008 14:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48176

2656 Andrei Elkin	2008-06-19
      Bug#36443 Server crashes when executing insert when insert trigger on table
                        
      The crash appeared to be a result of allocating an instance of Discrete_interval 
      automatically that that was referred in out-of-declaration scope.
                        
      Fixed with correcting backing up and restoring scheme of
      auto_inc_intervals_forced, introduced by bug#33029, by means of shallow copying;
      added simulation code that forces executing those fixes of the former bug that
      targeted at master-and-slave having incompatible bug#33029-prone versions.
[19 Jun 2008 18:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48195

2656 Andrei Elkin	2008-06-19
      Bug#36443 Server crashes when executing insert when insert trigger on table
                              
            The crash appeared to be a result of allocating an instance of Discrete_interval 
            automatically that that was referred in out-of-declaration scope.
                              
            Fixed with correcting backing up and restoring scheme of
            auto_inc_intervals_forced, introduced by bug#33029, by means of shallow copying;
            added simulation code that forces executing those fixes of the former bug that
            targeted at master-and-slave having incompatible bug#33029-prone versions.
[20 Jun 2008 11:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48228

2657 Andrei Elkin	2008-06-20 [merge]
      merging prior to push Bug #36443
[20 Jun 2008 14:08] Georgi Kodinov
Pushed to 5.1.26
[24 Jun 2008 14:53] Jon Stephens
Documented in the 5.1.26 changelog as follows:

        Performing an insert on a table having an AUTO_INCREMENT column and an 
        INSERT trigger that was being replicated from a master running MySQL 5.0  
        or any version of MySQL 5.1 up to and including MySQL 5.1.11 to a slave 
        running MySQL 5.1.12 or later caused the replication slave to crash.
[28 Jul 2008 14:14] Georgi Kodinov
Pushed into 6.0.7-alpha
[28 Jul 2008 16:50] Bugs System
Pushed into 5.1.27  (revid:joerg@mysql.com-20080624140759-fhv6gr7yxtye9aiy) (version source revid:joerg@mysql.com-20080624140759-fhv6gr7yxtye9aiy) (pib:3)
[28 Jul 2008 18:08] Paul DuBois
Noted in 6.0.7 changelog.