Debian Codegraph @

mysql-test/suite/ndb/t/ndb_metadata_upgrade_fk.test

raw
1 --source include/have_multi_ndb.inc
2 --source suite/ndb/include/have_little_endian.inc
3 --source suite/ndb/include/backup_restore_setup.inc
4
5 #
6 # The table structures are as follows:
7 # # CREATE TABLE parent (
8 # c1 int PRIMARY KEY,
9 # c2 int,
10 # UNIQUE p_ukc2(c2)
11 # ) ENGINE NDB;
12 #
13 # INSERT INTO parent VALUES(1, 10),(2, 20);
14 #
15 # CREATE TABLE child (
16 # c1 int,
17 # c2 int,
18 # INDEX c_xc1 (c1),
19 # INDEX c_xc2 (c2),
20 # CONSTRAINT c_fk1
21 # FOREIGN KEY (c1)
22 # REFERENCES parent(c1),
23 # CONSTRAINT c_fk2
24 # FOREIGN KEY (c2)
25 # REFERENCES parent(c2)
26 # ) ENGINE NDB;
27 #
28 # INSERT INTO child
29 # VALUES (1, 10), (2, 20), (1, 20), (2, 10);
30 #
31 # CREATE TABLE self_ref (
32 # c1 int NOT NULL,
33 # c2 int,
34 # UNIQUE sr_ukc1(c1),
35 # CONSTRAINT sr_fk1
36 # FOREIGN KEY (c2)
37 # REFERENCES self_ref(c1)
38 # ON DELETE CASCADE
39 # ON UPDATE SET DEFAULT
40 # ) ENGINE NDB;
41 #
42 # INSERT INTO self_ref VALUES(1,1), (2,1);
43 #
44
45 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
46 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
47 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
48
49 --echo Backup from 7.5 restored
50
51 --echo Restart server 1 to trigger schema distribution synchronization
52 let $mysqld_name=mysqld.1.1;
53 --source include/restart_mysqld.inc
54
55 connection server2;
56 --echo Restart server 2 to trigger schema distribution synchronization
57 let $mysqld_name=mysqld.2.1;
58 --source include/restart_mysqld.inc
59
60 --echo # Check schema and then perform DML and DDL on the tables
61 connection server1;
62 --echo # Verify that parent has been installed in DD
63 SHOW CREATE TABLE parent;
64 --sorted_result
65 SELECT * FROM parent;
66
67 --echo # Check foreign key constraints
68 INSERT INTO parent VALUES (3, 30);
69 --error ER_ROW_IS_REFERENCED_2
70 DELETE FROM parent WHERE c1 = 1;
71 --sorted_result
72 SELECT * FROM parent;
73
74 --echo # Verify that child has been installed in DD
75 query_vertical
76 SELECT * FROM information_schema.referential_constraints
77 WHERE constraint_schema='test'
78 AND table_name='child'
79 ORDER BY constraint_name ASC;
80 SHOW CREATE TABLE child;
81 --sorted_result
82 SELECT * FROM child;
83
84 --echo # Check foreign key constraints
85 INSERT INTO child VALUES (3, 30);
86 --error ER_NO_REFERENCED_ROW_2
87 INSERT INTO child VALUES (4, 40);
88 --sorted_result
89 SELECT * FROM child;
90
91 --echo # Verify that self_ref has been installed in DD
92 query_vertical
93 SELECT * FROM information_schema.referential_constraints
94 WHERE constraint_schema='test'
95 AND table_name='self_ref'
96 ORDER BY constraint_name ASC;
97 SHOW CREATE TABLE self_ref;
98 --sorted_result
99 SELECT * FROM self_ref;
100
101 --echo # Check foreign key constraints
102 INSERT INTO self_ref VALUES (3, 2);
103 --error ER_NO_REFERENCED_ROW_2
104 INSERT INTO self_ref VALUES (4, 5);
105 DELETE FROM self_ref WHERE c1 = 1;
106 --sorted_result
107 SELECT * FROM self_ref;
108
109 --echo # Cleanup
110 DROP TABLE child;
111 DROP TABLE parent;
112 DROP TABLE self_ref;
113
114 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
115 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
116 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
117
118 --echo Backup from 7.5 restored
119
120 --echo # Test upgrade of the metadata during discovery from NDB
121 --echo # Discover and verify table parent
122 SHOW CREATE TABLE parent;
123 --sorted_result
124 SELECT * FROM parent;
125
126 --echo # Check foreign key constraints
127 INSERT INTO parent VALUES (3, 30);
128 --error ER_ROW_IS_REFERENCED_2
129 DELETE FROM parent WHERE c1 = 1;
130 --sorted_result
131 SELECT * FROM parent;
132
133 --echo # Discover and verify table child
134 SHOW CREATE TABLE child;
135 query_vertical
136 SELECT * FROM information_schema.referential_constraints
137 WHERE constraint_schema='test'
138 AND table_name='child'
139 ORDER BY constraint_name ASC;
140 --sorted_result
141 SELECT * FROM child;
142
143 --echo # Check foreign key constraints
144 INSERT INTO child VALUES (3, 30);
145 --error ER_NO_REFERENCED_ROW_2
146 INSERT INTO child VALUES (4, 40);
147 --sorted_result
148 SELECT * FROM child;
149
150 --echo # Discover and verify table self_ref
151 SHOW CREATE TABLE self_ref;
152 query_vertical
153 SELECT * FROM information_schema.referential_constraints
154 WHERE constraint_schema='test'
155 AND table_name='self_ref'
156 ORDER BY constraint_name ASC;
157 --sorted_result
158 SELECT * FROM self_ref;
159
160 --echo # Check foreign key constraints
161 INSERT INTO self_ref VALUES (3, 2);
162 --error ER_NO_REFERENCED_ROW_2
163 INSERT INTO self_ref VALUES (4, 5);
164 DELETE FROM self_ref WHERE c1 = 1;
165 --sorted_result
166 SELECT * FROM self_ref;
167
168 DROP TABLE child;
169 DROP TABLE parent;
170 DROP TABLE self_ref;
171
172 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 -m -r --disable-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
173 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 2 -r $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
174 --exec $NDB_RESTORE $NDB_RESTORE_NO_ENCR -b 1 -n 1 --rebuild-indexes $NDB_SAVED_BACKUPS/metadata_upgrade_fk_backup >> $NDB_TOOLS_OUTPUT
175
176 --echo Backup from 7.5 restored
177
178 --echo # During upgrades, the tables might get upgraded in
179 --echo # any order. Test this by discovering parent and
180 --echo # child in the reverse order and verifying the FKs.
181
182 SHOW CREATE TABLE child;
183 SHOW CREATE TABLE parent;
184
185 --sorted_result
186 SELECT * FROM parent;
187 --sorted_result
188 SELECT * FROM child;
189
190 --echo # Check foreign key constraints
191 query_vertical
192 SELECT * FROM information_schema.referential_constraints
193 WHERE constraint_schema='test'
194 AND table_name='child'
195 ORDER BY constraint_name ASC;
196
197 INSERT INTO parent VALUES (3, 30);
198 --error ER_ROW_IS_REFERENCED_2
199 DELETE FROM parent WHERE c1 = 1;
200 INSERT INTO child VALUES (3, 30);
201 --error ER_NO_REFERENCED_ROW_2
202 INSERT INTO child VALUES (4, 40);
203
204 --sorted_result
205 SELECT * FROM parent;
206 --sorted_result
207 SELECT * FROM child;
208
209 --echo # Cleanup
210 DROP TABLE child;
211 DROP TABLE parent;
212 DROP TABLE self_ref;
213
214 --remove_file $NDB_TOOLS_OUTPUT