Bug #48415 | SELECT on INFORMATION_SCHEMA.TRIGGERS does not obey case sensitive OS | ||
---|---|---|---|
Submitted: | 29 Oct 2009 18:00 | Modified: | 30 Oct 2009 14:30 |
Reporter: | Chuck Bell | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
Version: | 5.1.41, 5.4.4, 5.5.0 | OS: | Any (Case sensitive OS) |
Assigned to: | CPU Architecture: | Any |
[29 Oct 2009 18:00]
Chuck Bell
[29 Oct 2009 19:13]
Peter Laursen
".. when there are triggers 'TRG' and 'trg' .." I think the bug is that this should not be allowed to happen at all. *ONLY* database names and table names should be allowed to be case sensitive. So I would reformulate the problem like 'identical triggers except for different LETTERCASE can be created what they should not'.
[29 Oct 2009 20:27]
Peter Gulutzan
You can see with select collation_name from information_schema.columns where table_name='triggers' and column_name='trigger_name'; that the collation of information_schema.triggers.trigger_name is utf8_general_ci. And _ci means "case insensitive". So this should be "not a bug", though I should acknowledge that there has been disagreement about a related subject. See discussion of Bug#34921 "comparisons with Information schema tables don't honor collation".
[29 Oct 2009 20:39]
Peter Laursen
How can two identical triggers be created at all? If that is possible that is a bug in my understanding! LETTERCASE for database objects only applies to databases and tables (because they are implemented as folders/files in the file system) not to columns, indexes, and 'stored programs'. Inconsistency in this respect with VIEWS exists (reported by me 3 years ago) though. Creating TRIGGER 'trg' if 'TRG' already exists should return error 'TRIGGER already exists'.
[29 Oct 2009 20:41]
Chuck Bell
I concur with the last post. If I specify the collation the query works correctly. mysql> SELECT trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name = 'TRG'; +--------------+--------------------+ | trigger_name | event_object_table | +--------------+--------------------+ | TRG | T | | trg | t | +--------------+--------------------+ 2 rows in set (0.04 sec) mysql> SELECT trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name COLLATE utf8_bin = 'TRG'; +--------------+--------------------+ | trigger_name | event_object_table | +--------------+--------------------+ | TRG | T | +--------------+--------------------+ 1 row in set (0.04 sec) mysql> SELECT trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_name COLLATE utf8_bin = 'TRG'; It's still odd and should be fixed.
[29 Oct 2009 20:54]
Peter Laursen
The *bug* is that it is possible to create those two TRIGGERS in the same database! The 'your last post' referred to is mine or PG's? Besides there is nothing like a 'case (in)sensitive OS' .. what exists are 'case (in)sensitive file systems'. Some OS's are restricted to run on either type of files systems - others are not and will run on both types (most notably OS-X)
[29 Oct 2009 21:45]
Peter Laursen
@Chuck: were the 2 TRIGGERS defined in the same database or not? If yes: I have told what I think ;-) If no: I agree with PG, that there is no problem. TRIGGERS are not file system objects and the collation defined for the I_S.triggers table makes 'trg' and 'TRG' equal. (Sorry but I cannot test 5.4.4 myself)
[29 Oct 2009 23:17]
Chuck Bell
Both TRG and trg were defined in the same database. See example above.
[30 Oct 2009 10:58]
Valeriy Kravchuk
Verified just as described on Linux, also with next-mr and recent 5.1.41 from bzr: openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot 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 1 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.T (a int); Query OK, 0 rows affected (0.02 sec) mysql> create table TT.t (a int); Query OK, 0 rows affected (0.02 sec) mysql> create trigger TT.TRG before insert ON TT.T FOR EACH ROW insert into TT.T values (1); Query OK, 0 rows affected (0.02 sec) mysql> create trigger TT.trg before insert ON TT.t FOR EACH ROW insert into TT.t values (1); Query OK, 0 rows affected (0.01 sec) mysql> SELECT trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS WHERE -> trigger_name = 'TRG'; +--------------+--------------------+ | trigger_name | event_object_table | +--------------+--------------------+ | TRG | T | | trg | t | +--------------+--------------------+ 2 rows in set (1.56 sec) mysql> SELECT trigger_name, event_object_table FROM INFORMATION_SCHEMA.TRIGGERS WHERE -> trigger_name = 'trg'; +--------------+--------------------+ | trigger_name | event_object_table | +--------------+--------------------+ | TRG | T | | trg | t | +--------------+--------------------+ 2 rows in set (0.05 sec)
[30 Oct 2009 13:54]
Peter Laursen
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 */ 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" Here docs are wrong when claiming "However, names of triggers .. are case sensitive". Obviously not on all platforms! But there is no reason why platforms should differ in this respect as triggers are not file system objects. This is a stupid inconsistency (in my opinion) for two reasons: * no idea that platforms should differ in this respect * no idea that triggers should be different than other stored programs in this respect. However I agree with PG that Chuck's original report is *not a bug* in the sense that a SELECT from I_S will select strings - and for strings collations apply.
[30 Oct 2009 13:57]
Peter Laursen
I forgot to mention that my last post referred to MySQL 5.4.3.
[30 Oct 2009 14:30]
Peter Gulutzan
I'm not sure what Chuck Bell means when he says "I concur with the last post." I think he is agreeing that Peter Laursen is right, it should be impossible to create triggers named "t" and "T" in the same database. However, that's a different bug: Bug#15187 Triggers: names are case sensitive. (Won't fix.) This item isn't about creating, it's about whether we should silently switch to a case-sensitive collation when searching information_schema, if we have a case-sensitive platform. Well, no. We use the column's collation; that's what it's for. If there's a desire for a sudden big behaviour change, take it up with the appropriate authorities.
[30 Oct 2009 14:44]
Peter Laursen
Since you do not want to discuss my points here that documentation is obviously incorrect and case sensitivity with triggers across platforms is inconsistent and unnecessary, I have created another report: http://bugs.mysql.com/bug.php?id=48437