Guide to convert from MyISAM to InnoDB

Guide to convert from MyISAM to InnoDB

Jim Boy

Well-known member
Assuming you are using MariaDB, this table can be converted to InnoDB.
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.
Not really that big an issue for the tables listed.

Am toying with the idea of going to Aurora, which doesn't support MyISAM at all and probably never will.
 

Jim Boy

Well-known member
If your session table crashes and requires manual repair, no one can login to your site. That is rather noticeable compared to the system fixing itself.
Yes, but the point I was making is that the data is disposable. You dont need to repair the table, you can drop and recreate. There will be inconvenience, but there wont be data loss. That would have been a major consideration when the guys specified the table as MyISAM. The benefits of using MyISAM outweighed the risk.
 

eva2000

Well-known member
Assuming you are using MariaDB, this table can be converted to InnoDB.
Code:
xf_search_index
might want to clarify that as MariaDB 10.0.5+ only as MariaDB 5.5 and MariaDB <10.0.5 don't support fulltext InnoDB AFAIK.
 
  • Like
Reactions: Xon

fly

Active member
I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?
 

fly

Active member
I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?
I forgot about this post. Anyone?
 

Tracy Perry

Well-known member
I forgot about this post. Anyone?

The DB's tend to be larger and I do believe it uses more memory - but for XenForo, that's what the it was designed to use. I can tell you for a fact that with MyISAM if you have a dirty shutdown you will have problems. Kind of like trying to run a Ferrari on 87 octane gas.
 
Last edited:

SneakyDave

Well-known member
I can tell you for a fact that with MyISAM if you have a dirty shutdown you will have problems.
Well, that's just the nature of MyISAM without transactions. You can develop something that doesn't utilize transactions, but yeah, when XenForo is designed to use them, you can have problems if those tables aren't Innodb.
 

Xon

Well-known member
I haven't read up on this recently, but doesn't InnoDB have much larger memory requirements? I don't suppose there is any way around them if I happen to be running on a small VPS, is there?
There isn't any real difference for modern versions of MySQL.

But the modern defaults for both MyISAM and InnoDB are aimed at systems with more than a hundred megabytes of memory; so tuning may be required if the total memory of the system is small.

Well, that's just the nature of MyISAM without transactions. You can develop something that doesn't utilize transactions, but yeah, when XenForo is designed to use them, you can have problems if those tables aren't Innodb.
It isn't a matter of transactions, but file layout design.

The Aria table type from MariaDB doesn't support transactions but is crash resistant, where as MyISAM doesn't support transactions and will randomly lose all your data in a table on a crash.
 

Bubka3

Active member
ALTER TABLE `xf_trophy_combination` ENGINE=InnoDB;
ALTER TABLE `xf_trophy_category` ENGINE=InnoDB;
ALTER TABLE `xf_tmdb_ratings` ENGINE=InnoDB;

Doesn't exist on default installs. Or at least mine.
 

Tracy Perry

Well-known member
Very likely that those were for another add-on that I missed.... I know the last one was. I think the other two may be one of Waindigo's add-ons. I did most of them from memory since I didn't have blank install. Will update the listing. Thanks!
 

bzcomputers

Well-known member
Why should these be left as MyISAM as per Guide to convert from MyISAM to InnoDB?

xf_import_log
xf_search_index
xf_session
xf_session_admin

That guide and alot of the responses to it are quite old...
xf_import_log is now import_log_* which is InnoDB by default.

There are still 4 tables that are MyISAM by default:
xf_search_index
xf_session
xf_session_admin
xf_session_install

There were some limitations with early versions of InnoDB including fulltext search limitations. The xf_search_index has fulltext indexes. These are now supported by InnoDB (as long as you have a version of MySQL and MariaDB released in the last 5 years or so).

The xf_session* tables I believe remained MyISAM due to early performance issues with InnoDB and the use of delayed inserts. I am pretty sure none of that implies anymore.

I personally haven't converted these table over to InnoDB ...yet, but I know some have without issue. Maybe they will chime in with their results.
 
Top