MySQL Bugs: #11587: trigger causes lost connection error

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

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)

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

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

Noted in 5.0.12 changelog.