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:

le="color: #000000"><?php 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:

le="color: #000000"><?php 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: Jul 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!

Latest GitHub Commits