Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 0.8.6

Request #15886 Import Problem with Foreign Keys
Submitted: 2009-02-09 16:25 UTC
From: tacki Assigned:
Status: Open Package: MDB2_Schema
PHP Version: 5.2.8 OS: Fedora
Roadmaps: (Not assigned)    
Subscription  


 [2009-02-09 16:25 UTC] tacki (Markus Schlegel)
Description: ------------ I exported a simple mysql table-layout with 1 foreign key: <?xml version="1.0" encoding="ISO-8859-1" ?> <database> <name>test</name> <create>true</create> <overwrite>false</overwrite> <charset>utf8</charset> <table> <name>table1</name> <declaration> <field> <name>id</name> <type>integer</type> <default>0</default> <notnull>true</notnull> <autoincrement>1</autoincrement> <unsigned>true</unsigned> <length>4</length> </field> <field> <name>fk</name> <type>integer</type> <default>0</default> <notnull>true</notnull> <unsigned>true</unsigned> <length>4</length> </field> <index> <name>fk</name> <field> <name>fk</name> <sorting>ascending</sorting> </field> </index> <foreign> <name>table1_ibfk_1</name> <match>SIMPLE</match> <ondelete>NO ACTION</ondelete> <onupdate>NO ACTION</onupdate> <field>fk</field> <references> <table>table2</table> <field>id</field> </references> </foreign> </declaration> </table> <table> <name>table2</name> <declaration> <field> <name>id</name> <type>integer</type> <default>0</default> <notnull>true</notnull> <autoincrement>1</autoincrement> <unsigned>true</unsigned> <length>4</length> </field> <field> <name>data</name> <type>integer</type> <default></default> <notnull>true</notnull> <length>4</length> </field> </declaration> </table> </database> Now i try to import it into a new table and receive an error. I added a sql-dump to show you the table in sql... I think the problem is, that the fk is created before table2 even exists Test script: --------------- Script that i used to export the table: $schema =& MDB2_Schema::factory($dsn); $dump_options = array ( 'output_mode' => 'file', 'output' => 'schema.xml', 'end_of_line' => "\n" ); $definition = $schema->getDefinitionFromDatabase(); $op = $schema->dumpDatabase($definition, $dump_options, MDB2_SCHEMA_DUMP_ALL); var_dump($op); Script to import: $schema =& MDB2_Schema::factory($dsn); $definition = $schema->parseDatabaseDefinitionFile('schema.xml'); $op = $schema->createDatabase($definition, array()); var_dump($op); Expected result: ---------------- true Actual result: -------------- MDB2_Error UserInfo: _doQuery: [Error message: Could not execute statement] [Last executed query: ALTER TABLE table1 ADD CONSTRAINT table1_ibfk_1_idx FOREIGN KEY (fk) REFERENCES table2 (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION] [Native code: 1005] [Native message: Can't create table './test/#sql-4714_1c8.frm' (errno: 150)]

Comments

 [2009-02-22 19:47 UTC] ifeghali (Igor Feghali)
Hello Markus, This is a bug indeed. We need some kind of intelligence to determine the correct order of table creation. In the mean time did you tried swapping table1 and table2 in your XML file ? That should make MDB2_Schema create table2 first.
 [2009-02-22 19:47 UTC] ifeghali (Igor Feghali)
-Status: Open +Status: Feedback
 [2009-02-22 21:42 UTC] tacki (Markus Schlegel)
The following patch has been added/updated: Patch Name: simple_workaround URL: patch simple_workaround
 [2009-02-22 21:51 UTC] tacki (Markus Schlegel)
this patch worked for me... just make sure all tables are created before any of the foreign keys are added. dont know if this will crash something else, but phpmyadmin exports the same way
 [2009-02-23 01:46 UTC] ifeghali (Igor Feghali)
-Status: Feedback +Status: Open -Type: Bug +Type: Feature/Change Request
Hello Markus, your patch won't work for the cases where we are updating the schema. The ideal fix goes in the same direction of Request #12154.