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?

Improve this question
2

1 Answer 1

Reset to default
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.
Improve this answer
1
  • 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, 2018 at 23:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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