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
Tags: bfsm_2007_01_18

[25 Aug 2006 8:13] Andrea Pantaleoni
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
[25 Aug 2006 13:48] Valerii Kravchuk
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
[30 Aug 2006 17:00] Mauro Braggio
Also with UPDATE statement return this error.
[10 Jan 2007 17:24] Marc Alff
See related Bug#25435.
[7 Mar 2007 1:04] Marc Alff
This bug has been fixed with Bug#8407.
[7 Mar 2007 22:02] Konstantin Osipov
Pushed into 5.0.38 and 5.1.17.
[9 Mar 2007 4:33] Jon Stephens
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.