Bug #46096 SHOW CREATE TRIGGER does not respect lower_case_table_names

• Submitted: 9 Jul 2009 17:26
• Modified: 9 Jul 2009 17:48
• Reporter: Ingo Strüwing
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1, 5.4
• OS: Linux
• CPU Architecture: Any

[9 Jul 2009 17:26] Ingo Strüwing

Description:
When running with --lower_case_table_names=1, SHOW CREATE TRIGGER does not convert the case of the trigger's table. SELECT FROM INFORMATION_SCHEMA works as expected.

Run the below test case with --lower_case_table_names=0 and --lower_case_table_names=1 (on Linux). You will see that INFORMATION_SCHEMA converts the table name, while SHOW CREATE TRIGGER does not.

This is a problem for BACKUP/RESTORE when backup image files are exchanged between platforms. If the "backup server" runs with --lower_case_table_names=1, but the "restore server" does not. BACKUP uses SHOW CREATE TRIGGER to retrieve the trigger creation statement.

Sure, there is a workaround: have --lower_case_table_names=1 on both servers. But since this requires a restart of the server, it may be a problem on some systems. If the problem is fixed, this constraint can go away.

(See also WL#4771 - Online Backup: Test cross-platform compatibility)

How to repeat:
--echo #
--echo # Check setting of lower_case_table_names.
--echo #
SELECT @@lower_case_table_names;
#
--echo #
--echo # Create tables.
--echo #
CREATE TABLE table1 (c1 INT);
CREATE TABLE TABLE2 (c1 INT);
#
--echo #
--echo # Create trigger.
--echo #
delimiter |;
CREATE TRIGGER TRIGGER2 AFTER INSERT ON TABLE2 FOR EACH ROW
  DELETE FROM table1 WHERE table1.c1 = NEW.c1;|
delimiter ;|
#
--echo #
--echo # Show trigger from INFORMATION_SCHEMA.
--echo #
query_vertical
SELECT TRIGGER_NAME, EVENT_OBJECT_TABLE
  FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = 'TRIGGER2';
#
--echo #
--echo # Show trigger by SHOW.
--echo #
query_vertical
SHOW CREATE TRIGGER TRIGGER2;

[9 Jul 2009 17:48] Ingo Strüwing

Suggested triage values:
Defect: minor. Table name letter case conversion forgotten.
Workaround: partial. Server restart required.
Impact: Exchange of backup image file between platforms may not be too seldom.

[14 Jul 2009 15:59] Sveta Smirnova

Can affect backup/restore and mysqldump usage.

[6 Aug 2009 8:26] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090806082225-qssc912qdv1mm6xv) (version source revid:[e-mail]-20090720092748-euvku4kthos51btb) (merge vers: 5.4.4-alpha) (pib:11)