Tag: SQL

Changing your phpBB tables’ storage engine

On: By: Mark

Note: this post was updated October 17, 2019.

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. If your database is MariaDB, don’t worry. These instructions work for MariaDB too. Why? It’s because MariaDB is a clone of an earlier community edition of MySQL. Functionally, it is virtually identical.

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 and MariaDB 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 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. 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. 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. These instructions assume phpMyAdmin is used.

A few caveats:

  • Backup your database first.
  • You should disable your forum during the process. Users might get error messages or timeouts while this is happening.
  • 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. Simply look at the tables for your forum’s database and examine the TYPE column to see which tables remain to be done, which are those where the type still shows MyISAM.
  • 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.

  1. Look at your config.php file for your forum. You can download it with FTP or you can view it in your web host control panel’s file manager. It should look something like this:
<?php
// phpBB 3.2.x auto-generated configuration file
// Do not change anything in this file!
$dbms = 'phpbb\\db\\driver\\mysqli';
$dbhost = 'localhost';
$dbport = '';
$dbname = 'forum_database';
$dbuser = 'forum_admin';
$dbpasswd = 'forum_password';
$table_prefix = 'phpbb_';
$phpbb_adm_relative_path = 'adm/';
$acm_type = 'phpbb\\cache\\driver\\file';

@define('PHPBB_INSTALLED', true);
// @define('PHPBB_DISPLAY_LOAD_TIME', true);
@define('PHPBB_ENVIRONMENT', 'production');
// @define('DEBUG_CONTAINER', true);
  1. Make a note of the value for $dbname
  2. Log into phpMyAdmin
  3. Select the database containing your forum, which is the same as the value of $dbname.
  4. 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.
  5. Click on the SQL tab.
  6. Copy and paste the SQL below into the window. Replace the value in quotes for dbname with the value of the variable $dbname in your config.php file. The result of the query is a bit strange, because it renders SQL. You will use the generated SQL to actually change the storage engine.
SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ENGINE=INNODB;') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'dbname' AND engine = 'MyISAM'
  1. It will render a list like this. In most cases the tables will start with “phpbb_” but if it doesn’t don’t worry. The tables should start with the value of $table_prefix in your config.php file. Note that what the previous query does is construct the SQL you need to change the storage engines, but only where the current engine is MYISAM. Only a few rows are shown for demonstration purposes.
...
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_config_text ENGINE=INNODB; 
ALTER TABLE phpbb_confirm ENGINE=INNODB;
...
  1. Check the Show All checkbox if it is there to see all the rows.
  2. In phpMyAdmin at the bottom of the screen look for a link Copy to clipboard. Click on the link and all the generated SQL will be copied to your clipboard.
  3. Open a text editor and paste it into a new document. Remove any lines before the first ALTER statement. If there are any statements where the table name does not start with the value of $table_prefix, you might want to edit these out because these tables are probably not used by phpBB.
  4. Disable your forum while doing these changes: ACP > General > Board configuration > Board settings > Disable Board > Yes
  5. While you could just paste all these statements into the SQL window in phpMyAdmin again for your database, this is not recommended unless your forum is very small in size. Instead, it’s recommended that you do a block of these at a time, say ten statements per block. If there are no errors, then do the next block until all are done. For very large tables like the phpbb_posts table or the phpbb_search_wordmatch table, it is recommended that you do these individually. Each line that is executed should change the storage engine for the table from MyISAM to InnoDB.
  6. When all done, you can verify that the table storage engines have changed by clicking on the link for the database in phpMyAdmin and examining the TYPE column again. They should all report InnoDB.
  7. Reenable your forum: ACP > General > Board configuration > Board settings > Disable Board > No
  8. You don’t need to save the SQL in your editor.

That’s it! Enjoy!