SQL version incompatibility, having trouble when importing SQL file

shadow_boi · 02-20-2006, 11:45 PM

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:
????: NamePros.com http://www.namepros.com/programming/169681-sql-version-incompatibility-having-trouble-when.html

Quote:

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} · 02-21-2006, 12:06 AM

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:

Quote:

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

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

Quote:

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

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

AllValley · 02-21-2006, 08:17 AM

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 · 02-21-2006, 10:14 AM

thanks for your help.
I have replaced "DEFAULT CHARSET=latin1" with "".
????: NamePros.com http://www.namepros.com/showthread.php?t=169681

but I still got error

Quote:

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 · 02-21-2006, 10:19 AM

take out

Quote:

CHARACTER SET latin1 COLLATE latin1_bin

and see what happens...
????: NamePros.com http://www.namepros.com/showthread.php?t=169681

oops. didn't notice this

Quote:

) 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 · 02-21-2006, 11:43 AM

problem solved.

Many many thanks to Superprogrammer and AllValley

superprogrammer · 02-21-2006, 11:47 AM

Thanks ...

AllValley · 02-21-2006, 01:02 PM

No problem.

02-21-2006, 08:17 AM	#3 (permalink)
AllValley NamePros Member Join Date: Feb 2006 Location: Chicago IL Posts: 73	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. __________________ Hassle Free Computing and Web Solutions for Personal and Business! Rochelle Network Communications!

02-21-2006, 11:47 AM	#7 (permalink)
superprogrammer Senior Member Join Date: Aug 2004 Location: Washington Posts: 4,327	Thanks ... __________________ Download youtube videos at www.HugYouTube.com -NO LINKS TO PARKED PAGES- Anonymous access at www.Banned.net

02-21-2006, 01:02 PM	#8 (permalink)
AllValley NamePros Member Join Date: Feb 2006 Location: Chicago IL Posts: 73	No problem. __________________ Hassle Free Computing and Web Solutions for Personal and Business! Rochelle Network Communications!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
**HUGE List of FREE File/Image/Video Hosts!**	thetzfreak	Free Resources	31	02-12-2006 07:06 AM
Connecting to your SQL Database using Enterprise Manager	priyanka[imported]	Webmaster Tutorials	0	12-06-2004 09:58 PM
2 Huge Name Forsale	Vegas Entertainment	Domains For Sale - Make Offer	9	09-21-2004 03:37 PM

02-21-2006, 11:43 AM	THREAD STARTER #6 (permalink)
shadow_boi NamePros Regular Join Date: Jul 2005 Location: Vancouver Posts: 533	problem solved. Many many thanks to Superprogrammer and AllValley

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)