error when importing mysqldump file

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

error when importing mysqldump file

Post by Swede78 »

I have created a backup batch file on my server that uses mysqldump to create the .sql. However, when I try to load it into mysql, I get the following error:
ERROR 1064 at line 234544: 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 'FieldName6 tinyint(1) NOT NULL default '0', FieldName7 double(4,2) Not
This is the statement in question. I can't see why it doesn't work. And I definitely can't understand why mysqldump is creating bad syntax. I am using mysql 4.0.23 on a windows 2003 server to create the sql file. Same version is being used to import it.

Code: Select all

CREATE TABLE ship_promos (
  FieldName1 char(10) NOT NULL default '',
  FieldName2 tinyint(1) NOT NULL default '0',
  FieldName3 double(4,2) NOT NULL default '0.00',
  FieldName4 tinyint(1) NOT NULL default '0',
  FieldName5 double(4,2) NOT NULL default '0.00',
  FieldName6 tinyint(1) NOT NULL default '0',
  FieldName7 double(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (ShipPromoID)
) TYPE=MyISAM;
This is the mysqldump statement I am using:

Code: Select all

mysqldump -u USERNAME -pPASSWORD --add-drop-table --extended-insert DATABASENAME > output.sql
If anyone sees what it's doing wrong, please advise. Thank you.

Swede
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

I think I've already found the problem, which you won't be able to see from the code, since I changed the actual field names to "FieldName1, 2, 3...". Apparently, MySQL reserves certain words - see this page:

http://www.modwest.com/help/kb6-265.html

One of the words it reserves, I am using as a field name ("Both"). So, I'll have to change this. This is strange, because the old way I made backups created sql that worked just fine with this field name.

SQL created by Navicat's built-in sql dump command:

Code: Select all

CREATE TABLE `ship_promos` (
  `FieldName1` char(10) NOT NULL default '',
  `FieldName2` tinyint(1) NOT NULL default '0',
  `FieldName3` double(4,2) NOT NULL default '0.00',
  `FieldName4` tinyint(1) NOT NULL default '0',
  `FieldName5` double(4,2) NOT NULL default '0.00',
  `FieldName6` tinyint(1) NOT NULL default '0',
  `FieldName7` double(4,2) NOT NULL default '0.00',
  PRIMARY KEY  (`ShipPromoID`)
) TYPE=MyISAM;
Obviously it works because it uses the ` character to surround the names. Does anyone know how to add this with mysqldump?
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Sorry that I'm not giving anyone a chance to answer this. :) When I first tried searching for the solution, before coming here, I just couldn't find it. Of course, now that I bothered to post my problem, I find the solution in a matter of minutes.

I had to add the option "--allow-keywords" to my mysqldump statement to get it to include quotes around the column names:

Code: Select all

mysqldump -u USERNAME -pPASSWORD --add-drop-table --extended-insert --allow-keywords DATABASENAME > output.sql
I suppose that it would probably be better practice to not include column names that use words in the list of MySQL reserved words. But, for now, this will work.

Thanks, Swede
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's best to have the "quotes" around all field, table, and database names anyways...
Post Reply