Page 1 of 1

phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 1:51 am
by cooch17
Greetings -

I've been running a relatively modded version of phpbb 2.0.23 on a RHEL 3 system, which has MySQL 3.xx installed. Everything has been running perfectly for years - I have ~1000 users, and no problems to speak of.

Except one - the server I have things running on is old (very), and the main HD is filling up. I'm planning the longterm move to a new server which has MySQL 5.xx installed (3.xx no longer being available). I'm sticking with phpBB 2.0.23, since the conversion from 2.xx -> 3.xx will no doubt choke on the various mods. I see no features in phpBB 3.xx to compel me to switch anyway.

But, I am concerned about migrating my MySQL 3.xx tables to MySQL 5.xx. I bounced around this forum enough to know that it is doable, but may or may not be less than trivial. I was wondering if there were some general steps I should take (besides making backups, then backups of the backups) before I start, or if there were pointers to sites that talk about this specific issue?

If it matters, I'm not upgrading from MySQL 3 -> 5 on the current box. I want to move the phpBB-related tables from the MySQL 3 box to a phpBB 2.xx install I'll do on the other box, which has mySQL 5.0.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 5:13 am
by espicom
You can leave the database type set to "mysql"; changing it to "mysql4" does not gain you anything worthwhile at this point.

Use phpmyadmin to back up the database, following the instructions in How to move a phpBB 2.0.x board to a new server. Using a text editor, find the definition for the Sessions table, and change its type from "heap" to "myisam", if it is not already set to that.

Restore the SQL to the new version 5 database. This will take care of most of the conversion "gotcha's" you run into when you try copying the raw data files to the new version.

Modify phpBB's config.php file to point to the new server, with the new userid and password.

IF you are not moving to PHP version 5, you also have to configure MySQL 5 to use "old passwords" (manual link in my signature).

Should be pretty simple!

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 12:11 pm
by cooch17
Thanks! Couple of quick followups.
espicom wrote:You can leave the database type set to "mysql"; changing it to "mysql4" does not gain you anything worthwhile at this point.
Seems easy enough.
Use phpmyadmin to back up the database, following the instructions in How to move a phpBB 2.0.x board to a new server. Using a text editor, find the definition for the Sessions table, and change its type from "heap" to "myisam", if it is not already set to that.
Also seems easy enough, except I don't have phpmyadmin installed - I have been doing basic backups/maintenance with webmin (there are backup elements to the mysql server module), and from phpbb Admin board. Is there something specific about what (or how) phpmyadmin backups things up, or it basically just a front end to a dump?
Restore the SQL to the new version 5 database. This will take care of most of the conversion "gotcha's" you run into when you try copying the raw data files to the new version.
How? Presumably from the command line in some fashion...or is this a phpmyadmin 'thing'?
Modify phpBB's config.php file to point to the new server, with the new userid and password.
Seems easy.
IF you are not moving to PHP version 5, you also have to configure MySQL 5 to use "old passwords" (manual link in my signature).
suhosin-patched and hardened php 5. I'll never play with 'soft' php again.
Should be pretty simple!
Famous last words!

Thanks again (and in advance if you have any further followup).

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 3:36 pm
by espicom
Around here, assuming someone has command-line access to the database is only going to be correct about .02% of the time...

With CL access, though, you can simply import the file using the mysql command line tool. That will take care of time-outs and all that sort of rot. Webmin is nice, but... it's subject to some limitations that the CL tools don't have. If you use mysqldump, for example, to do the backup, the resulting file can be run through vi and have the change done to the sessions table.

You can do a structure-only backup of the tables that have "session" or "search" in their names, if you want... In fact, for the search tables, I'd recommend it, because there is a chance the database character encoding will be different, so any accented words will have conflicts during restore. Check out this MOD to rebuild search tables to save the time and aggravation. The session tables are dynamic anyway, and the worst side effect of "losing" them is that people have to log in again.

