0

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?

2

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.
  • 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.) – Rick James Aug 24 '18 at 23:15

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.