Large Dbase Conversion Problems

Converting from other board software? Good decision! Need help? Have a question about a convertor? Wish to offer a convertor package? Post here.
Suggested Hosts
Locked
clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Large Dbase Conversion Problems

Post by clublakers2 »

I'm stumped with timeout issues (apparently) when converting from phpBB2 to phpBB3.

The dbase 1.7 Gb, and timeouts occur at about the same place when filling table phpbb_posts:

index.php?mode=convert&sub=in_progress&tag=phpbb20&current_table=15&skip_rows=302261&language=en

These were tried and failed:

- Request managed host to increase max parameters to avoid timeouts
- Install_Convert file edit: http://muhdzamri.blogspot.com/2007/11/c ... hpbb3.html
- Local Conversion (with above revision): http://www.phpbb.com/kb/article/offline-conversions/

Any other recommendations for large dbases?

Thanks in advance.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I'm in the same boat, but my DB is only about 400MB. I too get stuck at phpbb_posts and now all I can do is time out over and over again. I am stuck around skip_rows=254k and I am doing an offline conversion

I have not tried the "Install_Convert file edit" link that you supplied, so I am giving that a try now on a new conversion w/o the page refresh enabled. I've spend about 6 hours on this ridiculous conversion so far. I can't even imagine what kind of aggravation can come up w/ a 1.7G db conversion.

Image

clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Re: Large Dbase Conversion Problems

Post by clublakers2 »

I'll let you know if I find a way, please do the same.

I just tried pruning the dbase down on a test domain, thinking I may have to give up on retaining our history. That crashed the dbase, so perhaps that isn't a solution either.

Anyone else find a way to get past row 250k?

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

With the "Install_Convert file" hack, I was only converting about 200 rows at a time when I had
@ini_set('max_execution_time', '-1') in the code suggested to add to install_convert file. Once I commented it out I was back to about 2000.

I'm going through the no-refresh so the converter is taking a long time (convert 2k rows, click, convert, etc.). So far the only error I have seen was about copying 1 smiley over from phpbb2.

*edit*
This is getting unbelievable. There is no way to re-enable the refresh w/o restarting and I have 650k rows to click-click-click. I had to restart the converter a 4th time.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I am still not having any success. You would hope that it wouldn't take 14 hours for a conversion only to end up at another stall. Now I am about 255k into _posts and I am not proceeding any further.

clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Re: Large Dbase Conversion Problems

Post by clublakers2 »

ChocoboBop wrote:With the "Install_Convert file" hack, I was only converting about 200 rows at a time when I had
@ini_set('max_execution_time', '-1') in the code suggested to add to install_convert file. Once I commented it out I was back to about 2000.

I'm going through the no-refresh so the converter is taking a long time (convert 2k rows, click, convert, etc.). So far the only error I have seen was about copying 1 smiley over from phpbb2.

*edit*
This is getting unbelievable. There is no way to re-enable the refresh w/o restarting and I have 650k rows to click-click-click. I had to restart the converter a 4th time.
ha, yeah, i tried the manual conversion for about 8 minutes before I came to the same conclusion. I've restarted the process so many times now that one more restart didn't phase me.

This is my 3rd and last weekend of trying to convert to phpbb3. I'll either stick with phpbb2 forever, or pay someone to migrate it for me. If any clever mysql wizards out there needs beer money, please pm me.

Currently I'm trying to prune the backup board locally so I can see something (anything) convert so we can test the new forums... working through more timeout problems, but making slow progress...

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I'm about to give up converting to version 3. This is an ongoing issue with end users and there's no encouraging words at all from the developers. Granted they're under no obligation but you would think if they want their existing user base to convert they would of made it quite a bit simpler.

I made one last effort to convert my db today. After 8 hours of conversion I reached the 255k rows on _posts and timed out again. My cpu utilization before that moment was up and down with each run of the script until to the point where it tried to continue on past 255k, where the cpu utilization went up to 100% (sucking up 25MB of ram) and did not drop at all until the timeout occurred.

I've tried adjusting php settings, conversion script batch sizes, mysql settings, db types, and so on but there has been absolutely no positive impact. I've now wasted a whole weekend on this project (and ended up with one angry spouse) only to come out with nothing to show for it except frustration.

The only thing I can think of is to basically dump enough posts on my forum to get below the magical 255k number, and that pretty much defeats the idea of converting the existing forum to begin with.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I have got my conversion script to move on.

