CODEWITHSUNDEEP
mysql
Outstrip
Outstrip

Reputation: 21

MySQL syntax error for server version

What the problem with below code, it reverts with an error message "The reported error is: 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 'user bigint(20) NOT NULL, type enum('e','c') NOT NULL, eidcid bigint(20) N' at line 1"

Can someone please help???

$sqla[] = "CREATE TABLE `" . $GLOBALS['TBL_PREFIX'] . "accesscache` (`cacheid` INT NOT NULL AUTO_INCREMENT  `user` bigint(20) NOT NULL,  type enum('e','c') NOT NULL,  `eidcid` bigint(20) NOT NULL,  `result` enum('nok','readonly','ok') NOT NULL,  `timestamp_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY `cacheid` (`cacheid`), KEY `user` (`user`),  KEY `type` (`type`),  KEY `eidcid` (`eidcid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Interleave Access cache table';";

Upvotes: 0

Views: 195

Answers (3)

Devart
Devart

Reputation: 121932

There were 2 errors, I will show on worked script -

CREATE TABLE `accesscache` (
  `cacheid` int NOT NULL AUTO_INCREMENT, -- need ',' after cacheid
  `user` bigint(20) NOT NULL,
  type enum ('e', 'c') NOT NULL,
  `eidcid` bigint(20) NOT NULL,
  `result` enum ('nok', 'readonly', 'ok') NOT NULL,
  `timestamp_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`cacheid`), -- PK always named as PRIMARY
  KEY `user` (`user`),
  KEY `type` (`type`),
  KEY `eidcid` (`eidcid`)
)
ENGINE = INNODB
DEFAULT charset = utf8
COMMENT = 'Interleave Access cache table'

Upvotes: 1

Gopal Joshi
Gopal Joshi

Reputation: 2358

Try This Query. You Forgot to put , after AUTOINCREMENT

   $sqla[] = "CREATE TABLE `" . $GLOBALS['TBL_PREFIX'] . "accesscache` (`cacheid` INT NOT NULL AUTO_INCREMENT, `user` bigint(20) NOT NULL,  type enum('e','c') NOT NULL,  `eidcid` bigint(20) NOT NULL,  `result` enum('nok','readonly','ok') NOT NULL,  `timestamp_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY `cacheid` (`cacheid`), KEY `user` (`user`),  KEY `type` (`type`),  KEY `eidcid` (`eidcid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Interleave Access cache table';";

Upvotes: 0

Eduard Luca
Eduard Luca

Reputation: 6602

You are missing a comma after AUTO_INCREMENT.

$sqla[] = "CREATE TABLE `" . $GLOBALS['TBL_PREFIX'] . "accesscache` (`cacheid` INT NOT NULL AUTO_INCREMENT,  `user` bigint(20) NOT NULL,  type enum('e','c') NOT NULL,  `eidcid` bigint(20) NOT NULL,  `result` enum('nok','readonly','ok') NOT NULL,  `timestamp_last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY `cacheid` (`cacheid`), KEY `user` (`user`),  KEY `type` (`type`),  KEY `eidcid` (`eidcid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Interleave Access cache table';";

Upvotes: 1

Related Questions