Bug #58996 SHOW CREATE TRIGGER fails when subject table is shadowed by a temporary table.
Submitted: 17 Dec 2010 11:36 Modified: 30 Mar 2011 16:51
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.54-bzr, 5.5.8 OS:Any
Assigned to: Jon Olav Hauglid
Triage: Triaged: D3 (Medium)

[17 Dec 2010 11:36] Dmitry Lenev
Description:
SHOW CREATE TRIGGER fails to show trigger definition when there is a temporary table shadowing trigger's subject table. See How-to-repeat for details.

How to repeat:
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row begin end;
show create trigger t1_bi;
# Works shows:
#
# Trigger sql_mode        SQL Original Statement  character_set_client    collation_connection    Database Collation
# t1_bi           CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row begin end      latin1  latin1_swedish_ci       latin1_swedish_ci

create temporary table t1 (j int);
show create trigger t1_bi;
# Fails with
# Error: 1360: Trigger does not exist
[17 Dec 2010 11:57] Valerii Kravchuk
5.5.8 is also affected:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1 (i int);
ERROR 1050 (42S01): Table 't1' already exists
mysql> create trigger t1_bi before insert on t1 for each row begin end;
Query OK, 0 rows affected (0.33 sec)

mysql> show create trigger t1_bi;
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
| Trigger | sql_mode                                                       | SQL
 Original Statement
        | character_set_client | collation_connection | Database Collation |
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
| t1_bi   | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CRE
ATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1 for each row be
gin end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+----------------------------------------------------------------+----
--------------------------------------------------------------------------------
--------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> create temporary table t1 (j int);
Query OK, 0 rows affected (0.16 sec)

mysql> show create trigger t1_bi;
ERROR 1360 (HY000): Trigger does not exist
[4 Jan 2011 14:24] Bugs System
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/127876
[5 Jan 2011 14:44] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:jon.hauglid@oracle.com-20110105144328-c9ee981z819p2zmw) (version source revid:jon.hauglid@oracle.com-20110105144328-c9ee981z819p2zmw) (merge vers: 5.6.2) (pib:24)
[5 Jan 2011 14:44] Bugs System
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/127980
[30 Mar 2011 16:51] Paul Dubois
Noted in 5.6.2 changelog.

SHOW CREATE TRIGGER failed if there was a temporary table with the
same name as the trigger subject table. 

CHANGESET - http://lists.mysql.com/commits/127980