MySQL Bugs: #18914: Calling certain SPs from triggers fail

Bug #18914	Calling certain SPs from triggers fail
Submitted:	9 Apr 2006 4:33	Modified:	9 Mar 2007 3:14
Reporter:	Morgan Tocker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.21-BK, 5.0+	OS:	Any (ALL)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	rt_q1_2007

Description:
Test case:

CREATE DATABASE test2;
USE test2;

delimiter //
CREATE PROCEDURE test_proc ()
BEGIN
	CREATE table test2.foo (x int);
END;
//
delimiter ;

CREATE TABLE test_table (a int);
CREATE TRIGGER insert_on_test_table BEFORE INSERT ON test_table FOR EACH ROW call test_proc();
INSERT INTO test_table (a) VALUES (1);

On inserting into test_table (as above), this error is raised:
ERROR 1146 (42S02): Table 'test2.foo' doesn't exist

Is this related to http://bugs.mysql.com/bug.php?id=17843 ?

Verified in 5.0.18, 5.0.19 and 5.1.9 snapshot.

How to repeat:
As above.

Thank you for a bug report. Verified just as described on 5.0.21-BK (ChangeSet@1.2143, 2006-04-10 08:29:24+02:00) on Linux.

Hi, Morgan!

Please note that you can't create non-temporary table from stored function or trigger since such operation does implicit commit and thus prohibited there.
So the only bug here is time at which error is emitted and improper error message.

Problem comes from the fact that table to be created is listed as used by when parsing the SP to be called. Therefore, it is required to lock it before executing the trigger. But actually this table does not exist and we bail out when trying to open it, before actually locking all tables used by the trigger.

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

ChangeSet@1.2241, 2006-08-06 18:37:50+04:00, kroki@moonlight.intranet +3 -0
  BUG#18914: Calling certain SPs from triggers fail.
  
  The problem was that when a procedure that creates a table was used in a
  function or a trigger the table that wasn't yet created was added to the
  list of tables for prelocking, thus giving the error "Table doesn't
  exist".
  
  The solution is to not try to prelock such non-existent tables.

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

ChangeSet@1.2241, 2006-08-28 20:34:17+04:00, kroki@moonlight.intranet +3 -0
  BUG#18914: Calling certain SPs from triggers fail.
  
  CREATE TABLE from a function or a trigger should not work because of
  implicit commit, however wrong error was reported:
  "Table doesn't exist".
  
  The problem was that when a procedure that creates a table was used in a
  function or a trigger the table that wasn't yet created was added to the
  list of tables for prelocking, thus giving the error "Table doesn't
  exist".
  
  The solution is to not try to prelock such non-existent tables.  This
  does not break prelocking because later we'll report the error anyway.
  
  This patch also fixes DROP TABLE IF EXISTS in the case there are no
  such table: we'll correctly give "implicit commit" error instead of
  "table doesn't exists".
  
  TO THE REVIEWER: I'm worried a little bit about the fact that we do not
  lock tables to be deleted any more, as later we call functions named
  abort_locked_tables() and drop_locked_tables().  Also, I did not add
  the asserts, I'm not sure if asserts for other forbidden statements
  are there.

After more investigation, this bug is believed to be caused by Bug#8407

Putting this fix on hold.
After 8407 is fixed, this bug will need to be re-evaluated,
and very likely will be closed as a duplicate.

Changing to Verified, as this fix is waiting for Bug#8407

This bug has been fixed with Bug#8407.

Pushed into 5.0.38 and 5.1.17.

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.38 and 5.1.17 changelogs.

When both an event and a  trigger are active on a table.
My question is

I create one table with three columns 

Table Name           :Table1

Field Name  A1   int, b1 varchar (10), c1 int

A1                    b1                     c1
1                     ma                      0
2                     sam                     1
3                     sdf                     0
4                     sdf                     3

A1 value can be stored in c1 column (i.e exactly foreign key releation ship).
If i am pasing query delete from table1 where a1=1
 If now deleting the record that time i can check to c1 any value = 1 is there first delete the row and finally the  delete the condition wise.
is it possible in trigger in mysql 5.0.15-nt?

A1                    b1                     c1
1                     ma                      0    --condition wise
2                     sam                     1    --First delete
3                     sdf                     0
4                     sdf                     3

finally i will get output in
A1                    b1                     c1
3                     sdf                     0
4                     sdf                     3

I create trigger also
Create trigger tr1 before delete on table1
for each row begin
delete from table1 where c1=old.a1
end;

Trigger is executing perfectly, but i run the query

delete from table1 where a1=1;

some error is coming

Can't update table 'table1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

please give me the solution it's very urgent and send me the answer
in sai_subramani@yahoo.co.in