MySQL Convert to InnoDB

So, we read a few about converting to InnoDB and seems to be better for WordPress than MyISAM, so we converted them with Wp Optimize. After we went to PHPMyAdmin for optimizing those new converted DBs to InnoDB and got this errors:

- Warning: #1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'user_registered' at row 1
 
- Error: #1067 Invalid default value for 'user_registered'

So now we are stuck here with this errors and don′t know how to solve them.
Can anyone please help us to fix them?

We too saw that now we cannot optimize DB, as we get in all tables this kind of errors:

Table does not support optimize, doing recreate + ...
Invalid default value for 'scheduled_date_gm ...
Operation failed
Incorrect datetime value: '0000-00-00 00:00:00' for...

Is there anyway for solving all this situations as now we have all this problems just for updating the DB to InnoDB :S

Thank you in advance for your time.

InnoDB is a storage engine of MySQL/MariaDB (and several other flavours of MySQL-like database server software, like Percona or Drizzle)

MySQL/MariaDB usually can use InnoDB (somewhat more modern, faster writes, row-level locking, full-text indexes)
It is default choice for most modern installations.

or

MyISAM engine, it is old, but very reliable, much faster READ access than InnoDB, much slower writes (due to whole table locking. Its been default for older installations.

or… big bunch of other storage engines, with specialized use and some experimental features. Notably – Aria (MariaDB only, its a MyISAM based engine, but with mix of features from InnoDB), XtraDB (a fork of InnoDB)
and many many more.

It is SAFE to convert your tables to InnoDB. But you should always have a backup before doing changes to your site.. Or just timely made snapshot.

Hi @fierevere, thanks for all the explanation, we updated them because our sites aren′t static sites and so we read that wordpress recommends to update to InnoDB when websites are daily updated a lot of times and that are heavy.

But after updating, we go those errors, can′t optimize DB, we get all this erros in hole list:

- Table does not support optimize, doing recreate + ...
- Invalid default value for 'scheduled_date_gm ...
- Operation failed
- Incorrect datetime value: '0000-00-00 00:00:00' for...

And when repairing DB it says:

- The table manipulator does not allow support for...

And those other errors we shared:

- Warning: #1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'user_registered' at row 1
- Error: #1067 Invalid default value for 'user_registered'

Is there any way for solving this situation?

Thank you!

@ibiza69 Does repairing the tables from phpMyAdmin helps to fix the issue?

Good morning @bornforphp, no it doesn′t, it shows a larg list of those errors I shared here. I was al day yesterday trying to know how to fix all this, with no success.

Sorry, and I tryed too to fix this 2 errors all day with no success too:

1. Warning: #1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘user_registered’ at row 1

2. Error: #1067 Invalid default value for ‘user_registered’

• This reply was modified 2 years, 11 months ago by ibiza69.

@ibiza69 I think in this case you’ll have to contact your host to restore the database backup.

The error suggests that your database has a minor form of corruption – that one of the data fields contains an invalid value. That would be independent of what table engine is being used (just that the attempt to change results in it being detected).

Thank you @davidanderson, the DC said can’t do anthing. Do you think there is a way of solving this? Today I updated a simple static website with very fwe pmugins to InnoDB and same exact problem ??.

Thank you!

FWIW – I had used WPO 3.2.1 to “convert” all our MyISAM tables to InnoDB with the new feature. I reported a problem with something else and was asked by WPO to uninstall, deactivate the plugin and re-install it. I did that and, to my surprise, I found that the MyISAM tables were back as if they had never been converted … weird, but it might work for you.

I’m chewing on crow at the moment …

I’d like to apologize profusely to the WPO team regarding the conversion to InnoDB. It turns out that I had converted the tables on our staging site but not the production site. I was reporting my findings on the production site which I had never converted.

So sorry.

@jsm06278 No issues, I’ve replied to your ticket on paid support, waiting for your reply.

Is it a safe option to progressively convert the tables to INNODB.

And is the correct procedure simply to click each table one by one in
WP_OPTIMIZE> DATABASE > TABLES > CONVERT TO INNODB

Many thanks.

• This reply was modified 2 years, 11 months ago by ddixon62.

@ddixon62 Thanks for the explanation.

Hey guys please select each table one by one and then convert to InnoDB.

Wow, that’s enough to scare me away from a conversion to InnoDb.

Yes sounds scary, is it a safe and recommended conversion?