Guide to convert from MyISAM to InnoDB

[TPerry]

Tracy Perry submitted a new resource:

Guide to convert from MyISAM to INNODB - Convert from MyISAM to INNODB for hosts that used MyISAM as default engine

So, you've found that you have imported your DB into a new host that had their default engine as myIASM and they didn't have InnoDB configured for mySQL.

First, let's preface this with the warning of BEFORE YOU RUN ANY OF THESE, MAKE SURE YOU HAVE BACKED UP YOU DATABASE!

Well, here are the tables (at a minimum) that you will need to alter once InnoDB is enabled. This is set to use for phpMyAdmin in the SQL Query box. These are the ones that I am...

Read more about this resource...

 

[Xon]

Assuming you are using MariaDB +10.0.5, this table can be converted to InnoDB.

xf_search_index

If you have a semi-decent setup, or a fair number of users online at once; then the following tables should be converted to InnoDB.

xf_session
xf_session_admin
xf_session_activity

Remember; MyISAM is not crash resistant. You can lose the entire contents of the table if something goes wrong and the MySQL process stops or worse the system itself stops.

InnoDB will just lose some recent records, but should keep your data intact.

 

[melbo]

Nice tutorial.
Might be good to mention that there are some InnoDB specific configuration options that are probably not present in a my.conf file that was set up for myISAM.

 

[TPerry]

Nice tutorial.
Might be good to mention that there are some InnoDB specific configuration options that are probably not present in a my.conf file that was set up for myISAM.

That was why it commented on "once they have confirmed that InnoDB is working". The guide assumes that whomever is controlling the DB server is familiar enough to tune somewhat for InnoDB.

Assuming you are using MariaDB, this table can be converted to InnoDB.

Only system I use MariaDB on is my CentOS box (Percona is my poison of choice) and the guide was to get to what would be a default install situation as if InnoDB was originally working.

If you have a semi-decent setup, or a fair number of users online at once; then the following tables should be converted to InnoDB.

Good to know, but that would tend to be more in the line of optimizing the DB, again, not what the original guide is targeted at.

Remember; MyISAM is not crash resistant. You can lose the entire contents of the table if something goes wrong and the MySQL process stops or worse the system itself stops.

InnoDB will just lose some recent records, but should keep your data intact.

Agreed, but as above... guide is to get an individual to the base install setup of the correct (default) engines for the various tables in the DB.

Once they get to that point, then they can start "tuning" for performance.

 

[Luke F]

Assuming you are using MariaDB, this table can be converted to InnoDB.

xf_search_index

Or MySQL/Percona 5.6.4+

 

[m1ne]

Hmm, you suggest MyISAM for xf_session and Mike suggests InnoDB. What's suggested when using memcached to cache sessions?

 

[TPerry]

Hmm, you suggest MyISAM for xf_session and Mike suggests InnoDB. What's suggested when using memcached to cache sessions?

No, I don't "suggest"... I just state what the default install consists of. Converting it to another engine is part of the tuning aspect.

 

[xH3LLRAIZ3Rx]

Can't you just go into the table and go into operations and change it there?

 

[TPerry]

Can't you just go into the table and go into operations and change it there?

All I saw on my phpMyAdmin install was the ability to change the Collation... not the engine when going into the DB and then choosing Operations:

 

[MattW]

All I saw on my phpMyAdmin install was the ability to change the Collation... not the engine when going into the DB and then choosing Operations:
View attachment 97237

You need to click the table itself first, and then choose operations:

 

[SneakyDave]

Great notes Terry, thanks.

Just a small correction I noticed:

"These fields are to remain myIASM"

Actually, the entire title is misspelled too, oops.

 

[TPerry]

Great notes Terry, thanks.

Just a small correction I noticed:

"These fields are to remain myIASM"

Actually, the entire title is misspelled too, oops.

That's alright... I'm not the only one with that problem.

 

[TPerry]

You need to click the table itself first, and then choose operations:
View attachment 97238

To slow... that's a one at a time deal then (and when you consider that there is over 100 of them...). The SQL query that is shown will be much faster.
I knew there was a reason I did most of mine from the CLI.

 

[MattW]

To slow... that's a one at a time deal then. The SQL query that is shown will be much faster.
I knew there was a reason I did most of mine from the CLI.

Same as I do it, and as you've already provided the query, they can just run that via the SQL window inside PMA if they want to.

 

[TPerry]

Same as I do it, and as you've already provided the query, they can just run that via the SQL window inside PMA if they want to.

Yep, that's the main reason I did put the query in.. I was going through one at a time and figured out running a query to select all the current MyISAM for a list was easier then do a separate query to convert to InnoDB would be easier.

 

[TPerry]

Actually, the entire title is misspelled too, oops.

and all of them should be corrected to MyISAM now.

 

[rdn]

Assuming you are using MariaDB, this table can be converted to InnoDB.

xf_search_index

You can't unless your using ES.
Or else you will see this error every time you post:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: DELAYED option not supported for table 'xf_search_index' - library/Zend/Db/Statement/Mysqli.php:77

 

[Xon]

You can't unless your using ES.
Or else you will see this error every time you post:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: DELAYED option not supported for table 'xf_search_index' - library/Zend/Db/Statement/Mysqli.php:77

There is an option under performance to turn off 'delayed inserts' as they aren't supported with InnoDB.

Delayed inserts are just a hack for MyISAM to work around for the fact it uses whole table locks when doing writes. InnoDB doesn't need it, as multiple inserts do not block provided you have enough memory to stage the insert to.

 

[nrep]

You can't unless your using ES.
Or else you will see this error every time you post:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: DELAYED option not supported for table 'xf_search_index' - library/Zend/Db/Statement/Mysqli.php:77

Just disable delayed queries in the admin panel (there's an option for this) and it'll be fine . I've been running xf_search_index as innodb for years.

edit: beaten to it

 

[rdn]

Thanks hehe.