Changing your phpBB tables’ storage engine

Most phpBB forums use the MySQL database to hold the forum’s data. It’s an obvious choice because community editions are free and it comes bundled with your hosting package. When MySQL was first created, there were few storage engines available. Storage engines contain the code (software) and internal data structure for storing data in tables. Consequently unless you have created a new forum it’s likely that your forum’s tables use the old MYISAM storage engine.

With phpBB 3.1, the default storage engine for phpBB tables using MySQL has changed from MYISAM to INNODB. However, the conversion program from 3.0 to 3.1 will not change the storage engine. INNODB is a different way of storing the data in a table. It’s generally more self sufficient than MYISAM and you are less likely to need to repair it. INNODB tables are best used for tables that are frequently read and written to. In fact, the REPAIR command does not work on tables using the INNODB engine.

I generally recommend to my clients to change their tables’ storage engines to INNODB because they are less likely to have issues. I see my clients that struggle through issues like the sessions table needs to be repaired and they don’t know how to do it. Sometimes tables using MYISAM just get inconsistent leading to issues such as orphaned posts.

phpMyAdmin is a tool generally provided by your web host in its control panel. You can use phpMyAdmin to change your storage engine but you can also do it from SSH with the right privileges by running the mysql command. While you can change your storage engine in phpMyAdmin, it’s not intuitive. There is nothing in the software that allows you to do this in the user interface. You can do it on the SQL tab, however.

A few caveats:

  • Backup your database first.
  • You might want to disable your forum during the process. Users might get error messages or timeouts while this is happening.
  • Make sure you know the correct table prefix for your forum’s tables. You can find this in your config.php file in the forum’s root directory. It is usually phpbb_.
  • The more posts and users you have the longer it will take. Generally it takes only a few minutes. It’s possible that phpMyAdmin will time out. In this case go back in when you can, see which tables have been converted and convert any remaining tables.
  • You might want to convert a block of tables at a time, perhaps ten at a time, so you can get feedback that things are proceeding correctly.
  • In phpBB 3.1 there are two CAPTCHA tables that you may not have. These tables are created dynamicly if you decide to ask questions on registration. If you don’t have these tables, attempts to change their storage engine will obviously trigger an error.
  • If your sessions table uses a MEMORY storage engine, this is fine. You might want to use MEMORY for the sessions table only because the sessions table is accessed very frequently. The MEMORY storage engine very fast because nothing is stored to a file, but if the database server crashes or is rebooted all the rows in MEMORY tables are lost. This is okay in the case of the sessions table. It just means that people have to log in again.

These instructions assume you are using phpMyAdmin and are running phpBB 3.1 or phpBB 3.0. It’s possible that there will be additional tables. These tables if any are likely from any extensions installed (phpBB 3.1) or modifications installed (phpBB 3.0).

  1. Log into phpMyAdmin
  2. Select the database containing your forum
  3. You can see your tables’ storage engine in the TYPE column. If you are already using INNODB for all your tables there is no point in doing anything.
  4. Click on the SQL tab.
  5. Copy and paste the SQL below into the window. You may first need to edit the SQL to:
    1. Remove tables you don’t have
    2. Remove tables that are already using INNODB for the storage engine
    3. To change the table prefix from phpbb_
  6. When finished verify it worked correctly by reexamining the TYPE column.

The SQL is below the fold:

SQL for phpBB 3.1:

