doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-24 06:52:09 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

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

New Issue
Closed
opened 2026-01-22 14:00:36 +01:00 by admin · 9 comments
admin commented 2026-01-22 14:00:36 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Nov 9, 2012).

Originally assigned to: @Ocramius on GitHub.

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?

Originally created by @doctrinebot on GitHub (Nov 9, 2012). Originally assigned to: @Ocramius on GitHub. 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?
admin added the New FeatureWon't Fix labels 2026-01-22 14:00:36 +01:00
admin closed this issue 2026-01-22 14:00:37 +01:00
admin commented 2026-01-22 14:00:38 +01:00
Author
Owner
Copy Link

@marcobuschini commented on GitHub (Jul 7, 2016):

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

@marcobuschini commented on GitHub (Jul 7, 2016): I love this idea! May I try, and work on implementing it?
admin commented 2026-01-22 14:00:38 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Jul 7, 2016):

@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

@Ocramius commented on GitHub (Jul 7, 2016): @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
admin commented 2026-01-22 14:00:39 +01:00
Author
Owner
Copy Link

@marcobuschini commented on GitHub (Jul 7, 2016):

@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 commented on GitHub (Jul 7, 2016): @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.
admin commented 2026-01-22 14:00:40 +01:00
Author
Owner
Copy Link

@marcobuschini commented on GitHub (Jul 9, 2016):

@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?

@marcobuschini commented on GitHub (Jul 9, 2016): @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?
admin commented 2026-01-22 14:00:40 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Jul 11, 2016):

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

@Ocramius commented on GitHub (Jul 11, 2016): @marcobuschini where are you finding this stuff? Running DBAL tests is basically just `composer update` + `./vendor/bin/phpunit`.
admin commented 2026-01-22 14:00:41 +01:00
Author
Owner
Copy Link

@marcobuschini commented on GitHub (Jul 11, 2016):

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

@marcobuschini commented on GitHub (Jul 11, 2016): @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.
admin commented 2026-01-22 14:00:41 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Jul 11, 2016):

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

@Ocramius commented on GitHub (Jul 11, 2016): @marcobuschini I'll check where this is and remove it from the docs: evidently it's outdated :-)
admin commented 2026-01-22 14:00:42 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Jul 11, 2016):

Reported at https://github.com/doctrine/doctrine-website-sphinx/issues/130

@Ocramius commented on GitHub (Jul 11, 2016): Reported at https://github.com/doctrine/doctrine-website-sphinx/issues/130
admin commented 2026-01-22 14:00:43 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Sep 7, 2016):

Closing: won't be implemented in core (see #5964)

@Ocramius commented on GitHub (Sep 7, 2016): Closing: won't be implemented in core (see #5964)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label New Feature Won't Fix
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#2686
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?