back-ups samenvoegen

[eddie4]

Wat is het probleem? Een admin heeft bijna alle post verwijderd
phpBB versie: 2.0.22

Een admin heeft de prune optsie gebruikt en heeft zo 90% van de posts weg gehaald. Nu heb ik backups maar die zijn meer dan een maand oud is het mogelijk om deze samen te voegen met de bestaande.

dit zou mogelijk moeten zijn aangezien autonummering in de database door loopt.


Kenis gebied
sql skills 1
phpmyadmin skills 6
phpbb kennis 7
algemeende database kenis 4

[eddie4]

Ik zie zonet dat de database die ge pruned is groter is dan mijn backup. Dus staat de informatsie er nog in?

EDIT

spijtig genoeg zie ik in phpmyadmin dat phpbb_posts_text weldegelijk kleiner is

[eddie4]

Opgelost met data base merge

Code: Selecteer alles

<?php

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

$dbhost = 'localhost';

$dbuser = '';
$dbpasswd = 'you_wish';

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

$dbname1 = 'forumnew';
$table_prefix1 = 'phpbb_';

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


# 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",
   "banlist", // This one could be converted
   "disallow",
   "search_results",
   "search_wordlist",
   "search_wordmatch",
   "sessions",
   "smilies",
   "themes",
   "themes_name",
   "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()
   ),
   "groups" => array(
      "group_id",
      array(
         array("user_group", "group_id"),
         array("auth_access", "group_id")
      )
   ),
   "posts" => array(
      "post_id",
      array(
         array("posts_text", "post_id"),
         array("topics", "topic_first_post_id"),
         array("topics", "topic_last_post_id")
      )
   ),
   "privmsgs" => array(
      "privmsgs_id",
      array(
         array("privmsgs_text", "privmsgs_text_id"),
         array("users", "user_last_privmsg")
      )
   ),
   "topics" => array(
      "topic_id",
      array(
         array("posts", "topic_id"),
         array("topics_watch", "topic_id"),
         array("vote_desc", "topic_id")
      )
   ),
   "users" => array(
      "user_id",
      array(
         array("user_group", "user_id"),
         array("groups", "group_moderator"),
         array("posts", "poster_id"),
         array("topics", "topic_poster"),
         array("privmsgs", "privmsgs_to_userid"),
         array("privmsgs", "privmsgs_from_userid"),
         array("topics_watch", "user_id"),
         array("vote_voters", "vote_user_id")
      )
   ),
   "ranks" => array(
      "rank_id",
      array(
         array("users", "user_rank")
      )
   ),
   "vote_desc" => array(
      "vote_id",
      array(
         array("vote_voters", "vote_id"),
         array("vote_results", "vote_id")
      )
   )
);

$bla_tables = array(
   "auth_access",
   "user_group",
   "posts_text",
   "privmsgs_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";
   }
}

print "Merging users (username and either password or email are the same).<br />";
$sql = "
   SELECT
      u1.user_id as id1,
      u2.user_id as id2,
      u1.username
   FROM
      " . USERS_TABLE . " u1,
      " . USERS_TABLE . " u2
   WHERE
      u1.username = u2.username
      && (u1.user_password = u2.user_password
         || u1.user_email = u2.user_email)
      && u1.user_id != u2.user_id
      && u1.user_id < u2.user_id";
if(!$result = $db->sql_query($sql))
{
    message_die(GENERAL_ERROR, 'Could not query for double user records.', '', __LINE__, __FILE__, $sql);
}

print "<table cellpadding='0' cellspacing='0'>";
while($row = $db->sql_fetchrow($result))
{
   print "<tr><td> ".$row['id1']."&nbsp;</td><td> ".$row['id2']."&nbsp;</td><td> ".$row['username']."&nbsp;</td><td> ";
   merge_users($row['id1'], $row['id2']);
   print " </td></tr>\n";
}
print "</table>";


function merge_users($user_id1, $user_id2)
{
   global $db;
   global $shift_tables;
   global $table_prefix1;
   
   $user_deps = $shift_tables['users'][1];

   // The users table should be skipped and the user_posts column should be updated.
   
   foreach($user_deps as $key => $value)
   {
      $d_table = $value[0];
      $d_column = $value[1];
      $sql = "UPDATE $table_prefix1$d_table SET $d_column = $user_id1 WHERE $d_column = $user_id2";
      if(!$result = $db->sql_query($sql))
      {
          message_die(GENERAL_ERROR, 'Could not update user_id.', '', __LINE__, __FILE__, $sql);
      }
   }
   
   $sql = "DELETE FROM " . $table_prefix1 . "users WHERE user_id = $user_id2";
   if(!$result = $db->sql_query($sql))
   {
       message_die(GENERAL_ERROR, 'Could not delete user2.', '', __LINE__, __FILE__, $sql);
   }

   print "OK";
   return;
}

function double_users()
{
   global $db;
   global $table_prefix1;

   $users_table = $table_prefix1 . "users";

   $sql = "SELECT user_id, ";
}

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";

?>

[colintjuh1]

Heb je de admin die dit gedaan heeft al verwijderd of zijn rechten afgenomen?

[eddie4]

die ligt al tussen 6 planken ja

[Ramon Fincken]

mag ik vragen waar je die mod vandaan hebt ? ik ken m niet ??

is het een van deze?
http://david.smigit.com/mdd/?keyword=me ... opment=yes