CentOS Question posted in
The official documentation can be found here.

Incorrect datetime value: '0000-00-00 00:00:00' for column – CentOS

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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


  2. 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′

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search