bzr commit into mysql-trunk branch (jon.hauglid:3454) Bug#58996

[Jon Olav Hauglid]

#At file:///export/home/x/mysql-trunk-bug58996/ based on revid:marc.alff@strippeda

 3454 Jon Olav Hauglid	2011-01-04
      Bug #58996 SHOW CREATE TRIGGER fails when subject table is shadowed by
                 a temporary table.
      
      The problem was that SHOW CREATE TRIGGER failed to show a table trigger
      if a temporary table with the same name as the trigger table existed.
      This even if temporary tables cannot have triggers and SHOW CREATE
      TRIGGER specifies the name of the trigger (not the name of the table).
      
      This patch fixes the problem by forcing SHOW CREATE TRIGGER to ignore
      temporary tables when trying to open the table for which the trigger
      is defined.
      
      Test case added to show_check.test.

    modified:
      mysql-test/r/show_check.result
      mysql-test/t/show_check.test
      sql/sql_show.cc
=== modified file 'mysql-test/r/show_check.result'
--- a/mysql-test/r/show_check.result	2010-11-30 17:55:36 +0000
+++ b/mysql-test/r/show_check.result	2011-01-04 14:23:25 +0000
@@ -1540,3 +1540,19 @@ RELEASE_LOCK('t')
 óóóó
 1
 SET NAMES latin1;
+#
+# Bug#58996 SHOW CREATE TRIGGER fails when subject table is shadowed by
+#           a temporary table.
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
+SHOW CREATE TRIGGER t1_bi;
+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 (b INT);
+SHOW CREATE TRIGGER t1_bi;
+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
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1;

=== modified file 'mysql-test/t/show_check.test'
--- a/mysql-test/t/show_check.test	2010-11-19 05:47:50 +0000
+++ b/mysql-test/t/show_check.test	2011-01-04 14:23:25 +0000
@@ -1325,9 +1325,6 @@ DROP TABLE t1;
 disconnect con1;
 
 
-# Wait till all disconnects are completed
---source include/wait_until_count_sessions.inc
-
 --echo #
 --echo # Bug#57306 SHOW PROCESSLIST does not display string literals well.
 --echo #
@@ -1355,3 +1352,27 @@ SELECT RELEASE_LOCK('t');
 --connection default
 SET NAMES latin1;
 
+
+--echo #
+--echo # Bug#58996 SHOW CREATE TRIGGER fails when subject table is shadowed by
+--echo #           a temporary table.
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW BEGIN END;
+SHOW CREATE TRIGGER t1_bi;
+
+CREATE TEMPORARY TABLE t1 (b INT);
+# Should still work
+SHOW CREATE TRIGGER t1_bi;
+
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1;
+
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2011-01-03 15:45:43 +0000
+++ b/sql/sql_show.cc	2011-01-04 14:23:25 +0000
@@ -7805,7 +7805,7 @@ bool show_create_trigger(THD *thd, const
   /*
     Open the table by name in order to load Table_triggers_list object.
   */
-  if (open_tables(thd, &lst, &num_tables,
+  if (open_tables(thd, &lst, &num_tables, MYSQL_OPEN_SKIP_TEMPORARY |
                   MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL))
   {
     my_error(ER_TRG_CANT_OPEN_TABLE, MYF(0),

Attachment: [text/bzr-bundle] bzr/[e-mail]