1# Check the replication of the FOREIGN_KEY_CHECKS variable.
2
3-- source include/master-slave.inc
4
5eval CREATE TABLE t1 (a INT AUTO_INCREMENT KEY) ENGINE=$engine_type;
6eval CREATE TABLE t2 (b INT AUTO_INCREMENT KEY, c INT, FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=$engine_type;
7
8SET FOREIGN_KEY_CHECKS=0;
9INSERT INTO t1 VALUES (10);
10INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
11INSERT INTO t2 VALUES (5,0);
12INSERT INTO t2 VALUES (NULL,LAST_INSERT_ID());
13SET FOREIGN_KEY_CHECKS=1;
14SELECT * FROM t1 ORDER BY a;
15SELECT * FROM t2 ORDER BY b;
16--source include/sync_slave_sql_with_master.inc
17SELECT * FROM t1 ORDER BY a;
18SELECT * FROM t2 ORDER BY b;
19
20connection master;
21SET TIMESTAMP=1000000000;
22CREATE TABLE t3 ( a INT UNIQUE );
23SET FOREIGN_KEY_CHECKS=0;
24# Had to add 1022 for run with ndb as ndb uses different
25# error and error code for error ER_DUP_ENTRY. Bug 16677
26--error 1022, ER_DUP_ENTRY
27INSERT INTO t3 VALUES (1),(1);
28--source include/sync_slave_sql_with_master.inc
29
30connection master;
31SET FOREIGN_KEY_CHECKS=0;
32DROP TABLE IF EXISTS t1,t2,t3;
33SET FOREIGN_KEY_CHECKS=1;
34--source include/sync_slave_sql_with_master.inc
35
36#
37# Bug #32468 delete rows event on a table with foreign key constraint fails
38#
39
40connection master;
41
42eval create table t1 (b int primary key) engine = $engine_type;
43eval create table t2 (a int primary key, b int, foreign key (b) references t1(b))
44       engine = $engine_type;
45
46insert into t1 set b=1;
47insert into t2 set a=1, b=1;
48
49set foreign_key_checks=0;
50delete from t1;
51
52--echo must sync w/o a problem (could not with the buggy code)
53--source include/sync_slave_sql_with_master.inc
54select count(*) from t1 /* must be zero */;
55
56
57# cleanup for bug#32468
58
59connection master;
60drop table t2,t1;
61
62--echo #
63--echo # Bug 79610: Failed DROP DATABASE due FK constraint on master breaks slave
64--echo #
65
66SET foreign_key_checks=1;
67CREATE DATABASE `db2`;
68
69USE `db2`;
70
71create table a1(f1 int);
72
73eval CREATE TABLE `table0` (`ID` bigint(20) primary key) ENGINE=$engine_type;
74eval CREATE TABLE `table1` (`ID` bigint(20) primary key) ENGINE=$engine_type;
75
76create database db1;
77use db1;
78
79eval CREATE TABLE `table2` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT,
80                             `DIVISION_ID` bigint(20) DEFAULT NULL,
81                             PRIMARY KEY (`ID`), KEY `FK_TABLE1_DIVISION_1` (`DIVISION_ID`),
82                             CONSTRAINT `FK_TABLE1_DIVISION_1` FOREIGN KEY (`DIVISION_ID`)
83                             REFERENCES `db2`.`table1` (`ID`) ON DELETE NO ACTION ) ENGINE=$engine_type;
84
85--source include/sync_slave_sql_with_master.inc
86use db2;
87eval CREATE TABLE `table2` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT,
88                             `DIVISION_ID` bigint(20) DEFAULT NULL,
89                             PRIMARY KEY (`ID`), KEY `FK_TABLE1_DIVISION_1` (`DIVISION_ID`),
90                             CONSTRAINT `FK_TABLE1_DIVISION_1` FOREIGN KEY (`DIVISION_ID`)
91                             REFERENCES `db2`.`table0` (`ID`) ON DELETE NO ACTION ) ENGINE=$engine_type;
92
93--connection master
94--error ER_ROW_IS_REFERENCED
95DROP DATABASE db2;
96
97--echo # DROP DATABASE should not have deleted any tables
98--echo # master:
99SHOW TABLES IN db2;
100
101--connection slave
102--echo # slave:
103SHOW TABLES IN db2;
104
105--connection master
106SET foreign_key_checks=0;
107DROP DATABASE db2;
108DROP DATABASE db1;
109
110--source include/sync_slave_sql_with_master.inc
111
112--source include/rpl_end.inc
113