1

I have changed the MyISAM tables to InnoDB. During the conversion, I changed SQL_MODE (SET SQL_MODE='ALLOW_INVALID_DATES';) because in some tables I had DateTime records with the default value of '0000-00-00 00:00:00' and InnoDB won't allow it. I also know changing the SQL_MODE like this changes the sql_mode only during your current session. My question is what will happen now that the default values are still '0000-00-00 00:00:00'? The application is working fine. How is this possible if InnoDB won't allow it?

2 Answers 2

2

there are two sql modes NO_ZERO_IN_DATE,NO_ZERO_DATE which control that behaviour, as they only apply after you converted your table.

that isw hy you can convert such tables but you will not be able to enter or configure such value as default for date, without changing the sql mode for an insert.

If you want to keep it remove the sql modes from my.cnf/ini and restart the server.

3
  • what is interesting is that I have NO_ZERO_IN_DATE, NO_ZERO_DATE in my sql modes but am still able to add 0000-00-00 00:00:00 to my table. Why is that happening? Aug 23, 2021 at 20:18
  • @FarimanKashani - Was it via INSERT? LOAD DATA? or something else? And show us the full declaration of the column.
    – Rick James
    Aug 23, 2021 at 20:37
  • you have more sql modes like strict that can also have a influence if the warnig /error os shown foall modes see dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
    – nbk
    Aug 23, 2021 at 20:43
2

I encountered a similar problem of having 0000-00-00 00:00:00 already in some tables as I upgraded to 8.0. The data was fine -- until I went to ALTER the table for some unrelated issue.

Instead of changing the sql_mode, I decided to use NULL. The data needed converting, and that was a little tricky.

UPDATE t SET dt = "1971-01-01" WHERE dt <= "1971-01-01";
ALTER TABLE t MODIFY COLUMN dt DATE NULL;
UPDATE t SET dt = NULL WHERE dt <= "1971-01-01";

Plus checking the code to have respond correctly to NULL in place of "1971-01-01". (Mostly, I did not care what happened with either value.)

2
  • my hands are really tight as the code rely on 0000-00-00 00:00:00 value. I think I need to remove NO_ZERO_IN_DATE,NO_ZERO_DATE from sql modes. But the question is why I'm still able to store 0000-00-00 00:00:00 ? I'm generating rows using phpmyadmin and it's letting me to store 0000-00-00 00:00:00. Aug 23, 2021 at 20:23
  • 1
    @FarimanKashani - Fine. Your app has one requirement (and nbk addressed that); my app had different requirements. It seems that neither of us can answer you 'real' question of why the value was allowed into the table.
    – Rick James
    Aug 24, 2021 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.