--source include/galera_cluster.inc --connection node_1 set global foreign_key_checks=0; create table files ( file_id int not null auto_increment primary key, id int default null, key(id)) engine=innodb; create table userorganisation ( user_id int not null auto_increment primary key, id int default null, key(id)) engine=innodb; create table userorganisationcontact ( user_id int not null auto_increment primary key, id int default null, key(id)) engine=innodb; create table usertype ( user_id int not null auto_increment primary key, id int default null, key(id)) engine=innodb; CREATE TABLE `projects` ( `id` int NOT NULL AUTO_INCREMENT, `default_begeleider_id` int DEFAULT NULL, `source_project_id` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `usertype_id` int DEFAULT NULL, `project_id` int DEFAULT NULL, `invited_by_id` int DEFAULT NULL, `userorganisation_id` int DEFAULT NULL, `userorganisationcontact_id` int DEFAULT NULL, `photo_file_id` int DEFAULT NULL, `idcard_file_id` int DEFAULT NULL, `agreement_file_id` int DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `FK_users_files` FOREIGN KEY (`idcard_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_users_files2` FOREIGN KEY (`agreement_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_users_files3` FOREIGN KEY (`photo_file_id`) REFERENCES `files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_users_userorganisation` FOREIGN KEY (`userorganisation_id`) REFERENCES `userorganisation` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `FK_users_userorganisationcontact` FOREIGN KEY (`userorganisationcontact_id`) REFERENCES `userorganisationcontact` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`usertype_id`) REFERENCES `usertype` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `invoice` ( `id` int NOT NULL AUTO_INCREMENT, `project_id` int DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `invoice_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; create table invoice_type ( id int not null auto_increment primary key, type varchar(32) not null, key(type)) engine=innodb DEFAULT CHARSET=utf8; CREATE TABLE `invoice_data` ( `id` int NOT NULL AUTO_INCREMENT, `invoice_id` int DEFAULT NULL, `project_id` int DEFAULT NULL, `user_id` int DEFAULT NULL, `type` varchar(32) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `invoice_data_ibfk_3` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `invoice_data_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `invoice_data_ibfk_5` FOREIGN KEY (`type`) REFERENCES `invoice_type` (`type`) ON UPDATE CASCADE, CONSTRAINT `invoice_data_ibfk_6` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into files values (1,1); insert into files values (2,2); insert into files values (3,3); insert into files values (4,4); insert into files values (5,5); insert into files values (6,6); insert into files values (7,7); insert into files values (8,8); insert into files values (9,9); insert into userorganisation values (1,1); insert into userorganisation values (2,2); insert into userorganisation values (3,3); insert into userorganisation values (4,4); insert into userorganisationcontact values (1,1); insert into userorganisationcontact values (2,2); insert into userorganisationcontact values (3,3); insert into userorganisationcontact values (4,4); insert into usertype values (1,1); insert into usertype values (2,2); insert into usertype values (3,3); insert into usertype values (4,4); insert into projects values (1, 1, NULL); insert into projects values (2, 1, 1); insert into projects values (3, 2, 2); insert into projects values (4, 3, 3); insert into projects values (5, 4, 4); insert into users values (1,1,1, NULL, 1, 1, 1, 2, 3); insert into users values (2,2,2, 1, 2, 2, 4, 5, 6); insert into users values (3,3,3, 1, 3, 3, 7, 8, 9); insert into users values (4,4,4, NULL, 4, 4, 7, 8, 9); insert into invoice values (1, 1); insert into invoice values (2, 2); insert into invoice values (3, 3); insert into invoice values (4, 4); insert into invoice_type values (1, 'test'); insert into invoice_type values (2, 'test1'); insert into invoice_type values (3, 'test2'); insert into invoice_type values (4, 'test3'); insert into invoice_data values (1, NULL, 1, 1, 'test'); insert into invoice_data values (2, 1, 2, 2, 'test1'); insert into invoice_data values (3, 2, 3, 3, 'test2'); insert into invoice_data values (4, NULL, 4, 4, 'test3'); set global foreign_key_checks=1; ALTER TABLE users ADD FOREIGN KEY(invited_by_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE projects ADD FOREIGN KEY (source_project_id) REFERENCES projects(id) ON UPDATE CASCADE; ALTER TABLE projects ADD FOREIGN KEY (`default_begeleider_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE users ADD FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON UPDATE CASCADE; --connection node_2 set global wsrep_slave_threads=10; --sleep 5 --connection node_1 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 send DELETE FROM `invoice` WHERE `project_id`=3; #send DELETE FROM `invoice_data` WHERE `project_id`=2; --connection node_1 #send DELETE FROM `invoice_data` WHERE `project_id`=1; send DELETE FROM `invoice` WHERE `project_id`=4; --connection node_1 reap; --connection node_1a reap; --connection node_2 select * from invoice; select * from invoice_data;