MySQL Bugs: #12127: triggers do not show in info_schema before they are used if set to the database

Bug #12127	triggers do not show in info_schema before they are used if set to the database
Submitted:	23 Jul 2005 0:00	Modified:	3 Aug 2005 16:19
Reporter:	Omer Barnir (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.11-pre	OS:	Linux (SuSE 9.3)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The information schema triggers table does not show information about a trigger before it is executed if set to the information schema database as described below:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.11-beta-log

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

mysql> drop database if exists tb1;
Query OK, 1 row affected (0.00 sec)

mysql> create database tb1;
Query OK, 1 row affected (0.01 sec)

mysql> use tb1;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (f1 integer);
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger tr1 after insert on t1 for each row set @test_var=42;
Query OK, 0 rows affected (0.02 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select trigger_schema, trigger_name from triggers where trigger_name='tr1';
Empty set, 1 warning (0.04 sec)

OBN> Note the above should have showen the trigger.

mysql> use tb1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> set @test_var=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @test_var;
+-----------+
| @test_var |
+-----------+
| 1         |
+-----------+
1 row in set (0.00 sec)

mysql> insert into t1 values (10);
Query OK, 1 row affected (0.00 sec)

mysql> select @test_var;
+-----------+
| @test_var |
+-----------+
| 42        |
+-----------+
1 row in set (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select trigger_schema, trigger_name from triggers where trigger_name='tr1';
+----------------+--------------+
| trigger_schema | trigger_name |
+----------------+--------------+
| tb1            | tr1          |
+----------------+--------------+
1 row in set (0.01 sec)

mysql>      

Note: The above in not observed if quering theinformation schema without setting to the database i.e. using:
use tb1;
select trigger_schema, trigger_name 
from information_schema.triggers where trigger_name='tr1'; 

How to repeat:
drop database if exists tb1;
create database tb1;
use tb1;
drop table if exists t1;
create table t1 (f1 integer);
create trigger tr1 after insert on t1 for each row set @test_var=42;
use information_schema;
select trigger_schema, trigger_name from triggers where trigger_name='tr1';
use tb1;
set @test_var=1;
select @test_var;
insert into t1 values (10);
select @test_var;
use information_schema;
select trigger_schema, trigger_name from triggers where trigger_name='tr1';

Suggested fix:
Information schema should show triggers as soon as they are created

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/internals/27585

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/internals/27634

Fixed in 5.0.11

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:

Documented fix in 5.0.11 changelog; bug closed.