Tutorial :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?


Solution: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)  )  


Solution:2

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!


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »