WT Forums

Some of you might remember me: few years back, I came here with questions to resolve and misconceptions to clear up for my e-book about A-10.

Recently, I resumed my work on the aforementioned book and wanted to check "my" thread here -- only to find out site was no longer operational.

So I sent a mail to jackb, who was most helpful to me back then, and found out what happened. When I mentioned to him that I work in IT, run few phpBB boards, I've been recently reassigned to a database team at work and I would be happy to help, Jack said he has a backup, though it seems impossible to use it for it was for unsupported phpBB v2 and MSSQL.

So I gave it a look and after week of thoughts, determined that recovering the backup and bringing it to phpBB3 seems doable, just lengthy. So If anyone is facing simmilar issue -- or is just plain interested how the old board was recovered --, what was to be done?


1) open the MSSQL backup.
For that, I had to find a version of Mircosoft SQL Server which would work on my rig. I tried 4 different versions of MSSQL Express on my Windows 7 machine, but to no avail (either Win7SP1 was required, or the connector failed to connect.)
In the end, I reverted to by good ol' WinXP machine and used MSSQL Express 2008R1, which worked like a charm.



2) copy the database from MSSQL to MySQL.
Supposedly easy, in the end, not so easy.
I had WAMP package (MySQL+Apache+PHP5) installed, so there were both relational databases: source MSSQL and target MySQL.

Copy was supposed to be done using ODBC drivers (MSSQL2k8R1 installed it's own, MySQL ODBC driver had to be downloaded). Configuring the ODBC so that it would see the database system was the issue, though, especially on the MSSQL side: there was tiny detail which blocked be for two days. What was it? Well, the ODBC dialog asked for name of the SQL server instance you want to connect to -- that was "MSSQL" in my case. Problem was, this did not work: the ODBC dialog kept reporting it couldn't connect to the MSSQL server. Why?

Well, because you needed to also get the machine name, not only name of the MSSQL instance. That means the form of "<machine netbios name>\MSSQL". That's why. After that, the ODBC connections were up and running.

Nevertheless, exporting the database from MSSQL using the "export through ODBC" failed on some data comparasion mismatch. Solution? Either go through the entire phpBB DB structure (and I'm far too comfortable to do that) or find a workaround. Obviously, I choose the second option and used utility suite called "MySQL Workbench", available from the mysql site. With that, copying the DB from MSSQL to MySQL was quite easy.



3) get the backup on a new instance of phpBB2 board.
This seemed to be simple at first: I've installed a fresh phpBB2 board on my server, downloaded from phpBB archive, and thought I'd just load the backup using phpBB2 administration.
Wrong.
The backup jackb gave me had 200MB, so the connection timed out before phpBB2 script could read and recover the backup from the disk.

What now? Easy workaround offered itself: I had the fresh phpBB2 database on my MySQL server (DB named "phpBB2") and I had the copy of old boards backup, which was also a database on the same MySQL server, only it was called "phpbb_a10".

In phpBB2 config.php file, there is a line which tells the board which database contains the board's data. So I just changed that line from "phpbb2" to "phpbb_a10" and that was it: the old board was alive again, exactly as it was left!

Only issue was that all the fresh installations users were replaced --including my sysadmin-power account. So I had to



4) manually get myself admin priviledges on a phpBB board.
That was quick: I went to MySQL administration and opened the Db table in hich users are stored, that is, phpbb_users.
There, I searched for my old account (cover72) and compared it with board old sysadmin's account, that is, "admin".
What was the difference I was looking for? Well, some column was saying that "admin" account had admin priviledges while "cover72" account did not. That column was named "user_level" and had a value of '0' for all non-admins and '1' for admins. Changed user_level for the "cover72" row to 1 and voilà: I had the neccessary level of access.

Also, I forgot my old pasword, so I had to get a new one also directly using database administration instead of board's functionality. That was also simple: in phpBB2, password is encrypted as a md5 hash of what the user writes in, so I just took a new password, entered it into a MD5 generator and copied this value to the "user_password" column of "cover72" row. Finito.

With exact phpBB2 Warthog Territory board up and running, next step was to



5) update/migrate the old board to the up-to-date phpBB3 standard.
That was a process foresighted by phpBB3 designers, so when you install a new phpBB3 board, the installation wizard has an option not to start a new board, but to convert an old phpBB2 board to it. Pretty simple -- but I got stuck on a page 4 of the proccess, since there were way too many users.

I gave the phpbb_users table a quick "SELECT COUNT(*)" look and was amazed: there were 218,000 registered users on the old board! No wonder the backup had 200MB!

Who the heck were all these users?

Spammers. See, the old board allowed anyone to register but before the user could post any actual posts to the board, the account had to be manually approved by to board's sysadmin using mail. This had one drawback, though: when some spammer registered and didn't get approved, he couldn't spam the board... But he was still saved in the user table of the board's database. And over the years, 200k of spammers accumulated.

So before I continued with the conversion,



