MySQL Bugs: #372: Create Table problem

Bug #372	Create Table problem
Submitted:	5 May 2003 10:53	Modified:	6 May 2003 1:22
Reporter:	Martin Farrimond	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.12	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Being going nuts trying to create a new table on my hosted website. The only way of loading MySQL data on my ISP is via a script, loaded through PHPMyAdmin (using SQL -> Execute script from file). When I run the script containing the create table, I get the error as shown:

<example>
CREATE TABLE bnwc_foo(

Force smallint( 6 ) NOT NULL default '0',
Description char( 18 ) NOT NULL default '',
KnotsLow char( 4 ) NOT NULL default '',
KnotsHigh char( 4 ) NOT NULL default '',
mphLow char( 4 ) NOT NULL default '',
mphHigh char( 4 ) NOT NULL default '',
kphLow char( 4 ) NOT NULL default '',
kphHigh char( 4 ) NOT NULL default '',
mpsLow char( 4 ) NOT NULL default '',
mpsHigh char( 4 ) NOT NULL default '',
PRIMARY KEY ( Force ) 
) TYPE = MYISAM 

MySQL said: 

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 'Force smallint(6) NOT NULL default '0',
  Description char(18)
</example>

The problem seems to be related to the name of the first column - "Force". If I change this (& the Primary Key definition) to (say) "BftForce", the create table works fine.

If I manually create the table according to the above defintions (ie: with a column named "Force") - using PHPMyAdmin again - the table creates ok, but when I query it, I get errors then...

<example>
Error

SQL-query :  

SELECT * 
FROM `bnwc_beaufort` 
WHERE Force = 2
LIMIT 0 , 30 

MySQL said: 

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 'Force = 2 LIMIT 0, 30' at line 1
</example>

Note: I don't get these problems at all on the version of MySQL running on my PC - Win2k/MySQL 3.23.52-nt 

How to repeat:
See above

In the query example shown, the table name should also be "bnwc_foo" - I'd renamed the table during problem isolation.

Please check our manual in section "Database, Table, Index, Column, and Alias Names". FORCE is a reserved word (it is used to tell MySQL to use a certain index), so it must be enclosed in ` ` to be used as a column or table name.

CREATE TABLE bnwc_foo(

`Force` smallint( 6 ) NOT NULL default '0',
Description char( 18 ) NOT NULL default '',
KnotsLow char( 4 ) NOT NULL default '',
KnotsHigh char( 4 ) NOT NULL default '',
mphLow char( 4 ) NOT NULL default '',
mphHigh char( 4 ) NOT NULL default '',
kphLow char( 4 ) NOT NULL default '',
kphHigh char( 4 ) NOT NULL default '',
mpsLow char( 4 ) NOT NULL default '',
mpsHigh char( 4 ) NOT NULL default '',
PRIMARY KEY ( `Force` ) 
) TYPE = MYISAM  ;
works.
Same thing in your SELECT.
You didn't get the problem in 3.23 because FORCE was introduced in 4.0.

Hmm - not sure I like the description of "Bogus".

Anyhow, thanks for the reply:

Before I posted my observations, I suspected that "Force" may have become a reserved word in v 4, so I did a search for it across the documentation. The search didn't reveal anything to suggest that "Force" is a reserved word. Although you directed me to the "Database, Table, Index, Column, and
Alias Names" section of the documentation, I only found the reference cunningly hidden away in a different section - "Is MySQL picky about reserved words".

"Bogus" means it's not a bug but a user's error.
When I simply open our doc (the HTML file), and ask my browser to look for "FORCE", in two clicks I find "FORCE INDEX". Thus I know this is a reserved word.
The section I pointed you to is the place where we explain how reserved words must be quoted. The section you found is the complement, where there is a list of these reserved words.