doctrine schema update always asking for constraints and indexes

Question

i work with symfony on ubuntu 12.04, when i operate a doctrine schema update from command line, the dump always prints out that he has to make things like this:

ALTER TABLE melu_numeros ADD CONSTRAINT FK_8062E62AFB539063 FOREIGN KEY (id_fan) REFERENCES melu_fanzine (id_fan);
ALTER TABLE Image ADD CONSTRAINT FK_4FC2B5BABEDD0E7 FOREIGN KEY (id_num_fan) REFERENCES melu_numeros (id_num_fan);
CREATE INDEX IDX_4FC2B5BABEDD0E7 ON Image (id_num_fan);
DROP INDEX IDX_7B0247F15D172A78 ON melu_categories;
ALTER TABLE melu_categories ADD id_num_fan INT NOT NULL, DROP numero_id;
ALTER TABLE melu_categories ADD CONSTRAINT FK_7B0247F1ABEDD0E7 FOREIGN KEY (id_num_fan) REFERENCES melu_numeros (id_num_fan);
CREATE INDEX IDX_7B0247F1ABEDD0E7 ON melu_categories (id_num_fan);
ALTER TABLE melu_fichiers ADD CONSTRAINT FK_E2A048FCD6E65D60 FOREIGN KEY (id_agen) REFERENCES melu_agenda (id_agen);
ALTER TABLE melu_fanzine ADD CONSTRAINT FK_99AC1D58A656D38D FOREIGN KEY (id_asso) REFERENCES melu_association (id_asso);
ALTER TABLE melu_tarifs ADD CONSTRAINT FK_409B4C8D6E65D60 FOREIGN KEY (id_agen) REFERENCES melu_agenda (id_agen);
ALTER TABLE melu_newsletter CHANGE email email VARCHAR(50) NOT NULL;
ALTER TABLE melu_commentaire ADD CONSTRAINT FK_EE62B25DABEDD0E7 FOREIGN KEY (id_num_fan) REFERENCES melu_numeros (id_num_fan);
ALTER TABLE melu_contenus DROP INDEX FK_D9DCDC6130453DE8, ADD UNIQUE INDEX UNIQ_D9DCDC6130453DE8 (type_cont);
ALTER TABLE melu_contenus ADD CONSTRAINT FK_D9DCDC6130453DE8 FOREIGN KEY (type_cont) REFERENCES melu_types (id_types);
CREATE UNIQUE INDEX id_cont ON melu_contenus (id_cont);
ALTER TABLE melu_types ADD CONSTRAINT FK_8AB9AD2AB7B5C800 FOREIGN KEY (id_types) REFERENCES melu_contenus (type_cont);
ALTER TABLE melu_photos ADD CONSTRAINT FK_F5C79087D6E65D60 FOREIGN KEY (id_agen) REFERENCES melu_agenda (id_agen);
ALTER TABLE melu_jour ADD CONSTRAINT FK_85C738A2D6E65D60 FOREIGN KEY (id_agen) REFERENCES melu_agenda (id_agen);
DROP INDEX IDX_6C66C2856D2F0F48 ON melu_util_droit;
ALTER TABLE melu_util_droit DROP useur_id;
ALTER TABLE melu_util_droit ADD CONSTRAINT FK_6C66C28550EAE44 FOREIGN KEY (id_utilisateur) REFERENCES melu_utilisateurs (id_util);

and when i force the update, i have this error:

[Doctrine\DBAL\DBALException]                                                                                                   
An exception occurred while executing 'ALTER TABLE Image ADD CONSTRAINT FK_4FC2B5BABEDD0E7 FOREIGN KEY (id_num_fan) REFERENCES  melu_numeros (id_num_fan)':
SQLSTATE[HY000]: General error: 1005 Can't create table 'meluzineorg1.#sql-5cf_438' (errno: 150)

the 2 entites Image and melu_numero that provke the error http://pastie.org/9100202

how could i make this all work well ?

Answer 1

solved, i inherited data tables from an old bunch of myisam tables.

Doctrine seems to have issues with foreign keys in myisam engine, so i converted everything in innoDb and now there is no more jokes about schema update half done :)

thanks everyone

Answer 2

error 150 suggests you have something wrong with your primary keys, one example might be that you changed what is the key in one table, bu you didnt change the constraint on another table

try reading through this answer as its similar(if not identical) MySQL: Can't create table (errno: 150)

Answer 3

The problem is in your mapping for img

/**
 * @var arrayCollection
 * @ORM\OneToMany(targetEntity="Tykayn\MeluzineBundle\Entity\Image", mappedBy="numero", cascade={"persist", "merge"})
 * @ORM\JoinColumn(name="id_num_fan", referencedColumnName="id_num_fan")
 */
private $img;

as you seee you set the refrencedColumnName as id_num_fan but in you mapping in Image entity you did not defined id_num_fan as key to be used a foreign key in the top entity
You can change the mapping in Image entity as below and remove the $id property (if is not used as foreign key in other entity) or you can change the top mapping as * @ORM\JoinColumn(name="id", referencedColumnName="id") because id is a key, too.

/**
 * @var integer 
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 * @ORM\Column(name="id_num_fan", type="integer", nullable=false )
 */
private $idNumFan;