MySQL 5.6→5.7复制：datetime字段带时区DELETE报错但SELECT正常

将数据从MySQL 5.6.33复制到5.7.41时，某包含datetime字段的表出现如下异常：

• 手动在5.7从库执行带时区偏移的SELECT语句可正常运行：

  select count(*) from login_activities where date_created < '2023-01-15 04:00:15 -0800';
  

• 复制过程中执行同条件的DELETE语句失败，报错：

  Error 'Incorrect datetime value: '2023-01-15 04:00:15 -0800' for column 'date_created' at row 1' on query. Default database: 'sms'. Query: 'delete from login_activities where date_created < '2023-01-15 04:00:15 -0800''
  

已尝试移除sql_mode相关配置，但问题仍存在。

1. 显式转换带时区时间为标准datetime格式 #

datetime字段不存储时区信息，复制线程对日期格式的解析规则比手动执行SELECT更严格。可以通过MySQL函数将带时区的时间转换为数据库本地时区的标准格式，确保语句兼容5.7：

DELETE FROM login_activities 
WHERE date_created < CONVERT_TZ(
    STR_TO_DATE('2023-01-15 04:00:15 -0800', '%Y-%m-%d %H:%i:%s %z'),
    '+00:00',
    @@session.time_zone
);

需在主库执行该调整后的语句，确保binlog记录的是兼容5.7的指令，从库复制时即可正常执行。

2. 对齐主从库的sql_mode配置 #

MySQL 5.7默认sql_mode包含严格校验规则（如STRICT_TRANS_TABLES），而5.6默认规则更宽松，这是导致复制时解析不一致的核心原因之一：

1. 在主库5.6执行命令获取当前sql_mode：

   SELECT @@sql_mode;
   

2. 在从库5.7的配置文件（如my.cnf/my.ini）中设置相同的sql_mode，例如主库返回NO_ENGINE_SUBSTITUTION，则配置：

   sql_mode=NO_ENGINE_SUBSTITUTION
   

3. 重启从库MySQL服务，或动态生效并重启复制线程：

   SET GLOBAL sql_mode='NO_ENGINE_SUBSTITUTION';
   STOP SLAVE;
   START SLAVE;
   

3. 从根源避免带时区的DELETE语句 #

在主库执行DELETE时，直接使用无时区的datetime字符串作为条件。例如先将2023-01-15 04:00:15 -0800转换为数据库时区对应的本地时间（如数据库时区为UTC时，对应2023-01-15 12:00:15），再执行：

DELETE FROM login_activities where date_created < '2023-01-15 12:00:15';

内容的提问来源于stack exchange，提问作者Robert A. Decker