release/src/router/mysql/mysql-test/extra/rpl_tests/rpl_insert_id.test

   1 ###########################################################
   2 # See if queries that use both auto_increment and LAST_INSERT_ID()
   3 # are replicated well
   4 ############################################################
   5 # REQUIREMENT
   6 # Auto increment should work for a table with an auto_increment
   7 # column and index but without primary key.
   8 ##############################################################
   9
  10 --echo #
  11 --echo # Setup
  12 --echo #
  13
  14 use test;
  15
  16 --echo #
  17 --echo # See if queries that use both auto_increment and LAST_INSERT_ID()
  18 --echo # are replicated well
  19 --echo #
  20 --echo # We also check how the foreign_key_check variable is replicated
  21 --echo #
  22
  23 -- source include/master-slave.inc
  24 #should work for both SBR and RBR
  25
  26 # If concurrent inserts are on, it is not guaranteed that the rows
  27 # inserted by INSERT are immediately accessible by SELECT in another
  28 # thread. This would cause problems near the line 'connection master1'
  29 # below. So we turn off concurrent inserts.
  30 connection master;
  31 SET @old_concurrent_insert= @@global.concurrent_insert;
  32 SET @@global.concurrent_insert= 0;
  33
  34 connection master;
  35 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
  36 eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
  37 insert into t1 values (1),(2),(3);
  38 insert into t1 values (null);
  39 insert into t2 values (null,last_insert_id());
  40 sync_slave_with_master;
  41 select * from t1 ORDER BY a;
  42 select * from t2 ORDER BY b;
  43 connection master;
  44 #check if multi-line inserts,
  45 #which set last_insert_id to the first id inserted,
  46 #are replicated the same way
  47 drop table t1;
  48 drop table t2;
  49 --disable_warnings
  50 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
  51 eval create table t2(b int auto_increment, c int, key(b), foreign key(b) references t1(a)) engine=$engine_type;
  52 --enable_warnings
  53 SET FOREIGN_KEY_CHECKS=0;
  54 insert into t1 values (10);
  55 insert into t1 values (null),(null),(null);
  56 insert into t2 values (5,0);
  57 insert into t2 values (null,last_insert_id());
  58 SET FOREIGN_KEY_CHECKS=1;
  59 sync_slave_with_master;
  60 select * from t1;
  61 select * from t2;
  62 connection master;
  63
  64 --echo #
  65 --echo # check if INSERT SELECT in auto_increment is well replicated (bug #490)
  66 --echo #
  67
  68 drop table t2;
  69 drop table t1;
  70 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
  71 eval create table t2(b int auto_increment, c int, key(b)) engine=$engine_type;
  72 insert into t1 values (10);
  73 insert into t1 values (null),(null),(null);
  74 insert into t2 values (5,0);
  75 insert into t2 (c) select * from t1 ORDER BY a;
  76 select * from t2 ORDER BY b;
  77 sync_slave_with_master;
  78 select * from t1 ORDER BY a;
  79 select * from t2 ORDER BY b;
  80 connection master;
  81 drop table t1;
  82 drop table t2;
  83 sync_slave_with_master;
  84
  85 --echo #
  86 --echo # Bug#8412: Error codes reported in binary log for CHARACTER SET,
  87 --echo #           FOREIGN_KEY_CHECKS
  88 --echo #
  89
  90 connection master;
  91 SET TIMESTAMP=1000000000;
  92 eval CREATE TABLE t1 ( a INT UNIQUE ) engine=$engine_type;
  93 SET FOREIGN_KEY_CHECKS=0;
  94 # Duplicate Key Errors
  95 --error 1022, ER_DUP_ENTRY
  96 INSERT INTO t1 VALUES (1),(1);
  97 sync_slave_with_master;
  98 connection master;
  99 drop table t1;
 100 sync_slave_with_master;
 101
 102 --echo #
 103 --echo # Bug#14553: NULL in WHERE resets LAST_INSERT_ID
 104 --echo #
 105
 106 connection master;
 107 eval create table t1(a int auto_increment, key(a)) engine=$engine_type;
 108 eval create table t2(a int) engine=$engine_type;
 109 insert into t1 (a) values (null);
 110 insert into t2 (a) select a from t1 where a is null;
 111 insert into t2 (a) select a from t1 where a is null;
 112 select * from t2;
 113 sync_slave_with_master;
 114 connection slave;
 115 select * from t2;
 116 connection master;
 117 drop table t1;
 118 drop table t2;
 119
 120 --echo #
 121 --echo # End of 4.1 tests
 122 --echo #
 123
 124 --echo #
 125 --echo # BUG#15728: LAST_INSERT_ID function inside a stored function returns 0
 126 --echo #
 127 --echo # The solution is not to reset last_insert_id on enter to sub-statement.
 128 --echo #
 129
 130 connection master;
 131 --disable_warnings
 132 drop function if exists bug15728;
 133 drop function if exists bug15728_insert;
 134 drop table if exists t1, t2;
 135 --enable_warnings
 136
 137 eval create table t1 (
 138   id int not null auto_increment,
 139   last_id int,
 140   primary key (id)
 141 ) engine=$engine_type;
 142 create function bug15728() returns int(11)
 143   return last_insert_id();
 144
 145 insert into t1 (last_id) values (0);
 146 insert into t1 (last_id) values (last_insert_id());
 147 insert into t1 (last_id) values (bug15728());
 148
 149 # Check that nested call replicates too.
 150 eval create table t2 (
 151   id int not null auto_increment,
 152   last_id int,
 153   primary key (id)
 154 ) engine=$engine_type;
 155 delimiter |;
 156 create function bug15728_insert() returns int(11) modifies sql data
 157 begin
 158   insert into t2 (last_id) values (bug15728());
 159   return bug15728();
 160 end|
 161 create trigger t1_bi before insert on t1 for each row
 162 begin
 163   declare res int;
 164   select bug15728_insert() into res;
 165   set NEW.last_id = res;
 166 end|
 167 delimiter ;|
 168
 169 insert into t1 (last_id) values (0);
 170
 171 drop trigger t1_bi;
 172
 173 # Check that nested call doesn't affect outer context.
 174 select last_insert_id();
 175 select bug15728_insert();
 176 select last_insert_id();
 177 insert into t1 (last_id) values (bug15728());
 178 # This should be exactly one greater than in the previous call.
 179 select last_insert_id();
 180
 181 # BUG#20339 - stored procedure using LAST_INSERT_ID() does not
 182 # replicate statement-based
 183 --disable_warnings
 184 drop procedure if exists foo;
 185 --enable_warnings
 186 delimiter |;
 187 create procedure foo()
 188 begin
 189   declare res int;
 190   insert into t2 (last_id) values (bug15728());
 191   insert into t1 (last_id) values (bug15728());
 192 end|
 193 delimiter ;|
 194 call foo();
 195
 196 select * from t1;
 197 select * from t2;
 198 sync_slave_with_master;
 199 select * from t1;
 200 select * from t2;
 201 connection master;
 202
 203 drop function bug15728;
 204 drop function bug15728_insert;
 205 drop table t1,t2;
 206 drop procedure foo;
 207
 208 # test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in
 209 # auto_increment breaks binlog
 210
 211 eval create table t1 (n int primary key auto_increment not null,
 212                       b int, unique(b)) engine=$engine_type;
 213
 214 # First, test that we do not call restore_auto_increment() too early
 215 # in write_record():
 216 set sql_log_bin=0;
 217 insert into t1 values(null,100);
 218 replace into t1 values(null,50),(null,100),(null,150);
 219 select * from t1 order by n;
 220 truncate table t1;
 221 set sql_log_bin=1;
 222
 223 insert into t1 values(null,100);
 224 select * from t1 order by n;
 225 sync_slave_with_master;
 226 # make slave's table autoinc counter bigger
 227 insert into t1 values(null,200),(null,300);
 228 delete from t1 where b <> 100;
 229 # check that slave's table content is identical to master
 230 select * from t1 order by n;
 231 # only the auto_inc counter differs.
 232
 233 connection master;
 234 replace into t1 values(null,100),(null,350);
 235 select * from t1 order by n;
 236 sync_slave_with_master;
 237 select * from t1 order by n;
 238
 239 # Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE
 240
 241 # We first check that if we update a row using a value larger than the
 242 # table's counter, the counter for next row is bigger than the
 243 # after-value of the updated row.
 244 connection master;
 245 insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000;
 246 select * from t1 order by n;
 247 sync_slave_with_master;
 248 select * from t1 order by n;
 249
 250 # and now test for the bug:
 251 connection master;
 252 drop table t1;
 253 eval create table t1 (n int primary key auto_increment not null,
 254                       b int, unique(b)) engine=$engine_type;
 255 insert into t1 values(null,100);
 256 select * from t1 order by n;
 257 sync_slave_with_master;
 258 insert into t1 values(null,200),(null,300);
 259 delete from t1 where b <> 100;
 260 select * from t1 order by n;
 261
 262 connection master;
 263 insert into t1 values(null,100),(null,350) on duplicate key update n=2;
 264 select * from t1 order by n;
 265 sync_slave_with_master;
 266 select * from t1 order by n;
 267
 268 connection master;
 269 drop table t1;
 270 sync_slave_with_master;
 271
 272 #
 273 # BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values"
 274 #
 275
 276 connection master;
 277 # testcase with INSERT VALUES
 278 eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT,
 279                       UNIQUE(b)) ENGINE=$engine_type;
 280 INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10;
 281 SELECT * FROM t1 ORDER BY a;
 282 sync_slave_with_master;
 283 SELECT * FROM t1 ORDER BY a;
 284 connection master;
 285 drop table t1;
 286
 287 # tescase with INSERT SELECT
 288 eval CREATE TABLE t1 (
 289   id bigint(20) unsigned NOT NULL auto_increment,
 290   field_1 int(10) unsigned NOT NULL,
 291   field_2 varchar(255) NOT NULL,
 292   field_3 varchar(255) NOT NULL,
 293   PRIMARY KEY (id),
 294   UNIQUE KEY field_1 (field_1, field_2)
 295 ) ENGINE=$engine_type;
 296 eval CREATE TABLE t2 (
 297   field_a int(10) unsigned NOT NULL,
 298   field_b varchar(255) NOT NULL,
 299   field_c varchar(255) NOT NULL
 300 ) ENGINE=$engine_type;
 301 INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a');
 302 INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b');
 303 INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c');
 304 INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d');
 305 INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e');
 306 # Updating table t1 based on values from table t2
 307 INSERT INTO t1 (field_1, field_2, field_3)
 308 SELECT t2.field_a, t2.field_b, t2.field_c
 309 FROM t2
 310 ON DUPLICATE KEY UPDATE
 311 t1.field_3 = t2.field_c;
 312 # Inserting new record into t2
 313 INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f');
 314 # Updating t1 again
 315 INSERT INTO t1 (field_1, field_2, field_3)
 316 SELECT t2.field_a, t2.field_b, t2.field_c
 317 FROM t2
 318 ON DUPLICATE KEY UPDATE
 319 t1.field_3 = t2.field_c;
 320 SELECT * FROM t1 ORDER BY id;
 321 sync_slave_with_master;
 322 SELECT * FROM t1 ORDER BY id;
 323 connection master;
 324 drop table t1, t2;
 325
 326 #
 327 # BUG#20339: stored procedure using LAST_INSERT_ID() does not
 328 # replicate statement-based.
 329 #
 330 # There is another version of the test for bug#20339 above that is
 331 # actually originates in 5.1, and this is the version that is merged
 332 # from 5.0.
 333 #
 334 connection master;
 335
 336 --disable_warnings
 337 DROP PROCEDURE IF EXISTS p1;
 338 DROP TABLE IF EXISTS t1, t2;
 339 --enable_warnings
 340
 341 # Reset result of LAST_INSERT_ID().
 342 SELECT LAST_INSERT_ID(0);
 343
 344 eval CREATE TABLE t1 (
 345   id INT NOT NULL DEFAULT 0,
 346   last_id INT,
 347   PRIMARY KEY (id)
 348 ) ENGINE=$engine_type;
 349
 350 eval CREATE TABLE t2 (
 351   id INT NOT NULL AUTO_INCREMENT,
 352   last_id INT,
 353   PRIMARY KEY (id)
 354 ) ENGINE=$engine_type;
 355
 356 delimiter |;
 357 CREATE PROCEDURE p1()
 358 BEGIN
 359   INSERT INTO t2 (last_id) VALUES (LAST_INSERT_ID());
 360   INSERT INTO t1 (last_id) VALUES (LAST_INSERT_ID());
 361 END|
 362 delimiter ;|
 363
 364 CALL p1();
 365 SELECT * FROM t1 ORDER BY id;
 366 SELECT * FROM t2 ORDER BY id;
 367
 368 sync_slave_with_master;
 369 SELECT * FROM t1 ORDER BY id;
 370 SELECT * FROM t2 ORDER BY id;
 371
 372 connection master;
 373
 374 DROP PROCEDURE p1;
 375 DROP TABLE t1, t2;
 376
 377
 378 #
 379 # BUG#21726: Incorrect result with multiple invocations of
 380 # LAST_INSERT_ID
 381 #
 382 --disable_warnings
 383 DROP PROCEDURE IF EXISTS p1;
 384 DROP FUNCTION IF EXISTS f1;
 385 DROP FUNCTION IF EXISTS f2;
 386 DROP FUNCTION IF EXISTS f3;
 387 DROP TABLE IF EXISTS t1, t2;
 388 --enable_warnings
 389
 390 eval CREATE TABLE t1 (
 391     i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 392     j INT DEFAULT 0
 393 ) ENGINE=$engine_type;
 394 eval CREATE TABLE t2 (i INT) ENGINE=$engine_type;
 395
 396 delimiter |;
 397 CREATE PROCEDURE p1()
 398 BEGIN
 399   INSERT INTO t1 (i) VALUES (NULL);
 400   INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
 401   INSERT INTO t1 (i) VALUES (NULL), (NULL);
 402   INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
 403 END |
 404
 405 CREATE FUNCTION f1() RETURNS INT MODIFIES SQL DATA
 406 BEGIN
 407   INSERT INTO t1 (i) VALUES (NULL);
 408   INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
 409   INSERT INTO t1 (i) VALUES (NULL), (NULL);
 410   INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
 411   RETURN 0;
 412 END |
 413
 414 CREATE FUNCTION f2() RETURNS INT NOT DETERMINISTIC
 415   RETURN LAST_INSERT_ID() |
 416
 417 CREATE FUNCTION f3() RETURNS INT MODIFIES SQL DATA
 418 BEGIN
 419   INSERT INTO t2 (i) VALUES (LAST_INSERT_ID());
 420   RETURN 0;
 421 END |
 422 delimiter ;|
 423
 424 INSERT INTO t1 VALUES (NULL, -1);
 425 CALL p1();
 426 SELECT f1();
 427 INSERT INTO t1 VALUES (NULL, f2()), (NULL, LAST_INSERT_ID()),
 428                       (NULL, LAST_INSERT_ID()), (NULL, f2()), (NULL, f2());
 429 INSERT INTO t1 VALUES (NULL, f2());
 430 # Test replication of substitution "IS NULL" -> "= LAST_INSERT_ID".
 431 INSERT INTO t1 VALUES (NULL, 0), (NULL, LAST_INSERT_ID());
 432 UPDATE t1 SET j= -1 WHERE i IS NULL;
 433
 434 # Test statement-based replication of function calls.
 435 INSERT INTO t1 (i) VALUES (NULL);
 436
 437 # Here, we rely on having set @@concurrent_insert= 0 (see comment at
 438 # the top of this file).
 439 connection master1;
 440 INSERT INTO t1 (i) VALUES (NULL);
 441
 442 connection master;
 443 SELECT f3();
 444
 445 SELECT * FROM t1 ORDER BY i;
 446 SELECT * FROM t2 ORDER BY i;
 447
 448 sync_slave_with_master;
 449 SELECT * FROM t1;
 450 SELECT * FROM t2;
 451
 452 connection master;
 453 DROP PROCEDURE p1;
 454 DROP FUNCTION f1;
 455 DROP FUNCTION f2;
 456 DROP FUNCTION f3;
 457 DROP TABLE t1, t2;
 458
 459
 460 sync_slave_with_master;
 461
 462 --echo #
 463 --echo # End of 5.0 tests
 464 --echo #
 465
 466 # Tests in this file are tightly bound together.  Recreate t2.
 467 connection master;
 468 eval create table t2 (
 469   id int not null auto_increment,
 470   last_id int,
 471   primary key (id)
 472 ) engine=$engine_type;
 473
 474
 475 # Test for BUG#20341 "stored function inserting into one
 476 # auto_increment puts bad data in slave"
 477
 478 connection master;
 479 truncate table t2;
 480 # no auto_increment
 481 eval create table t1 (id tinyint primary key) engine=$engine_type;
 482
 483 delimiter |;
 484 create function insid() returns int
 485 begin
 486   insert into t2 (last_id) values (0);
 487   return 0;
 488 end|
 489 delimiter ;|
 490 set sql_log_bin=0;
 491 insert into t2 (id) values(1),(2),(3);
 492 delete from t2;
 493 set sql_log_bin=1;
 494 #inside SELECT, then inside INSERT
 495 select insid();
 496 set sql_log_bin=0;
 497 insert into t2 (id) values(5),(6),(7);
 498 delete from t2 where id>=5;
 499 set sql_log_bin=1;
 500 insert into t1 select insid();
 501 select * from t1 order by id;
 502 select * from t2 order by id;
 503
 504 sync_slave_with_master;
 505 select * from t1 order by id;
 506 select * from t2 order by id;
 507
 508 connection master;
 509 drop table t1;
 510 drop function insid;
 511
 512 truncate table t2;
 513 eval create table t1 (n int primary key auto_increment not null,
 514                       b int, unique(b)) engine=$engine_type;
 515 delimiter |;
 516 create procedure foo()
 517 begin
 518   insert into t1 values(null,10);
 519   insert ignore into t1 values(null,10);
 520   insert ignore into t1 values(null,10);
 521   insert into t2 values(null,3);
 522 end|
 523 delimiter ;|
 524 call foo();
 525 select * from t1 order by n;
 526 select * from t2 order by id;
 527
 528 sync_slave_with_master;
 529 select * from t1 order by n;
 530 select * from t2 order by id;
 531
 532 connection master;
 533 drop table t1, t2;
 534 drop procedure foo;
 535 SET @@global.concurrent_insert= @old_concurrent_insert;
 536
 537 --source include/rpl_end.inc