SQL version incompatibility, having trouble when importing SQL file

[shadow_boi]

I have a SQL file from my previous hosting. But now I use a new host, and want to export my SQL file into it. But I got syntax errors.

Nothing wrong with the SQL file, because it works well on my old host.
then I ask my server provider from my old host. He gave me this:

Our server is using
MySQL version 4.1.14-standard

Most servers that run MySQL version 4.0 or around that ballpark will have a hard time importing the SQL version due to version incompatibility.

Yea, the new host is using Mysql 4.0
and my old host uses MySQL version 4.1.14

Is there a way to import my SQL file succefully into my new host?

 

[{insert name here}]

Yeah, but it will take you some time.

Every line, actually only at the end of a table, looks like this in 4.1.xx:

CREATE TABLE `GIFTS` (
...
...
ENGINE=MyISAM DEFAULT CHARSET=latin1;
)

You need to remove all the "DEFAULT CHARSET=latin1"
So it looks like this:

CREATE TABLE `GIFTS` (
...
...
ENGINE=MyISAM;
)

If you do this, you will have no issues importing the new version into the older version.

 

[AllValley]

Yes. Copy the file into notepad and do a 'find-replace' replacing "DEFAULT CHARSET=latin1" with ""

Also, some hosts, if they own their own servers, will upgrade the version of software on request.

 

[shadow_boi]

thanks for your help.
I have replaced "DEFAULT CHARSET=latin1" with "".

but I still got error

CREATE TABLE `faq` (

`faqname` varchar( 250 ) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL default '',
`faqparent` varchar( 50 ) NOT NULL default '',
`displayorder` smallint( 5 ) unsigned NOT NULL default '0',
`volatile` smallint( 5 ) unsigned NOT NULL default '0',
PRIMARY KEY ( `faqname` ) ,
KEY `faqparent` ( `faqparent` )
) ENGINE = MYISAM;

#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 'collate latin1_bin NOT NULL default '',
`faqparent` varchar(5

it's also about latin.

Please help.

 

[AllValley]

take out

CHARACTER SET latin1 COLLATE latin1_bin

and see what happens...

oops. didn't notice this

) ENGINE = MYISAM;

The paren ")" should be between the MYISAM and the semi colon ;

So.. Replace "CHARACTER SET latin1 COLLATE latin1_bin" with ""
and replace " )ENGINE = MYISAM;" with "ENGINE = MYISAM);"

BAH! I need to read the SQL more carefully. You should not have ANY double quotes " in your sql so... when I said do a find-replace, and replace with "" I meant leave the field blank, not replace with " XD Sorry, should have been more clear.Just leave the replace field blank when you're trying to delete stuff.

 

[shadow_boi]

problem solved.

Many many thanks to Superprogrammer and AllValley

 

[superprogrammer]

Thanks ...

 

[AllValley]

No problem.