createTablesFromModels() wants to create already existing FOREIGN KEY constraints

I've figure out the errorno: 121 which I thinking is that the process try re-adding existing CONSTRAINT name. The resolve that I've found is for MySQL engine and modify some Doctrine libraries.

1. I've modify Doctrine_Export_Mysql by adding override function of:

public function createForeignKeySql($table, array $definition)
{
    $tableInfo = $this->conn->execute('SHOW CREATE TABLE '.$this->conn->quoteIdentifier($table));
    if ($tableInfo && ($row = $tableInfo->fetch()))
      if ($row['Create Table'] && preg_match('/CONSTRAINT `'.$definition['name'].'`.+/i', $row['Create Table'])) return '';
    return parent::createForeignKeySql($table, $definition);
}

2. Since if CONSTRAINT already exist the createForeignKeySql return empty string, we need modify function on Doctrine_Export with:

public function createForeignKey($table, array $definition)
{
    $sql = $this->createForeignKeySql($table, $definition);
    if ($sql != '')
      return $this->conn->execute($sql);
    else
      return -1;
}

Now by add/modify function above I can savely run createTablesFromModels because if CONSTRAINT of a table exist on database the ALTER TABLE ... ADD CONSTRAINT will not generated. Hope this can help some of you guys.

I have same problem, but this problem is face on db engine side. When we look at the last query sql like:

ALTER TABLE category ADD CONSTRAINT category_company_id_company_id FOREIGN KEY (company_id) REFERENCES company(id)

The first call of createTablesFromModels will create the table and add contraint which run fine because table and constraint not exist yet. Then when we execute createTablesFromModels again it will fail because it still try to create constraint which already exist on table. I'm still looking on MySql section how to check the constrain if it exist or not. If the constraint not exist then we can execute the sql above, if not we need to avoid execution for sql above, this mean we cannot MODIFY the constrain (like ON UPDATE and ON DELETE mode).

I put some debugging statements in Doctrine/Export.php around line 1216:
------------
$connection->exec($query);
} catch (Doctrine_Connection_Exception $e) {
// we only want to silence table already exists errors
+ print_r($e->getPortableCode());
+ print_r(Doctrine_Core::ERR_ALREADY_EXISTS);
+ print_r($e->getMessage());
+ echo "<br><br>";
+// exit;
+
+
if ($e->getPortableCode() !== Doctrine_Core::ERR_ALREADY_EXISTS) {
$connection->rollback();

----------------------

Here's the output I get:

-5-5SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'category' already exists. Failing Query: "CREATE TABLE category (id BIGINT AUTO_INCREMENT, name VARCHAR(80), company_id BIGINT, INDEX company_id_idx (company_id), PRIMARY KEY(id)) ENGINE = INNODB"

-5-5SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'company' already exists. Failing Query: "CREATE TABLE company (id BIGINT AUTO_INCREMENT, name VARCHAR(80) UNIQUE, address1 VARCHAR(80), address2 VARCHAR(80), city VARCHAR(80), state VARCHAR(10), zipcode VARCHAR(20), phone VARCHAR(20), fax VARCHAR(20), url VARCHAR(255), logo VARCHAR(255), perferedcolumn BIGINT, randomsort BIGINT, comment LONGTEXT, PRIMARY KEY(id)) ENGINE = INNODB"

-5-5SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'user' already exists. Failing Query: "CREATE TABLE user (email VARCHAR(255), password VARCHAR(80), fullname VARCHAR(80), company_id BIGINT, cellphone VARCHAR(20), voice VARCHAR(20), voiceext VARCHAR(20), fax VARCHAR(20), comment LONGTEXT, status TINYINT(1), admin TINYINT(1), timeadded DATETIME, timechagned DATETIME, INDEX company_id_idx (company_id), PRIMARY KEY(email)) ENGINE = INNODB"

-15-5SQLSTATE[HY000]: General error: 1005 Can't create table './iprfpartners/#sql-7eeb_2d8.frm' (errno: 121). Failing Query: "ALTER TABLE category ADD CONSTRAINT category_company_id_company_id FOREIGN KEY (company_id) REFERENCES company(id)"

I'm having the same issue. However, I've tried the test case using a sqlite memory only database and i can't trigger the problem.

Here's my mysql schema, for your review - which does trigger the issue using 1.2.1.

Hi,

Just a quick comment - the test case you've provided works fine for me using 1.2.1 and also latest code from 1.2 branch... you might try that.

The error above (1005) is caused by mismatch between local and foreign key definitions. So it seems somewhere along the lines you/Doctrine messed up field definitions (although I can't see it provided test case). Try using integer without length definition in YAML, like:

id:
type: integer
autoincrement: true
primary: true

and check if this is working.

Moved model and setup code from description to attachment.