Jump to content
Sign in to follow this  
Linuc82

Convert MyISAM to InnoDB

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated