| Bug #34252 | MySql server crashes on show create trigger statement after dropping the table | ||
|---|---|---|---|
| Submitted: | 2 Feb 2008 19:36 | Modified: | 12 Feb 2008 17:33 |
| Reporter: | Rakesh Kumar | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.1.22-rc-community | OS: | Any (I tested on windows XP and Ubuntu gutsy 7.10) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | drop table, triggers | ||
[2 Feb 2008 19:36]
Rakesh Kumar
[2 Feb 2008 21:46]
MySQL Verification Team
Thank you for the bug report. Could you please provide the complete script test case just with the description isn't possible to repeat the behavior reported. Thanks in advance.
[3 Feb 2008 14:43]
MySQL Verification Team
This crashed my 5.1.22 but *not* 5.1.24BK build: delimiter ; drop table if exists `t1`; create table `t1`(`a` int)engine=myisam; drop trigger `trig`; delimiter // create trigger `trig` before insert on `t1` for each row begin end// delimiter ; show create trigger `trig`; drop table if exists `t1`; show create trigger `trig`;
[4 Feb 2008 8:00]
Rakesh Kumar
Here is a sequence of commands to reproduce the error. I have used City table as example (created using database dump world.sql available from http://dev.mysql.com/doc/ "example database" section): mysql> use world; Database changed mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | City | | Country | | CountryLanguage | +-----------------+ 3 rows in set (0.00 sec) mysql> show create table City; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | City | CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> create table city_deleted like City; Query OK, 0 rows affected (0.00 sec) mysql> create trigger city_backup after delete on City for each row insert into city_deleted values(old.id, old.name,old.countrycode,old.district,old.population); Query OK, 0 rows affected (0.00 sec) mysql> show create trigger city_backup\G *************************** 1. row *************************** Trigger: city_backup sql_mode: SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger city_backup after delete on City for each row insert into city_deleted values(old.id, old.name,old.countrycode,old.district,old.population) character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> select * from city_deleted; Empty set (0.00 sec) mysql> delete from City where name='delhi'; Query OK, 1 row affected (0.01 sec) mysql> select * from city_deleted; +------+-------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +------+-------+-------------+----------+------------+ | 1025 | Delhi | IND | Delhi | 7206704 | +------+-------+-------------+----------+------------+ 1 row in set (0.00 sec) mysql> drop table City; Query OK, 0 rows affected (0.00 sec) mysql> show create trigger city_backup; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> show tables; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: world +-----------------+ | Tables_in_world | +-----------------+ | Country | | CountryLanguage | | city_deleted | +-----------------+ 3 rows in set (27.18 sec)
[4 Feb 2008 10:18]
Valeriy Kravchuk
Verified using Shane's simple test case.
[4 Feb 2008 11:27]
Valeriy Kravchuk
And yes, in latest 5.1.24-BK I do not have a crash, just proper error message. Looks like trigger is gropped with table now.
[12 Feb 2008 17:33]
Valeriy Kravchuk
Actually, not repetable with 5.1.24-BK. Just needs a test case to prevent future regressions.
