1

I have a table with 2M records and everything works fine until few hours ago. suddenly it throw error on a query that previously works fine for more than 1 year. The problem is that inserting a correct dateTime like 2019-07-15 22:22:47 into a timestamp column return error:

Incorrect datetime value: '0000-00-00 00:00:00' for column 'created_at' at row 1

1- I did duplicated table structure and query works fine on duplicated table
2- I did run yum update today.
3- OS: CentOS release 6.10 (Final)
4- MySql: Server version: 8.0.16 MySQL Community Server - GPL

Edit: i have read other questions but its completely different, I've posted the answer

osyan
  • 1,784
  • 2
  • 25
  • 54
  • you have few old records in your table with incorrect datetime value of `0000-00-00 00:00:00`. You need to fix those. If you dont know the correct value for them; you can set them to `null`. Do: `SET sql_mode = ''; UPDATE your_table_name SET created_at = null WHERE created_at = '0000-00-00 00:00:00';` – Madhur Bhaiya Jul 16 '19 at 08:53
  • Possible duplicate of [MySQL Incorrect datetime value: '0000-00-00 00:00:00'](https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000) – Madhur Bhaiya Jul 16 '19 at 08:53
  • @MadhurBhaiya No, there is not any records with '0000-00-00 00:00:00' – osyan Jul 16 '19 at 22:42
  • The question you had mentioned is completely a different situation, I'm inserting a new row with valid data – osyan Jul 16 '19 at 22:44

2 Answers2

2

I found it, posting as answer that may help others

It seems that latest mysql update have some new roles added for comparing datetime values, however i think the error thrown is completely irrelevant. i had a trigger on my table which check some parameter and also check if created_at column is equal to '0000-00-00 00:00:00' then change it to current_timestamp. part of trigger is

IF(NEW.created_at = '0000-00-00 00:00:00') THEN
    SET NEW.created_at = current_timestamp();
END IF

it just a simple compare and the result should be true or false, and should not throw the zero date exception.
however i removed this part and everything is up now

osyan
  • 1,784
  • 2
  • 25
  • 54
0
  1. Edit /etc/my.cnf and add line

sql-mode = ""

Save this file

  1. Restart MySQL service

systemctl restart mysqld

  1. Try your sql command,

example:

UPDATE TABLE test SET modified ='0000-00-00 00:00:00'

VietPublic
  • 39
  • 2