Debian Codegraph @

mysql-test/suite/ndb_ddl/verify_foreign_keys.inc

raw
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=