cancel
Showing results for 
Search instead for 
Did you mean: 

MySQL: Downgrade Tables?

Community Veteran
Posts: 14,439
Thanks: 728
Fixes: 12
Registered: ‎01-08-2007

MySQL: Downgrade Tables?

Hi
Anyone know if ts possible to downgrade Mysql 5 DB's and tables to Mysql 4?
I need a new signature... i'm bored of the old one!
1 REPLY
Highlighted
Community Veteran
Posts: 3,789
Registered: ‎08-06-2007

Re: MySQL: Downgrade Tables?

yes.
From the command line, issue the following command to dump the database from Mysql5:

mysqldump -p --opt --compatible=mysql40 dbname >dbname.sql

Then import it into MySQL 4 using the following set of commands:

mysqladmin -p create dbname
mysql -p -D dbname <dbname.sql
mysql -p -D dbname <dbname-fix.sql

If you have Autoincrement fields in MySQL5, they may or may not be exported correctly, (depends on the base storage type, and whether it's 5.0.19 or earlier).   If you find that the import fails on the MySQL 4 side, then use this mysql-aifix script:
Paste the following contents into a file named mysql-aifix, then chmod +x it, then run:  mysql-aifix dbname.sql.  Finally, import into MySQL4.

#!/usr/bin/php
<?php
$hostname = "localhost";
$username = "CHANGEME";
$password = "CHANGEME";
if ($argc < 2) {
echo "Usage: mysql-aifix [dbname] \n";
echo "Adds autoincrement to mysqldump for database dbname \n";
echo "Outputs file dbname-fix.sql \n";
exit;
}
$dbName = $argv[1];
mysql_connect($hostname,$username,$password) or die("Can't create connection");
$res1 = mysql_query("SHOW TABLES FROM $dbName");
if (empty($res1)) die("Can't find database '" . $dbName . "'");
$i=0;
$fname = $dbName . "-fix.sql";
$fp = fopen ($fname, "w");
while($row1 = mysql_fetch_array($res1)){
$res2 = mysql_db_query($dbName, "SHOW CREATE TABLE $row1[0]");
while($row2 = mysql_fetch_array($res2)){
preg_match("/\s*(\W\w+\W)(.*auto_increment)/", $row2[1], $matches);
if($matches){
$i++;
$str = "ALTER TABLE ".$row2[0]." CHANGE ".$matches[1]."
".$matches[1]."".$matches[2].";\n";
fwrite($fp, $str);
}
}
mysql_free_result($res2);
}
fclose($fp);
mysql_free_result($res1);
mysql_close();
echo "Entries created: ".$i . " in file " . $fname;
?>

HTH
B.