mysql syntax error for timestamp

Question

I have this piece of SQL that is being fed to Mysql.

CREATE TABLE pn_history(
 member INT,
 action INT,
 with INT,
 timestamp DATETIME,
 details VARCHAR(256)
)

But is comes back as an error about the syntax.

#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 'with INT,
timestamp DATETIME,
details VARCHAR(256)
)' at line 4 

Why is this failing?

Answer 1

The problem is the name of the with column. Change the name into something like withValue.

CREATE TABLE pn_history(
 member INT,
 action INT,
 withValue INT,
 timestamp DATETIME,
 details VARCHAR(256)
)

Answer 2

timestamp is a keyword (it is a data type in mysql) which may be causing you problems.

I would suggest using a different name, but if it must be named timestamp, try using backticks to quote it.

Answer 3

Both 'with' and 'timestamp' are reserved words in MySQL. So to get this to work, you'd need to escape each one:

CREATE TABLE pn_history(
 member INT,
 action INT,
 `with` INT,
 `timestamp` DATETIME,
 details VARCHAR(256)
)

Really though, you need to consider changing the names of your columns identifiers.

Read more about MySQL Reserved Words.

EDIT: Actually, TIMESTAMP is not a reserved word. The documentation says:

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

• ACTION
• BIT
• DATE
• ENUM
• NO
• TEXT
• `TIME
• TIMESTAMP

So I guess that means peer pressure took TIMESTAMP off the reserved word list. Hah!