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

Open
doctrinebot opened this Issue Jun 20, 2013 · 1 comment

2 participants

@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 userreferrals 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 userid 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 userreferrals 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 userid 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 AUTOINCREMENT NOT NULL, CHANGE article_id article_id INT DEFAULT NULL, CHANGE articleSet_id articleSetid 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 AUTOINCREMENT NOT NULL, ADD PRIMARY KEY (id), CHANGE article_id article_id INT DEFAULT NULL, CHANGE articleSet_id articleSetid INT DEFAULT NULL

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

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment