Question :
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?
Answer :
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.)
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.