phpmyadmin is "special" in that it is the most widely-distributed web interface to MySQL in hosting environments. It can't do some of the things Webmin can (because it runs at a much lower privilege level), but it is relatively easy to use.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 8:12 pm
by cooch17
espicom wrote:Around here, assuming someone has command-line access to the database is only going to be correct about .02% of the time...
Gotcha...
With CL access, though, you can simply import the file using the mysql command line tool. That will take care of time-outs and all that sort of rot. Webmin is nice, but... it's subject to some limitations that the CL tools don't have. If you use mysqldump, for example, to do the backup, the resulting file can be run through vi and have the change done to the sessions table.
I don't really 'do' mySQL (the only use I have for it at all is phpbb), so I'm not sure what a sessions table is, but, mysqldump is something I can fathom. ;-)
You can do a structure-only backup of the tables that have "session" or "search" in their names, if you want... In fact, for the search tables, I'd recommend it, because there is a chance the database character encoding will be different, so any accented words will have conflicts during restore. Check out this MOD to rebuild search tables to save the time and aggravation. The session tables are dynamic anyway, and the worst side effect of "losing" them is that people have to log in again.
My board is low traffic (very technical), despite having a lot of registered users. So, having to have a few folks 'login again' is not a big hassle...
phpmyadmin is "special" in that it is the most widely-distributed web interface to MySQL in hosting environments. It can't do some of the things Webmin can (because it runs at a much lower privilege level), but it is relatively easy to use.
Do you know off-hand if the 'backups' created by webmin and phpmyadmin are identical? If so, then I'd probably stick with webmin, since I have it running, and know where all the 'mySQL' module switches are. If they're not the same, then I'll pursue phpmyadmin (although the docs suggest that setup is a bit daunting...at least compared to webmin, which took about 60 seconds to figure out).

Thanks again!

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Nov 26, 2008 9:28 pm
by ric323
cooch17 wrote:....
I don't really 'do' mySQL (the only use I have for it at all is phpbb), so I'm not sure what a sessions table is, but, mysqldump is something I can fathom. ;-)
...
It's more a feature of phpBB than SQL. It's the table used to remember information about your logged in users.
Jeff is saying that you do not need the data in that table when you move to a new server. You can just copy the "structure" of the table, and not the data. This will just force your users to login again the first time they connect to the new board.
Similarly, the data in the three search tables is not required. You do need the "structure", but the data can be easily regenerated with the "rebuild" MOD that Jeff posted a link to. The search tables often make up more than 50% of the backup file.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Thu Nov 27, 2008 12:11 am
by espicom
It looks like webmin generates backups that contain SQL statements to restore the data. That's pretty much what both phpmyadmin and mysqldump would do. The tool doesn't really matter, just the result.

Skipping tables is a speed and size thing; you NEED all 29 tables to be present (more if you have MODs that have their own tables), which means you need to back up the structure. So start with that - tell webmin's SQL module to not bother with the data at first (no rows).

Then, you do a data backup, selecting ONLY the tables that do NOT have "session" or "search" in their names - there will be 24 like that. Generate this second backup to a file.

Then, take the first file (structure only), and make sure all the "type=" portion of the CREATE TABLE statements say "myisam", and save this to a file. You can use webmin to import this to your new database on the v5 server.

Finally, restore the data.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Thu Nov 27, 2008 12:27 am
by cooch17
espicom wrote:It looks like webmin generates backups that contain SQL statements to restore the data. That's pretty much what both phpmyadmin and mysqldump would do. The tool doesn't really matter, just the result.

Skipping tables is a speed and size thing; you NEED all 29 tables to be present (more if you have MODs that have their own tables), which means you need to back up the structure. So start with that - tell webmin's SQL module to not bother with the data at first (no rows).

Then, you do a data backup, selecting ONLY the tables that do NOT have "session" or "search" in their names - there will be 24 like that. Generate this second backup to a file.

Then, take the first file (structure only), and make sure all the "type=" portion of the CREATE TABLE statements say "myisam", and save this to a file. You can use webmin to import this to your new database on the v5 server.

Finally, restore the data.
Thanks very much for the 'kinder and gentler' outlining of the steps. I'll probably give this a shot once the semester is over, and will post results - if successful, thanks to you. If not, probably my fault.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Tue Dec 16, 2008 3:08 am
by cooch17
So far, so good - followed the advice of several folks replying to my OP, and all seems to be working. Here are the details of what I did (in case anyone else has to go through this).

1. on old host, fired up webmin, and created a full backup of all the mySQL databases (call it oldphpbb.sql). Basically webmin uses a straight mysql dump. Old server has php 4.x and myql 3.23.x. Moved oldphpbb.sql to new server, which runs php 5.2.8 and mysql 5.xx

2. on new host, fired up webmin, and created the database (call it newphpbb) I wanted to import the old phpbb database into

