Failure to insert test data in MySQL - depends on first inserted row inserting all columns

[crantok]

I have a two-column InnoDB MySQL table generated by Idiorm Phinx that looks like this...

CREATE TABLE `details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `derived_from_detail_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `derived_from_detail_id` (`derived_from_detail_id`),
  CONSTRAINT `details_ibfk_1` FOREIGN KEY (`derived_from_detail_id`) REFERENCES `details` (`id`)
)

On the second column (called "derived_from_detail_id") is a foreign key constraint, so that any non-null values in that column must be present in the first column (called "id").

I can insert a row with a non-null value for "derived_from_detail_id" in PhpMyAdmin. Trying this in dbunit silently fails to insert the value of "derived_from_detail_id", which instead is NULL.

I'm using code copied (apart from the class name) from the MyApp_DbUnit_ArrayDataSet example in order to initialise tables with test data.

When my test data looks like this

[
    'details' => [
        [ 'id' => 1 ],
        [ 'id' => 2 ],
    ]
];

...the tables are initialised as I expect them to be, i.e.

id	derived_from_detail_id
1	NULL
2	NULL

And when my test data looks like this...

[
    'details' => [
        [ 'id' => 1 ],
        [ 'id' => 2, 'derived_from_detail_id' => 10 ],
    ]
];

...I get an appropriate error message, i.e.

PHPUnit_Extensions_Database_Operation_Exception: COMPOSITE[INSERT] operation failed on query: 
                INSERT INTO `details`
                (`id`, `derived_from_detail_id`)
                VALUES
                (?, ?)
             using args: Array
(
    [0] => 1
    [1] => 10
)
 [SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`rpb_test`.`details`, CONSTRAINT `details_ibfk_1` FOREIGN KEY (`derived_from_detail_id`) REFERENCES `details` (`id`))]

But when my test data looks like this...

[
    'details' => [
        [ 'id' => 1 ],
        [ 'id' => 2, 'derived_from_detail_id' => 1 ],
    ]
];

...I get still get this table content:

id	derived_from_detail_id
1	NULL
2	NULL

[crantok]

I would be as happy to see a workaround as I would to see a fix for this, just so I could get on with developing that part of my app.

[crantok]

I stripped out the foreign key mentioned in my first OP to make the table even simpler and the same behaviour (except for the foreign key violation) occurred.

Now, when my test data looks like this...

[
    'details' => [
        [ 'id' => 1 ],
        [ 'id' => 2, 'derived_from_detail_id' => 1 ],
    ]
];

...I get this table content:

id	derived_from_detail_id
1	NULL
2	NULL

And when my test data looks like this...

[
    'details' => [
        [ 'id' => 1, 'derived_from_detail_id' => NULL ],
        [ 'id' => 2, 'derived_from_detail_id' => 10 ],
    ]
];

...I then get this table content:

id	derived_from_detail_id
1	NULL
2	1

Is this intended behaviour?

If not then at least I now have a workaround, i.e. explicitly insert NULL values in to the first row of test data.

[crantok]

BTW, even though (in my OP) the relevant data never made it in to the DB, it still got far enough along the pipeline to cause a FK violation, which seems a bit crazy.

[stale[bot]]

This issue has been automatically marked as stale because it has not had activity within the last 60 days. It will be closed after 7 days if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had activity since it was marked as stale. Thank you for your contributions.