| 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: | |
| 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: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.
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