Mysql – SQL dump loads with Error 1064

MySQLmysqldump

Looks like this issue is just the wrong version of MySQL, need to go to 5.5

So I have a backup of an offsite source SQL database. I am not sure how they are preforming the data dump but I am trying to find out. This database needs to be imported on the new computer. Every time I try to run the commands below I get the error (at the bottom of the post). I want to be able to load this data with out an error. What do I need to do to get the data to load correctly? So far none of the tables load at all from the dump.

mysql -u username -p database_name < file.sql

I get the error 1064. I also tried to load this in MySQL with

source \home\user\Desktop\test.sql;

MySQL v12.22 Distrib 4.0.26-nt-max, for Win32 (ia32)
Protocol
version, 10
Client characterset, latin1
Server characterset, latin1
Windows XP SP3
InnoDB is coming up at the type in show create table tablename;

ERROR 1064: You have an error in your SQL syntax. Check the manual
that corresponds to your MySQL version for the right syntax to use
near 'DEFAULT CHARSET=latin1' at line 1

when trying to add a different table

ERROR 1074: Too big column length for column 'City' . Use BLOB instead

ok I kept messing with this, the following code works when I remove, DEFAULT CHATSET=latin1

CREATE TABLE `aaaa` (`MYID` int(11) DEFAULT NULL) ENGINE=MyISAM;

Best Answer

In the commandline tool, use forwardslash, not backslash, for path delimiters:

source /home/user/Desktop/test.sql;

Related Solutions

MySQLDump wrong dump

Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13 Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!

Mysql query not working

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!

Related Question