6) 218 thounsand users-spammers had to be removed.
First thought, obviously, was issuning a "DELETE FROM 'phpbb_users' WHERE 'user_active' = 0" and get rid of all of them with a single SQL querry. However, that's a no go.

phpBB doesn't store all the user information in one table: each user is has a record of what group is he in, and that is stored sideways, in the "phpbb_user_group" table -- and there might be even more of which I did not know. I gave this way a thought, but I couldn't think of a single SQL querry, no matter how complicated, which would delete all these records in so many different tables. Not in MySQL (which I don't know so well), anyway -- I could perfectly imagine doing it on IBM DB2, though.

Anyway, I came to the conclusion I need some php script, which would load all the inactive spammers and properly delete every trace of them in not only from phpbb_users table, but also from all the other tables they might appear in.

phpBB2's administration panel only allows you to delete users one-at a time, so this clearly wasn't an option.
There were some addons for bulk deleting of users, but most of these were for phpBB3, not 2, and they also usually deleted users based on their registration date -- which was a no go in my scenario, since spammers' and regular members' registration times overleaped.

Finally, I found a solution: phpBB 2 toolkit by starFoxTJ.
Thi simple utility has an option to list all the inactive users, sort them by the post count, check and uncheck any number of them and delete all the checked ones.
Which was exactly what I needed, because even some regular users were inactive for some reason, and I surely didn't want to delete their posts also.

So I set the utility to list 2000 inactive users and delete them, and expected tham to disappear quite quickly. However, to my surprise, it took over 20 minutes to process and delete these users (also because the script was checking whether they really didn't post anything, to make the delete really safe and consistent.)
Oops. 218,000 / 20 minutes = 36 hours, or also day and a half of constant issuing delete for another batch of 2000 spammers and waiting for it to finish. I'm not a slave, there is no way I would do that.

So I modified the StarFoxTj's script to list not 2000, but 20,000 users at once. Better, innit?
Well, guess what: timeout. I had to change the PHP timeout to insane number of seconds. Okay, this did work, but it took proportionally large ammout of time to cope with. After doing this for a few times between my return from work and the time I went to sleep (which was 1900 to 0130 hrs), I edited the script again, this time to list 50,000 users in a batch.

And I started the mass-delete of 50k users in the morning, before I left to work, expecting to see it done when I get back.
So, at 0900 the script started. When I returned at 2200, it was still running -- and didn't end till something past 0130!
Yes, it took my poor machine more than 16 hours of machine time to crunch deletion of some 45,000 spammers. So I reduced the number of users in one bulk to 40,000 and this went better.
Finally, after a week of whole day bulk deletions, number of users was reduced from 218,000 down to 2316, which is the number of proper, human WT board users. Hurray!

7) phpBB2 to phpBB3 conversion timed out.
Whaaaaat? PHP timeout was set to manage more than 20 hours of operations! What the heck?

When the phpBB3 script wasn't overwhelmed by sheer number of spammers, it became unresponsive on something. Tough luck.
Everything went down when I got past the step 17/28 of the conversion: within 4 seconds, Opera (O12, my standard browser -- I've been using Opera since v7.5) said "Connection closed by remote host" and Chrome, my alternate browser, wasn't much more verbose with it's "Error 101 CONN_RESET" (which is BTW dumb from Chrome's side, for 3-digit codes are easily confused with HTTP status codes; and HTTP 101 means "switching protocols and going on.")

More people have actually expirienced this unpleasant mess-up. To some, editing the install_convert.php file like this:

Worked. For me, it did not.
Next, I tried modifying MySQL packet settings, as Dicky from phpbb.com suggested:


I set all the values to completely ridiculous ones but this also did not help.

Next, I read that posts with more than 50,000 characters or with hundreds of characters long "words" (including links) might have caused this.
Dicky from phpbb.com is a great guy: he has written a PHP script to detect such posts. Using that, I've removed 3 looong posts and shortened about 20 URLs longer than 500 characters.

Step 10/28, step 17/28, connection closed. Aaargh.

Okay, so I used Dicky's script to list all posts with >300 char "words" and shortened these, too.
Aaaaand I also noticed that the original phpbb_a10 database was InnoDB, while the phpbb3 database was MyISAM. So I created another phpBB3 installation, this time with InnoDB database, and tried the conversion again.

And it worked! Either getting rid of the >300char posts or syncing the DB type FINALLY got me past the timeout/ChromeErr101/Connection Closed by Remote Host error!

8) finalize. And/or merge.
Just one last step needed to be done: I needed to rebuild the search index. Done.
At this point, it was possible to export the phpBB3 board and load it to jackb's domain (/or get the board a new one), overwriting all the new board's posts and users in the proccess.

However, it was also possible to attempt merging of the two boards -- the jackb's new one and recovered phpBB3-converted old one -- to one. And since there was a manual how to merge 22 phpBB3 boards, I just followed it with some minor modifications to speed things up and ensure that nothing hampered the process (PMs from JackB's "2013" board weren't merged) and you could see the results right here.