Err 1064 - You have an error in your SQL syntax; Cannot create table

Question

sql:

CREATE TABLE `forum`
(
  `id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` Varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
  `password` Varchar() CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
  `register` Datetime(0) NOT NULL,
  `last_login` Datetime(0) NOT NULL,
  `birth` Date NOT NULL,
  `about` Varchar(500),
  `sex` Bool,
  `group` Smallint UNSIGNED NOT NULL,
  `rank` Smallint UNSIGNED NOT NULL,
  `avatar` Varbinary(2000) NOT NULL,
  `status` Tinyint UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`,`group`,`rank`),
  PRIMARY KEY (`id`,`group`,`rank`),
 UNIQUE `id` (`id`)
) ENGINE = InnoDB
 DEFAULT CHARACTER SET utf8mb4
 COLLATE utf8mb4_polish_ci
; 
ALTER TABLE `Forum`.`users` ADD UNIQUE `login` (`login`)
;

Message log:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE `forum`
(
  `id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
  `log' at line 1
[Err] CREATE TABLE `forum`
(
  `id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
  `login` Varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
  `password` Varchar() CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
  `register` Datetime(0) NOT NULL,
  `last_login` Datetime(0) NOT NULL,
  `birth` Date NOT NULL,
  `about` Varchar(500),
  `sex` Bool,
  `group` Smallint UNSIGNED NOT NULL,
  `rank` Smallint UNSIGNED NOT NULL,
  `avatar` Varbinary(2000) NOT NULL,
  `status` Tinyint UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`,`group`,`rank`),
  PRIMARY KEY (`id`,`group`,`rank`),
 UNIQUE `id` (`id`)
) ENGINE = InnoDB
 DEFAULT CHARACTER SET utf8mb4
 COLLATE utf8mb4_polish_ci
;
[Msg] Finished - Unsuccessfully
--------------------------------------------------

I'm looking at this and looking, seeking for solution on web and can't find what's wrong here.

Just can't execute this sql file, errors. UTF-8 coding.

SQL file dump created with Toad Data Modeler 6.1 Trial

Password varchar() - varchar needs a length,and primary key duplicated. — P.Salmon
– P.Salmon, Commented Jul 22, 2017 at 9:35
Apart from the errors mentioned already, your error message suggests that you are running multiple queries at once in a context where there is only one query at a time allowed (e.g. running your code using php), that might be an additional problem. — Solarflare
– Solarflare, Commented Jul 22, 2017 at 9:42
@P.Salmon Made change. Still the same error. As error says http://wklej.to/0rCIT — Krystian Polska
– Krystian Polska, Commented Jul 22, 2017 at 9:44
@Solarflare Running multiple queries on execution I have unchecked. And I run it by Navicat Premium — Krystian Polska
– Krystian Polska, Commented Jul 22, 2017 at 9:45
Your alter statement may not be correct either. If you really meant to alter forum then the .users may be incorrect. On the other hand if you have a db called forum with a table called users it may be correct... — P.Salmon
– P.Salmon, Commented Jul 22, 2017 at 9:48

wchiquito · Accepted Answer · 2017-07-22 09:34:59Z

1

Change:

...
-- `password` Varchar() CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
`password` Varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci NOT NULL,
...

and

...
-- PRIMARY KEY (`id`,`group`,`rank`),
PRIMARY KEY (`id`,`group`,`rank`),
...

answered Jul 22, 2017 at 9:34

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Krystian Polska Over a year ago

Made change. Still the same error. As error says ;http://wklej.to/0rCIT

wchiquito Over a year ago

@KrystianPolska: I can't reproduce the problem, see db<>fiddle.

Krystian Polska Over a year ago

I doens't understand that. That's kinda insane. That I got this error on the same query and you doesn't : |

Rick James Over a year ago

But the error message is pointing at CREATE, implying that what came immediately before it is the problem.

wchiquito Over a year ago

@RickJames: The problem may be hidden characters at the end of the CREATE statement.

|

Collectives™ on Stack Overflow

Err 1064 - You have an error in your SQL syntax; Cannot create table

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related