Zero Date Values & Converting Entire DB From MyISAM to InnoDB

Question

I'm trying to update the MYSQL DB for an older wordpress/woocommerce site that's currently using MyISAM by switching the engine type to InnoDB. The issue I'm having using Alter Table is that there's a number of tables that have a zero date set as the default value, which SQL is not allowing me to change to null.

I don't want to set mysql to ignore the error since it's been deprecated and will likely cause additional problems down the road. Any suggestions would be appreciated.

Also, should I be concerned about causing conflicts by converting all tables to InnoDB from MyISAM?

• Rick James: What was the strict mode? Is this relevant: MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value ?
• Rick James: Check for other issues here: mysql.rjweb.org/doc.php/myisam2innodb

Answer 1

It would be great if your provide the table schema and mention the column name.

But you can follow this method to mitigate this.

1. Get the table schema
2. Create a new table with the same schema, but the engine=InnoDB.
3. Insert into the new table using select * from the MyISAM table.
4. Rename the current table as tbl_older
5. Rename the new table as the actual name.

• Rick James: Also, what is the history of the MyISAM table? Probably it was created before you upgraded to 5.7? Did you run mysql_upgrade? (There are several places where something might have happened, not just the change from MyISAM to InnoDB.)