I was reading through the [BETA] SMF 1.1 to phpBB3 Convertor topic and noticed this little comment:
A_Jelly_Doughnut wrote: It is very difficult to find out what is causing a problem like this. Often, it is a post with a lot of BBCode or smilies that will cause the script to stall -- something like 1,000 smilies at a time.
So I wrote a little hacky php script that allowed me to pull up post _text records from the phpbb2's phpbb_posts_text table by giving it a starting post_id and an ending post_id. Not to be used on a live server of course.

I then looked at phpbb3's phpbb_posts table to get the last post_id the conversion script was able to convert. With that id I put it as the starting id in the script, and then put in an id about 1000 numbers later as the end point for the range. After I ran my hacky script I found a post topic where someone has posted hundreds of the "twisted" similies.

I then went into the phpbb2 db, looked up the problem post_id from phpbb_posts_text table, edited out all of the smilies, and then started the phpbb3 conversion script again.

It freaking worked. I am now currently at row 263000+ and continuing as intended. The phpbb3 script has stopped timing out.

I posted the code below if it'll help you out as well. It isn't anything fancy or graphical (as I was more interested at the time to get it to work rather than what it looks like), and it probably should not be used on a live server, but hopefully it'll help you find your problem topic as well. You just have to plug in your system variables, save the file in a web directory, and run it to bring up the form where you can type in your post_id range.

Code: Select all

<?php

// Looks for problem post_text fields in phpbb2 that might be timing 
// out the phpbb3 converter. Keep an eye out for excess smilies or 
// bbcode tags in the post_text. If you find a post_id that matches 
// the symptoms, edit the post in the phpbb2 posts_text table and 
// start the phpbb3 converter again.
//
// To be used for off-line converters only, as it is not secured 
// against explotation. I take no responsibility if you choose to 
// ignore this warning.
//
//

if(isset($_POST['start']) && isset($_POST['end'])){

	$dbh = mysql_connect("localhost", "(your db user name)", "(your db password)") or die ('I cannot connect to the database because: ' . mysql_error());
	mysql_select_db("(your phpbb2 database)", $dbh);

	$query = "SELECT * FROM phpbb_posts_text WHERE post_id BETWEEN " . $_POST['start'] . " AND " . $_POST['end'];
	$results = mysql_query($query) or die(mysql_error());

	echo "<table border=\"1\" cellpadding=\"3\" cellspacing=\"5\"><tr><th>post_id</th><th>bbcode_uid</th><th>post_subject</th><th>post_text</th></tr>\n";

	while($row = mysql_fetch_array($results, MYSQL_ASSOC)){
		echo "<tr><td>" . $row['post_id'] . "</td><td>" .$row['bbcode_uid'] . "</td><td>" . $row['post_subject'] . "</td><td>" . $row['post_text'] . "</td></tr>\n\n";
	}

	echo "</table>";

	mysql_close();
	}
else{
	?>
	<html>
	<head>
	<title>Post text reader</title>
	</head>
	<body>
	<form method="post" action="<?php echo $PHP_SELF ?>">
	Start: <input type="text" name="start"><br />
	End: <input type="text" name="end"><br />
	<input type="submit" name="submit" value="Enter information">
	</form>
	</body>
	</html>
	<?php
}
?>
I am making a note of the problem post_id's from phpbb2's phpbb_posts_text table, as I am going to have to restart my whole conversion again next weekend. Since I couldn't get it to work at all last weekend I had to unlock my live form and now there's more data in the database, but next weekend it is looking promising to do a full conversion finally :D

clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Re: Large Dbase Conversion Problems

Post by clublakers2 »

Great news! I'm out of town until the weekend... I'll give this a shot saturday morning. Holler back if you get the entire conversion to work, I'll do the same.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I have come to report that my little 400MB db is now converted 8-) (minus the search index of course, which is the next step). I had 1 post from phpbb2's _posts_text table and 14 from _privmsgs_text that caused the script to time out because of excessive smilies, but a simple edit via phpmyadmin fixed the problem.

Getting phpbb3 on a live environment looks to be possible now. I'm going to attempt another upgrade this weekend now that my conversion script time out issue is resolved.

Having $batch_size set to 2000 and $num_wait_rows set for 10 is something I wouldn't do again :lol: It took over 24 hours to convert the whole database. I'll be bumping those numbers up the next time I go through the conversion script.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I have finally converted my database w/o any errors or time-outs. It took 15 and a half hours to convert the 408MB db file, and I haven't even gotten to the point of uploading it back up to the live site yet.

