Junction of Forum's

The 2.0.x discussion forum has been locked; this will remain read-only. The 3.0.x discussion forum has been renamed phpBB Discussion.
alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Mon Apr 11, 2005 7:33 pm

Here's a weird one for you guys:
I just ran the phpbb merge on a test copy of my db, and it seemed to do the merge ok, except for a couple things:
1) The forum post count seems to go DOWN with each merge
2) Forum last post id is not showing anything for the merged db's

Now, I should note I use a modified version of this script, because I don't want to merge users, just forums.
<?php

$dbms = 'mysql';
$phpbb_root_path = '../m3f/';

$dbhost = 'localhost';

$dbuser = 'dbusername';
$dbpasswd = 'userpw';

// Name and prefix for the database that should keep the original IDs

$dbname1 = 'dbname';
$table_prefix1 = 'phpbb_';

// Name and prefix for the database that is going to be added
// to DB1.
$dbname2 = 'dbname2';
$table_prefix2 = 'phpbb2_';


# NO NEED TO MODIFY CODE BELOW
# MODIFY ONLY IF U KNOW WHAT U R DOING


define('IN_PHPBB', true);
// Use DB1 for the initial config table etc.
$dbname = $dbname1;
$table_prefix = $table_prefix1;
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'includes/constants.'.$phpEx);
include($phpbb_root_path . 'includes/db.'.$phpEx);


// These tables can be dropped from DB2 (not used in this script)
$drop_tables = array(
"config",
"search_results",
"search_wordlist",
"search_wordmatch",
"sessions",
"words"
);

// All tables in DB2 that should shift ID.
// - table that needs shifting (categories)
// - id in table (cat_id)
// nested array:
// - table that depends on id (forums)
// - id that corresponds to id in original table (cat_id)
$shift_tables = array(
"categories" => array(
"cat_id",
array(
array("forums", "cat_id")
)
),
"forums" => array(
"forum_id",
array(
array("posts", "forum_id"),
array("topics", "forum_id"),
array("forum_prune", "forum_id"),
array("auth_access", "forum_id")
)
),
"forum_prune" => array(
"prune_id",
array()
),
"posts" => array(
"post_id",
array(
array("posts_text", "post_id"),
array("topics", "topic_first_post_id"),
array("topics", "topic_last_post_id")
)
),
"topics" => array(
"topic_id",
array(
array("posts", "topic_id"),
array("topics_watch", "topic_id"),
array("vote_desc", "topic_id")
)
),
"vote_desc" => array(
"vote_id",
array(
array("vote_voters", "vote_id"),
array("vote_results", "vote_id")
)
)
);

$bla_tables = array(
"auth_access",
"posts_text",
"topics_watch",
"vote_results",
"vote_voters"
);


// Traverse the shift_tables array

foreach($shift_tables as $key => $value)
{
$table = $key;
$merge_tables[$table] = 0; // keep an array with all tables that need merging
$column = $value[0]; // Column with ID that needs to be shifted
$ref = $value[1]; // Tables that are using the mentioned ID.
print "Shifting IDs in table $table<br />\n";
$max = shift_ids($table, $column);
flush();
// Do the dependent tables
foreach($ref as $key => $value)
{
$d_table = $value[0];
$merge_tables[$d_table] = 0;
$d_column = $value[1];
print "&nbsp; Altering dependent table: $d_table : $d_column (offset = $max)<br />\n";
flush();
shift_ids($d_table, $d_column, $max);
}

print "<br />\n";
flush();
}

foreach($merge_tables as $table => $value)
{
print "Merging $table table: ";
if(merge_tables($table))
{
print " OK<br />\n";
}
else
{
print " FAILED!<br />\n";
}
}

function merge_tables($table)
{

echo "<H1>Merging table : $table </H1>";

global $db;
global $dbname1, $table_prefix1, $dbname2, $table_prefix2;


$sql = "SHOW FIELDS FROM $table_prefix1$table";
if(!$result = $db->sql_query($sql))
{
message_die(GENERAL_ERROR, 'Could not get field info from $table.', '', __LINE__, __FILE__, $sql);
}

$fields = array();
while($row = $db->sql_fetchrow($result))
{
$fields[] = $row['Field'];
}
$fieldlist = implode($fields, ', ');


if($table == 'users')
{
$where = " WHERE $dbname2.$table_prefix2" . $table . ".user_id > 0";
}
else
{
$where = '';
}

echo "$table";

$sql = "INSERT INTO $dbname1.$table_prefix1".$table." ($fieldlist) SELECT $fieldlist from $dbname2.$table_prefix2" . $table . $where;
if(!$db->sql_query($sql))
{
message_die(GENERAL_ERROR, 'Could not merge $table.', '', __LINE__, __FILE__, $sql);
}

return TRUE;
}


