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!
 
Top