| Bug #26445 | MySQL crash: DROP/CREATE trigger and a stored procedure | ||
|---|---|---|---|
| Submitted: | 16 Feb 2007 15:23 | Modified: | 4 Jun 2007 21:31 |
| Reporter: | William Chiquito | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S1 (Critical) |
| Version: | 5.0.33-community-max,5.0.36-BK/5.1 | OS: | Any (windows, linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | drop trigger, insert delayed | ||
[16 Feb 2007 15:24]
William Chiquito
Windows XP SP2
[16 Feb 2007 16:34]
Valeriy Kravchuk
Thank you for a problem report. Sorry, but iwas not able to repeat the behaviour described with latest 5.0.36-BK on Linux:
openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.36 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t1 (c1 varchar(5)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2 (c1 varchar(5) primary key, c2 int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter //
mysql> create trigger trgtest after INSERT on t1 for each row
-> begin
-> insert into t2 (c1, c2) values (NEW.c1, 1) on duplicate key update c2 =
c2 + 1;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql> insert into t1 (c1) values ('world');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 (c1) values ('world');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-------+
| c1 |
+-------+
| world |
| world |
+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+-------+------+
| c1 | c2 |
+-------+------+
| world | 2 |
+-------+------+
1 row in set (0.00 sec)
mysql> insert delayed into t1 (c1) values ('world');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+-------+
| c1 |
+-------+
| world |
| world |
+-------+
2 rows in set (0.00 sec)
mysql> select * from t2;
+-------+------+
| c1 | c2 |
+-------+------+
| world | 3 |
+-------+------+
1 row in set (0.00 sec)
mysql> drop trigger trgtest;
Query OK, 0 rows affected (0.00 sec)
mysql> insert delayed into t1 (c1) values ('world');
Query OK, 1 row affected (0.00 sec)
So, looks like a bug, if any, is already fixed.
[16 Feb 2007 17:54]
William Chiquito
The problem is appearing me in environments Windows (Windows XP SP2). I took your script and I modified some things to make the problem more comprehensible.
DELIMITER $$
DROP PROCEDURE IF EXISTS `getbug`$$
CREATE PROCEDURE `getbug`()
BEGIN
CALL bug;
CALL bug;
END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bug`$$
CREATE PROCEDURE `bug`()
BEGIN
insert into t1 (c1) values ('world');
insert into t1 (c1) values ('world');
select * from t1;
select * from t2;
insert delayed into t1 (c1) values ('world');
select * from t1;
select * from t2;
drop trigger `trgtest`;
insert delayed into t1 (c1) values ('world');
END$$
DELIMITER ;
drop table t1;
drop table t2;
create table t1 (c1 varchar(5)) ENGINE=MyISAM;
create table t2 (c1 varchar(5) primary key, c2 int) ENGINE=MyISAM;
DELIMITER $$
DROP TRIGGER `trgtest`$$
CREATE TRIGGER `trgtest` AFTER INSERT on `t1`
FOR EACH ROW
BEGIN
insert into t2 (c1, c2) values (NEW.c1, 1) on duplicate key update c2 = c2 + 1;
END$$
DELIMITER ;
CALL getbug;
[17 Feb 2007 16:29]
MySQL Verification Team
verified as described. A crash occurs, see attached stack trace.
[17 Feb 2007 16:30]
MySQL Verification Team
stack for windows
Attachment: bug26445_windows_5.0.36BK_stack.txt (text/plain), 1.31 KiB.
[17 Feb 2007 16:34]
MySQL Verification Team
crashed linux version too.
Attachment: bug26445_linux_5.0.36BK_stack.txt (text/plain), 3.13 KiB.
[18 Feb 2007 18:03]
MySQL Verification Team
This bug affects 5.1 too.
[4 Jun 2007 21:31]
Konstantin Osipov
I can not repeat the original problem any more, when Bug#21483 "Server abort or deadlock on INSERT DELAYED with another implicit insert" was fixed. The second test case still produces valgrind warnings with the following valgrind trace: ==7135== Invalid read of size 4 ==7135== at 0x824F0B1: st_table_list::reinit_before_use(THD*) (table.cc:3043) ==7135== by 0x8283AC7: reinit_stmt_before_use(THD*, st_lex*) (sql_prepare.cc:2161) ==7135== by 0x8370D86: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned*, bool, sp_instr*) (sp_head.cc:2390) ==7135== by 0x8376B7E: sp_instr_stmt::execute(THD*, unsigned*) (sp_head.cc:2496) ==7135== by 0x8375138: sp_head::execute(THD*) (sp_head.cc:1048) ==7135== by 0x8375AB2: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1697) ==7135== by 0x821FCC3: mysql_execute_command(THD*) (sql_parse.cc:4620) ==7135== by 0x8370BA4: sp_instr_stmt::exec_core(THD*, unsigned*) (sp_head.cc:2543) ==7135== by 0x8370DC4: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned*, bool, sp_instr*) (sp_head.cc:2396) ==7135== by 0x8376B7E: sp_instr_stmt::execute(THD*, unsigned*) (sp_head.cc:2496) ==7135== by 0x8375138: sp_head::execute(THD*) (sp_head.cc:1048) ==7135== by 0x8375AB2: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1697) ==7135== by 0x821FCC3: mysql_execute_command(THD*) (sql_parse.cc:4620) ==7135== by 0x822251F: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:6052) ==7135== by 0x8223167: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1806) ==7135== by 0x82243C8: do_command(THD*) (sql_parse.cc:1583) ==7135== by 0x82251B1: handle_one_connection (sql_parse.cc:1194) ==7135== by 0x404331A: start_thread (in /lib/tls/i686/cmov/libpthread-2.5.so) ==7135== by 0x419057D: clone (in /lib/tls/i686/cmov/libc-2.5.so) ==7135== Address 0x53B42E4 is 52 bytes inside a block of size 432 free'd ==7135== at 0x402123A: free (vg_replace_malloc.c:233) ==7135== by 0x84E1ACA: my_no_flags_free (my_malloc.c:59) ==7135== by 0x84E2445: free_root (my_alloc.c:351) ==7135== by 0x837518C: sp_head::execute(THD*) (sp_head.cc:1071) ==7135== by 0x8375AB2: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1697) ==7135== by 0x821FCC3: mysql_execute_command(THD*) (sql_parse.cc:4620) ==7135== by 0x8370BA4: sp_instr_stmt::exec_core(THD*, unsigned*) (sp_head.cc:2543) ==7135== by 0x8370DC4: sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned*, bool, sp_instr*) (sp_head.cc:2396) ==7135== by 0x8376B7E: sp_instr_stmt::execute(THD*, unsigned*) (sp_head.cc:2496) ==7135== by 0x8375138: sp_head::execute(THD*) (sp_head.cc:1048) ==7135== by 0x8375AB2: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1697) ==7135== by 0x821FCC3: mysql_execute_command(THD*) (sql_parse.cc:4620) ==7135== by 0x822251F: mysql_parse(THD*, char const*, unsigned, char const**) (sql_parse.cc:6052) ==7135== by 0x8223167: dispatch_command(enum_server_command, THD*, char*, unsigned) (sql_parse.cc:1806) ==7135== by 0x82243C8: do_command(THD*) (sql_parse.cc:1583) ==7135== by 0x82251B1: handle_one_connection (sql_parse.cc:1194) ==7135== by 0x404331A: start_thread (in /lib/tls/i686/cmov/libpthread-2.5.so) ==7135== by 0x419057D: clone (in /lib/tls/i686/cmov/libc-2.5.so) This is a duplicate of Bug#12093 SP not found on second PS execution if another thread drops other SP in between. Since it's an older bug, and the original report has nothing to do with the second problem, please refer to Bug#12093 to track progress of this bug.
Description: MySQL crash with following script (execute twice). How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.33-community-max Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DELIMITER // mysql> DROP TABLE t1 // Query OK, 0 rows affected (0.02 sec) mysql> DROP TABLE t2 // Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (c1 varchar(5)) ENGINE=MyISAM // Query OK, 0 rows affected (0.03 sec) mysql> create table t2 (c1 varchar(5) primary key, c2 int) ENGINE=MyISAM // Query OK, 0 rows affected (0.03 sec) mysql> create trigger trgtest after INSERT on t1 for each row -> begin -> insert into t2 (c1, c2) values (NEW.c1, 1) on duplicate key update c2 = c2 + 1; -> end // Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 (c1) values ('world') // Query OK, 1 row affected (0.00 sec) mysql> insert into t1 (c1) values ('world') // Query OK, 1 row affected (0.00 sec) mysql> select * from t1 // +-------+ | c1 | +-------+ | world | | world | +-------+ 2 rows in set (0.00 sec) mysql> select * from t2 // +-------+------+ | c1 | c2 | +-------+------+ | world | 2 | +-------+------+ 1 row in set (0.00 sec) mysql> insert delayed into t1 (c1) values ('world') // Query OK, 1 row affected (0.00 sec) mysql> select * from t1 // +-------+ | c1 | +-------+ | world | | world | +-------+ 2 rows in set (0.00 sec) mysql> select * from t2 // +-------+------+ | c1 | c2 | +-------+------+ | world | 3 | +-------+------+ 1 row in set (0.00 sec) mysql> drop trigger trgtest // Query OK, 0 rows affected (0.00 sec) mysql> insert delayed into t1 (c1) values ('world') // ERROR 2013 (HY000): Lost connection to MySQL server during query