This time I had $batch_size set to 6000 and $num_wait_rows set at 60. It still took longer than I had hoped, but luckily I only have to do this once.

clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Re: Large Dbase Conversion Problems

Post by clublakers2 »

Well done, thanks for the update. I will attempt to do the same tomorrow... and the next day, and the next day...

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

I am not in the process of rebuilding my search index and am back to having problems :lol: Just converting the posts was apparently only half of the conversion process.

Trying to rebuild the search on the live db did not work at all, and I'm just getting a blank page when I try it offline. Looks like it's time to do some more detective work.

*edit*

All seems well again. It appears even if you get a blank page, the script is working in the background.

clublakers2
Registered User
Posts: 13
Joined: Mon Oct 09, 2006 3:16 am

Re: Large Dbase Conversion Problems

Post by clublakers2 »

That's good news... my computer crashed and caused all kinds of wonderful errors to the dbase. I took the opportunity to increase the # of rows to 6,000 and started the test over again. Moving much faster now, already to 50k posts.

Previously I got to 2.1 million rows before crashin, and that took 2 full days. When I found an error, I took the liberty of just bulk-deleting 2,000 rows from the dbase at a time -- I don't think anyone will notice them missing from millions of posts. :)

Oh, I ran your script from fixme.php and received this error:
You don't have permission to access /phpBB2/<br /><b>Notice</b>: Undefined variable: PHP_SELF in <b>c:\program files\easyphp1-8\www\phpbb2\fixme.php</b> on line <b>40</b><br /> on this server.
I'm pretty sure I set the dbase variables correctly (using the local conversion method). If you are able to identify where I might have gone wrong, this might be useful to everyone else too.

Here are the contents of the page that received the error:

Code: Select all

<?php

// Looks for problem post_text fields in phpbb2 that might be timing
// out the phpbb3 converter. Keep an eye out for excess smilies or
// bbcode tags in the post_text. If you find a post_id that matches
// the symptoms, edit the post in the phpbb2 posts_text table and
// start the phpbb3 converter again.
//
// To be used for off-line converters only, as it is not secured
// against explotation. I take no responsibility if you choose to
// ignore this warning.
//
//

if(isset($_POST['start']) && isset($_POST['end'])){

   $dbh = mysql_connect("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error());
   mysql_select_db("test", $dbh);

   $query = "SELECT * FROM phpbb_posts_text WHERE post_id BETWEEN " . $_POST['start'] . " AND " . $_POST['end'];
   $results = mysql_query($query) or die(mysql_error());

   echo "<table border=\"1\" cellpadding=\"3\" cellspacing=\"5\"><tr><th>post_id</th><th>bbcode_uid</th><th>post_subject</th><th>post_text</th></tr>\n";

   while($row = mysql_fetch_array($results, MYSQL_ASSOC)){
      echo "<tr><td>" . $row['post_id'] . "</td><td>" .$row['bbcode_uid'] . "</td><td>" . $row['post_subject'] . "</td><td>" . $row['post_text'] . "</td></tr>\n\n";
   }

   echo "</table>";

   mysql_close();
   }
else{
   ?>
   <html>
   <head>
   <title>Post text reader</title>
   </head>
   <body>
   <form method="post" action="<?php echo $PHP_SELF ?>">
   Start: <input type="text" name="start"><br />
   End: <input type="text" name="end"><br />
   <input type="submit" name="submit" value="Enter information">
   </form>
   </body>
   </html>
   <?php
}
?>

Thanks again for leading the way here... will report back when I get further.

User avatar
ChocoboBop
Registered User
Posts: 144
Joined: Wed Sep 01, 2004 6:10 pm

Re: Large Dbase Conversion Problems

Post by ChocoboBop »

PHP_SELF is just the name of the file. You can fix it by finding

Code: Select all

 <form method="post" action="<?php echo $PHP_SELF ?>">
and replace it with

Code: Select all

 <form method="post" action="fixme.php">
The script just pulls the post data out of the database so you can review the full text of the posts. It's mainly for you to review and see which post is specifically full of smiley-junk so you can edit the post manually in the db. It doesn't really fix much of anything :)

My live forum is now completely converted over to phpbb3 and all seems to be fine, minus a few custom template/mod hiccups that I need to fixed.

Locked

Return to “[3.0.x] Convertors”