MySQL Syntax error on query, what's wrong

Question

I am going out of my mind here ... I am an SQL beginner .. but I cannot for the life of me see what is wrong with my statement :

CREATE TABLE usage 
  (id BIGINT AUTO_INCREMENT
  , use_date datetime 
  , ctn VARCHAR(255)
  , destination VARCHAR(255)
  , cost_type BIGINT
  , cost BIGINT
  , up_data bigint
  , down_data bigint
  , INDEX cost_type_idx (cost_type)
  , PRIMARY KEY(id) ) ENGINE = INNODB;

Here is the error thrown by MySQL (Version 5.5.8)

SQLSTATE[42000]: Syntax error or access violation: 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 'usage (id BIGINT AUTO_INCREMENT, use_date datetime, ctn VARCHAR(255), destinatio' at line 1. Failing Query: "CREATE TABLE usage (id BIGINT AUTO_INCREMENT, use_date datetime, ctn VARCHAR(255), destination VARCHAR(255), cost_type BIGINT, cost BIGINT, up_data bigint, down_data bigint, INDEX cost_type_idx (cost_type), PRIMARY KEY(id)) ENGINE = INNODB". Failing Query: CREATE TABLE usage (id BIGINT AUTO_INCREMENT, use_date datetime, ctn VARCHAR(255), destination VARCHAR(255), cost_type BIGINT, cost BIGINT, up_data bigint, down_data bigint, INDEX cost_type_idx (cost_type), PRIMARY KEY(id)) ENGINE = INNODB

It says near and then gives me about 30 characters !

I have tried different column names, in case I am using a keyword. I have tried different DataTypes - still no luck !

I'm sure it's very obvious why it's not working to someone who hasn't been tearing their hair out for 10 minutes - someone please put me out of my misery !

First one runs fine - second fails - If i run them individualy — Manse
I mentioned that because you really only needed to provide the second line. I'll edit it for you. :) — Fosco

Fosco Fosco · Accepted Answer · 2011-05-21T19:40:07

That error appears because USAGE is a reserved word in mysql.

See: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html for a list of reserved words.

Also, while it did give you a big part of the query, it's the first part that is most important... It said near 'usage so that is usually what it had an issue with.

MySQL Syntax error on query, what's wrong

1 Answers