XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. Q and A 
  4.  / MySQL Syntax Error
Register
1
jdseymour
MySQL Syntax Error

In testing database restore on my test site I have a problem with the following MySQL syntax:

CREATE TABLE xoops_block_instance(
instanceid int( 12 ) unsigned NOT NULL AUTO_INCREMENT ,
bid int( 12 ) unsigned NOT NULL default '0',
options text NOT NULL ,
title varchar( 255 ) NOT NULL default '',
side tinyint( 1 ) unsigned NOT NULL default '0',
weight smallint( 5 ) unsigned NOT NULL default '0',
visible tinyint( 1 ) unsigned NOT NULL default '0',
bcachetime int( 10 ) unsigned NOT NULL default '0',
PRIMARY KEY ( instanceid ) ,
KEY JOIN ( instanceid, visible, weight )
) TYPE = MYISAM

The error I am getting is:
Quote:
MySQL said: Documentation
#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 'join (instanceid,visible,weight)
) TYPE=MyISAM' at line 11

Like I said this is just a test restore of XOOPS version 2.1.1 using standard mysqldump on the server. The MySQL version is 4.0.22. Any idea what is causing the error with the above statement?
2
luciano
Re: MySQL Syntax Error

  • 2005/6/10 8:27

  • luciano

  • Quite a regular

  • Posts: 261

  • Since: 2003/11/3


Got the same problem some minutes ago. Changed the compatibility to MYSQL40 in the export window and it worked.
3
jdseymour
Re: MySQL Syntax Error

Thanks. But I think the "compatible" command is only available in MySQL 4.1 versions. Also I am importing from the same version I exported from.
4
luciano
Re: MySQL Syntax Error

  • 2005/6/10 8:35

  • luciano

  • Quite a regular

  • Posts: 261

  • Since: 2003/11/3


I had the same error at the same place, I even tried to change TYPE into ENGINE, INDEX into KEY (or the other way around, I don't remember. Anyway the 40 compatibility worked for me.
5
jdseymour
Re: MySQL Syntax Error

Well, I opened the archive, and reviewed the mysql.structure.sql included with the XOOPS install folder. Here is the relevant code:

CREATE TABLE `block_instance` (
  `instanceid` int(12) unsigned NOT NULL auto_increment,
  `bid` int(12) unsigned NOT NULL,
  `options` text NOT NULL default '',
  `title` varchar(255) NOT NULL default '',
  `side` tinyint(1) unsigned NOT NULL default '0',
  `weight` smallint(5) unsigned NOT NULL default '0',
  `visible` tinyint(1) unsigned NOT NULL default '0',
  `bcachetime` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY (`instanceid`),
  KEY `join` (`instanceid`, `visible`, `weight`)
) TYPE=MyISAM;

I added the prefix to this code and it created with no problems. The only difference I see is the single quotes.

So my next question is why did this particular table refuse to restore without the single quotes, when I had 70 other tables that did restore properly?
6
phppp
Re: MySQL Syntax Error

  • 2006/3/12 9:22

  • phppp

  • XOOPS Contributor

  • Posts: 2857

  • Since: 2004/1/25


JOIN is a preserved keyword in MySQL
so the line should be changed to
KEY anynonpreservedword (like `jointhem`) (`instanceid`, `visible`, `weight`)

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Donat-O-Meter

Stats
Goal: $15.00
Due Date: Oct 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!