• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.
Guide to convert from MyISAM to InnoDB

Guide to convert from MyISAM to InnoDB

Tracy Perry

Well-known member
#1
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...
 
Last edited:

Xon

Well-known member
#2
Assuming you are using MariaDB +10.0.5, this table can be converted to InnoDB.
Code:
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.
Code:
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.
 
Last edited:

melbo

Well-known member
#3
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.
 

Tracy Perry

Well-known member
#4
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.
 
Last edited:

Solidus

Well-known member
#6
Hmm, you suggest MyISAM for xf_session and Mike suggests InnoDB. What's suggested when using memcached to cache sessions?
 

Tracy Perry

Well-known member
#7
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. :D
 

SneakyDave

Well-known member
#11
Great notes Terry, thanks.

Just a small correction I noticed:

"These fields are to remain myIASM"

Actually, the entire title is misspelled too, oops.
 

MattW

Well-known member
#14
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.
 

Tracy Perry

Well-known member
#15
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.
 

RoldanLT

Well-known member
#17
Assuming you are using MariaDB, this table can be converted to InnoDB.
Code:
xf_search_index
You can't unless your using ES.
Or else you will see this error every time you post:
Code:
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

Well-known member
#18
You can't unless your using ES.
Or else you will see this error every time you post:
Code:
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

Well-known member
#19
You can't unless your using ES.
Or else you will see this error every time you post:
Code:
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 :)