28
28
-- source include/master-slave.inc
29
29
#should work for both SBR and RBR
32
create table t1(a int auto_increment, key(a));
33
create table t2(b int auto_increment, c int, key(b));
31
# If concurrent inserts are on, it is not guaranteed that the rows
32
# inserted by INSERT are immediately accessible by SELECT in another
33
# thread. This would cause problems near the line 'connection master1'
34
# below. So we turn off concurrent inserts.
36
SET @old_concurrent_insert= @@global.concurrent_insert;
37
SET @@global.concurrent_insert= 0;
40
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
41
eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
34
42
insert into t1 values (1),(2),(3);
35
43
insert into t1 values (null);
36
44
insert into t2 values (null,last_insert_id());
71
create table t1(a int auto_increment, key(a));
72
create table t2(b int auto_increment, c int, key(b));
79
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
80
eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
73
81
insert into t1 values (10);
74
82
insert into t1 values (null),(null),(null);
75
83
insert into t2 values (5,0);
95
103
connection master;
96
104
SET TIMESTAMP=1000000000;
97
CREATE TABLE t1 ( a INT UNIQUE );
105
eval CREATE TABLE t1 ( a INT UNIQUE ) engine=$engine_type;
98
106
SET FOREIGN_KEY_CHECKS=0;
99
107
# Duplicate Key Errors
100
108
--error 1022, ER_DUP_ENTRY
111
119
connection master;
112
create table t1(a int auto_increment, key(a));
113
create table t2(a int);
120
eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
121
eval create table t2(a int) engine=$engine_type;
114
122
insert into t1 (a) values (null);
115
123
insert into t2 (a) select a from t1 where a is null;
116
124
insert into t2 (a) select a from t1 where a is null;
152
160
insert into t1 (last_id) values (bug15728());
154
162
# Check that nested call replicates too.
163
eval create table t2 (
156
164
id int not null auto_increment,
167
) engine=$engine_type;
161
169
create function bug15728_insert() returns int(11) modifies sql data
215
223
# test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in
216
224
# auto_increment breaks binlog
218
create table t1 (n int primary key auto_increment not null,
226
eval create table t1 (n int primary key auto_increment not null,
227
b int, unique(b)) engine=$engine_type;
221
229
# First, test that we do not call restore_auto_increment() too early
222
230
# in write_record():
257
265
# and now test for the bug:
258
266
connection master;
260
create table t1 (n int primary key auto_increment not null,
268
eval create table t1 (n int primary key auto_increment not null,
269
b int, unique(b)) engine=$engine_type;
262
270
insert into t1 values(null,100);
263
271
select * from t1 order by n;
264
272
sync_slave_with_master;
283
291
connection master;
284
292
# testcase with INSERT VALUES
285
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
293
eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
294
UNIQUE(b)) ENGINE=$engine_type;
287
295
INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
296
SELECT * FROM t1 ORDER BY a;
289
297
sync_slave_with_master;
298
SELECT * FROM t1 ORDER BY a;
291
299
connection master;
294
302
# tescase with INSERT SELECT
303
eval CREATE TABLE t1 (
296
304
id bigint(20) unsigned NOT NULL auto_increment,
297
305
field_1 int(10) unsigned NOT NULL,
298
306
field_2 varchar(255) NOT NULL,
299
307
field_3 varchar(255) NOT NULL,
300
308
PRIMARY KEY (id),
301
309
UNIQUE KEY field_1 (field_1, field_2)
310
) ENGINE=$engine_type;
311
eval CREATE TABLE t2 (
304
312
field_a int(10) unsigned NOT NULL,
305
313
field_b varchar(255) NOT NULL,
306
314
field_c varchar(255) NOT NULL
315
) ENGINE=$engine_type;
308
316
INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
309
317
INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
310
318
INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
348
356
# Reset result of LAST_INSERT_ID().
349
357
SELECT LAST_INSERT_ID(0);
359
eval CREATE TABLE t1 (
352
360
id INT NOT NULL DEFAULT 0,
363
) ENGINE=$engine_type;
365
eval CREATE TABLE t2 (
358
366
id INT NOT NULL AUTO_INCREMENT,
369
) ENGINE=$engine_type;
364
372
CREATE PROCEDURE p1()
443
451
# Test statement-based replication of function calls.
444
452
INSERT INTO t1 (i) VALUES (NULL);
454
# Here, we rely on having set @@concurrent_insert= 0 (see comment at
455
# the top of this file).
446
456
connection master1;
447
457
INSERT INTO t1 (i) VALUES (NULL);
449
459
connection master;
462
SELECT * FROM t1 ORDER BY i;
463
SELECT * FROM t2 ORDER BY i;
455
465
sync_slave_with_master;
456
466
SELECT * FROM t1;
504
515
delete from t2 where id>=5;
505
516
set sql_log_bin=1;
506
517
insert into t1 select insid();
518
select * from t1 order by id;
519
select * from t2 order by id;
510
521
sync_slave_with_master;
522
select * from t1 order by id;
523
select * from t2 order by id;
514
525
connection master;
516
527
drop function insid;
518
529
truncate table t2;
519
create table t1 (n int primary key auto_increment not null,
530
eval create table t1 (n int primary key auto_increment not null,
531
b int, unique(b)) engine=$engine_type;
522
533
create procedure foo()
542
select * from t1 order by n;
543
select * from t2 order by id;
534
545
sync_slave_with_master;
546
select * from t1 order by n;
547
select * from t2 order by id;
538
549
connection master;
539
550
drop table t1, t2;
540
551
drop procedure foo;
552
SET @@global.concurrent_insert= @old_concurrent_insert;
541
553
sync_slave_with_master;