From 28a0209ffdf7d9c2ba8514def9a9085650a389fb Mon Sep 17 00:00:00 2001 From: Habeas Codice Date: Thu, 20 Nov 2014 12:41:48 -0800 Subject: fix driver issue for large dbs add rudimentary resume --- install/migrate-mypg.php | 101 +++++++++++++++++++++++++++++++++++------------ 1 file changed, 76 insertions(+), 25 deletions(-) (limited to 'install/migrate-mypg.php') diff --git a/install/migrate-mypg.php b/install/migrate-mypg.php index b7a7ced1b..10bf1e387 100644 --- a/install/migrate-mypg.php +++ b/install/migrate-mypg.php @@ -57,8 +57,10 @@ function run_sql($file, $db, &$err, &$n) { exit(); } foreach($sql as $stmt) { - if($stmt == '' || $stmt == "\n" || $stmt == "\n\n") + if($stmt == '' || $stmt == "\n" || $stmt == "\n\n") { + $c--; continue; + } $r = $db->exec($stmt); if($r===false) { echo "\nError executing $stmt: ".var_export($db->errorInfo(), true)."\n"; @@ -109,7 +111,18 @@ foreach(array('install','include','mod','view') as $dir) { } } -$cfg = parse_htconfig('.htconfig.php'); +$cfgfile = '.htconfig.php'; +if($argv[1] == '--resume') { + if($argc < 4) { + echo "Resume usage {$argv[0]} --resume \n"; + exit(); + } + $starttable = $argv[2]; + $startrow = $argv[3]; + $cfgfile = '.htconfig.php-mysql'; +} + +$cfg = parse_htconfig($cfgfile); $type = get_configtype($cfg); if($type != 'mysql') { echo "Error. Must start with standard mysql installation in .htconfig.php.\n"; @@ -125,6 +138,10 @@ try { exit(); } +// mysql insists on buffering even when you use fetch() instead of fetchAll() for some stupid reason +// http://stackoverflow.com/questions/6895098/pdo-mysql-memory-consumption-with-large-result-set +$mydb->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); + if(!file_exists('.htconfig.php-pgsql')) { echo "Enter postgres server info:\n"; $p['host'] = get_data("Hostname: ", '/[\w.]+/'); @@ -181,29 +198,38 @@ $H = "\033[0;35m"; $W = "\033[1;37m"; $M = "\033[1;31m"; $N = "\033[0m"; -$r = ask_question("Ready to migrate {$W}Red{$M}(#){$W}Matrix$N from mysql db @$B{$cfg['host']}$N/$B{$cfg['data']}$N to postgres db @$B{$pcfg['host']}$N/$B{$pcfg['data']}$N. + +if(isset($starttable)) { + $r = ask_question("Ready to migrate {$W}Red{$M}(#){$W}Matrix$N from mysql db @$B{$cfg['host']}$N/$B{$cfg['data']}$N to postgres db @$B{$pcfg['host']}$N/$B{$pcfg['data']}$N. + +Resuming failed migration ({$M}experimental$N) starting at table '$starttable' row $startrow. +Are you ready to begin (N/y)? ", + array('y', 'n'), + 'n' + ); + if($r == 'n') + exit(); +} else { + $r = ask_question("Ready to migrate {$W}Red{$M}(#){$W}Matrix$N from mysql db @$B{$cfg['host']}$N/$B{$cfg['data']}$N to postgres db @$B{$pcfg['host']}$N/$B{$pcfg['data']}$N. The site will be disabled during the migration by moving the $H.htconfig.php$N file to $H.htconfig.php-mysql$N. If for any reason the migration fails, you will need to move the config file back into place manually before trying again. Are you ready to begin (N/y)? ", array('y','n'), 'n' -); + ); -if($r == 'n') - exit(); + if($r == 'n') + exit(); -rename('.htconfig.php', '.htconfig.php-mysql'); + rename('.htconfig.php', '.htconfig.php-mysql'); -run_sql('install/schema_postgres.sql', $pgdb, $err, $n); -if($err) { - echo "There were $err errors creating the pgsql schema. Unable to continue.\n"; -/* $r = ask_question("There were $err errors (and $n ok) creating the schema. This shouldn't happen and continuing will probably fail. -Do you want to continue migration anyway (N/y)? ", array('y','n'), 'n' - ); - if($r == 'n')*/ + run_sql('install/schema_postgres.sql', $pgdb, $err, $n); + if($err) { + echo "There were $err errors creating the pgsql schema. Unable to continue.\n"; exit(); -} + } -echo "pgsql schema created. $n queries executed successfully.\n"; + echo "pgsql schema created. $n queries executed successfully.\n"; +} $res = $pgdb->query("select relname, attname, pg_type.typname from ((pg_attribute inner join pg_class on attrelid=pg_class.oid) inner join pg_type on atttypid=pg_type.oid) inner join pg_namespace on relnamespace=pg_namespace.oid where nspname='public' and atttypid not in (26,27,28,29) and relkind='r' and attname <> 'item_search_vector';"); if($res === false) { @@ -215,9 +241,23 @@ $schema = array(); while(($row = $res->fetch()) !== false) $schema[$row[0]][$row[1]] = $row[2]; +$res = $pgdb->query("select relname, attname from pg_attribute inner join pg_class on attrelid=pg_class.oid inner join pg_constraint on conrelid=pg_class.oid and pg_attribute.attnum = any (conkey) where contype='p';"); +if($res === false) { + echo "Error reading back primary keys. Unable to continue.\n"; + var_export($pgdb->errorInfo()); + exit(); +} +$pkeys = array(); +while(($row = $res->fetch()) !== false) + $pkeys[$row[0]] = $row[1]; + $err = 0; $n = 0; $reserved = array('ignore','key','with'); foreach($schema as $table=>$fields) { + if(isset($starttable) && !$n && $table != $starttable) { + echo "Skipping table $table\n"; + continue; + } $fnames = array_keys($fields); $pfnames = array_keys($fields); @@ -231,14 +271,16 @@ foreach($schema as $table=>$fields) { $pfname = '"' . $pfname . '"'; $pfstr = implode(',', $pfnames); - $res = $mydb->query("SELECT count(*) FROM $table;"); - if($res === false) { + $cres = $mydb->query("SELECT count(*) FROM $table;"); + if($cres === false) { echo "Fatal error counting table $table: ".var_export($mydb->errorInfo(), true)."\n"; exit(); } - $nrows = $res->fetchColumn(0); + $nrows = $cres->fetchColumn(0); + $cres->closeCursor(); + if(!$nrows) { - echo "\nTABLE $table has 0 rows in mysql db.\n"; + echo "TABLE $table has 0 rows in mysql db.\n"; continue; } @@ -246,10 +288,17 @@ foreach($schema as $table=>$fields) { for($x=0, $c=count($fields); $x < $c; $x++) $pstr .= ($x ? ',?' : '?'); - $crow = 0; + if(isset($starttable) && $table == $starttable) { + $selectsql = "SELECT $fstr FROM $table ORDER BY {$pkeys[$table]} LIMIT $nrows OFFSET $startrow;"; + $crow = $startrow; + } else { + $selectsql = "SELECT $fstr FROM $table ORDER BY {$pkeys[$table]};"; + $crow = 0; + } + echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K"; - - $res = $mydb->query("SELECT $fstr FROM $table;"); + + $res = $mydb->query($selectsql); if($res === false) { echo "Fatal Error importing table $table: ".var_export($mydb->errorInfo(), true)."\n"; exit(); @@ -273,14 +322,16 @@ foreach($schema as $table=>$fields) { $r = $istmt->execute(); if($r === false) { $err++; - echo "\nInsert error: ".var_export(array($pgdb->errorInfo(), $table, $fields, $row), true)."\n"; - //exit(); + echo "Insert error: ".var_export(array($pgdb->errorInfo(), $table, $fields, $row), true)."\nResume with {$argv[0]} --resume $table $crow\n"; + exit(); } else $n++; $crow++; if(($crow % 10) == 0 || $crow == $nrows) echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K"; } + $res->closeCursor(); + echo "\n"; } echo "Done with $err errors and $n inserts.\n"; -- cgit v1.2.3