we are on a very old mediatemple account. im managed to get the mysql 3.23 dump downloaded and our new setup is using mysql 5.0

no matter what i try i cannot get it to import the old dump file

does anyone have any suggestions?

thank you
-e

i just realized my post doesnt make the most sense and the WHT system wont letme edit it :p

we a fairly large database under mysql 3.23 and have been able to download a dump file.

no matter what we do it errors out when we try to import it to our new host running mysql 5.x (so much for backwards compatibility)

is there anything i can do to make the old dump compatible since media temple refuses to upgrade?

How did you do the export and how are you doing the import? What is the error?

One thing that nearly always works, is the MySQL Administrator program which you can download from mysql.com. Give that a go, may be a pleasant surprise.

How did you do the export and how are you doing the import? What is the error?

created a new database in phpmyadmin, clicked on import and it errors out at line 1 no matter what type of compatibility i select

-e

phpMyAdmin should give you some error, i.e. invalid field count. Does it give you anything?

thanks for peeking at this guys i really appreciate it :) i deleted a few lines of the TABLE as it seemed extraneous

CREATE TABLE `phpbb_account_hist` (
`user_id` mediumint( 8 ) default '0',
`lw_post_id` mediumint( 8 ) default '0',
`lw_money` float default '0',
) TYPE = MYISAM ;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_id` mediumint(8) default '0',
`lw_post_id` mediumint(8) default '0',
`l' at line 1

i assume i should just be making a blank database and trying to import this dump file correct?

i tried changing TYPE=MYISAM to ENGINE=MYISAM as well as changing it to say "CREATE TEMPORARY TABLE" and it is still erroring out the same way :(

-e

Try to drop the comma after lw_money row/

Edit: looking at your post again, you said that you dropped some data. We need to see the entire CREATE statement, the error you are looking at is caused by the comma (i just checked in phpMyAdmin), so this is not going to give you anything useful if the comma is only there because you left it when you chopped the data you did not think was useful.

here's the entire error that i get when trying to import:

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 20, 2008 at 05:10 PM
-- Server version: 3.23.58
-- PHP Version: 4.4.1
--
-- Database: `xxxxxxxx_com_-_phpbb`
--
-- --------------------------------------------------------
--
-- Table structure for table `phpbb_account_hist`
--
CREATE TABLE `phpbb_account_hist` (
`user_id` mediumint( 8 ) default '0',
`lw_post_id` mediumint( 8 ) default '0',
`lw_money` float default '0',
`lw_plus_minus` smallint( 5 ) default '0',
`MNY_CURRENCY` varchar( 16 ) default '',
`lw_date` int( 11 ) default '0',
`comment` varchar( 255 ) default '',
`status` varchar( 64 ) default '',
`txn_id` varchar( 64 ) default '',
`lw_site` varchar( 10 ) default ''
) TYPE = MYISAM ;

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user_id` mediumint(8) default '0',
`lw_post_id` mediumint(8) default '0',
`l' at line 1

thank you again for trying to assist me :)

-e

I just copied exactly what you have (copy and paste) and input it to phpMyAdmin v 2.11.4, on mySql 5.0.45 and it took it just fine.

I know that does not help much, but it makes me think the issue is not an import/export one.

Mysql backwards compatibility is usually very good, and I can also confirm that the query posted imports into 5.0 without errors.

Could it be a line-ending problem? Did you download the sql file to a Windows machine and then upload in binary mode? If so try an ascii upload.

Edit2: nvm

Try this

<?php

mysql_connect("localhost","root","****");
mysql_select_db("db1");

mysql_query_file("tablesql.txt", true);

function mysql_query_file($url, $ignoreerrors = false)
{
$file_content = file($url);
$query = "";

foreach($file_content as $sql_line)
{
$tsl = trim($sql_line);

if (($sql_line != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#"))
{
$query .= $sql_line;

if(preg_match("/;\s*$/", $sql_line))
{
$query = str_replace(";", "", "$query");
$result = mysql_query($query);

if (!$result && !$ignoreerrors) die(mysql_error());
$query = "";
}
}
}
}
?>

should be datatype error. Try to remove quote for default int.

CREATE TABLE `phpbb_account_hist` (
`user_id` mediumint( 8 ) default 0,
`lw_post_id` mediumint( 8 ) default 0,
`lw_money` float default 0,
`lw_plus_minus` smallint( 5 ) default 0,
`MNY_CURRENCY` varchar( 16 ) default '',
`lw_date` int( 11 ) default 0,
`comment` varchar( 255 ) default '',
`status` varchar( 64 ) default '',
`txn_id` varchar( 64 ) default '',
`lw_site` varchar( 10 ) default ''
) TYPE = MYISAM ;

well i am on a mac and trying this locally now with MAMP and still having no luck, same exact error :( i will try removing the quotes around the '0' to see if that helps, thanks

-e

ended up being an encoding issue finally got it working fine after a few days of being a dummy :)

-e