DDC-2134: Add referential integrity check for MySQL to console commands #2822

Open
doctrinebot opened this Issue Nov 9, 2012 · 8 comments

4 participants

@doctrinebot

Jira issue originally created by user holtkamp:

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:

SET FOREIGN*KEY*CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN*KEY*CHECKS = 1;

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

#Check the constraints of a specific database
SELECT *
    FROM INFORMATION*SCHEMA.KEY_COLUMN*USAGE 
        WHERE TABLE_SCHEMA = 'databaseName'
        AND REFERENCED*TABLE*NAME IS NOT NULL

#Generate table specific queries to find orphaned entries
SELECT CONCAT(
     'SELECT ', GROUP*CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, '.', P.COLUMN*NAME,
      ' AS `', P.TABLE*SCHEMA, '.', P.TABLE_NAME, '.', P.COLUMN_NAME, '`') ORDER BY P.ORDINAL*POSITION), ' ',
        'FROM ', K.TABLE*SCHEMA, '.', K.TABLE_NAME, ' AS ', K.CONSTRAINT*NAME, ' ',
            'LEFT OUTER JOIN ', K.REFERENCED*TABLE_SCHEMA, '.', K.REFERENCED_TABLE_NAME, ' AS ', K.REFERENCED_TABLE*NAME, ' ',
            ' ON (', GROUP*CONCAT(CONCAT(K.CONSTRAINT_NAME, '.', K.COLUMN_NAME) ORDER BY K.ORDINAL*POSITION),
            ') = (', GROUP*CONCAT(CONCAT(K.REFERENCED_TABLE_NAME, '.', K.REFERENCED_COLUMN_NAME) ORDER BY K.ORDINAL*POSITION), ') ',
            'WHERE ', K.REFERENCED*TABLE_NAME, '.', K.REFERENCED_COLUMN*NAME, ' IS NULL;'
      )
    INTO OUTFILE '/tmp/verifyDatabaseTableIntegrity.sql'
    FROM INFORMATION*SCHEMA.KEY_COLUMN*USAGE K
      INNER JOIN INFORMATION*SCHEMA.KEY_COLUMN*USAGE P
        ON (K.TABLE*SCHEMA, K.TABLE_NAME) = (P.TABLE_SCHEMA, P.TABLE*NAME)
        AND P.CONSTRAINT_NAME = 'PRIMARY'
    WHERE K.TABLE_SCHEMA = 'databaseName'
      AND K.REFERENCED*TABLE*NAME IS NOT NULL
      GROUP BY K.CONSTRAINT_NAME;

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?

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@marcobuschini

I love this idea! May I try, and work on implementing it?

@Ocramius
Doctrine member

@marcobuschini this seems to be mysql-specific, so it wouldn't be merged into doctrine2. I suggest writing an external tool that acceots a doctrine schema instance or a PDO/DBAL connection

@marcobuschini

@Ocramius what you say seems so obvious that I forgot to mention it in my previous post.
I think everything can be done in a database agnostic way using Doctrine documentation at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/schema-manager.html but I didn't study that documentation, yet.

@marcobuschini

@Ocramius Ok, I wrote the code to do that using Doctrine APIs instead of db specific queries. On a first analysis it works on a simple case. I tried to follow the contributing guides for properly integrating my changes before going on, but I fail at installing d51PearPkg2Task because the URL reported in the documentation http://pear.domain51.com/svn/Phing_d51PearPkg2Task/trunk/src/phing/tasks/ext/d51PearPkg2Task.php appears to be no longer available. How can I build Doctrine without it?

@Ocramius
Doctrine member

@marcobuschini where are you finding this stuff? Running DBAL tests is basically just composer update + ./vendor/bin/phpunit.

@marcobuschini

@Ocramius the guide that I am following to write a patch (at http://www.doctrine-project.org/contribute.html) mentions the phing tool in the Basic Build section, and it fails with:

Buildfile: /home/ubuntu/workspace/doctrine2/build.xml
[PHP Error] include_once(phing/tasks/ext/d51PearPkg2Task.php): failed to open stream: No such file or directory [line 1299 of /usr/share/php/phing/Phing.php]
[PHP Error] include_once(): Failed opening 'phing/tasks/ext/d51PearPkg2Task.php' for inclusion (include_path='/usr/share/php/../classes:.:/usr/share/php:/usr/share/pear') [line 1299 of /usr/share/php/phing/Phing.php]

BUILD FAILED
exception 'ConfigurationException' with message 'Error importing phing/tasks/ext/d51PearPkg2Task.php' in /usr/share/php/phing/Phing.php:1307
Stack trace:
#0 /usr/share/php/phing/Phing.php(1254): Phing::__import('phing/tasks/ext...', NULL)
#1 /usr/share/php/phing/Project.php(630): Phing::import('phing.tasks.ext...', NULL)
#2 /usr/share/php/phing/tasks/system/TaskdefTask.php(155): Project->addTaskDefinition('d51pearpkg2', 'phing.tasks.ext...', NULL)
#3 /usr/share/php/phing/UnknownElement.php(100): TaskdefTask->main()
#4 /usr/share/php/phing/Task.php(283): UnknownElement->main()
#5 /usr/share/php/phing/Target.php(336): Task->perform()
#6 /usr/share/php/phing/parser/ProjectConfigurator.php(197): Target->main()
#7 /usr/share/php/phing/parser/ProjectConfigurator.php(74): ProjectConfigurator->parse()
#8 /usr/share/php/phing/Phing.php(679): ProjectConfigurator::configureProject(Object(Project), Object(PhingFile))
#9 /usr/share/php/phing/Phing.php(196): Phing->runBuild()
#10 /usr/share/php/phing/Phing.php(343): Phing::start(Array, NULL)
#11 /usr/share/php/phing.php(58): Phing::fire(Array)
#12 {main}

Total time: 0.3560 seconds

#0 /usr/share/php/phing/Phing.php(1254): Phing::__import('phing/tasks/ext...', NULL)
#1 /usr/share/php/phing/Project.php(630): Phing::import('phing.tasks.ext...', NULL)
#2 /usr/share/php/phing/tasks/system/TaskdefTask.php(155): Project->addTaskDefinition('d51pearpkg2', 'phing.tasks.ext...', NULL)
#3 /usr/share/php/phing/UnknownElement.php(100): TaskdefTask->main()
#4 /usr/share/php/phing/Task.php(283): UnknownElement->main()
#5 /usr/share/php/phing/Target.php(336): Task->perform()
#6 /usr/share/php/phing/parser/ProjectConfigurator.php(197): Target->main()
#7 /usr/share/php/phing/parser/ProjectConfigurator.php(74): ProjectConfigurator->parse()
#8 /usr/share/php/phing/Phing.php(679): ProjectConfigurator::configureProject(Object(Project), Object(PhingFile))
#9 /usr/share/php/phing/Phing.php(196): Phing->runBuild()
#10 /usr/share/php/phing/Phing.php(343): Phing::start(Array, NULL)
#11 /usr/share/php/phing.php(58): Phing::fire(Array)
#12 {main}Error importing phing/tasks/ext/d51PearPkg2Task.php

Of course now I understand that I am missing something about the build process.

@Ocramius
Doctrine member

@marcobuschini I'll check where this is and remove it from the docs: evidently it's outdated :-)

@Ocramius
Doctrine member
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment