Bug #11587 trigger causes lost connection error
Submitted: 27 Jun 2005 10:21 Modified: 17 Aug 2005 17:26
Reporter: Yoshiaki Tajika (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.7-beta-nightly-20050521 OS:Linux (linux)
Assigned to: Michael Widenius CPU Architecture:Any

[27 Jun 2005 10:21] Yoshiaki Tajika
Description:
A trigger causes a lost connection error, when it includes a SELECT statement.

How to repeat:
1. In mysql command prompt, do like below. 

  -- 11. Create table. 
  use test;
  create table t1(c1 int, c2 datetime)
  engine=myisam default character set=latin1;

  -- 12. Create trigger.
  delimiter ;;
  create trigger tr1 before insert on t1 for each row 
  begin 
  set new.c2 = now();
  select 'hello';
  end;;
  delimiter ;

  -- 13. Check if the trigger works well.
  delete from t1; insert into t1(c1) values(1); select * from t1;

2. Prepare a batch file, named 'temp1.sql'

  delete from t1; insert into t1(c1) values(1); select * from t1;
  delete from t1; insert into t1(c1) values(1); select * from t1;
  delete from t1; insert into t1(c1) values(1); select * from t1;
   : (repeat 100 times)

3. Execte the batch in mysql command prompt.

  -- 31. Execute the batch.
  source /tmp/temp1.sql;

4. Then you'll sometimes see error messages like below.

  > +-------+
  > | hello |
  > +-------+
  > | hello |
  > +-------+
  > 1 row in set (0.01 sec)
  > 
  > ERROR 2013: Lost connection to MySQL server during query
  > ERROR 2006: MySQL server has gone away
  > No connection. Trying to reconnect...
  > Connection id:    151
  > Current database: test
  > 
  > Query OK, 0 rows affected (0.00 sec)
  > 
  > +-------+
  > | hello |
  > +-------+
  > | hello |
  > +-------+
  > 1 row in set (0.00 sec)
[27 Jun 2005 12:10] Miguel Solorzano
Verified against 5.0.9 Bk source.

Packets out of order (Found: 6, expected 1)
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> delete from t1; insert into t1(c1) values(1); select * from t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
[15 Aug 2005 17:33] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 5.0.12
[17 Aug 2005 17:26] Paul Dubois
Noted in 5.0.12 changelog.