MySQL Bugs: #27248: Triggers: error if insert affects temporary table

Bug #27248	Triggers: error if insert affects temporary table
Submitted:	18 Mar 2007 16:38	Modified:	18 Jul 2007 10:42
Reporter:	Peter Gulutzan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.37, 5.2.4-falcon-alpha-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I create two tables.
Table t1 is persistent.
Table t2 is temporary.
I create an INSERT trigger on t1 which causes INSERT into t2.
I create a DELETE trigger on t1 which causes DELETE from t2.

Now, if I insert into t1 in the same session, I see:
ERROR 1137 (HY000): Can't reopen table: 't2'

How to repeat:
create table t1 (s1 int);
create temporary table t2 (s1 int);
create trigger t1_bi before insert on t1 for each row insert into t2 values (0);
create trigger t1_bd before delete on t1 for each row delete from t2;
insert into t1 values (0);

Thank you for a bug report. Verified just as described.

This is fixed by the fix for Bug#26141.
Adding the test case and closing the bug.

This bug is a duplicate of Bug#26141 mixing table types in trigger causes full table lock on innodb table

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/31074

ChangeSet@1.2530, 2007-07-18 14:42:06+04:00, kostja@bodhi.(none) +2 -0
  Add a test case for Bug#27248 Triggers: error if insert affects temporary 
  table.
  The bug itself is yet another manifestation of Bug 26141.

Pushed into 5.0.48

Pushed into 5.1.21-beta