Convert MyISAM to InnoDB

[Linuc82]

Good day,

So, I seem to have an issue and can't figure it out.

Been using WHMCS since they opened for business.  Back in those days, the installation automatically created all tables using MyISAM.

We now want to install a specific module from ModulesGarden and they say the tbldomains must use InnoDB for their module to work.  So this means converting from one engine to the other.

Some tables convert without an issue, but many of them have "Created at" or "Updated at" values of 0000-00-00 00000 and when you run something like this:

ALTER TABLE tblemailtemplates ENGINE=InnoDB;

You will get:

Error

MySQL said: Documentation

#1067 - Invalid default value for 'created_at'

 

Since MyISAM is getting depreciated in MySQL 8 it would be a good idea to start converting these old tables, am I correct?

Any help would be much appreciated.

[Linuc82]

So, I am the only one using MyISAM on some tables and concerned about converting them to InnoDB?

[alinford]

Were you able to get this done?

What issues did you have?

[Linuc82]

The error I got went away after setting the MySQL mode to nothing in my.cnf

In other words, adding the following entry:

sql_mode=""

What issues did I have?  Well, I decided to change the tables in a staggered approach.  First, changing the engine for a few smaller tables, like tbltodo

I did not have any issues with the smaller tables that had only a few row entries.  

BUT, for some reason when I do big tables I see some row losses that I can't explain.

Example, my tblactivitylog had 305,000 entries before the engine change, but only 295,000 after the engine change.

I still cannot figure out why this would be the case.  I certainly don't want to lose rows altering the tbldomains or tblclients.

[zmenchhofer]

Hello!   Same issues trying to convert to "UTF8"  any help?