vBulletin MyISAM Tables

[prashman]

Hi,

MyISAM's point in recovery is unreliable and we've found the some of the tables that vBulletin uses are based on MyISAM engine (our db is Innodb). Can we simply convert them to InnoDB using mysql 'ALTER' statement?

I'm assuming they are using MyISAM for a reason but I'm more concerned about recovery in the event of a DB crash.

Thanks

MyISAM	blog
MyISAM	blog_text
MyISAM	contenttype
MyISAM	language
MyISAM	searchcore
MyISAM	searchcore_text
MyISAM	searchgroup
MyISAM	searchgroup_text
MyISAM	searchlog
MyISAM	tag
MyISAM	tagcontent
MyISAM	userfield
MEMORY	cpsession
MEMORY	session

[Zachery]

Not searchcore_text or searchgroup_text, otherwise, sure.

[prashman]

Not searchcore_text or searchgroup_text, otherwise, sure.

Hi Zachery - Thank you for your reply.

Regarding searchcore_text and searchgroup_text tables which you say must be MyISAM, these will not provide point in time recovery and they can get corrupted in the event of a crash. A mysql REPAIR command could be used but no guarantees. Are these static tables? If so, I can mysqldump these tables and restore them from backup. Do you have any better ideas on how to best keep the data consistent in the event of a crash for these tables?


What are the downsides of converting them to InnoDB?

Thanks

[Pogo]

What are the downsides of converting them to InnoDB?

You wouldn't be able to use the search in vBulletin.

vBulletin requires these tables to be of the MyISAM type because InnoDB didn't support FULLTEXT indexes up to MySQL 5.6.
In MySQL 5.6+ InnoDB supports FULLTEXT indexes but the results of a search may vary from the results you get with the MyISAM type.
Here are two interesting articles about this: InnoDB Full-text Search in MySQL 5.6: Part 1 and Part 2

So without thorough testing you shouldn't convert these tables to InnoDB.

In case of a crash you can always rebuild the search index in the maintenance area of your AdminCP. You won't lose any data.
Depending on your server and post count this can take a while, though. There is a shell script in your download package that rebuilds the search index a lot faster.

[prashman]

You wouldn't be able to use the search in vBulletin.

vBulletin requires these tables to be of the MyISAM type because InnoDB didn't support FULLTEXT indexes up to MySQL 5.6.
In MySQL 5.6+ InnoDB supports FULLTEXT indexes but the results of a search may vary from the results you get with the MyISAM type.
Here are two interesting articles about this: InnoDB Full-text Search in MySQL 5.6: Part 1 and Part 2

So without thorough testing you shouldn't convert these tables to InnoDB.

In case of a crash you can always rebuild the search index in the maintenance area of your AdminCP. You won't lose any data.
Depending on your server and post count this can take a while, though. There is a shell script in your download package that rebuilds the search index a lot faster.

Ah OK. I understand now. Thanks Pogo. So I can convert all the tables in my original post (excluding MEMORY of course) to InnoDB except searchgroup_text and searchcore_text. If these two tables get corrupted in the event of a crash then I can rebuild these two tables by running one of the utilities in maintenance section of admincp or a shellscript manually. This sounds like a plan and I can live with this risk.

Please could you let me know the name of the shell script that builds these two tables?

Many Thanks.

[Pogo]

It's called searchindex.php and you'll find it in the do_not_upload directory of your vBulletin download package.

[prashman]

Hi Pogo,

I started converting the tables on a test environment and I was not able to convert two more tables in addition to the ones that Zachery specified (searchgroup_text and searchcore_text). These were blog_text and language.

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '_INSERT_TABLE_SCHEMA_' and ENGINE <> 'InnoDB' and ENGINE <> 'MEMORY';
+------------------+--------+
| TABLE_NAME | ENGINE |
+------------------+--------+
| blog_text | MyISAM |
| language | MyISAM |
| searchcore_text | MyISAM |
| searchgroup_text | MyISAM |
+------------------+--------+
4 rows in set (0.00 sec)

blog_text complains about FULLTEXT.
language complains about "ERROR 1118 (42000): Row size too large (&gt; 8126)."


If I leave the engines of these tables like this, how do I recover language and blog_text in the event of a crash? One option may be is to download the XML from admin page - not sure about blog_text? Any better options?


(searchindex.php can build searchcore_text and searchgroup_text - I understand that)

[Pogo]

You can drop both fulltext indices from blog_text and then convert it to InnoDB.
They are not needed because the search is using the searchgroup_text and searchcore_text tables.

I don't think you can do anything about the language table.
If this table crashes you can always rebuild it in the AdminCP or create/import a new language in the AdminCP or via the file do_not_upload/tools.php from your vBulletin download package.

[prashman]

Thanks Pogo.

I can see two FULLTEXT indexes on blog_text table. I have dropped both of them and set the engine to InnoDB. Will test and see if everything is OK.

alter table blog_text drop index title;
alter table blog_text drop index title_2;

alter table blog_text ENGINE=InnoDB;

[prashman]

Hi Pogo,

It is looking OK and testing has not found any problems so far. One last thing I wanted to check with you - will any of this cause any problems with vbulletin upgrades?

Thanks.