Discussion in 'Server Configuration and Hosting' started by dutchbb, Sep 8, 2015.
Hi I read XF uses InnoDB for the database, yet in phpMyAdmin the type shows MyISAM?
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
Hm.. it's mostly MyISAM and a few Memory. This is what I run
Server version: 5.5.38-0ubuntu0.14.04.1
MySQL client version: 5.5.38
I thought InnoDB is supported since v5.5? Should I upgrade the server before installing/importing XF for the production site?
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.)
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.
default-storage-engine = InnoDB
InnoDB is the default storage engine in MySQL 5.5.
For some reason not on my server :s
If you go to the engines tab in phpMyAdmin do you see InnoDB listed in there?
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.
default-storage-engine = MyISAM
yup although i made special note on Xenforo setup page at http://centminmod.com/nginx_configure_xenforo_seo_friendly_urls.html
Yeah correct, my old host probably set that.
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
phblttt..should be step #1!
better yet just made InnoDB default in latest update https://community.centminmod.com/posts/18252/
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.
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
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.
For future reference, I don't provide support via conversation.
Support requests should be posted in the relevant forum or via ticket.
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?
Separate names with a comma.