Debian Codegraph @

mysql-test/suite/rpl/r/rpl_xa_generated_columns.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CALL mtr.add_suppression("Statement is unsafe because it is being used inside a XA transaction");
7 # Table st1: stored column genarated on primary key
8 CREATE TABLE st1 (c1 INT PRIMARY KEY);
9 ALTER TABLE st1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1+1) STORED, INDEX(c2));
10 INSERT INTO st1(c1) VALUES(1);
11 INSERT INTO st1(c1) VALUES(2);
12 XA START 'xstatement';
13 UPDATE st1 SET c1=3 WHERE c2=3;
14 UPDATE st1 SET c1=4 WHERE c1=3;
15 DELETE FROM st1 WHERE c2=5;
16 XA END 'xstatement';
17 XA PREPARE 'xstatement';
18 XA COMMIT 'xstatement';
19 include/sync_slave_sql_with_master.inc
20 # check that st1 exists and has same values in both servers
21 include/diff_tables.inc [master:st1, slave:st1]
22 # Table st2: stored column as primary key
23 [connection master]
24 CREATE TABLE st2 (
25 c1 VARCHAR(10),
26 c2 CHAR(2) GENERATED ALWAYS AS (SUBSTRING(c1,1,2)) STORED PRIMARY KEY
27 );
28 INSERT INTO st2(c1) VALUES("abcd");
29 INSERT INTO st2(c1) VALUES("efgh");
30 XA START 'xstatement';
31 UPDATE st2 SET c1="abgh" WHERE c1='abcd';
32 UPDATE st2 SET c1="cdgh" WHERE c2='ef';
33 DELETE FROM st2 WHERE c2='cd';
34 XA END 'xstatement';
35 XA PREPARE 'xstatement';
36 XA COMMIT 'xstatement';
37 include/sync_slave_sql_with_master.inc
38 # check that st2 exists and has same values in both servers
39 include/diff_tables.inc [master:st2, slave:st2]
40 # Table st3: stored column as unique
41 [connection master]
42 CREATE TABLE st3 (
43 c1 INT AUTO_INCREMENT PRIMARY KEY,
44 c2 VARCHAR(10) NOT NULL,
45 c3 VARCHAR(10) NOT NULL,
46 c4 VARCHAR(21) AS (CONCAT(c2,c3)) STORED UNIQUE
47 );
48 INSERT INTO st3(c2,c3) VALUES('first','insert');
49 INSERT INTO st3(c2,c3) VALUES('second','insert');
50 INSERT INTO st3(c2,c3) VALUES('first','insert');
51 ERROR 23000: Duplicate entry 'firstinsert' for key 'st3.c4'
52 XA START 'xstatement';
53 UPDATE st3 SET c3='update' WHERE c2='first';
54 UPDATE st3 SET c3='update' WHERE c2='second';
55 DELETE FROM st3 WHERE c2='first';
56 DELETE FROM st3 WHERE c3='update';
57 XA END 'xstatement';
58 XA PREPARE 'xstatement';
59 XA COMMIT 'xstatement';
60 include/sync_slave_sql_with_master.inc
61 # check that st3 exists and has same values in both servers
62 include/diff_tables.inc [master:st3, slave:st3]
63 # Table st4: Stored column as foreign key
64 [connection master]
65 CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a-10) STORED, PRIMARY KEY(b));
66 CREATE TABLE st4 (
67 c1 INT PRIMARY KEY,
68 c2 INT GENERATED ALWAYS AS (c1%10) STORED,
69 FOREIGN KEY(c2) REFERENCES t1(b)
70 );
71 INSERT INTO t1(a) VALUES(11);
72 INSERT INTO t1(a) VALUES(12);
73 INSERT INTO st4(c1) VALUES(31);
74 INSERT INTO st4(c1) VALUES(32);
75 XA START 'xstatement';
76 UPDATE st4 SET c1=21 WHERE c2=1;
77 DELETE FROM st4 WHERE c2=2;
78 DELETE FROM st4 WHERE c1=21;
79 XA END 'xstatement';
80 XA PREPARE 'xstatement';
81 XA COMMIT 'xstatement';
82 include/sync_slave_sql_with_master.inc
83 # check that st4 exists and has same values in both servers
84 include/diff_tables.inc [master:st4, slave:st4]
85 # Table vt1: virtual column genarated on primary key
86 [connection master]
87 CREATE TABLE vt1 (c1 DATE PRIMARY KEY, c2 INT AS (year(c1)) VIRTUAL);
88 INSERT INTO vt1(c1) VALUES('2008-09-02');
89 INSERT INTO vt1(c1) VALUES('1998-06-17');
90 UPDATE vt1 SET c1='2009-09-02' WHERE c2=2008;
91 XA START 'xstatement';
92 UPDATE vt1 SET c1='1999-06-17' WHERE c1='1998-06-17';
93 DELETE FROM vt1 WHERE c1='2009-09-02';
94 DELETE FROM vt1 WHERE c2=1999;
95 XA END 'xstatement';
96 XA PREPARE 'xstatement';
97 XA COMMIT 'xstatement';
98 include/sync_slave_sql_with_master.inc
99 # check that vt1 exists and has same values in both servers
100 include/diff_tables.inc [master:vt1, slave:vt1]
101 # Table vt2: virtual column as unique key
102 [connection master]
103 CREATE TABLE vt2 (
104 c1 TEXT, PRIMARY KEY(c1(20)),
105 c2 TEXT GENERATED ALWAYS AS (SUBSTRING(c1, 1, 2)) VIRTUAL,
106 UNIQUE(c2(2))
107 );
108 INSERT INTO vt2(c1) VALUES('first insert');
109 INSERT INTO vt2(c1) VALUES('second insert');
110 UPDATE vt2 SET c1='first update' WHERE c1='first insert';
111 XA START 'xstatement';
112 UPDATE vt2 SET c1='second update' WHERE c2='se';
113 DELETE FROM vt2 WHERE c1='first update';
114 DELETE FROM vt2 WHERE c2='se';
115 XA END 'xstatement';
116 XA PREPARE 'xstatement';
117 XA COMMIT 'xstatement';
118 include/sync_slave_sql_with_master.inc
119 # check that vt2 exists and has same values in both servers
120 include/diff_tables.inc [master:vt2, slave:vt2]
121 # Table vt3: Virtual Column with foreign keys
122 [connection master]
123 CREATE TABLE t2(
124 a INT PRIMARY KEY,
125 b INT GENERATED ALWAYS AS (a+1) VIRTUAL,
126 INDEX(b)
127 );
128 CREATE TABLE vt3(
129 a INT PRIMARY KEY,
130 b INT,
131 FOREIGN KEY(b) REFERENCES t2(a) ON UPDATE SET NULL ON DELETE RESTRICT,
132 c INT AS (a*b) VIRTUAL
133 );
134 INSERT INTO t2(a) VALUES(1);
135 INSERT INTO t2(a) VALUES(3);
136 INSERT INTO vt3(a,b) VALUES(100,1),(200,3);
137 UPDATE t2 SET a=2 WHERE b=4;
138 UPDATE vt3 SET b=2 WHERE b IS NULL;
139 UPDATE t2,vt3 SET vt3.a=t2.a WHERE t2.b=vt3.b;
140 DELETE FROM t2 WHERE a=2;
141 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`vt3`, CONSTRAINT `vt3_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`) ON DELETE RESTRICT ON UPDATE SET NULL)
142 XA START 'xstatement';
143 DELETE FROM vt3 WHERE a=1;
144 DELETE FROM vt3 WHERE c=100;
145 XA END 'xstatement';
146 XA PREPARE 'xstatement';
147 XA COMMIT 'xstatement';
148 include/sync_slave_sql_with_master.inc
149 # check that vt3 exists and has same values in both servers
150 include/diff_tables.inc [master:vt3, slave:vt3]
151 # Table gt1: gt1: both stored and virtual columns
152 [connection master]
153 CREATE TABLE gt1 (a INT, b INT AS (a+1) STORED PRIMARY KEY);
154 ALTER TABLE gt1 ADD COLUMN c INT GENERATED ALWAYS AS (b+1) VIRTUAL;
155 ALTER TABLE gt1 ADD COLUMN d INT GENERATED ALWAYS AS (c+10) STORED;
156 INSERT INTO gt1(a) VALUES(5);
157 INSERT INTO gt1(a) VALUES(4);
158 INSERT INTO gt1(a) VALUES(3);
159 INSERT INTO gt1(a) VALUES(2);
160 INSERT INTO gt1(a) VALUES(1);
161 UPDATE gt1 SET a=21 WHERE b=2;
162 UPDATE gt1 SET a=22 WHERE c=4;
163 UPDATE gt1 SET a=23 WHERE d=15;
164 XA START 'xstatement';
165 UPDATE gt1 SET a=24 WHERE a=4;
166 DELETE FROM gt1 WHERE a=5;
167 DELETE FROM gt1 WHERE b=24;
168 DELETE FROM gt1 WHERE c=24;
169 DELETE FROM gt1 WHERE d=33;
170 XA END 'xstatement';
171 XA PREPARE 'xstatement';
172 XA COMMIT 'xstatement';
173 include/sync_slave_sql_with_master.inc
174 # check that gt1 exists and has same values in both servers
175 include/diff_tables.inc [master:gt1, slave:gt1]
176 # Clean Up
177 [connection master]
178 DROP TABLE st1,st2,st3,st4;
179 DROP TABLE vt1,vt2,vt3;
180 DROP TABLE gt1;
181 DROP TABLE t1,t2;
182 include/rpl_end.inc