MyISAM / InnoDB

[dutchbb]

Hi I read XF uses InnoDB for the database, yet in phpMyAdmin the type shows MyISAM?

 

[Paul B]

You should have a mixture of InnoDB, MyISAM and Memory tables.

If you don't then it's likely InnoDB is not supported so the tables have reverted to MyISAM.

MySQL :: MySQL 5.5 Reference Manual :: 14.6 InnoDB Configuration

 

[dutchbb]

Hm.. it's mostly MyISAM and a few Memory. This is what I run

MySQL
Server version: 5.5.38-0ubuntu0.14.04.1
nginx/1.4.6
MySQL client version: 5.5.38

phpMyAdmin
Version: 3.4.3.1

I thought InnoDB is supported since v5.5? Should I upgrade the server before installing/importing XF for the production site?

 

[Mike]

It sounds like it's turned off in my.cnf. There are a few different ways of doing this: How to disable innodb in mysql?

After enabling it, you may want to convert your tables: Guide to convert from MyISAM to InnoDB (Note that the query list is version specific, but it explains the exceptions to the table list.)

 

[dutchbb]

Thanks. My webhosts said that with the version of MySQL I'm using (libmysql - 5.5.38), innodb is not well supported and should not be the default.

He recommended Mariadb 10 for better support.

Another thing, I removed 'skip_innodb' from my.cnf and installed XF again. It still created MyISAM tables. I guess for now I just use that database.

 

[rdn]

Another thing, I removed 'skip_innodb' from my.cnf and installed XF again. It still created MyISAM tables. I guess for now I just use that database.

default-storage-engine = InnoDB

 

[Snog]

My webhosts said that with the version of MySQL I'm using (libmysql - 5.5.38), innodb is not well supported and should not be the default.

InnoDB is the default storage engine in MySQL 5.5.

https://dev.mysql.com/doc/refman/5.5/en/innodb-introduction.html

 

[dutchbb]

For some reason not on my server :s

 

[Fred.]

If you go to the engines tab in phpMyAdmin do you see InnoDB listed in there?

 

[TPerry]

For some reason not on my server :s

Most likely because they have

default-storage-engine = MyISAM

in the my.cnf. As @RoldanLT indicated - change it to InnoDB and restart the mysql service. If you use CentMin Mod, I noticed that it ships with the my.cnf using MyISAM as the default engine via the my.cnf.

 

[eva2000]

If you use CentMin Mod, I noticed that it ships with the my.cnf using MyISAM as the default engine via the my.cnf.

yup although i made special note on Xenforo setup page at http://centminmod.com/nginx_configure_xenforo_seo_friendly_urls.html

 

[dutchbb]

Most likely because they have

default-storage-engine = MyISAM

in the my.cnf. As @RoldanLT indicated - change it to InnoDB and restart the mysql service. If you use CentMin Mod, I noticed that it ships with the my.cnf using MyISAM as the default engine via the my.cnf.

Yeah correct, my old host probably set that.

 

[TPerry]

yup although i made special note on Xenforo setup page at http://centminmod.com/nginx_configure_xenforo_seo_friendly_urls.html

Yeah, but then you get those like me who run Wordpress, IPS 4, MyBB, XenForo, CopperMine Gallery, and a few other things that don't look for a specific setup for CentMin since we run several on one system.

 

[eva2000]

Yeah, but then you get those like me who run Wordpress, IPS 4, MyBB, XenForo, CopperMine Gallery, and a few other things that don't look for a specific setup for CentMin since we run several on one system.

yeah got that covered in Getting Started Guide Step 11

Note: default /etc/my.cnf sets default storage engine to MyISAM (default-storage-engine = MyISAM). If you want to use InnoDB storage engine tables, set in /etc/my.cnf, default-storage-engine = InnoDB and restart MySQL server.

 

[TPerry]

yeah got that covered in Getting Started Guide Step 11

phblttt..should be step #1!

 

[eva2000]

phblttt..should be step #1!

better yet just made InnoDB default in latest update https://community.centminmod.com/posts/18252/

 

[ForumCube]

He recommended Mariadb 10 for better support.

Directly moving to MariaDB 10 not sounds like a good idea. If you're currently using MySQL 5.5.38 then you should move to MariaDB 5.5.x. As that would be just drop-in replacement. You can upgrade to MariaDB 10 in afterwards.

 

[Induslady]

We run a cron to MySQL dump all DBs in our server. Recently during the time this cron is running, our host noticed that there were locked tables in our XF database that caused all Apache connections to fill-up.

So hosting is recommending to convert all tables using MyISAM to InnoDB, they are:
|xf_search_index | MyISAM |
| xf_session | MyISAM |
| xf_session_admin | MyISAM |

We are running MySQL 5.6, that allows InnoDB engines to support Full Indexes. So they are asking to convert all MyISAM tables to InnoDB and leave those memory tables as it is.

1. Is it ok to convert all tables using MyISAM to InnoDB?
2. Other than support for full indexes, is there any other need to have them use MyISAM?
3. Can the conversion from MyISAM to InnoDB happen when the community is up and running?

@Brogan , I sent a conversation about the same. Please revert. Thank you

 

[Paul B]

InnoDB full text search has different behaviour to MyISAM full text search.
We can't recommend changing the table types and can only officially support the (default) MyISAM-based search.

@Brogan , I sent a conversation about the same. Please revert. Thank you

For future reference, I don't provide support via conversation.
Support requests should be posted in the relevant forum or via ticket.

 

[Induslady]

@Brogan,

Thank you for your response.

If I cannot change table types, are there options to backup the MyISAM tables (using Mysqldump) without locking the tables for long?