Bug #78763 mysqldump with --add-drop-trigger option when trigger name contains dots
Submitted: 8 Oct 2015 16:22 Modified: 8 Oct 2015 17:01
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6/5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: --add-drop-trigger, mysqldump, trigger

[8 Oct 2015 16:22] Valeriy Kravchuk
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.
[8 Oct 2015 17:01] Miguel Solorzano
Thank you for the bug report. Verified as described.