Bug #78763 mysqldump with --add-drop-trigger option when trigger name contains dots
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.