3. dropped down to CL (since webmin doesn't have any obvious features for a mysql import), and ran

mysql -u <user> -p <passwd> newphpbb < oldphpbb.sql

Worked like a charm - all the tables were there, and the forum seemed to fire up just fine. Didn't need to edit a thing in the oldphobb.sql before importing it into the new db.

I haven't doen a full test of everything, but that first 'smoke test' (i.e., where you try to access the forum and hope everything at least comes up on screen) passed with flying colours.

Want to thank earlier respondents to this topic - couldn't have done it without you (well, probably not as quickly or as easily). Thanks! :D

Once I get my forum up and running perfectly on the new machine using phpbb 2.0.23, I *may* see just how horrible a port to phpbb 3.xx will be. I'm not confident that even moderately modded 2.xx ports to 3.xx, but...less of an issue now that I know (or, am fairly sure), I can resurrect the 2.0.23 version as needed.

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Tue Dec 16, 2008 8:57 pm
by Lumpy Burgertushie
the first issue you will have is that the cookie settings will probably be wrong.

this includes;

domain name
script path
cookie domain
cookie path
cookie name

if all of those remain the same on the new server, then no problems.

if not, you can edit those fields in your database to the correct settings.

or , there is a cookie MOD around here that will do it for you.

as for phpBB3, it is a very straight forward conversion.

notice I said conversion and not update.

phpbb3 uses only the data from phpbb2. none of the files at all,
and none of the tables in the database.

it will convert the phpbb2 data to phpbb3 tables.

it will convert attachments and birthdays if you have either one of those MODs installed.

NO OTHER phpbb2 MODs will be converted and none will work with phpbb3.

NO phpbb2 styles will work with phpbb3 either.

to convert, you simply do a complete install of phpbb3 right beside phpbb2, then you choose the convert tab on the install page and click the button and that is it.


once you have phpbb3 up and running and are happy , you can get rid of the phpbb2 installation if you wish .


robert

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Tue Dec 16, 2008 9:40 pm
by cooch17
Lumpy Burgertushie wrote:the first issue you will have is that the cookie settings will probably be wrong.

this includes;

domain name
script path
cookie domain
cookie path
cookie name

if all of those remain the same on the new server, then no problems.
Server will have the same name, and identical directory structure, so I'm hoping everything will simply fall into place.


if not, you can edit those fields in your database to the correct settings.

or , there is a cookie MOD around here that will do it for you.
Thanks - good to know if I ever need it...
as for phpBB3, it is a very straight forward conversion.

notice I said conversion and not update.

phpbb3 uses only the data from phpbb2. none of the files at all,
and none of the tables in the database.

it will convert the phpbb2 data to phpbb3 tables.

it will convert attachments and birthdays if you have either one of those MODs installed.

NO OTHER phpbb2 MODs will be converted and none will work with phpbb3.

NO phpbb2 styles will work with phpbb3 either.

to convert, you simply do a complete install of phpbb3 right beside phpbb2, then you choose the convert tab on the install page and click the button and that is it.


once you have phpbb3 up and running and are happy , you can get rid of the phpbb2 installation if you wish .


robert
Alas, I have some mission critical phpbb2 mods that are not likely to ever be converted over (I've posted a query about one in the phpbb 3 mods forum), so I'll be with 2.0.23 for a while...

Thanks!

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Wed Dec 17, 2008 11:47 pm
by Lumpy Burgertushie
understandable,
as you can see in my signature below, there are others that will stay with phpbb2 as well.

robert

Re: phpbb 2.0.23 | MySQL 3.xx -> 5.xx upgrade

Posted: Thu Dec 18, 2008 1:03 am
by cooch17
Lumpy Burgertushie wrote:the first issue you will have is that the cookie settings will probably be wrong.


as for phpBB3, it is a very straight forward conversion.



to convert, you simply do a complete install of phpbb3 right beside phpbb2, then you choose the convert tab on the install page and click the button and that is it.
For grins, I tried it - installed phpBB3 alongside phpBB2, and 'clicked the button'. Error after error popped up, all having to do with read/write permissions on avatar directories, or missing gifs, and the like. I suspect its because I don't have avatars (or anything related to it) implemented on my phpBB2 board (which is used by many hundreds of people with advanced degrees in rather technical math/stats - who as a demographic aren't prone to be interested in avatars and the like...). I'll start a different topic on conversion issues, but thought I'd remind everyone that 'click..and thats it' is almost never true in practice.