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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-1813849/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-1813849/