Mysql – Zero Date Values & Converting Entire DB From MyISAM to InnoDB

innodbmyisamMySQLmysql-5.7

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?

Best Answer

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.
Related Question