MySQL Bugs: #21825: Incorrect message error deleting records in a table with a trigger for inserting

Bug #21825	Incorrect message error deleting records in a table with a trigger for inserting
Submitted:	25 Aug 2006 8:13	Modified:	9 Mar 2007 4:33
Reporter:	Andrea Pantaleoni	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.25-BK, 5.0.24	OS:	Linux (Linux)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	bfsm_2007_01_18

Description:
I try to delete a record in a table for example called A that has a trigger for an insert event.
The trigger writes something to a second table for example called B.
When I drop the second table B and I try to delete a record from the first table A I got an error "the table B does not exist".
Note that the trigger was for Inserting!!
The same operation on oracle10g works fine!!

How to repeat:
I create an example database and i select it:
Create database example;
use example

I create the tables and the trigger:
Create table A (id int(10)); 
Create table B (id int(10));
delimiter //
Create trigger trA after insert on A
for each row
begin
Insert into B (id) values (1);
end
//
delimiter ;

Now I insert two records:
Insert into A (id) values (10);
Insert into A (id) values (20);

I drop the table B:
drop table B;

I try to delete a record in table A
delete from A where id=20;

I got:
ERROR 1146 (42S02): Table 'example.b' doesn't exist

Note that I can create the trigger also before that the table B were created
mysql do not perform any check

Oracle let perform the same operation
Thanks
Ciao

Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table A;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table B;
Query OK, 0 rows affected (0.00 sec)

mysql> Create table A (id int(10));
CQuery OK, 0 rows affected (0.01 sec)

mysql> Create table B (id int(10));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> Create trigger trA after insert on A
    -> for each row
    -> begin
    -> Insert into B (id) values (1);
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> Insert into A (id) values (10);
Query OK, 1 row affected (0.01 sec)

mysql> Insert into A (id) values (20);
Query OK, 1 row affected (0.00 sec)

mysql> drop table B;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from A where id=20;
ERROR 1146 (42S02): Table 'test.B' doesn't exist

Also with UPDATE statement return this error.

See related Bug#25435.

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.