Debian Codegraph @

mysql-test/suite/galera/t/fk.test

raw
1 --source include/galera_cluster.inc
2 --source include/have_innodb.inc
3
4 #
5 # MDEV-6651: MariaDB galera cluster crashes in file row0mysql.cc line 684
6 # DELETE FROM ports WHERE ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516'
7 # where table ports have foreign keys
8 #
9
10 USE test;
11 --echo
12 --echo # On node_1
13 --connection node_1
14
15 CREATE TABLE networks (
16 `tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
17 `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
18 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
19 `status` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
20 `admin_state_up` tinyint(1) DEFAULT NULL,
21 `shared` tinyint(1) DEFAULT NULL,
22 PRIMARY KEY (`id`)
23 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
24
25 CREATE TABLE ports (
26 `tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
27 `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
28 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
29 `network_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
30 `mac_address` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
31 `admin_state_up` tinyint(1) NOT NULL,
32 `status` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
33 `device_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
34 `device_owner` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
35 PRIMARY KEY (`id`),
36 KEY `network_id` (`network_id`),
37 CONSTRAINT `ports_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES networks (`id`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
39
40 CREATE TABLE subnets (
41 `tenant_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
42 `id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
43 `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
44 `network_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
45 `ip_version` int(11) NOT NULL,
46 `cidr` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
47 `gateway_ip` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
48 `enable_dhcp` tinyint(1) DEFAULT NULL,
49 `shared` tinyint(1) DEFAULT NULL,
50 `ipv6_ra_mode` enum('slaac','dhcpv6-stateful','dhcpv6-stateless') COLLATE utf8_unicode_ci DEFAULT NULL,
51 `ipv6_address_mode` enum('slaac','dhcpv6-stateful','dhcpv6-stateless') COLLATE utf8_unicode_ci DEFAULT NULL,
52 PRIMARY KEY (`id`),
53 KEY `network_id` (`network_id`),
54 CONSTRAINT `subnets_ibfk_1` FOREIGN KEY (`network_id`) REFERENCES networks (`id`)
55 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
56
57 CREATE TABLE `ipallocations` (
58 `port_id` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL,
59 `ip_address` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
60 `subnet_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
61 `network_id` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
62 PRIMARY KEY (`ip_address`,`subnet_id`,`network_id`),
63 KEY `port_id` (`port_id`),
64 KEY `subnet_id` (`subnet_id`),
65 KEY `network_id` (`network_id`),
66 CONSTRAINT `ipallocations_ibfk_1` FOREIGN KEY (`port_id`) REFERENCES `ports` (`id`) ON DELETE CASCADE,
67 CONSTRAINT `ipallocations_ibfk_2` FOREIGN KEY (`subnet_id`) REFERENCES `subnets` (`id`) ON DELETE CASCADE,
68 CONSTRAINT `ipallocations_ibfk_3` FOREIGN KEY (`network_id`) REFERENCES `networks` (`id`) ON DELETE CASCADE
69 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
70
71 INSERT INTO networks VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','MyNet','ACTIVE',0,0);
72
73 INSERT INTO ports VALUES ('','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','fa:16:3e:e3:cc:bb',1,'DOWN','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','network:router_gateway');
74
75 INSERT INTO subnets VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','ext-subnet','f37aa3fe-ab99-4d0f-a566-6cd3169d7516',4,'10.25.0.0/24','10.25.0.4',0,1,NULL,NULL);
76
77 INSERT INTO ipallocations VALUES ('f37aa3fe-ab99-4d0f-a566-6cd3169d7516','10.25.0.17','f37aa3fe-ab99-4d0f-a566-6cd3169d7516','f37aa3fe-ab99-4d0f-a566-6cd3169d7516');
78
79 select * from ports where ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
80
81 --connection node_2
82
83 select * from ports where ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
84
85 --let $galera_diff_statement = SELECT * FROM ports
86 --source include/galera_diff.inc
87
88 --connection node_1
89
90 DELETE FROM ports WHERE ports.id = 'f37aa3fe-ab99-4d0f-a566-6cd3169d7516';
91
92 select * from networks;
93 select * from ports;
94 select * from subnets;
95 select * from ipallocations;
96 select * from ports;
97
98 --echo
99 --echo # On node_2
100 --connection node_2
101 select * from networks;
102 select * from ports;
103 select * from subnets;
104 select * from ipallocations;
105 select * from ports;
106
107 --let $galera_diff_statement = SELECT * FROM ports
108 --source include/galera_diff.inc
109
110 --connection node_1
111 drop table ipallocations;
112 drop table subnets;
113 drop table ports;
114 drop table networks;
115
116 --source include/galera_end.inc