I'm replicating from mysql 5.6.33 to 5.7.41. I have a table with a datetime field. If I understand correctly, between 5.6 and 5.7 the decreased the space a datetime field uses because it doesn't store timezone data. (but a timestamp does).
This query works on 5.7 (note the presence of the timezone field):
select count(*) from login_activities where date_created < '2023-01-15 04:00:15 -0800';
This delete statement does not work:
delete from login_activities where date_created < '2023-01-15 04:00:15 -0800'
ERROR:
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''
How can I get the delete to work in the same way the select works? I've even removed sql_mode entries but still can't get it to work in 5.7
edit: not sure if this matters, but the error with the delete statement is happining during replication (5.6 -> 5.7), but I'm running the select statement manually. I haven't tried running the delete statement manually because it will through off the replication.