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 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1, 5.4 OS:Linux
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[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:alik@sun.com-20090806082225-qssc912qdv1mm6xv) (version source revid:ingo.struewing@sun.com-20090720092748-euvku4kthos51btb) (merge vers: 5.4.4-alpha) (pib:11)