Description:
MySQL server allows to create trigger with dots ('.') in its name if it's quoted:
mysql> create database tri;
Query OK, 1 row affected (1.34 sec)
mysql> use tri;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (4.39 sec)
mysql> create trigger trigger.with.dots before insert on t1 for each row set @a
=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '.dots
before insert on t1 for each row set @a=1' at line 1
mysql> create trigger `trigger.with.dots` before insert on t1 for each row set @
a=1;
Query OK, 0 rows affected (0.80 sec)
Unfortunately mysqldump with --add-drop-trigger option generates unquoted name for such a trigger in DROP statement:
C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --triggers --no-data --no-
create-info --add-drop-trigger -uroot -proot -P3314 tri
Warning: Using a password on the command line interface can be insecure.
-- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64)
--
-- Host: localhost Database: tri
-- ------------------------------------------------------
-- Server version 5.6.24-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = cp866 */ ;
/*!50003 SET character_set_results = cp866 */ ;
/*!50003 SET collation_connection = cp866_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO
_ENGINE_SUBSTITUTION' */ ;
/*!50032 DROP TRIGGER IF EXISTS trigger.with.dots */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger `trigge
r.with.dots` before insert on t1 for each row set @a=1 */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2015-10-08 19:10:56
This dump can not be restored without errors.
How to repeat:
Execute this:
create database tri;
use tri;
create table t1(id int);
create trigger `trigger.with.dots` before insert on t1 for each row set @a=1;
and then dump with --add-drop-trigger option:
mysqldump --triggers --no-data --no-create-info --add-drop-trigger -uroot -proot -P3314 tri
to see these:
/*!50032 DROP TRIGGER IF EXISTS trigger.with.dots */;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger `trigger.with.dots` before insert on t1 for each row set @a=1 */;;
...
This drop statement will fail when executed:
mysql> use tri
Database changed
mysql> DROP TRIGGER IF EXISTS trigger.with.dots;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '.dots
' at line 1
mysql>
Suggested fix:
Add quotes to trigger name in DROP TRIGGER STATEMENT generated.
Description: MySQL server allows to create trigger with dots ('.') in its name if it's quoted: mysql> create database tri; Query OK, 1 row affected (1.34 sec) mysql> use tri; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (4.39 sec) mysql> create trigger trigger.with.dots before insert on t1 for each row set @a =1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.dots before insert on t1 for each row set @a=1' at line 1 mysql> create trigger `trigger.with.dots` before insert on t1 for each row set @ a=1; Query OK, 0 rows affected (0.80 sec) Unfortunately mysqldump with --add-drop-trigger option generates unquoted name for such a trigger in DROP statement: C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldump --triggers --no-data --no- create-info --add-drop-trigger -uroot -proot -P3314 tri Warning: Using a password on the command line interface can be insecure. -- MySQL dump 10.13 Distrib 5.6.24, for Win64 (x86_64) -- -- Host: localhost Database: tri -- ------------------------------------------------------ -- Server version 5.6.24-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = cp866 */ ; /*!50003 SET character_set_results = cp866 */ ; /*!50003 SET collation_connection = cp866_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO _ENGINE_SUBSTITUTION' */ ; /*!50032 DROP TRIGGER IF EXISTS trigger.with.dots */; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger `trigge r.with.dots` before insert on t1 for each row set @a=1 */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2015-10-08 19:10:56 This dump can not be restored without errors. How to repeat: Execute this: create database tri; use tri; create table t1(id int); create trigger `trigger.with.dots` before insert on t1 for each row set @a=1; and then dump with --add-drop-trigger option: mysqldump --triggers --no-data --no-create-info --add-drop-trigger -uroot -proot -P3314 tri to see these: /*!50032 DROP TRIGGER IF EXISTS trigger.with.dots */; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger `trigger.with.dots` before insert on t1 for each row set @a=1 */;; ... This drop statement will fail when executed: mysql> use tri Database changed mysql> DROP TRIGGER IF EXISTS trigger.with.dots; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.dots ' at line 1 mysql> Suggested fix: Add quotes to trigger name in DROP TRIGGER STATEMENT generated.