// Shift all ID's in column $id in table $table in
// database 2 by MAX($id) or (if not 0) by $offset
function shift_ids($table, $id, $offset = 0)
{
global $db;
global $dbname1, $table_prefix1;
global $dbname2, $table_prefix2;

// Offset hasn't been given, we're going to figure it out ourselfs
if($offset == 0)
{
if(!$offset = getmax($dbname1, $table_prefix1.$table, $id))
{
// Empty table, no need to shift IDs
print "Empty table? Skipping...<br />\n";
return;
}
}

// What's the max_id in the current table?
$max2 = getmax($dbname2, $table_prefix2.$table, $id);
$max2 = intval($max2); // Make sure that max2 contains a number, make it 0 if this table is empty.

// First we add the offset + the max of the current table
// Treat values of 0 and lower as special values.
$sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id + $max2 + $offset WHERE $id > 0";
print "$sql<br />\n";
if(!$result = $db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
}

// Then we subtract the max of the current table again.
// We do this to prevent problems with key constrains from happening
// i.e. if we do id=id+20 on key 1 when key 21 already exists we would get an error
$sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id - $max2 WHERE $id > 0";
print "$sql<br />\n";
if(!$result = $db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
}

return $offset;
}

function getmax($dbname, $table, $id)
{
global $db;
$sql = "SELECT MAX($id) as max_id FROM $dbname." . $table;
if(!$result = $db->sql_query($sql) )
{
message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
}
if($max = $db->sql_fetchrow($result))
{
return($max['max_id']);
}
else
{
// Probably no rows where returned.. Empty table.
return FALSE;
}
}


function message_die($msg_code, $msg_text = '', $msg_title = '', $err_line = '', $err_file = '', $sql = '')
{
global $db, $template, $board_config, $theme, $lang, $phpEx, $phpbb_root_path, $nav_links, $gen_simple_header;
global $userdata, $user_ip, $session_length;
global $starttime;

$sql_store = $sql;

//
// Get SQL error if we are debugging. Do this as soon as possible to prevent
// subsequent queries from overwriting the status of sql_error()
//
$sql_error = $db->sql_error();

$debug_text = '';

if ( $sql_error['message'] != '' )
{
$debug_text .= '<br /><br />SQL Error : ' . $sql_error['code'] . ' ' . $sql_error['message'];
}

if ( $sql_store != '' )
{
$debug_text .= "<br /><br />$sql_store";
}

if ( $err_line != '' && $err_file != '' )
{
$debug_text .= '</br /><br />Line : ' . $err_line . '<br />File : ' . $err_file;
}

print $debug_text;

exit;
}

echo "Done";

?>

encryption
Registered User
Posts: 392
Joined: Tue Jul 08, 2003 2:07 pm
Contact:

Post by encryption » Tue Apr 12, 2005 11:04 am

in my case too, I did notice that the post count went down but that was also because a lot of the posts on the forums I wanted to merge were from the same board that was originally split up. Now I am not sure if that created a problem and how but am sure it did. Though I didnt sweat a whole lot over it.

Additionally, you will have to run both these mods on here to ensure that post counts and forums are resynced. You will defo see a chance in post numbers after this

http://www.phpbbhacks.com/searchresults ... rch_type=1


-e-

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Tue Apr 12, 2005 3:04 pm

Hmm, didn't work on the test I just did, but going to run a new test to make sure.

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Tue Apr 12, 2005 3:23 pm

Well, the sync tools did fix the last post counts, but didn't fix the board pc.

HOWEVER, I simply posted 2 test posts (1 new topic, 1 post in another topic) and everything seemed to right itself:
Original: Our users have posted a total of 234619 articles in 16999 topics
After Merge: Our users have posted a total of 234619 articles in 17373 topics
After 2 posts: Our users have posted a total of 236253 articles in 17374 topics

http://development.fmvperformance.com/m3f/

Now I'm just hoping nothing screws up, because if I do this on the live board and posts start getting overwritten or something...

encryption
Registered User
Posts: 392
Joined: Tue Jul 08, 2003 2:07 pm
Contact:

Post by encryption » Tue Apr 12, 2005 4:56 pm

did you run the resync mods ?

-e-

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Tue Apr 12, 2005 4:59 pm

encryption wrote: did you run the resync mods ?

-e-


Yep, that's whta I meant by:
alphadan wrote: Well, the sync tools did fix the last post counts, but didn't fix the board pc.


Oh, and myb ad - that should read "did fix the last forum post id's"

encryption
Registered User
Posts: 392
Joined: Tue Jul 08, 2003 2:07 pm
Contact:

Post by encryption » Tue Apr 12, 2005 10:30 pm

I meant the second time around..... odd its giving that error, since you'de edited it a bit, not sure whats causing the prob since I am not a php programmer

-e-

kiltannen
Registered User
Posts: 17
Joined: Fri May 09, 2003 2:44 am
Contact:

Post by kiltannen » Thu Apr 28, 2005 1:53 am

I just wanted to report that I have successfully merged two forums - I now have 2100 users 68302 posts 3424 topics.

