DDC-2522: When changing a manyToMany relationship to a stand alone table with the same table name, doctrine fails to properly update schema.

[doctrinebot]

Jira issue originally created by user jmoore2026:

To start with I created a manyToMany relationship in my user entity to my referrers entity. The association was named "referrals" and used a table named "user_referrals" as the manyToMany join table.

I later removed the manyToMany join association in favor of a stand-alone entity. I created an entity named UserReferrals. I kept the table name "user_referrals".

When doctrine attempts to update the mysql database schema, I receive this error...

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

This is the SQL attempting to be executed:
ALTER TABLE user_referrals ADD id INT AUTO_INCREMENT NOT NULL, ADD created DATETIME NOT NULL, ADD updated DATETIME NOT NULL, ADD status VARCHAR(255) NOT NULL, CHANGE referrer_id referrer_id INT DEFAULT NULL, CHANGE user_id user_id INT DEFAULT NULL

Is this a bug? Running the SQL directly in MYSQL also fails with the same error.

[doctrinebot]

Comment created by peterrehm:

In the SQL Statement there is the primary key definition missing. In your case the adjustment to

ALTER TABLE user_referrals ADD id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (id), ADD created DATETIME NOT NULL, ADD updated DATETIME NOT NULL, ADD status VARCHAR(255) NOT NULL, CHANGE referrer_id referrer_id INT DEFAULT NULL, CHANGE user_id user_id INT DEFAULT NULL

should make it.

If have the same issue where the schema tool / migrations generated the following statements:

ALTER TABLE ArticleToSet DROP PRIMARY KEY
ALTER TABLE ArticleToSet ADD id INT AUTO_INCREMENT NOT NULL, CHANGE article_id article_id INT DEFAULT NULL, CHANGE articleSet_id articleSet_id INT DEFAULT NULL
ALTER TABLE ArticleToSet ADD PRIMARY KEY (id)

Updating it manually to the following fixes it:

ALTER TABLE ArticleArticleToSet DROP PRIMARY KEY"
ALTER TABLE ArticleArticleToSet ADD id INT AUTO_INCREMENT NOT NULL, ADD PRIMARY KEY (id), CHANGE article_id article_id INT DEFAULT NULL, CHANGE articleSet_id articleSet_id INT DEFAULT NULL

The situation appeared when I have changed from a composite key to a separate key.

[weaverryan]

I can confirm this is still an issue for both the ORM and DBAL at version 2.5.4. It's exactly as described, and manually updating the statement to move the ADD PRIMARY KEY (id) into the ALTER TABLE as described above fixes it.

But, I'm not quite smart enough to fix it :)

[lesterlpena]

I am having the same problem. Any update on this?

[arnegroskurth]

+1

Still an issue with dbal 2.8.0

edit: will probably be fixed by mr 3311 of doctrine/dbal