Sql syntax issue with adding values

Question

I have an error in my sql on second addSql() line:

$this->addSql('CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, `key` VARCHAR(60) NOT NULL, value VARCHAR(60) NOT NULL, UNIQUE INDEX UNIQ_E545A0C58A90ABA9 (`key`), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');

$this->addSql("INSERT INTO user (id,key,value) VALUES (1,'theKey','14')");

SQLSTATE[42000]: Syntax error or access violation: 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 'key,value) VALUES (1,'theKey','14')' at line 1

Can someone help to locate it..

Do not supply the name/value for the AUTO_INCREMENT column btw. — Alex K., Nov 25 '20 at 14:15
You need backticks around the column name `key`, same as you have in the first statement. It's a reserved word. — iainn, Nov 25 '20 at 14:16

score 1 · Answer 1 · answered Nov 25 '20 at 14:18

The 'key` word is reserved word in MySQL. I advice to avoid using reserved as column names. Any way is it necessary use backticks to quote them:

$this->addSql('CREATE TABLE user (`id` INT AUTO_INCREMENT NOT NULL, `key` VARCHAR(60) NOT NULL, `value` VARCHAR(60) NOT NULL, UNIQUE INDEX UNIQ_E545A0C58A90ABA9 (`key`), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB');

$this->addSql("INSERT INTO user (`id`, `key`, `value`) VALUES (1,'theKey','14')");

Test it on SQLize.online

Great! This did it. Thank you. :) @SlavaRozhnev – joopeerr Nov 25 '20 at 14:21 — joopeerr, Nov 25 '20 at 14:21

score 0 · Answer 2 · answered Nov 25 '20 at 14:14

0

changing VALUE to VALUES should do the trick

answered Nov 25 '20 at 14:14

Andy Edgar

86
1
5

I have. Same error. @AndrewEdgar – joopeerr Nov 25 '20 at 14:15

Sql syntax issue with adding values

2 Answers2