Please help my hosting runs out soon!

The Devil · 23.08.2004, 16:41

I am moving my mabo site and when I execute the dump (Part of it below) on the new server I seem to get an error :
SQL-query :

INSERT INTO `mos_components` ( id, name, link, menuid, parent, admin_menu_link, admin_menu_alt,
OPTION , ordering, admin_menu_img, iscore )
VALUES ( 1, 'Banners', '', 0, 0, '', 'Banner Management', 'com_banners', 0, 'js/ThemeOffice/component.png', 1 )

MySQL said:

#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 'option, ordering, admin_menu_img, iscore) VALUES (1, 'Banners',

Part of the dump in question

# Dumping Table Structure for 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) unsigned NOT NULL default '0',
`admin_menu_img` varchar(255) NOT NULL default '',
`iscore` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;
#
# Dumping Data for mos_components
#
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (1, 'Banners', '', 0, 0, '', 'Banner Management', 'com_banners', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (2, 'Manage Banners', '', 0, 1, 'option=com_banners', 'Active Banners', 'com_banners', 1, 'js/ThemeOffice/edit.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (3, 'Manage Clients', '', 0, 1, 'option=com_banners&task=listclients', 'Manage Clients', 'com_banners', 2, 'js/ThemeOffice/categories.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (4, 'Web Links', 'option=com_weblinks', 0, 0, '', 'Manage Weblinks', 'com_weblinks', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (5, 'Newsflash', '', 0, 0, 'option=com_newsflash', 'Manage newsflashes', 'com_newsflash', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (6, 'Contact', 'option=com_contact', 0, 0, 'option=com_contact', 'Edit contact details', 'com_contact', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (7, 'Weblink Items', '', 0, 4, 'option=com_weblinks', 'View existing weblinks', 'com_weblinks', 1, 'js/ThemeOffice/edit.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (8, 'Weblink Categories', '', 0, 4, 'option=categories&section=com_weblinks', 'Manage weblink categories', '', 2, 'js/ThemeOffice/categories.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (9, 'FrontPage', 'option=com_frontpage', 0, 0, '', 'Manage Front Page Items', 'com_frontpage', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (10, 'Manage Items', '', 0, 9, 'option=com_frontpage', 'Manage FrontPage Items', 'com_frontpage', 1, 'js/ThemeOffice/edit.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (11, 'Settings', '', 0, 9, 'option=com_frontpage&act=settings', 'FrontPage Settings', 'com_frontpage', 2, 'js/ThemeOffice/config.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (12, 'Polls', 'option=com_poll', 0, 0, 'option=com_poll', 'Manage Polls', 'com_poll', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (13, 'News Feeds', '', 0, 0, '', 'News Feeds Management', 'com_newsfeeds', 0, 'js/ThemeOffice/component.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (14, 'Manage News Feeds', '', 0, 13, 'option=com_newsfeeds', 'Manage News Feeds', 'com_newsfeeds', 1, 'js/ThemeOffice/edit.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (15, 'Manage Categories', '', 0, 13, 'option=categories&section=com_newsfeeds', 'Manage Categories', '', 2, 'js/ThemeOffice/categories.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (16, 'Newsfeeds (Component)', 'option=com_newsfeeds', 0, 0, '', '', '', 0, '', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (17, 'Media Manager', '', 0, 0, 'option=com_media', 'Media Manager', 'com_media', 0, 'js/ThemeOffice/media.png', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (18, 'Login', 'option=com_login', 0, 0, '', '', 'com_login', 0, '', 1);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (19, 'phpBB', 'option=com_forum', 0, 0, 'option=com_forum', 'phpBB', 'com_forum', 0, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (20, 'Configuration', '', 0, 19, 'option=com_forum', 'Configuration', 'com_forum', 0, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (22, 'Import phpBB users into mambo', '', 0, 19, 'option=com_forum&task=import', 'Import phpBB users into mambo', 'com_forum', 2, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (23, 'phpBB', 'option=com_forum', 0, 0, 'option=com_forum', 'phpBB', 'com_forum', 0, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (24, 'Configuration', '', 0, 23, 'option=com_forum', 'Configuration', 'com_forum', 0, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (37, 'Manage Events Categories', '', 0, 35, 'option=categories&section=com_events', 'Manage Events Categories', 'com_events', 1, 'js/ThemeOffice/component.png', 0);
INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, option, ordering, admin_menu_img, iscore) VALUES (26, 'Import phpBB users into mambo', '', 0, 23, 'option=com_forum&task=import', 'Import phpBB users into mambo', 'com_forum', 2, 'js/ThemeOffice/component.png', 0);

jluyt · 23.08.2004, 18:18

Hi,

I had the same thing once.... I realy am trying to remember what the fix was, but I think it was permitions based.

If I remember more details, I will post it....

Justin
http://www.sadfw.com

Ektoras · 25.08.2004, 23:28

Hi. This one is subtle but quite simple in practice.

The word option is a MySQL reserved word/token. Unfortunately, it also appears as a column name in the mos_components table and it's this clash that is causing the problem you are experiencing.

Fortunately, you can work around this by surrounding occurances of option with backquotes. Then, when MySQL comes to read the line of SQL, it will not treat option as a reserved word. An example is shown below:

INSERT INTO `mos_components` (id, name, link, menuid, parent, admin_menu_link, admin_menu_alt, `option`, ordering, admin_menu_img, iscore) VALUES (1, 'Banners', '', 0, 0, '', 'Banner Management', 'com_banners', 0, 'js/ThemeOffice/component.png', 1);

Manually substituting backquotes won't take long - a text search and replace will do it for you. If you happened to create your database dump file with phpmyadmin, you can get it to automatically add the backquotes for you as it makes the dump file. If you still have access to your source database, you can just dump it again with that option enabled.

I hope my post hasn't come too late to help you. Let me know if you need more help.

prj · 03.09.2004, 04:00

I had that problem but now have this one

SQL-query :

CREATE TABLE mos_content(

id int( 11 ) unsigned DEFAULT '' NOT NULL AUTO_INCREMENT ,
title varchar( 100 ) DEFAULT '' NOT NULL ,
title_alias varchar( 100 ) DEFAULT '' NOT NULL ,
introtext mediumtext DEFAULT '' NOT NULL ,
FULLTEXT mediumtext DEFAULT '' NOT NULL ,
state tinyint( 3 ) DEFAULT '0' NOT NULL ,
sectionid int( 11 ) unsigned DEFAULT '0' NOT NULL ,
mask int( 11 ) unsigned DEFAULT '0' NOT NULL ,
catid int( 11 ) unsigned DEFAULT '0' NOT NULL ,
created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
created_by int( 11 ) unsigned DEFAULT '0' NOT NULL ,
created_by_alias varchar( 100 ) DEFAULT '' NOT NULL ,
modified datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
modified_by int( 11 ) unsigned DEFAULT '0' NOT NULL ,
checked_out int( 11 ) unsigned DEFAULT '0' NOT NULL ,
checked_out_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
publish_up datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
publish_down datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
images text DEFAULT '' NOT NULL ,
urls text DEFAULT '' NOT NULL ,
attribs text DEFAULT '' NOT NULL ,
version int( 11 ) unsigned DEFAULT '1' NOT NULL ,
parentid int( 11 ) unsigned DEFAULT '0' NOT NULL ,
ordering float unsigned unsigned DEFAULT '0' NOT NULL ,
metakey text DEFAULT '' NOT NULL ,
metadesc text DEFAULT '' NOT NULL ,
access int( 11 ) unsigned DEFAULT '0' NOT NULL ,
hits int( 11 ) unsigned DEFAULT '0' NOT NULL ,
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 )
)

MySQL said:

#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 'mediumtext DEFAULT '' NOT NULL ,
state tinyint(3) DEFAULT

prj · 03.09.2004, 04:06

And this

SQL-query :

CREATE TABLE mos_help(

id int( 11 ) DEFAULT '' NOT NULL AUTO_INCREMENT ,
lang char( 3 ) DEFAULT 'eng' NOT NULL ,
context varchar( 40 ) DEFAULT '' NOT NULL ,
name varchar( 40 ) DEFAULT '' NOT NULL ,
title varchar( 100 ) DEFAULT '' NOT NULL ,
parent int( 11 ) DEFAULT '0' NOT NULL ,
ordering int( 11 ) DEFAULT '0' NOT NULL ,
helptext text DEFAULT '' NOT NULL ,
catid int( 11 ) unsigned DEFAULT '0' NOT NULL ,
checked_out int( 11 ) unsigned DEFAULT '0' NOT NULL ,
checked_out_time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
PRIMARY KEY ( id ) ,
UNIQUE IdLangIdx( id, lang ) ,
UNIQUE id( id ) ,
KEY id_2( id ) ,
KEY HeltextIdx( helptext )
)

MySQL said:

#1170 - BLOB column 'helptext' used in key specification without a key length

prj · 03.09.2004, 04:34

And this

SQL-query :

CREATE TABLE mos_philaform_detail(

form_field_id int( 11 ) DEFAULT '' NOT NULL AUTO_INCREMENT ,
form_id int( 11 ) DEFAULT '0' NOT NULL ,
element varchar( 255 ) DEFAULT '' NOT NULL ,
field varchar( 255 ) DEFAULT '' NOT NULL ,
title mediumtext DEFAULT '' NOT NULL ,
size varchar( 255 ) DEFAULT '' NOT NULL ,
maxlength varchar( 255 ) DEFAULT '' NOT NULL ,
cols varchar( 255 ) DEFAULT '' NOT NULL ,
rows varchar( 255 ) DEFAULT '' NOT NULL ,
options varchar( 255 ) DEFAULT '' NOT NULL ,
db varchar( 255 ) DEFAULT '' NOT NULL ,
TABLE varchar( 255 ) DEFAULT '' NOT NULL ,
KEY varchar( 255 ) DEFAULT '' NOT NULL ,
value mediumtext DEFAULT '' NOT NULL ,
dateformat varchar( 255 ) DEFAULT '' NOT NULL ,
maxsize varchar( 255 ) DEFAULT '' NOT NULL ,
uploadfolder varchar( 255 ) DEFAULT '' NOT NULL ,
checked_value varchar( 255 ) DEFAULT '' NOT NULL ,
unchecked_value varchar( 255 ) DEFAULT '' NOT NULL ,
onblur mediumtext DEFAULT '' NOT NULL ,
ordering float unsigned unsigned DEFAULT '0' NOT NULL ,
hidefromresults int( 11 ) DEFAULT '0' NOT NULL ,
custom1 varchar( 255 ) DEFAULT '' NOT NULL ,
required int( 11 ) DEFAULT '0' NOT NULL ,
custom2 varchar( 255 ) DEFAULT '' NOT NULL ,
style varchar( 100 ) DEFAULT '' NOT NULL ,
custom3 varchar( 255 ) DEFAULT '' NOT NULL ,
custom4 varchar( 255 ) DEFAULT '' NOT NULL ,
custom5 varchar( 255 ) DEFAULT '' NOT NULL ,
custom6 varchar( 255 ) DEFAULT '' NOT NULL ,
PRIMARY KEY ( form_field_id )
)

MySQL said:

#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 'table varchar(255) DEFAULT '' NOT NULL ,
key varchar(255)

prj · 03.09.2004, 04:48

Ho Hum

And this

SQL-query :

CREATE TABLE mos_poll_data(

id int( 11 ) DEFAULT '' NOT NULL AUTO_INCREMENT ,
pollid int( 4 ) DEFAULT '0' NOT NULL ,
text text DEFAULT '' NOT NULL ,
hits int( 11 ) DEFAULT '0' NOT NULL ,
PRIMARY KEY ( id ) ,
KEY pollid( pollid, text )
)

MySQL said:

#1170 - BLOB column 'text' used in key specification without a key length

prj · 03.09.2004, 04:50

This is looking daunting

I have about 12 sites to move!

And this is only site one!!

At least that is all the issues.

Ektoras · 03.09.2004, 12:46

Hi. Can you provide the following information please:
1. The name of the tool you used to produce the database dump.
2. The version of the MySQL server where the database was dumped from;
3. The version of MySQL server you are trying to load the dump into;

While you do that, here is some music: in your first example, FULLTEXT is the name of a column in mos_content. It's also a MySQL reserved word so applying the backquote method to resolve the ambiguity ought to do the trick. The same goes for the column named TABLE in mos_philaform_detail.

prj · 03.09.2004, 13:16

Quote:

Originally Posted by Ektoras

Hi. Can you provide the following information please:
1. The name of the tool you used to produce the database dump.
2. The version of the MySQL server where the database was dumped from;
3. The version of MySQL server you are trying to load the dump into;

While you do that, here is some music: in your first example, FULLTEXT is the name of a column in mos_content. It's also a MySQL reserved word so applying the backquote method to resolve the ambiguity ought to do the trick. The same goes for the column named TABLE in mos_philaform_detail.

1. The tool is proprietory on virtual host.
2. Dump from 3.53
3. 4.0.20-standard

And thanks for the music!