Symfony - SQLSTATE[42000]: Syntax error or access violation

Question

So I created 4 entities in Symfony using the make:entity command provided by the maker bundle. I then used the make:migration command to get the migration file generated by Symfony but when I try to run the command doctrine:migrations:migrate it outputs this error:

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 'groups (id INT AUTO_INCREMENT NOT NULL, ort_id INT NOT NULL, confession VARCHAR(' at line 1

I have no idea why it outputs an error as this file is automatically generated by Symfony with the make:migration command but here is the migrations file it generated

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20210705091302 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE event (id INT AUTO_INCREMENT NOT NULL, churchgroups_id INT NOT NULL, title VARCHAR(255) NOT NULL, date DATE NOT NULL, time TIME NOT NULL, language VARCHAR(255) NOT NULL, description VARCHAR(255) NOT NULL, INDEX IDX_3BAE0AA7B686F042 (churchgroups_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE groups (id INT AUTO_INCREMENT NOT NULL, ort_id INT NOT NULL, confession VARCHAR(255) NOT NULL, INDEX IDX_F06D3970B62F846A (ort_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE places (id INT AUTO_INCREMENT NOT NULL, place VARCHAR(255) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user (id INT AUTO_INCREMENT NOT NULL, churchgroup_id INT NOT NULL, email VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, firstname VARCHAR(255) NOT NULL, function VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, roles LONGTEXT NOT NULL COMMENT \'(DC2Type:array)\', INDEX IDX_8D93D649294D5D31 (churchgroup_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('ALTER TABLE event ADD CONSTRAINT FK_3BAE0AA7B686F042 FOREIGN KEY (churchgroups_id) REFERENCES groups (id)');
        $this->addSql('ALTER TABLE groups ADD CONSTRAINT FK_F06D3970B62F846A FOREIGN KEY (ort_id) REFERENCES places (id)');
        $this->addSql('ALTER TABLE user ADD CONSTRAINT FK_8D93D649294D5D31 FOREIGN KEY (churchgroup_id) REFERENCES groups (id)');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('ALTER TABLE event DROP FOREIGN KEY FK_3BAE0AA7B686F042');
        $this->addSql('ALTER TABLE user DROP FOREIGN KEY FK_8D93D649294D5D31');
        $this->addSql('ALTER TABLE groups DROP FOREIGN KEY FK_F06D3970B62F846A');
        $this->addSql('DROP TABLE event');
        $this->addSql('DROP TABLE groups');
        $this->addSql('DROP TABLE places');
        $this->addSql('DROP TABLE user');
    }
}

If you need anything else let me know in the comments and I'll be sure to edit the post asap

Please share more details - what have you tried to resolve the problem? How is such an error even related to PHP, or Symfony? — Nico Haase, Jul 05 '21 at 09:23

score 1 · Accepted Answer · answered Jul 05 '21 at 09:24

1

groups is a reserved keyword in MySQL, see https://dev.mysql.com/doc/refman/8.0/en/keywords.html for a full list of such keywords.

You should either quote the table name (with backticks), or use another table name in your entity (which is the better solution, as this avoids other problems that might occur in the future)

answered Jul 05 '21 at 09:24

Nico Haase

11,420
35
43
69

oh yeah that seems to be the problem. Weird.. normally when you generate entities through console with the make:entity command it tells you with an error if the name for the entity you use is a reserved word. for some reason it didnt there – madebyjo_l Jul 05 '21 at 09:47

Symfony - SQLSTATE[42000]: Syntax error or access violation

1 Answers1