• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 UGH! Converted Forum - Now I find out about INNODB


Well-known member
Well, isn't this just peachy!

I spend months evaluating xF, testing my conversion, and today I finally pull the trigger on my biggest site conversion. I'm 6 hours into the conversion (to give you an idea it took 2 hours to import posts / topics) and then while my search cache rebuild is underway I decide to browse the forum here.

Well, I stumble on this MySQL: MyISAM or INNODB thread and check my tables...


So, what do I do? I mean, my forum seems to be running fine, but it also hasn't been hammered by a bunch of concurrent users yet.

The last time I tried to convert a platform from MyISAM to INNODB (another project different platform) it went terribly terribly wrong and I had to do a full server restore and roll back to MyISAM. The thought of going through that again is terrifying at best.

So my questions for the masters:
  1. Why the heck on a brand new install weren't the correct table structures setup?
  2. Do I "have to" convert the tables to INNODB?
  3. What happens if I don't convert to INNODB?
I vaguely remember that some of the problems I ran into before was related to the fact that I'm still on a 32 bit CentOS and innodb didn't play well with my settings, memory, or whatever :(

Some of my tables aren't super huge, but also not tiny:

I'm kinda freaking out about this, so thanks in advance for your help and suggestions!


Well-known member
To answer 1, I suspect the default engine set in your MySQL install is MyISAM, which is why on a fresh install, the tables are MyISAM.

You can also change the tables to InnoDB with phpmyadmin, you just have a to do a table at a time. I've changed a phpbb3 forum from MyISAM to InnoDB, and back again with no ill effects.

Chris D

XenForo developer
Staff member
It's worth noting that if you do decide to convert the tables there's a couple which are deliberately MyIsam I think.


Well-known member
Thanks guys!

Yup, I've seen the masters, like Brogan post about the tables that should remain myISAM, but I'm just freaking out about the process of converting them and if it will make things better.

As it is right now, my loads on my relatively beefy VPS are going up and there are barely any peeps on my forum. I'm guessing it might have to do with the table types, but at this point that's totally just a guess.


Well-known member
What do you have in your my.cnf file? A few people report that load increases after conversions when MySQL isn't tuned for the new XF setup


Well-known member
Here's my.cnf:


long_query_time=5 # in seconds
log_slow_queries = /var/lib/mysql/slow_query.log



#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_log_arch_dir = /var/lib/mysql
#innodb_additional_mem_pool_size = 128M
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 4M
#innodb_flush_log_at_trx_commit = 0
#innodb_lock_wait_timeout = 50
#innodb_file_per_table = 1
# innodb_buffer_pool_size = 2G

log-error = /var/log/mysqld.log
So, it looks like innodb is turned off? This is starting to get over my payscale / skill level for sure.


Well-known member
Yes, InnoDB is disabled, so as it stands, you'd not be able to convert the tables.

You need to remove the skip-InnoDB value from the config file, and remove the hash before the other InnoDB settings, as they are currently commented out.

Then restart MySQL.

You will then be able to use InnoDB.


Formerly CyclingTribe
Firstly, relax - it's something that can be sorted out with a little time and patience.

Secondly I would expect the server load to be a little higher at the start because visitors are downloading everything afresh (caching should help to lower the load as more people login and things move forward), usergroup promotions are checked at first login (and lots of people will rush in at the start), people will "play" with the new software (and you'll be doing lots of admin stuff as things crop up), and you're unlikely to be optimised for XF on your server at the start - which, if you ask here, will be something you can get help with once you're settled in. (y)

Shaun :D
Last edited:


Well-known member
Thanks for all the help Matt!

Hopefully this discussion will help some other poor saps er... newbie users like me that got into the same bind.

Is there any way to quantify how much better INNODB is for xF than myisam? I mean, is it like I put diesel gasoline in my unleaded car and now it's all gummed up, or are we talking the difference between 87 and 88 octane gas (i.e., very slight difference)?

Also, are the sizes of those tables such that I would expect to run into problems converting them to Innodb in phpmyadmin?


Well-known member
No, should be fine converting those to InnoDB via phpmyadmin once you have enabled it in my.cnf

Probably best to check the max execution time allowed for PHP in your PHP.ini file to make sure the script can run for as long as needed.


Well-known member
My friend that runs a bunch of stuff on xF just shot over the following... how's this look?

$db = mysql_connect('localhost','username','password');
if(!$db) echo "Cannot connect to the database - incorrect details";
$result=mysql_query('show tables');
$tables_to_ignore = array('xf_import_log','xf_search_index','xf_session','xf_session_admin','xf_attachment_view','xf_session_activity','xf_thread_view');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
if(!in_array($value, $tables_to_ignore)){
mysql_query("ALTER TABLE $value ENGINE=innodb");
echo "Your database tables have been changed to innodb!";
It made me wonder if all the tables from various addons should all also be innodb too? I'm assuming they should be.


Well-known member
Looks good!

I'd dump a copy of the DB before running it just incase.

All the add ons I've got installed have InnoDB tables as they pick up the default table type when they are installed.


Well-known member
Before you convert to InnoDB it is worth to get familiar with the very different administration tasks of that table engine. If you are used to MyISAM you have to change all infrastructure. InnoDB tables cannot be handled as easily as MyISAM tables.

Maybe you have to change your backup solution also.

And last but not least check if there is a reason why InnoDB is disabled at your machine.


Well-known member
Ugh, I have MORE complexity coming my way?

Also, I finally got my backup processes / scripts all configured and now I may have to change those too?!?! <sad face>

I believe InnoDB was disabled since we just never used it on our VPS and never expected we would.

On a side note, I had a bit of a freak-out! I removed the skip-innodb line and removed the commented out "#" and restarted mysql and got this nasty error:

root@server [/var/lib/mysql]# /etc/init.d/mysql restart
ERROR! MySQL manager or server PID file could not be found!
Starting MySQL...... ERROR! Manager of pid-file quit without updating file.

... and then all my sites were dead. :eek:

After some testing I discovered it was one of the following (which I recommented and restarted):
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M
#innodb_log_group_home_dir = /var/lib/mysql
#innodb_log_arch_dir = /var/lib/mysql

So, one of those was the thing that gave me that error and didn't let me restart mysql


Well-known member
You should have an error log in your /var/lib/mysql folder that should give an indication why it's not starting. Looking at those, I suspect it could be either or both of these

#innodb_data_file_path = ibdata1:250M:autoextend
#innodb_autoextend_increment = 50M


Well-known member
You disabled very important lines in your config.

I highly recommend that you get familiar with InnoDB first (with reading the MySQL documentation) and stay with MyISAM until you are comfortable with it.


Well-known member
Trial and error... it was this one:
#innodb_log_arch_dir = /var/lib/mysql

How much do I have to worry about fine tuning all these settings before converting over to innodB? I'm on VPS, 32 bit CentOS version 5.10 with 4 GB RAM.