ALTER TABLE phpbb_acl_groups ENGINE=INNODB;
ALTER TABLE phpbb_acl_options ENGINE=INNODB;
ALTER TABLE phpbb_acl_roles ENGINE=INNODB;
ALTER TABLE phpbb_acl_roles_data ENGINE=INNODB;
ALTER TABLE phpbb_acl_users ENGINE=INNODB;
ALTER TABLE phpbb_attachments ENGINE=INNODB;
ALTER TABLE phpbb_banlist ENGINE=INNODB;
ALTER TABLE phpbb_bbcodes ENGINE=INNODB;
ALTER TABLE phpbb_bookmarks ENGINE=INNODB;
ALTER TABLE phpbb_bots ENGINE=INNODB;
-- Dynamically created tables follow, check to see if they exists and remove lines if they do not
ALTER TABLE phpbb_captcha_answers ENGINE=INNODB; 
ALTER TABLE phpbb_captcha_questions ENGINE=INNODB;
-- End dynamically created tables
ALTER TABLE phpbb_config ENGINE=INNODB;
ALTER TABLE phpbb_config_text ENGINE=INNODB;
ALTER TABLE phpbb_confirm ENGINE=INNODB;
ALTER TABLE phpbb_disallow ENGINE=INNODB;
ALTER TABLE phpbb_drafts ENGINE=INNODB;
ALTER TABLE phpbb_ext ENGINE=INNODB;
ALTER TABLE phpbb_extension_groups ENGINE=INNODB;
ALTER TABLE phpbb_extensions ENGINE=INNODB;
ALTER TABLE phpbb_forums ENGINE=INNODB;
ALTER TABLE phpbb_forums_access ENGINE=INNODB;
ALTER TABLE phpbb_forums_track ENGINE=INNODB;
ALTER TABLE phpbb_forums_watch ENGINE=INNODB;
ALTER TABLE phpbb_groups ENGINE=INNODB;
ALTER TABLE phpbb_icons ENGINE=INNODB;
ALTER TABLE phpbb_lang ENGINE=INNODB;
ALTER TABLE phpbb_log ENGINE=INNODB;
ALTER TABLE phpbb_login_attempts ENGINE=INNODB;
ALTER TABLE phpbb_migrations ENGINE=INNODB;
ALTER TABLE phpbb_moderator_cache ENGINE=INNODB;
ALTER TABLE phpbb_modules ENGINE=INNODB;
ALTER TABLE phpbb_notifications ENGINE=INNODB;
ALTER TABLE phpbb_notification_types ENGINE=INNODB;
ALTER TABLE phpbb_oauth_accounts ENGINE=INNODB;
ALTER TABLE phpbb_oauth_tokens ENGINE=INNODB;
ALTER TABLE phpbb_poll_options ENGINE=INNODB;
ALTER TABLE phpbb_poll_votes ENGINE=INNODB;
ALTER TABLE phpbb_posts ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_folder ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_rules ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_to ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields_data ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields_lang ENGINE=INNODB;
ALTER TABLE phpbb_profile_lang ENGINE=INNODB;
ALTER TABLE phpbb_qa_confirm ENGINE=INNODB;
ALTER TABLE phpbb_ranks ENGINE=INNODB;
ALTER TABLE phpbb_reports ENGINE=INNODB;
ALTER TABLE phpbb_reports_reasons ENGINE=INNODB;
ALTER TABLE phpbb_search_results ENGINE=INNODB;
ALTER TABLE phpbb_search_wordlist ENGINE=INNODB;
ALTER TABLE phpbb_search_wordmatch ENGINE=INNODB;
ALTER TABLE phpbb_sessions ENGINE=INNODB;
ALTER TABLE phpbb_sessions_keys ENGINE=INNODB;
ALTER TABLE phpbb_sitelist ENGINE=INNODB;
ALTER TABLE phpbb_smilies ENGINE=INNODB;
ALTER TABLE phpbb_styles ENGINE=INNODB;
ALTER TABLE phpbb_teampage ENGINE=INNODB;
ALTER TABLE phpbb_topics ENGINE=INNODB;
ALTER TABLE phpbb_topics_posted ENGINE=INNODB;
ALTER TABLE phpbb_topics_track ENGINE=INNODB;
ALTER TABLE phpbb_topics_watch ENGINE=INNODB;
ALTER TABLE phpbb_user_group ENGINE=INNODB;
ALTER TABLE phpbb_users ENGINE=INNODB;
ALTER TABLE phpbb_user_notifications ENGINE=INNODB;
ALTER TABLE phpbb_warnings ENGINE=INNODB;
ALTER TABLE phpbb_words ENGINE=INNODB;
ALTER TABLE phpbb_zebra ENGINE=INNODB;

SQL for phpBB 3.0:

