MySQL syntax error in the CREATE TABLE statement

Question

SQL query:

CREATE TABLE  `comment_threads` (
 `comment_id` INT( 11 ) UNSIGNED NOT NULL DEFAULT  '0',
 `updated` TIMESTAMP NOT NULL ,
 `timestamp` TIMESTAMP NOT NULL ,
) ENGINE = MYISAM ;

This is an old file that I'm trying to run on HostGator through phpMyAdmin. I get an error that says:

MySQL said: #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 ') ENGINE=MyISAM' at line 5

UPDATE: If I change the statement to this, I still get an error:

CREATE TABLE comment_threads (
comment_id INT( 11 ) UNSIGNED NOT NULL DEFAULT '0',
updated TIMESTAMP( 14 ) NOT NULL ,
timestamp TIMESTAMP NOT NULL
PRIMARY KEY ( comment_id ) )
ENGINE = MYISAM ;

I get the error:

ERROR 1064 (42000): 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 '( 14 ) NOT NULL , timestamp TIMESTAMP NOT NULL PRIMARY KEY ( comment_id ) ) ENGI' at line 3

What? Do you want to run this query through phpmyadmin, then use the sql tab. (you could also use the built-in table-creator) — Wottensprels, Jul 08 '12 at 18:24
MySQL said: #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 ') ENGINE=MyISAM' at line 5 — kevin fantini, Jul 08 '12 at 18:30
thats the error i get when I import the file thru myphpadmin... I aldready read to change all the Types to Engine and this like here `timestamp` TIMESTAMP NOT NULL , used to be `timestamp` TIMESTAMP (14) NOT NULL , — kevin fantini, Jul 08 '12 at 18:31
@kevinfantini If my answer was helpful, you can accept it by clicking the checkmark underneath its vote counter. That allows people searching to see that it's correct. — Ricardo Altamirano, Jul 09 '12 at 18:25

score 7 · Answer 1 · edited May 23 '17 at 12:08

Your MySQL query is incorrect. Correcting it to this works.

CREATE TABLE  `comment_threads` (
     `comment_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
     `updated` TIMESTAMP NOT NULL ,
     `timestamp` TIMESTAMP NOT NULL
  ) ENGINE=MyISAM;

To run this in phpMyAdmin, you can use the in-built table creator or enter the corrected SQL statement in the SQL query window.

Note that I removed the comma after the last TIMESTAMP NOT NULL line (immediately before the ending ).

UPDATE: The second statement you posted corrects to this:

CREATE TABLE  `comment_threads` (
     `comment_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
     `updated` TIMESTAMP NOT NULL ,
     `timestamp` TIMESTAMP NOT NULL,
PRIMARY KEY(`comment_id`)
  ) ENGINE=MyISAM;

Here are the problems you introduced in your second CREATE TABLE statement:

TIMESTAMP( 14 ) should just be TIMESTAMP (per the documentation)
You need a comma after the line TIMESTAMP NOT NULL line. The comma is necessary now because unlike in the first example, you're separated two parts of the statement: the TIMESTAMP NOT NULL line and the PRIMARY KEY declaration.

If you want more information on simple methods for debugging SQL statements, I strongly suggest you look at my answer to this question (see the section titled A bit more information on how to methodically fix errors like this).

Make sure that when you change a CREATE TABLE statement, or any piece of code, that you make changes in small enough increments that you're only "breaking at most one thing at a time." In the case of your second CREATE TABLE statement, there was no reason to change the first TIMESTAMP declaration, and doing so broke the code. That line was working; no need to change it.

Next time, ask a new question if you radically change the `CREATE TABLE` statement in incorrect ways (as you did in this case). I'm adding the statement you just posted to your question, and I'll edit my answer with a solution to that as well. — Ricardo Altamirano, Jul 09 '12 at 01:52

MySQL syntax error in the CREATE TABLE statement

1 Answers1