[DDC-2134] Add referential integrity check for MySQL to console commands Created: 09/Nov/12 Updated: 09/Nov/12
|Project:||Doctrine 2 - ORM|
|Reporter:||Menno Holtkamp||Assignee:||Benjamin Eberlei|
Today I spent some time solving a PHP 'White Screen of Death'. I traced it back to a Entity of which the proxy's __load() function was invoked because af a EXTRA_LAZY association. Due to incorrect database contents (the entry ID was changed due to an update: referential integrity broke), the __load() query resulted in no results. The EntityNotFoundException did for some reason not show up in our logs, probably because the lazy load was triggered by a magic __toString() function.
The cause is because of the way we populate or tables with domain data:
MySQL does not trigger any errors when the foreign key checks are turned back on, leaving the table in an inconsistent state.
To prevent this, I found some information in this post: http://www.mysqlperformanceblog.com/2011/11/18/eventual-consistency-in-mysql/, which I used to come with the following queries
By running the generated queries, we can now easily find the records that break referential integrity.
It might be an idea of adding this functionality to the orm:validate-schema, or a new orm:validate-database-integrity?