CODEWITHSUNDEEP
mysql
kevin fantini
kevin fantini

Reputation: 21

MySQL syntax error in the CREATE TABLE statement

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

Upvotes: 1

Views: 15382

Answers (1)

Ricardo Altamirano
Ricardo Altamirano

Reputation: 15208

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:

  1. TIMESTAMP( 14 ) should just be TIMESTAMP (per the documentation)
  2. 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.

Upvotes: 7

Related Questions