[DC-368] createTablesFromModels() wants to create already existing FOREIGN KEY constraints Created: 18/Dec/09  Updated: 24/Aug/10

Status: Open
Project: Doctrine 1
Component/s: Import/Export
Affects Version/s: 1.2.1
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Mike Reiche Assignee: Jonathan H. Wage
Resolution: Unresolved Votes: 1
Labels: None
Environment:

mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2
PHP 5.2.11-0.dotdeb.1 with Suhosin-Patch 0.9.7 (cli) (built: Sep 20 2009 09:41:43)
Ubuntu 9.04


Attachments: Text File schema.txt     Zip Archive testcase.zip    

 Description   

If you create InnoDB tables from a given model directory, the table generator wants to create a foreign key constraint that already exists.

1. Doctrine_Core::generateModelsFromYaml('yaml', 'models');
2. Doctrine_Core::createTablesFromModels('models');
3. Running this setup twice, will print out the following error:

SQLSTATE[HY000]: General error: 1005 Can't create table './dev_mreiche/#sql-930_2cb7.frm' (errno: 121). Failing Query: "ALTER TABLE group_user ADD CONSTRAINT group_user_user_id_user_id FOREIGN KEY (user_id) REFERENCES user(id)". Failing Query: ALTER TABLE group_user ADD CONSTRAINT group_user_user_id_user_id FOREIGN KEY (user_id) REFERENCES user(id)



 Comments   
Comment by Mike Reiche [ 18/Dec/09 ]

Moved model and setup code from description to attachment.

Comment by Michal Olszewski [ 09/Feb/10 ]

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.

Comment by PpW8bNH3jy2A [ 01/Mar/10 ]

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.

Comment by PpW8bNH3jy2A [ 01/Mar/10 ]

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)"

Comment by B. Ariston Darmayuda [ 23/Aug/10 ]

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).

Comment by B. Ariston Darmayuda [ 24/Aug/10 ]

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.

Generated at Thu Nov 27 04:42:41 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.