MySQL Bugs: #48437: docs state that triggers are case sensitive but it depends on platform

Bug #48437	docs state that triggers are case sensitive but it depends on platform
Submitted:	30 Oct 2009 14:37	Modified:	16 Dec 2009 4:26
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.x	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
this is created with reference to: 
http://bugs.mysql.com/48415

http://dev.mysql.com/doc/refman/5.4/en/identifier-case-sensitivity.html

"Column, index, stored routine, and event names are not case sensitive
on any platform, nor are column aliases.

However, names of triggers and logfile groups are case sensitive. This
differs from standard SQL"

How to repeat:
On Windows:

CREATE DATABASE TT;
CREATE TABLE TT.t1 (a INT);
CREATE TABLE TT.t2 (a INT);
CREATE TRIGGER TT.TRG BEFORE INSERT ON TT.t1 FOR EACH ROW INSERT INTO
TT.t1 VALUES (1);
CREATE TRIGGER TT.trg BEFORE INSERT ON TT.t2 FOR EACH ROW INSERT INTO
TT.t2 VALUES (1);
/*
Error Code : 1359
Trigger already exists
*/

Suggested fix:
So on Windows (and OS-X with default file system?) it is wrong that names of triggers are case sensitive. At least docs should be updated, but there is no compelling reason why file system case sensitivity should affect trigger case sensitivity as triggers are not file system objects.

So best of all make trigger case sensitivity same on all platforms.

corrected spelling mistake in synopsis.

Verified on Mac OS X as well. Case sensitivity for triggers depends on case sensitivity for the filesystem, as they are represented by filesystem objects. Look:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.41-debug Source distribution

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

mysql> CREATE DATABASE TT;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE TT.t1 (a INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE TT.t2 (a INT);
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TRIGGER TT.TRG BEFORE INSERT ON TT.t1 FOR EACH ROW INSERT INTO
    -> TT.t1 VALUES (1);
Query OK, 0 rows affected (0.22 sec)

mysql> CREATE TRIGGER TT.trg BEFORE INSERT ON TT.t2 FOR EACH ROW INSERT INTO
    -> TT.t2 VALUES (1);
ERROR 1359 (HY000): Trigger already exists
mysql> exit
Bye
77-52-222-60:5.1 openxs$ ls var/TT/
TRG.TRN	db.opt	t1.MYD	t1.MYI	t1.TRG	t1.frm	t2.MYD	t2.MYI	t2.frm

Note these .TRN and .TRG files created...

To summarize: this is a bug in documentation.

OK .. thanks for the explanation!  I was not aware of these files created for triggers.

I also think then it is a small inconsistency that 'lower_case_table_names' variable does not affect triggers (it affects databases and tables).

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I'm just going to remove the statement that trigger names are case sensitive because language earlier in the section indicates that trigger name case sensitivity depends on the file system.