MySQL illegal field names in Mambo/Joomla Import

I had this problem when trying to import an SQL file which was a dump from a MySQL database for Mambo:

 ERROR 1064 (42000) at line 142: 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 'option varchar(50) NOT NULL default '',
  ordering int(11) NOT NULL default '0',' at line 9

The solution was to wrap the names in double quotes as described below…
The following articles states that these names actually _can_ be used (in my opinion they should not!) by quoting the names wherever they are used. I suspect that they do that inside the code.

http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html (Especially ANSI_QUOTES)
http://dev.mysql.com/doc/mysql/en/Legal_names.html
http://dev.mysql.com/doc/mysql/en/Reserved_words.html

However, it seems that quoting with single quotes ('xxx') fails on column names on my server.

So I set this in the beginning of your SQL script.

SET sql_mode='ANSI_QUOTES';

This enables you to use double quotes ("xxx") in addition to single quotes ('xxx') to quote the names.

Now, in your SQL create script, quote the column option in table mos_components and column fulltext in table mos_content like this

DROP TABLE IF EXISTS mos_components;
CREATE TABLE mos_components (
id int(11) NOT NULL auto_increment,
name varchar(50) NOT NULL default '',
link varchar(255) NOT NULL default '',
menuid int(11) unsigned NOT NULL default '0',
parent int(11) unsigned NOT NULL default '0',
admin_menu_link varchar(255) NOT NULL default '',
admin_menu_alt varchar(255) NOT NULL default '',
"option" varchar(50) NOT NULL default '',
ordering int(11) NOT NULL default '0',
admin_menu_img varchar(255) NOT NULL default '',
iscore tinyint(4) NOT NULL default '0',
params text NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

#…and…

DROP TABLE IF EXISTS mos_content;
CREATE TABLE mos_content (
id int(11) unsigned NOT NULL auto_increment,
title varchar(100) NOT NULL default '',
title_alias varchar(100) NOT NULL default '',
introtext mediumtext NOT NULL,
"fulltext" mediumtext NOT NULL,
state tinyint(3) NOT NULL default '0',
sectionid int(11) unsigned NOT NULL default '0',
mask int(11) unsigned NOT NULL default '0',
catid int(11) unsigned NOT NULL default '0',
created datetime NOT NULL default '0000-00-00 00:00:00',
created_by int(11) unsigned NOT NULL default '0',
created_by_alias varchar(100) NOT NULL default '',
modified datetime NOT NULL default '0000-00-00 00:00:00',
modified_by int(11) unsigned NOT NULL default '0',
checked_out int(11) unsigned NOT NULL default '0',
checked_out_time datetime NOT NULL default '0000-00-00 00:00:00',
publish_up datetime NOT NULL default '0000-00-00 00:00:00',
publish_down datetime NOT NULL default '0000-00-00 00:00:00',
images text NOT NULL,
urls text NOT NULL,
attribs text NOT NULL,
version int(11) unsigned NOT NULL default '1',
parentid int(11) unsigned NOT NULL default '0',
ordering int(11) NOT NULL default '0',
metakey text NOT NULL,
metadesc text NOT NULL,
access int(11) unsigned NOT NULL default '0',
hits int(11) unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY idx_section (sectionid),
KEY idx_access (access),
KEY idx_checkout (checked_out),
KEY idx_state (state),
KEY idx_catid (catid),
KEY idx_mask (mask)
) TYPE=MyISAM;

Note that this create script was made from mysqladmin and I guess there may be slight viariances in the scripts depending on which tool you used to create the dump.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>