Doctrine 2 - ORM

Add referential integrity check for MySQL to console commands

Details

  • Type: New Feature New Feature
  • Status: Open Open
  • Priority: Minor Minor
  • Resolution: Unresolved
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None
  • Environment:
    MySQL

Description

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?

Activity

Ascending order - Click to sort in descending order
Menno Holtkamp made changes -
Field Original Value New Value
Description 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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;'
) AS _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 TABLE_SCHEMA = 'databaseName'
AND K.REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY K.CONSTRAINT_NAME;

{code}
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? 		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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;'
) AS _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;

{code}
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?
Menno Holtkamp made changes -
Description 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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;'
) AS _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;

{code}
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? 		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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;

{code}
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?
Menno Holtkamp made changes -
Description 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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;

{code}
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? 		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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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 = 'caos_13_menno'
      AND K.REFERENCED_TABLE_NAME IS NOT NULL
      GROUP BY K.CONSTRAINT_NAME;

{code}
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?
Menno Holtkamp made changes -
Description 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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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 = 'caos_13_menno'
      AND K.REFERENCED_TABLE_NAME IS NOT NULL
      GROUP BY K.CONSTRAINT_NAME;

{code}
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? 		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:


{code}
SET FOREIGN_KEY_CHECKS = 0;
#IMPORT STUFF from CSV
SET FOREIGN_KEY_CHECKS = 1;
{code}

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


{code}
#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

{code}

{code}
#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;

{code}
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?
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-2134, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Menno Holtkamp
Vote (0)
Watch (1)

Dates

  • Created:
    Updated: