mysql恢复数据数据出现Variable sql_mode cant 解决方法

用mysqldump导出的数据文件,再用source导进去的时候常常有一些报错 ariable sql_mode cant be set to the value of NULL,百度了好几回,终于找到是mysql导出的注释语句问题,导出的文件常常如下:

1. /*!40101SETSQL_MODE=@OLD_SQL_MODE*/;
2. /*!40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
3. /*!40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;
4. /*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;
5. /*!40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;
6. /*!40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;
7. /*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/;

解决方法:删除注释语句后再执行批量SQL语句操作.

利用二进制日志恢复报错？Variable 'sql_mode' can't be set to the value of '1344274432'.

1. Serverversion5.0.18-log
2. Protocolversion10
3. ConnectionlocalhostviaTCP/IP

操作系统:windowsXp,mysql为InnoDB存储引擎,数据库为utf.

win.ini中配置,代码如下:

1. [mysql]
2. default-character-set=utf8
4. [mysqld]
5. basedir="c:/MySQL5/"
6. datadir="c:/MySQL5/Data/"
8. log-bin="d:/log/log"
9. binlog-do-db=test
10. innodb_data_home_dir=
11. innodb_data_file_path=e:/MySQLDatafiles/ibdata1:10M;e:/MySQLDatafiles/ibdata2:1M:autoextend
13. innodb_log_group_home_dir="d:/log/binlog/"
14. default-character-set=utf8
15. default-storage-engine=INNODB
17. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
19. ...............

恢复冷备份后,执行日志恢复报错:

C:＼mysqlbinlog d:/log/log.000002|mysql -uroot -pa

ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of