[DDC-2522] When changing a manyToMany relationship to a stand alone table with the same table name, doctrine fails to properly update schema. Created: 20/Jun/13  Updated: 05/Nov/13

Status: Open
Project: Doctrine 2 - ORM
Component/s: Tools
Affects Version/s: 2.2.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Jeremy Moore Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 2
Labels: None
Environment:

Discovered while using doctrine orm in Symfony 2.1. MySQL



 Description   

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.



 Comments   
Comment by Peter Rehm [ 05/Nov/13 ]

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.

Generated at Mon Nov 24 20:22:08 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.