| Bug #35712 | SHOW TRIGGERS LIKE acts unpredictably | ||
|---|---|---|---|
| Submitted: | 31 Mar 2008 16:57 | Modified: | 25 Feb 2009 12:56 |
| Reporter: | James Cohen | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 5.0.45-community-log, 5.0.56 | OS: | Linux |
| Assigned to: | Alexander Nozdrin | CPU Architecture: | Any |
| Tags: | trigger triggers show | ||
[31 Mar 2008 16:57]
James Cohen
Test case SQL
Attachment: trigger_test_case.sql (application/octet-stream, text), 548 bytes.
[31 Mar 2008 17:34]
Valeriy Kravchuk
Thank you for a bug report. SHOW TRIGGERS WHERE ... also deos NOT work: mysql> SHOW TRIGGERS WHERE `Trigger` LIKE '%two'\G Empty set, 1 warning (0.03 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1168 Message: Unable to open underlying table which is differently defined or of non- MyISAM type or doesn't exist 1 row in set (0.00 sec)
[3 Apr 2008 19:15]
Omer Barnir
workaround: use information schema
[25 Feb 2009 12:56]
Alexander Nozdrin
According to The Manual (http://dev.mysql.com/doc/refman/5.1/en/show-triggers.html): --------------------------------------------------------- Note When using a LIKE clause with SHOW TRIGGERS, the expression to be matched (expr) is compared with the name of the table on which the trigger is declared, and not with the name of the trigger: --------------------------------------------------------- So, the test case above works as expected. Also: > SHOW TRIGGERS LIKE 'trigger%'\G *************************** 1. row *************************** Trigger: monkeyone Event: INSERT Table: trigger_test_table_1 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci *************************** 2. row *************************** Trigger: monkeytwo Event: INSERT Table: trigger_test_table_2 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 2 rows in set (0.00 sec) > SHOW TRIGGERS LIKE '%test_table%'\G *************************** 1. row *************************** Trigger: monkeyone Event: INSERT Table: trigger_test_table_1 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci *************************** 2. row *************************** Trigger: monkeytwo Event: INSERT Table: trigger_test_table_2 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 2 rows in set (0.02 sec) > SELECT version(); +--------------------+ | version() | +--------------------+ | 6.0.10-alpha-debug | +--------------------+ 1 row in set (0.00 sec)
[25 Feb 2009 13:23]
Valeriy Kravchuk
Indeed, WHERE version now also works in 5.1.30 and 5.0.74:
mysql> show triggers where `Trigger` like 'i%'\G
*************************** 1. row ***************************
Trigger: i
Event: INSERT
Table: ttr
Statement: begin set @a=1; end
Timing: BEFORE
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITU
TION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.24 sec)
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.30-community-log |
+----------------------+
1 row in set (0.06 sec)
Description: SHOW TRIGGERS with a LIKE clause doesn't appear to work. I've attached a test case here's the result I got when running it. <snip> $ mysql -v test < trigger_test_case.sql -------------- CREATE TABLE trigger_test_table_1 ( id INT(10) ) -------------- -------------- CREATE TABLE trigger_test_table_2 LIKE trigger_test_table_1 -------------- -------------- CREATE TRIGGER monkeyone AFTER INSERT ON trigger_test_table_1 FOR EACH ROW BEGIN END -------------- -------------- CREATE TRIGGER monkeytwo AFTER INSERT ON trigger_test_table_2 FOR EACH ROW BEGIN END -------------- -------------- SHOW TRIGGERS -------------- *************************** 1. row *************************** Trigger: monkeyone Event: INSERT Table: trigger_test_table_1 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost *************************** 2. row *************************** Trigger: monkeytwo Event: INSERT Table: trigger_test_table_2 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost -------------- SHOW TRIGGERS LIKE 'monkey%' -------------- -------------- SHOW TRIGGERS LIKE 'monkeyone' -------------- -------------- SHOW TRIGGERS LIKE 'monkeytwo' -------------- -------------- SHOW TRIGGERS LIKE '%two' -------------- -------------- SHOW TRIGGERS -------------- *************************** 1. row *************************** Trigger: monkeyone Event: INSERT Table: trigger_test_table_1 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost *************************** 2. row *************************** Trigger: monkeytwo Event: INSERT Table: trigger_test_table_2 Statement: BEGIN END Timing: AFTER Created: NULL sql_mode: Definer: root@localhost -------------- DROP TABLE trigger_test_table_1 -------------- -------------- DROP TABLE trigger_test_table_2 -------------- </snip> How to repeat: Run the attached test case SQL file.