How to convert drupal 6 site from postgres to mysql I have a project that was set up on drupal 6 on postgres database as i always prefered it over Mysql. You can read more about it here
I am currently in progress of moving some stuff to dreamhost.com (which RULES!) and i finally have shell access and i can actually do stuff. The only problem is they dont support pgsql at all! LOL! So i have decided that it takes too much effort and time to maintain it.
Here are 3 simple steps to migrate your drupal installation (possibly other apps) from postgres to mysql.
Recreate the schema
First of all you need databases to have the same tables. You dont have to worry about minor differences like type names etc. Unfortunately there is no automatic way of doing it so you just have to install a new drupal setup using mysql. Then you go to modules and make sure all modules you have on pgsql are enabled on your mysql setup. Also make sure modules missing in pgsql setup are disabled in mysql setup.
Migrate the data
Once databases are ready you can use the convert.php scrip presented below.
Please note! You can use the script free of charge and you can modify it, but remember this is not a product nor a ready program in any way. Its not secure to use it and using of it might cause data loss or any other damage to your system i cant even imagine.
$my_host = '';
$my_user = '';
$my_pass = '';
$my_db = '';
$count_all = 0;
error_reporting ( E_ALL );
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
$pg_conn = pg_connect( "dbname='cake_artur' host='localhost' ".
"user='cake_artur' password='cake_artur'" );
$my_conn = mysql_connect( $my_host, $my_user, $my_pass );
if( !mysql_select_db($my_db) ){
echo "Unable to select mydbname: " . mysql_error();
exit;
}
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8'");
mysql_query("SET character_set_connection = 'utf8', character_set_database = 'utf8', ".
"character_set_server = 'utf8'");
$tables = array();
$res = mysql_query( 'show tables' );
while ($row = mysql_fetch_assoc($res)) {
$tables[] = array_pop($row);
}
foreach( $tables as $table ){
$columns = array();
$res = mysql_query( 'desc '.$table );
while ($row = mysql_fetch_assoc($res)) {
$columns[ $row['Field'] ] = $row['Type'];
}
$inserts = array();
$data_res = pg_query( "select * from ".$table.";" );
while( $row = pg_fetch_assoc($data_res) ){
$cols = '';
$data = '';
$joiner = '';
foreach( $row as $key => $val ){
$cols .= $joiner.$key;
// we need a hack if column is tinyint and we insert t/f then convert
if( $columns[$key] == 'tinyint(1)' && ($val == 't' || $val =='f') ){
if( $val == 't' ){
$data .= $joiner."1";
}elseif( $val == 'f' ){
$data .= $joiner."0";
}
}else{
$data .= $joiner."'".mysql_escape_string($val)."'";
}
// this will set joiner to , so only the first one does not have coma in front
$joiner = ",";
}
$inserts[] = "INSERT INTO ".$table." (".$cols.") VALUES (".$data.");";
}
mysql_query( "DELETE FROM $table;" );
foreach ( $inserts as $insert ){
mysql_query( $insert );
if( mysql_errno() != 0 ){
print_r( mysql_error() );
die();
}
if( $count_all % 101 == 0 ){
echo '.';
}
}
}
Compare the data
Moving data across is one thing but you have to make sure its all the same on both sides. You can use the script below to compare if data is equal. I made it just for my own usage so its very simple. It wont work well for big databases but it should work well for medium drupal setups.
Please note! You can use the script free of charge and you can modify it, but remember this is not a product nor a ready program in any way. Its not secure to use it and using of it might cause data loss or any other damage to your system i cant even imagine.
// configurateion for mysql database
$myHost = '';
$myUser = '';
$myPass = '';
$myDB = '';
$pgDB = '';
$pgHost = '';
$pgUser = '';
$pgPass = '';
$countFail = 0;
$countMatch = 0;
$countRowsMatched = 0;
$countRowsFailed = 0;
// make sure we know if anything goes wrong
error_reporting ( E_ALL );
ini_set("display_errors", 1);
ini_set("display_startup_errors", 1);
// connect to postgres
$pgConn = pg_connect( "dbname='".$pgDB."' host='".$pgHost."' user='".$pgUser.
"' password='".$pgPass."'" );
// connect to the mysql
$myConn = mysql_connect( $myHost, $myUser, $myPass );
if( !mysql_select_db($myDB) ){
echo "Unable to select mydbname: " . mysql_error();
exit;
}
mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8'");
mysql_query("SET character_set_connection = 'utf8', character_set_database = 'utf8', ".
"character_set_server = 'utf8'");
// create list of tables
$tables = array();
$res = mysql_query( 'show tables' );
while ($row = mysql_fetch_assoc($res)) {
$tables[] = array_pop($row);
}
// build all the get queries and order by PK so we get same rows order
$queries = array();
foreach( $tables as $table ){
$primaryKeys = array();
$secondaryKeys = array();
$res = mysql_query( 'desc '.$table.';' );
while($row = mysql_fetch_assoc($res)) {
if( $row['Key'] == 'PRI' ){
$primaryKeys[] = $row['Field'];
}
if( $row['Key'] == 'MUL' ){
$secondaryKeys[] = $row['Field'];
}
}
if( !empty($primaryKeys) ){
$order = implode(") ASC, md5(", $primaryKeys);
}else{
$order = implode(") ASC, md5(", $secondaryKeys);
}
$queries[ $table ] = "SELECT * FROM $table ORDER BY md5(".$order.") ASC;";
}
// process each query
foreach($queries as $table => $query){
echo "Checking table $table wihtL $query \n";
$myRes = mysql_query( "SELECT count(*) as ct FROM $table;" );
$pgRes = pg_query( "SELECT count(*) as ct FROM $table;" );
$myRow = mysql_fetch_assoc($myRes);
$pgRow = pg_fetch_assoc($pgRes);
echo "Rows count mysql: ".$myRow['ct'].", pgsql: ".$pgRow['ct']."\n";
$failures = array();
$countFail = 0;
$countMatch = 0;
$myRes = mysql_query( $query );
$pgRes = pg_query( $query );
do{
$rowFailed = false;
$myRow = mysql_fetch_assoc($myRes);
$pgRow = pg_fetch_assoc($pgRes);
// if we have
if( $myRow && $pgRow ){
foreach($myRow as $col => $val){
if( $myRow[$col] != $pgRow[$col] ){
$rowFailed = true;
}
}
}elseif($myRow || $pgRow){
$rowFailed = true;
}else{
break;
}
// counters
if($rowFailed == true){
$failures[] = array('my' => $myRow, 'pg' => $pgRow);
$countFail++;
}else{
$countMatch++;
}
}while( $pgRow && $myRow );
$countRowsMatched += $countMatch;
$countRowsFailed += $countFail;
if( !empty($failures) ){
print_r($failures);
}
echo "Matched: ".$countMatch."\n";
echo "Mismatched: ".$countFail."\n\n";
}
echo "Total Matched: ".$countRowsMatched."\n";
echo "Total Mismatched: ".$countRowsFailed."\n";
Few notes on data differences
Remember you might have to fix something so its good to remember:
- Numeric columns will have to be converted to float etc
- Please note that nulls might become 0s in mysql so take that to the account (it was not a problem for me but who knows).
- Also boolean columns in postgres will become tinyint(1) and t=1 f=0.
Comments
Post new comment