have an error creating a table in mysql

Question

I installed to database and got the following error:

Don't created: 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 timestamp(10), KEY vid (vid)' at line 4

As you see I had errors to start with and used (As recommended here) the "`" , but now I am stuck with this latest error on same table.

$sql = "CREATE TABLE ".$ine.C_MYSQL_VOTE_IPS." ( vid smallint(5) UNSIGNED NOT NULL, ip int(10) UNSIGNED NOT NULL default '0', `time` `timestamp`(10), `KEY` vid (vid)"; — Welshcowboy, Jul 12 '16 at 07:05

PravinS · Answer 1 · 2016-07-12T07:13:35.700

0

MySQL retrieves and displays TIMESTAMP values in 'YYYY-MM-DD HH:MM:SS' format and you have used timestamp(10), which is invalid

Also TIME is reserved word, so its recommended to use some other name for table column.

Try this

CREATE TABLE `pravion` (
  `vid` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` INT(10) UNSIGNED NOT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`vid`)
);

edited Jul 12 '16 at 07:13

answered Jul 12 '16 at 07:00

PravinS

2,640
3
21
25

Ok, so I can take out (10) from the table query? – Welshcowboy Jul 12 '16 at 07:03
1

Yes, read mysql data type article. here 10 is used for field length which is not applicable for date OR datetime or few more data type field – Rupal Javiya Jul 12 '16 at 07:06

score 0 · Answer 2 · edited May 23 '17 at 10:28

$sql = "CREATE TABLE ".$ine.C_MYSQL_VOTE_IPS." ( vid smallint(5) UNSIGNED NOT NULL, ip int(10) UNSIGNED NOT NULL default '0', time timestamp(10), KEY vid (vid)";

QUERY

SQL> CREATE TABLE `C_MYSQL_VOTE_IPS` (
  `vid` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` INT(10) UNSIGNED NOT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`vid`)
);

Don't use reserved words and if you are using reserved words try to use these words within backtick (`)

Ref: When to use single quotes, double quotes, and backticks in MySQL

have an error creating a table in mysql

2 Answers2