| 1 |
################################################################################ |
| 2 |
# Bug#17047208 REPLICATION DIFFERENCE FOR MULTIPLE TRIGGERS |
| 3 |
# Problem: If DML invokes a trigger or a stored function that inserts into an |
| 4 |
# AUTO_INCREMENT column, that DML has to be marked as 'unsafe' statement. If the |
| 5 |
# tables are locked in the transaction prior to DML statement (using LOCK |
| 6 |
# TABLES), then the DML statement is not marked as 'unsafe' statement. |
| 7 |
|
| 8 |
# Steps to reproduce the reported test case (BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS) |
| 9 |
# |
| 10 |
# This test script adds test case to cover few other unsafe statements. |
| 11 |
# Case-1: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST |
| 12 |
################################################################################ |
| 13 |
--source include/force_myisam_default.inc |
| 14 |
--source include/have_myisam.inc |
| 15 |
--source include/have_binlog_format_mixed.inc |
| 16 |
--source include/master-slave.inc |
| 17 |
|
| 18 |
# Case-1: BINLOG_STMT_UNSAFE_AUTOINC_NOT_FIRST |
| 19 |
# INSERT into autoincrement field which is not the first part in the |
| 20 |
# composed primary key is unsafe |
| 21 |
# |
| 22 |
# Step-1.1: Create a table with auto increment column and a composed primary key |
| 23 |
# (second column is auto increment column). Such a definition is allowed only |
| 24 |
# with 'myisam' engine. |
| 25 |
CREATE TABLE t1(i int, id INT AUTO_INCREMENT, PRIMARY KEY (i, id)) ENGINE=MYISAM; |
| 26 |
|
| 27 |
# Step-1.2: Inserting into such a table is unsafe. |
| 28 |
INSERT INTO t1 (i) values (1); |
| 29 |
|
| 30 |
# Step-1.3: Repeat step 3.2, now with 'LOCK TABLES' logic. |
| 31 |
START TRANSACTION; |
| 32 |
LOCK TABLES t1 WRITE; |
| 33 |
INSERT INTO t1 (i) values (2); |
| 34 |
UNLOCK TABLES; |
| 35 |
COMMIT; |
| 36 |
|
| 37 |
# Step-1.4: Sync slave with master |
| 38 |
--sync_slave_with_master |
| 39 |
|
| 40 |
# Step-1.5: Diff master-slave tables to make sure everything is in sync. |
| 41 |
--let $diff_tables=master:t1, slave:t1 |
| 42 |
--source include/diff_tables.inc |
| 43 |
|
| 44 |
# Step-1.6: Cleanup |
| 45 |
--connection master |
| 46 |
DROP TABLE t1; |
| 47 |
|
| 48 |
--source include/rpl_end.inc |