ALTER TABLE phpbb_acl_groups ENGINE=INNODB;
ALTER TABLE phpbb_acl_options ENGINE=INNODB;
ALTER TABLE phpbb_acl_roles ENGINE=INNODB;
ALTER TABLE phpbb_acl_roles_data ENGINE=INNODB;
ALTER TABLE phpbb_acl_users ENGINE=INNODB;
ALTER TABLE phpbb_attachments ENGINE=INNODB;
ALTER TABLE phpbb_banlist ENGINE=INNODB;
ALTER TABLE phpbb_bbcodes ENGINE=INNODB;
ALTER TABLE phpbb_bookmarks ENGINE=INNODB;
ALTER TABLE phpbb_bots ENGINE=INNODB;
ALTER TABLE phpbb_config ENGINE=INNODB;
ALTER TABLE phpbb_confirm ENGINE=INNODB;
ALTER TABLE phpbb_disallow ENGINE=INNODB;
ALTER TABLE phpbb_drafts ENGINE=INNODB;
ALTER TABLE phpbb_extension_groups ENGINE=INNODB;
ALTER TABLE phpbb_extensions ENGINE=INNODB;
ALTER TABLE phpbb_forums ENGINE=INNODB;
ALTER TABLE phpbb_forums_access ENGINE=INNODB;
ALTER TABLE phpbb_forums_track ENGINE=INNODB;
ALTER TABLE phpbb_forums_watch ENGINE=INNODB;
ALTER TABLE phpbb_groups ENGINE=INNODB;
ALTER TABLE phpbb_icons ENGINE=INNODB;
ALTER TABLE phpbb_lang ENGINE=INNODB;
ALTER TABLE phpbb_log ENGINE=INNODB;
ALTER TABLE phpbb_login_attempts ENGINE=INNODB;
ALTER TABLE phpbb_moderator_cache ENGINE=INNODB;
ALTER TABLE phpbb_mods ENGINE=INNODB;
ALTER TABLE phpbb_modules ENGINE=INNODB;
ALTER TABLE phpbb_poll_options ENGINE=INNODB;
ALTER TABLE phpbb_poll_votes ENGINE=INNODB;
ALTER TABLE phpbb_posts ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_folder ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_rules ENGINE=INNODB;
ALTER TABLE phpbb_privmsgs_to ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields_data ENGINE=INNODB;
ALTER TABLE phpbb_profile_fields_lang ENGINE=INNODB;
ALTER TABLE phpbb_profile_lang ENGINE=INNODB;
ALTER TABLE phpbb_ranks ENGINE=INNODB;
ALTER TABLE phpbb_reports ENGINE=INNODB;
ALTER TABLE phpbb_reports_reasons ENGINE=INNODB;
ALTER TABLE phpbb_search_results ENGINE=INNODB;
ALTER TABLE phpbb_search_wordlist ENGINE=INNODB;
ALTER TABLE phpbb_search_wordmatch ENGINE=INNODB;
ALTER TABLE phpbb_sessions ENGINE=INNODB;
ALTER TABLE phpbb_sessions_keys ENGINE=INNODB;
ALTER TABLE phpbb_sitelist ENGINE=INNODB;
ALTER TABLE phpbb_smilies ENGINE=INNODB;
ALTER TABLE phpbb_styles ENGINE=INNODB;
ALTER TABLE phpbb_styles_imageset ENGINE=INNODB;
ALTER TABLE phpbb_styles_imageset_data ENGINE=INNODB;
ALTER TABLE phpbb_styles_template ENGINE=INNODB;
ALTER TABLE phpbb_styles_template_data ENGINE=INNODB;
ALTER TABLE phpbb_styles_theme ENGINE=INNODB;
ALTER TABLE phpbb_topics ENGINE=INNODB;
ALTER TABLE phpbb_topics_posted ENGINE=INNODB;
ALTER TABLE phpbb_topics_track ENGINE=INNODB;
ALTER TABLE phpbb_topics_watch ENGINE=INNODB;
ALTER TABLE phpbb_user_group ENGINE=INNODB;
ALTER TABLE phpbb_users ENGINE=INNODB;
ALTER TABLE phpbb_warnings ENGINE=INNODB;
ALTER TABLE phpbb_words ENGINE=INNODB;
ALTER TABLE phpbb_zebra ENGINE=INNODB;

Leave a Reply

Your email address will not be published. Required fields are marked *