Page 1 of 1

error when importing mysqldump file

Posted: Wed Mar 30, 2005 1:56 pm
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

Posted: Wed Mar 30, 2005 2:11 pm
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?

Posted: Wed Mar 30, 2005 2:37 pm
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

Posted: Wed Mar 30, 2005 2:47 pm
by feyd
it's best to have the "quotes" around all field, table, and database names anyways...