It went reasonably smoothly - but i am GLAD I testeds first. It was only after trying it 2 or 3 times that I figured out I needed EXACTLY the same fields in the user table - and I had installed a whole bunch of hacks + some fields of my own in there...
The wonderful thing about not planning,
is that failure comes as a complete surprise,
and is not preceded by a period of worry or depression.

Check out my book database
http://www.ibdof.com

encryption
Registered User
Posts: 392
Joined: Tue Jul 08, 2003 2:07 pm
Contact:

Post by encryption » Thu Apr 28, 2005 4:08 pm

correct me if I am wrong but MySQL has a caching problem so if you create a new DB and the merge doesnt work as per your expectations, DO NOT perform the merge in the same database again. I experienced abnormal results i.e. forums pointing to the wrong cat_ID even though a diff cat_ID is listed in the phpbb_categories table....

anycase I just performed my 5th merge after having joined forces with an online radio station and it went smoothly as expected...

only one small prob I have noticed is that while the merging of the user tables checks for usernames and passwords, it doesnt compare email addresses which should also be included in the script because a few multiple users were created. Other than that... its flawless 8)

-e-

hlfritz
Registered User
Posts: 41
Joined: Sat Jun 29, 2002 7:59 pm
Location: San Diego
Contact:

Post by hlfritz » Mon May 16, 2005 3:23 am

used this script last week to merge 3 forums into one. worked great! i did find that i had to drop the table prefix as the script got confused as one of my databases had a prefix as well, and it just could not handle two databases with a prefix it seems. once i created the new installation database without a prefix everything worked great.

a couple anomolies are:

-sometimes threads are not in proper date order within a forum.

-some users post counts are not correct.

i have yet to install and run the two hacks mentiuoned earlier in this thread to fix that problem, and am wondering if there is any way around doing that?
Helmut

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Thu May 19, 2005 7:58 pm

Why wouldn't you want to run the hacks? THey're pretty straightforward.




Anyway, I thought I had fixed the script - it was working - but now on a new test db, I'm having problems:
Warning: main(../extension.inc): failed to open stream: No such file or directory in /home/develope/public_html/v2phpmerge.php on line 30

Warning: main(): Failed opening '../extension.inc' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/develope/public_html/v2phpmerge.php on line 30

Warning: main(../includes/constants.): failed to open stream: No such file or directory in /home/develope/public_html/v2phpmerge.php on line 31

Warning: main(): Failed opening '../includes/constants.' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/develope/public_html/v2phpmerge.php on line 31

Warning: main(../includes/db.): failed to open stream: No such file or directory in /home/develope/public_html/v2phpmerge.php on line 32

Warning: main(): Failed opening '../includes/db.' for inclusion (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/develope/public_html/v2phpmerge.php on line 32
Shifting IDs in table categories

Fatal error: Call to a member function on a non-object in /home/develope/public_html/v2phpmerge.php on line 238

hlfritz
Registered User
Posts: 41
Joined: Sat Jun 29, 2002 7:59 pm
Location: San Diego
Contact:

Post by hlfritz » Thu May 19, 2005 8:09 pm

because i work 12-16 hours a day and am really busy, and even small stuff is a huge additional workload for me. :(

plus i do not know if they are hacks to the code, or are they just a couple of scripts to run? i really don't like adding non-standard stuff because of course it causes issues down the road with upgrades, etc. i know, i could look myself but see above... :)

that seems similar to what i got, but if i recall properly i had an error with something like this in it:

...phpbb_phpbb_...

which lead me to the idea that it did not like merging two databases with a prefix. so i removed it from the new to-be-merged-into database and it worked great then.

regardless, this script is great and i thank everyone that worked on it!!!
Helmut

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Thu May 19, 2005 9:16 pm

btw guys, I figured out my problems.

Also, those two scripts make barely any changes... they're really quite simple.

LeonSyn
Registered User
Posts: 11
Joined: Mon Apr 04, 2005 8:13 am
Contact:

Post by LeonSyn » Sun May 22, 2005 1:56 pm

Hey, just a quick question, no problems, just want to know if this'll work with PhpNuke?

alphadan
Registered User
Posts: 80
Joined: Wed Jul 30, 2003 10:26 pm

Post by alphadan » Mon May 23, 2005 10:25 pm

LeonSyn wrote: Hey, just a quick question, no problems, just want to know if this'll work with PhpNuke?


Personally, no idea.




OK guys, got another problem. I've had this problem with two different attempts, now. Everything goes fine until it gets to posts_text:
Merging table : posts_text
posts_text

SQL Error : 1062 Duplicate entry '292419' for key 1

INSERT INTO develope_betaphpbb2.phpbb_posts_text (post_id, bbcode_uid, post_subject, post_text) SELECT post_id, bbcode_uid, post_subject, post_text from develope_betaphpbb2.s40_posts_text

Line : 181
File : /home/mazdaown/public_html/v2phpmerge.php


Not sure why there's a duplicate entry problem?

Locked

Return to “2.0.x Discussion”