MySQL 1064 error on creating table

Question

I have an MySQL command:

use hsm_thilisar_cz; CREATE TABLE GODS (
      id INT NOT NULL AUTO _INCREMENT ,
      name TEXT ,
      range BOOL ,
      free BOOL ,
      lore LONGTEXT ,
      PRIMARY KEY ( id )
);

but if I execute it, i would get an error: (translated from Czech because of Czech MySQL server)

#1064-Your syntax is strange near `range BOOL ,free BOOL ,lore LONGTEXT ,
PRIMARY KEY ( id ));` on line 4

Is there any idea, what I have wrong? Thank you for your answers. ^Solved^

But another problem...

CREATE TABLE GUIDE ( 
   id INT NOT NULL AUTO_INCREMENT,
   godId INT NOT NULL,
   userId INT NOT NULL,
   item1 INT,
   item2 INT,
   item3 INT,
   item4 INT,
   item5 INT,
   item6 INT,
   itemA1 INT,
   itemA2 INT,
   itemC1 INT,
   itemC2 INT,
   notes LONGTEXT,
   mode TEXT,
   guideTimeDate TIMESTAMP,
   PRIMARY KEY(id);

an error on line 18

#1064-Your syntax is strange near '' on line 18.

How can I solve this issue, when MySQL doesn't tell me, where is my syntax incorrect... Thank for your time, that you spent on solving this problem.

Duplicate of [How can I write SQL for a table that shares the same name as a protected keyword in MySql?](http://stackoverflow.com/q/10706920/1409082). — Jocelyn, Apr 24 '13 at 14:12
I don't think it's duplicate, but ok, I will try to find error message by the way described on given page... thx — RadarCZ, Apr 25 '13 at 15:23

score 2 · Accepted Answer · answered Apr 24 '13 at 07:29

RANGE is a Reserved Keyword and happens to be the name of your column. In order to avoid syntax error, the column name should be escaped using backticks. Ex,

use hsm_thilisar_cz; 
CREATE TABLE GODS 
(
      id INT NOT NULL AUTO _INCREMENT ,
      name TEXT ,
      `range` BOOL ,          -- wrap with backticks
      free BOOL ,
      lore LONGTEXT ,
      PRIMARY KEY ( id )
);

MySQL Reserved Keywords List

I'd rather change the column name that is not on the Reserved Keyword List to prevent the same error from getting back again on the future.

use hsm_thilisar_cz; 
CREATE TABLE GODS 
(
      id INT NOT NULL AUTO _INCREMENT ,
      name TEXT ,
      gods_range BOOL ,         
      free BOOL ,
      lore LONGTEXT ,
      PRIMARY KEY ( id )
);

score 0 · Answer 2 · answered Apr 24 '13 at 07:31

0

"range" is a MySql keyword. You cannot use it. I tried this and it worked :

CREATE TABLE GODS (
      id INT NOT NULL AUTO_INCREMENT ,
      name TEXT ,
      myrange BOOL ,
      free BOOL ,
      lore LONGTEXT ,
      PRIMARY KEY ( id )
);

answered Apr 24 '13 at 07:31

Julien

2,616
1
30
43

MySQL 1064 error on creating table

2 Answers2