Yes, but now it likes them more, and here is why.
Intro
Using the LIKE clause to filter triggers or views from a specific table is common. However, it can play a trick on you, especially if you don’t get to see the output (i.e., in a non-interactive session). Let’s take a look at a simple example and how to deal with the task in a more reliable way. And a bonus link to the mydumper bug that was fixed based on the investigation that led to this lab.
The lab
First, we’ll create two dummy tables with just one unsigned integer column. The column will also be the primary key for both tables.
1 2 3 4 5 6 7 8 9 | CREATE TABLE `test_lab` ( `id` int unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `test2lab` ( `id` int unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
We’ll also create a third table that will be our “log” to track INSERTs made in the first two. We will use a composite Primary Key here (table name and ID).
1 2 3 4 5 | CREATE TABLE `log_lab` ( `changed_table` varchar(50) NOT NULL, `id` int unsigned NOT NULL, PRIMARY KEY (`changed_table`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
Now, let’s add a trigger for each of the tables. The triggers for both tables are identical and will replicate records to the log table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DELIMITER // CREATE TRIGGER `test_lab_trigger_INS` AFTER INSERT ON `test_lab` FOR EACH ROW BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test_lab', NEW.id) ; END // CREATE TRIGGER `test2lab_trigger_INS` AFTER INSERT ON `test2lab` FOR EACH ROW BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test2lab', NEW.id) ; END // DELIMITER ; |
Finally, let’s check we’ve done everything correctly:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> insert into `test_lab` values (8) ; Query OK, 1 row affected (0.00 sec) mysql> insert into `test2lab` values (2) ; Query OK, 1 row affected (0.01 sec) mysql> select * from log_lab ; +---------------+----+ | changed_table | id | +---------------+----+ | test_lab | 8 | | test2lab | 2 | +---------------+----+ 2 rows in set (0.00 sec) |
Now, we have everything ready to run the test case that we created the lab for. Let’s start with using this statement:
1 2 3 4 5 6 7 8 9 | mysql> SHOW TRIGGERS LIKE 'test2lab'; +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | test2lab_trigger_INS | INSERT | test2lab | BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test2lab', NEW.id) ; END | AFTER | 2024-04-13 10:35:45.29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | msandbox@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) |
One table, one trigger, one row. All good.
This time, we’ll use the other table name:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SHOW TRIGGERS LIKE 'test_lab' ; +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | test2lab_trigger_INS | INSERT | test2lab | BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test2lab', NEW.id) ; END | AFTER | 2024-04-13 10:35:45.29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | msandbox@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | | test_lab_trigger_INS | INSERT | test_lab | BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test_lab', NEW.id) ; END | AFTER | 2024-04-13 10:35:45.29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | msandbox@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ 2 rows in set (0.00 sec) |
One might be surprised to see two rows for both tables with their triggers, but it is expected behavior. LIKE statement does not perform strict comparisons and filters based on patterns. You still can have the wildcard (%) in it that can be used in lieu of any number of characters anywhere in the search string. It is less common to use a placeholder (_) in the LIKE statement to match against any single character. And the irony here is that we match both “_” and “2” characters in the table names using the placeholder.
At the beginning of the article, I promised to show a more reliable way to deal with the task, so here you go:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> SHOW TRIGGERS WHERE `Table` = 'test2lab'; +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | test2lab_trigger_INS | INSERT | test2lab | BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test2lab', NEW.id) ; END | AFTER | 2024-04-13 10:35:45.29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | msandbox@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ 1 row in set (0.01 sec) mysql> SHOW TRIGGERS WHERE `Table` = 'test_lab'; +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ | test_lab_trigger_INS | INSERT | test_lab | BEGIN INSERT INTO log_lab (`changed_table`,`id`) VALUES ('test_lab', NEW.id) ; END | AFTER | 2024-04-13 10:35:45.29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | msandbox@localhost | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci | +----------------------+--------+----------+------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+--------------------+----------------------+----------------------+--------------------+ 1 row in set (0.00 sec) |
You can filter on any other column in the output, not limited to Table. Don’t forget to use backticks for Table, as it is a reserved word in MySQL, and you won’t get away without using them (as well as most of the other column names).
I’ll leave it up to the reader to test the same with VIEWs if you want to do your own lab.
In the wild
At this time, you may be asking what this corner case has to do with the real world. It is not common to see the table names differ so slightly when one contains an underscore and the other a number. Besides not getting the output you’d be willing to get, it was also a cause of a bug in mydumper/myloader, that I reported here:
https://github.com/mydumper/mydumper/issues/1418
Mydumper put each table’s triggers into separate files, and using the LIKE statement caused triggers belonging to test2lab to end up in the test_lab triggers file.
Conclusions
The article covered the importance of understanding how operators perform exactly on the example of the LIKE clause, how to create a quick lab for testing the outcome of commands to be sure about the outcome, and how to use the operator’s alternative to LIKE.
If you’re looking for experts in all things MySQL, including triggers, SQL, backup solutions, and labs for corner cases (and then some), Percona has you covered.
Our MySQL Performance Tuning guide covers the critical aspects of MySQL performance optimization. It will help you ensure your databases run smoother, faster, and more reliably. Get it today:
