2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
8
SET @my_sql_mode= @@global.sql_mode;
9
SET GLOBAL SQL_MODE='STRICT_ALL_TABLES';
11
CREATE TABLE t1_int (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
12
CREATE TABLE t1_bit (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
13
CREATE TABLE t1_char (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
14
CREATE TABLE t1_nodef (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
15
CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
16
CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE='InnoDB';
17
CREATE TABLE t4 (a INT) ENGINE='InnoDB';
18
CREATE TABLE t5 (a INT, b INT, c INT) ENGINE='InnoDB';
19
CREATE TABLE t6 (a INT, b INT, c INT) ENGINE='InnoDB';
20
CREATE TABLE t7 (a INT NOT NULL) ENGINE='InnoDB';
21
CREATE TABLE t8 (a INT NOT NULL) ENGINE='InnoDB';
22
CREATE TABLE t9 (a INT) ENGINE='InnoDB';
23
ALTER TABLE t1_int ADD x INT DEFAULT 42;
25
ADD x BIT(3) DEFAULT b'011',
26
ADD y BIT(5) DEFAULT b'10101',
27
ADD z BIT(2) DEFAULT b'10';
28
ALTER TABLE t1_char ADD x CHAR(20) DEFAULT 'Just a test';
29
ALTER TABLE t1_nodef ADD x INT NOT NULL, ADD y INT NOT NULL, ADD z INT NOT NULL;
30
ALTER TABLE t2 DROP b;
31
ALTER TABLE t4 MODIFY a FLOAT;
32
ALTER TABLE t5 MODIFY b FLOAT;
33
ALTER TABLE t6 MODIFY c FLOAT;
34
ALTER TABLE t7 ADD e1 INT, ADD e2 INT, ADD e3 INT, ADD e4 INT,
35
ADD e5 INT, ADD e6 INT, ADD e7 INT, ADD e8 INT;
36
ALTER TABLE t8 ADD e1 INT NOT NULL DEFAULT 0, ADD e2 INT NOT NULL DEFAULT 0,
37
ADD e3 INT NOT NULL DEFAULT 0, ADD e4 INT NOT NULL DEFAULT 0,
38
ADD e5 INT NOT NULL DEFAULT 0, ADD e6 INT NOT NULL DEFAULT 0,
39
ADD e7 INT NOT NULL DEFAULT 0, ADD e8 INT NOT NULL DEFAULT 0;
40
set @@global.slave_exec_mode= 'IDEMPOTENT';
41
INSERT INTO t1_int VALUES (2, 4, 4711);
42
INSERT INTO t1_char VALUES (2, 4, 'Foo is a bar');
43
INSERT INTO t1_bit VALUES (2, 4, b'101', b'11100', b'01');
45
INSERT INTO t1_int VALUES (1,2);
46
INSERT INTO t1_int VALUES (2,5);
47
INSERT INTO t1_bit VALUES (1,2);
48
INSERT INTO t1_bit VALUES (2,5);
49
INSERT INTO t1_char VALUES (1,2);
50
INSERT INTO t1_char VALUES (2,5);
51
SELECT * FROM t1_int ORDER BY a;
55
SELECT * FROM t1_bit ORDER BY a;
59
SELECT * FROM t1_char ORDER BY a;
64
set @@global.slave_exec_mode= default;
65
SELECT a,b,x FROM t1_int ORDER BY a;
69
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
70
a b HEX(x) HEX(y) HEX(z)
73
SELECT a,b,x FROM t1_char ORDER BY a;
78
UPDATE t1_int SET b=2*b WHERE a=2;
79
UPDATE t1_char SET b=2*b WHERE a=2;
80
UPDATE t1_bit SET b=2*b WHERE a=2;
81
SELECT * FROM t1_int ORDER BY a;
85
SELECT * FROM t1_bit ORDER BY a;
89
SELECT * FROM t1_char ORDER BY a;
94
SELECT a,b,x FROM t1_int ORDER BY a;
98
SELECT a,b,HEX(x),HEX(y),HEX(z) FROM t1_bit ORDER BY a;
99
a b HEX(x) HEX(y) HEX(z)
102
SELECT a,b,x FROM t1_char ORDER BY a;
106
INSERT INTO t9 VALUES (2);
107
INSERT INTO t1_nodef VALUES (1,2);
110
Master_Host 127.0.0.1
114
Master_Log_File master-bin.000001
115
Read_Master_Log_Pos #
118
Relay_Master_Log_File master-bin.000001
124
Replicate_Ignore_Table
125
Replicate_Wild_Do_Table
126
Replicate_Wild_Ignore_Table
128
Last_Error <Last_Error>
130
Exec_Master_Log_Pos #
135
Master_SSL_Allowed No
141
Seconds_Behind_Master #
142
Master_SSL_Verify_Server_Cert No
144
Last_IO_Error <Last_IO_Error>
146
Last_SQL_Error <Last_SQL_Error>
147
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
149
INSERT INTO t9 VALUES (2);
151
INSERT INTO t2 VALUES (2,4);
161
Master_Host 127.0.0.1
165
Master_Log_File master-bin.000001
166
Read_Master_Log_Pos #
169
Relay_Master_Log_File master-bin.000001
171
Slave_SQL_Running Yes
175
Replicate_Ignore_Table
176
Replicate_Wild_Do_Table
177
Replicate_Wild_Ignore_Table
179
Last_Error <Last_Error>
181
Exec_Master_Log_Pos #
186
Master_SSL_Allowed No
192
Seconds_Behind_Master #
193
Master_SSL_Verify_Server_Cert No
195
Last_IO_Error <Last_IO_Error>
197
Last_SQL_Error <Last_SQL_Error>
198
INSERT INTO t9 VALUES (4);
199
INSERT INTO t4 VALUES (4);
202
Master_Host 127.0.0.1
206
Master_Log_File master-bin.000001
207
Read_Master_Log_Pos #
210
Relay_Master_Log_File master-bin.000001
216
Replicate_Ignore_Table
217
Replicate_Wild_Do_Table
218
Replicate_Wild_Ignore_Table
220
Last_Error <Last_Error>
222
Exec_Master_Log_Pos #
227
Master_SSL_Allowed No
233
Seconds_Behind_Master #
234
Master_SSL_Verify_Server_Cert No
236
Last_IO_Error <Last_IO_Error>
238
Last_SQL_Error <Last_SQL_Error>
239
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
241
INSERT INTO t9 VALUES (5);
242
INSERT INTO t5 VALUES (5,10,25);
245
Master_Host 127.0.0.1
249
Master_Log_File master-bin.000001
250
Read_Master_Log_Pos #
253
Relay_Master_Log_File master-bin.000001
259
Replicate_Ignore_Table
260
Replicate_Wild_Do_Table
261
Replicate_Wild_Ignore_Table
263
Last_Error <Last_Error>
265
Exec_Master_Log_Pos #
270
Master_SSL_Allowed No
276
Seconds_Behind_Master #
277
Master_SSL_Verify_Server_Cert No
279
Last_IO_Error <Last_IO_Error>
281
Last_SQL_Error <Last_SQL_Error>
282
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
284
INSERT INTO t9 VALUES (6);
285
INSERT INTO t6 VALUES (6,12,36);
288
Master_Host 127.0.0.1
292
Master_Log_File master-bin.000001
293
Read_Master_Log_Pos #
296
Relay_Master_Log_File master-bin.000001
302
Replicate_Ignore_Table
303
Replicate_Wild_Do_Table
304
Replicate_Wild_Ignore_Table
306
Last_Error <Last_Error>
308
Exec_Master_Log_Pos #
313
Master_SSL_Allowed No
319
Seconds_Behind_Master #
320
Master_SSL_Verify_Server_Cert No
322
Last_IO_Error <Last_IO_Error>
324
Last_SQL_Error <Last_SQL_Error>
325
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2;
327
INSERT INTO t9 VALUES (6);
330
Master_Host 127.0.0.1
334
Master_Log_File master-bin.000001
335
Read_Master_Log_Pos #
338
Relay_Master_Log_File master-bin.000001
340
Slave_SQL_Running Yes
344
Replicate_Ignore_Table
345
Replicate_Wild_Do_Table
346
Replicate_Wild_Ignore_Table
348
Last_Error <Last_Error>
350
Exec_Master_Log_Pos #
355
Master_SSL_Allowed No
361
Seconds_Behind_Master #
362
Master_SSL_Verify_Server_Cert No
364
Last_IO_Error <Last_IO_Error>
366
Last_SQL_Error <Last_SQL_Error>
367
INSERT INTO t7 VALUES (1),(2),(3);
368
INSERT INTO t8 VALUES (1),(2),(3);
369
SELECT * FROM t7 ORDER BY a;
374
SELECT * FROM t8 ORDER BY a;
379
SELECT * FROM t7 ORDER BY a;
380
a e1 e2 e3 e4 e5 e6 e7 e8
381
1 NULL NULL NULL NULL NULL NULL NULL NULL
382
2 NULL NULL NULL NULL NULL NULL NULL NULL
383
3 NULL NULL NULL NULL NULL NULL NULL NULL
384
SELECT * FROM t8 ORDER BY a;
385
a e1 e2 e3 e4 e5 e6 e7 e8
392
INSERT INTO t1_nodef VALUES (1,2);
393
INSERT INTO t1_nodef VALUES (2,4);
396
INSERT INTO t1_nodef VALUES (1,2,3,4,5);
397
INSERT INTO t1_nodef VALUES (2,4,6,8,10);
399
UPDATE t1_nodef SET b=2*b WHERE a=1;
400
SELECT * FROM t1_nodef ORDER BY a;
405
SELECT * FROM t1_nodef ORDER BY a;
410
DELETE FROM t1_nodef WHERE a=2;
411
SELECT * FROM t1_nodef ORDER BY a;
415
SELECT * FROM t1_nodef ORDER BY a;
419
DROP TABLE IF EXISTS t1_int,t1_bit,t1_char,t1_nodef;
420
DROP TABLE IF EXISTS t2,t3,t4,t5,t6,t7,t8,t9;
421
SET @@global.sql_mode= @my_sql_mode;