doctrine manyToMany SQLSTATE[HY000]: General error: 1005 Can't create table (errno: 150)

Question

I have an issue with

/**
 * @package Entity
 * @ORM\Entity
 * @ORM\Table(name="r_country")
 */

class RCountry {
/**
 * @ORM\Column(type="string", length=2)
 * @ORM\Id
 */
protected $id;

...

    /**
 *
 * @ORM\ManyToMany(targetEntity="RLanguage")
 * @ORM\JoinTable(name="prohibited_language_display",
 *      joinColumns={@ORM\JoinColumn(name="country_id", referencedColumnName="id", onDelete="CASCADE")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="language_id", referencedColumnName="id", onDelete="CASCADE")}
 *      )
 *
 */
protected $prohibitedLanguageDisplay;

...

}

/**
 * @package Entity
 * @ORM\Entity
 * @ORM\Table(name="r_language")
 */
class RLanguage
{
    /**
     * @ORM\Column(type="string", length=20)
     * @ORM\Id
     *
     */
    protected $id;
...
}

i'm trying to do a ManyToMany relation with doctrine but when i do an update it fires an error

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'ALTER TABLE prohibited_language_display ADD CONSTRAINT FK_77B02D13F92F3E70 FOREIGN KEY (country_id) REFERENCES r_country (id) ON DELETE CASCADE':
SQLSTATE[HY000]: General error: 1005 Can't create table 'database.#sql-498_8797' (errno: 150)

                                                                                                         [Doctrine\DBAL\Driver\PDOException]                                   

SQLSTATE[HY000]: General error: 1005 Can't create table 'database.#sql-498_8797' (errno: 150)

                                                                                                         [PDOException]                                                        

SQLSTATE[HY000]: General error: 1005 Can't create table 'database.#sql-498_8797' (errno: 150)

this is th dump sql i get

CREATE TABLE prohibited_language_google (country_id VARCHAR(2) NOT NULL, language_id VARCHAR(20) NOT NULL, INDEX IDX_77B02D13F92F3E70 (country_id), INDEX IDX_77B02D1382F1BAF4 (language_id), PRIMARY KEY(country_id, language_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB; ALTER TABLE prohibited_language_display ADD CONSTRAINT FK_77B02D13F92F3E70 FOREIGN KEY (country_id) REFERENCES r_country (id) ON DELETE CASCADE; ALTER TABLE prohibited_language_google ADD CONSTRAINT FK_77B02D1382F1BAF4 FOREIGN KEY (language_id) REFERENCES r_language (id) ON DELETE CASCADE;

i've tryed de drop the database and create a new one with doctrine with and it's ok that way, but i want to just do an update otherwise i ill lose all data in my database.

Is there any one who can help me?

Answer 1

the problem was that Doctrine generates a script to create the table with collation utf_unicode_ci, i changed it to be utf8_general_ci in order to change that i had to add this to my doctrineconfiguration in app/config.yml

doctrine.dbal.default_table_options.collate: "utf8_general_ci"