event中有多余的分号引起mysqldump备份导入时报错
有一个导入一直提示报错，看起来应该是正常。
就对这个报错进行查看：
查看到sql文本中的
DELIMITER ;;
/*!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 = utf8 */ ;;
/*!50003 SET character_set_results = utf8 */ ;;
/*!50003 SET collation_connection = utf8_general_ci */ ;;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;;
/*!50003 SET sql_mode = '' */ ;;
/*!50003 SET @saved_time_zone = @@time_zone */ ;;
/*!50003 SET time_zone = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`aaa`@`%`*/ /*!50106 EVENT `bbb` ON SCHEDULE EVERY 1 DAY STARTS '2014-07-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '清理10前的操作日志' DO DELETE FROM aaa_log WHERE datediff(now(),aaa_time)>10^M
; */ ;;
是执行到这个地方报错的。
就把这最后一个语句拿出来 执行：
mysql> DELIMITER ;;
mysql> /*!50106 CREATE*/ /*!50117 DEFINER=`aaa`@`%`*/ /*!50106 EVENT `bbb` ON SCHEDULE EVERY 1 DAY STARTS '2014-07-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '清理10前的操作日志' DO DELETE FROM aaa_log WHERE datediff(now(),aaa_time)>10^M
-> ; */ ;;
Query OK, 0 rows affected, 1 warning (0.00 sec)
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 '*/' at line 1
看起来是执行了两条语句：
mysql> show create event bbb \G
*************************** 1. row ***************************
Event: bbb
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`aaa`@`%` EVENT `bbb` ON SCHEDULE EVERY 1 DAY STARTS '2014-07-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '清理10前的操作日志' DO DELETE FROM aaa_log WHERE datediff(now(),aaa_time)>10^M
;
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
事件是建立起来了，下面也报了个错，所以导入失败了。
其实想跳过错误也是可以。 mysql 时加 -f即可
只是想明白这里怎么错了。
在主库上
mysql> show create event bbb \G
*************************** 1. row ***************************
Event: clear_operation_log_schedule
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`aaa`@`%` EVENT `bbb` ON SCHEDULE EVERY 1 DAY STARTS '2014-07-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '清理10前的操作日志' DO DELETE FROM aaa_log WHERE datediff(now(),aaa_time)>10
;
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
这里好像是多了一点 （上面红色）
在语句中去掉，再次执行
mysql> DELIMITER ;;
mysql> /*!50106 CREATE*/ /*!50117 DEFINER=`aaa`@`%`*/ /*!50106 EVENT `bbb` ON SCHEDULE EVERY 1 DAY STARTS '2014-07-14 00:00:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT '清理10前的操作日志' DO DELETE FROM aaa_log WHERE datediff(now(),aaa_time)>10^M */ ;;
Query OK, 0 rows affected, 1 warning (0.00 sec)
这次可以了。
那就把语句中的 ; 去掉吧
看起来mysqldump备份event时， 最后还不能有 分号。
转载请注明源出处
QQ 273002188 欢迎一起学习
QQ 群 236941212
QQ 273002188 欢迎一起学习
QQ 群 236941212