phpBB and Xaraya, Tips for Importing and Converting Large Databases

We have been looking at transferring to a new server and a new CMS/Forum system. Currently it uses phpBBPlus, an extensively modded version of phpBB. Over the years, as we've built up to over 80,000 posts, the performance has gotten worse and worse. Moving to a whole new server at our ISP has made only an incremental difference, so now we're looking at alternative systems. One which looks interesting is Xaraya, it has a forum module and import scripts for phpBB.

The first problem involved getting the database imported onto the new host we're using for testing. The MySQL database is now in excess of 90M in size, and the standard phpMyAdmin export and import produces a huge file, even when compressed, which generally causes a timeout or out of memory error when you try to import it. Even doing it a table at a time leads to errors for the really big tables like phpbb_posts_text. Here is my solution:

  1. Export your existing database in two stages, first structure only then data only - the options for this are on the phpMyAdmin export tab
  2. Import the structure into your new database, this should work well enough in a single operation
  3. Now you need to break the data into separate files, to do this use the split command. All Linux users should have this in their default install, I expect Mac users will have it too (though I've no way of checking). Windows users will have to either install Cygwin or get a hold of a native Win32 implementation. Enter the following command:

    # split --lines=2500 data_export_file.sql

    Where the file name is the data file you exported above. You may be able to get away with a larger number than 2500 in the lines parameter, experiment if you want, but that worked for me.

  4. You should now have a whole bunch of files with names like zaa, zab, zac and so on. You can optionally compress these files:

    # gzip *

    This will make the import a bit quicker, but is not required.
  5. Now import the files one at a time, starting with zaa and proceeding in alphabetical order

Next step was the import into XarBB. I copied the import files into the Xaraya root directory, but found I had to make a few modifications. Several of the scripts caused memory errors so I added the line ini_set("memory_limit","24M"); to the top of all the import scripts as well as to includes/exceptions/xarayacomponents.php. I also had a problem when it came to importing the posts - I kept getting an error that the query was exceeding MAX_JOIN_SIZE. A bit of Googling and I figured out a solution, I added the following at line 63 of import_phpbb_posts.php:

$query = 'SET SQL_BIG_SELECTS=1; ';
$rob =& $dbconn->Execute($query);

I also ran into a few problems with modules not being enabled, but I discovered I was able to enable them in the Admin Modules section in another tab and then reload the import page and everything seemed to work fine.