| 1 |
# |
| 2 |
# Print the foreign key definitions for the given table and then |
| 3 |
# verify that they are consistent across all connected mysqld DDs and NDB |
| 4 |
# |
| 5 |
# ==== Usage ==== |
| 6 |
# [--let $ndb_db_name= <database name>] |
| 7 |
# --let $ndb_table_name= <table name> |
| 8 |
# --source verify_foreign_keys.inc |
| 9 |
# |
| 10 |
# Parameters: |
| 11 |
# $ndb_db_name, $ndb_table_name |
| 12 |
# DB and name of the table from which the names of |
| 13 |
# FKs should to be printed and verified |
| 14 |
# Note : default db name "test" will be used if |
| 15 |
# none is passed to $ndb_db_name |
| 16 |
# |
| 17 |
|
| 18 |
# Table name is mandatory option |
| 19 |
if (!$ndb_table_name) { |
| 20 |
--die ERROR IN TEST: Please set $ndb_table_name before calling verify_foreign_keys.inc script |
| 21 |
} |
| 22 |
|
| 23 |
if (!$ndb_db_name) { |
| 24 |
let $ndb_db_name=test; |
| 25 |
} |
| 26 |
|
| 27 |
# Print foreign key definitions using show create table |
| 28 |
eval SHOW CREATE TABLE $ndb_db_name.$ndb_table_name; |
| 29 |
|
| 30 |
--disable_query_log |
| 31 |
|
| 32 |
# Create view which displays all user tables and foreign keys. |
| 33 |
# NOTE! Uses a format which is backward compatible with the output |
| 34 |
# from "ndb_show_tables" previously used by this test |
| 35 |
CREATE VIEW test.ndb_object_list AS |
| 36 |
SELECT |
| 37 |
id, |
| 38 |
/* Hardcoded names for the types selected */ |
| 39 |
CASE type |
| 40 |
WHEN 2 THEN "UserTable" |
| 41 |
WHEN 25 THEN "ForeignKey" |
| 42 |
ELSE "UnexpectedType" |
| 43 |
END AS type, |
| 44 |
|
| 45 |
/* Database is first part of fq_name for user tables */ |
| 46 |
CASE type |
| 47 |
WHEN 2 THEN SUBSTRING_INDEX(fq_name, "/", 1) |
| 48 |
ELSE "" |
| 49 |
END AS _database, |
| 50 |
|
| 51 |
/* Table name is last part of fq_name for user tables and fq_name for fk */ |
| 52 |
CASE type |
| 53 |
WHEN 2 THEN SUBSTRING_INDEX(fq_name, "/", -1) |
| 54 |
ELSE fq_name |
| 55 |
END AS name |
| 56 |
FROM ndbinfo.dict_obj_info WHERE type IN ( 2 /* User table */, |
| 57 |
25 /* Foreign key definition */); |
| 58 |
|
| 59 |
# Find the object id of the table whose FKs we have to list and verify |
| 60 |
let $ndb_table_id= `SELECT id FROM ndb_object_list |
| 61 |
WHERE type = "UserTable" |
| 62 |
AND _database = "$ndb_db_name" |
| 63 |
AND name = "$ndb_table_name"`; |
| 64 |
|
| 65 |
# Now list all the foreign key names using this table id. |
| 66 |
# They will be of form <parent_id>/<$table_id(child)>/fk_name |
| 67 |
# Also extract the parent table name to verify it with the ones stored in DD |
| 68 |
CREATE TABLE test.ndb_fk_info (name VARCHAR(255), parent VARCHAR(255)) engine ndb; |
| 69 |
eval INSERT INTO test.ndb_fk_info |
| 70 |
SELECT REGEXP_REPLACE(fk_info.name, '[0-9]+/$ndb_table_id/', ''), parent_info.name |
| 71 |
FROM test.ndb_object_list as fk_info, test.ndb_object_list as parent_info |
| 72 |
WHERE fk_info.type = 'ForeignKey' |
| 73 |
AND fk_info.name LIKE '%/$ndb_table_id/%' |
| 74 |
AND parent_info.type = 'UserTable' |
| 75 |
AND parent_info.id = REGEXP_REPLACE(fk_info.name, '/$ndb_table_id/.+', ''); |
| 76 |
|
| 77 |
let $ndb_fk_count= `SELECT count(*) FROM ndb_fk_info`; |
| 78 |
|
| 79 |
# Loop all the connected mysqlds, print and verify foreign keys |
| 80 |
let $i = 1; |
| 81 |
while($i <= $NUM_MYSQLDS) |
| 82 |
{ |
| 83 |
--connection mysqld$i |
| 84 |
|
| 85 |
# Verify that the same set of FKs are present in NDB and DD |
| 86 |
# Do left and right outer joins to detect if either DD or NDB has |
| 87 |
# any foreign key that does not have a match yet. |
| 88 |
let $fks_in_ndb_but_not_in_dd= |
| 89 |
`SELECT COUNT(*) |
| 90 |
FROM test.ndb_fk_info as ndb |
| 91 |
LEFT JOIN information_schema.referential_constraints as dd |
| 92 |
ON ndb.name = dd.constraint_name |
| 93 |
WHERE dd.constraint_name is NULL |
| 94 |
AND dd.referenced_table_name = ndb.parent`; |
| 95 |
|
| 96 |
let $fks_in_dd_but_not_in_ndb= |
| 97 |
`SELECT COUNT(*) |
| 98 |
FROM test.ndb_fk_info as ndb |
| 99 |
RIGHT JOIN information_schema.referential_constraints as dd |
| 100 |
ON ndb.name = dd.constraint_name |
| 101 |
WHERE dd.constraint_schema='$ndb_db_name' |
| 102 |
AND dd.table_name='$ndb_table_name' |
| 103 |
AND ndb.name is NULL |
| 104 |
AND dd.referenced_table_name = ndb.parent`; |
| 105 |
|
| 106 |
if ($fks_in_ndb_but_not_in_dd != 0 || |
| 107 |
$fks_in_dd_but_not_in_ndb != 0) { |
| 108 |
# There is a foreign key mismatch. Print the list of |
| 109 |
# foreign keys in this DD and NDB to help debug. |
| 110 |
eval SELECT name |
| 111 |
AS 'Foreign Keys in NDB on table `$ndb_db_name.$ndb_table_name`' |
| 112 |
FROM ndb_fk_info ORDER BY name ASC; |
| 113 |
|
| 114 |
eval SELECT constraint_name |
| 115 |
AS 'Foreign Keys in mysqld$i\'s DD on table `$ndb_db_name.$ndb_table_name`' |
| 116 |
FROM information_schema.referential_constraints |
| 117 |
WHERE constraint_schema='$ndb_db_name' |
| 118 |
AND table_name='$ndb_table_name' |
| 119 |
ORDER BY constraint_name ASC; |
| 120 |
|
| 121 |
--die Foreign keys mismatch in NDB and DD |
| 122 |
} |
| 123 |
|
| 124 |
inc $i; |
| 125 |
} |
| 126 |
|
| 127 |
# Successfully verified |
| 128 |
if ($ndb_fk_count == 0) { |
| 129 |
--echo No foreign keys found on table `$ndb_db_name.$ndb_table_name` |
| 130 |
} |
| 131 |
if ($ndb_fk_count > 0) { |
| 132 |
--echo Foreign keys consistent across NDB and all connected mysqld's DDs |
| 133 |
} |
| 134 |
|
| 135 |
# Drop the info table and view |
| 136 |
--connection mysqld1 |
| 137 |
DROP TABLE test.ndb_fk_info; |
| 138 |
DROP VIEW test.ndb_object_list; |
| 139 |
|
| 140 |
--enable_query_log |
| 141 |
|
| 142 |
# Reset input parameters |
| 143 |
--let $ndb_db_name= |
| 144 |
--let $ndb_table_name= |