|
|
Test case 1 - Unexpected warning
|
--source include/have_binlog_format_mixed.inc
|
|
CREATE TABLE t1 (
|
a INT,
|
b INT,
|
UNIQUE (a),
|
UNIQUE (b)
|
);
|
|
INSERT INTO t1 VALUES (1,10);
|
INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30;
|
|
# Cleanup
|
DROP TABLE t1;
|
Despite the MBR mode which is supposed to switch to row-based format automatically, the second INSERT causes a warning:
|
10.4 e6373551
|
2020-02-24 18:32:01 10 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
|
However, the event is still written into the binlog in the row format:
BEGIN
|
/*!*/;
|
# at 718
|
# at 800
|
#200224 18:32:01 server id 1 end_log_pos 800 CRC32 0x699db07f Annotate_rows:
|
#Q> INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
|
#200224 18:32:01 server id 1 end_log_pos 846 CRC32 0xb43256af Table_map: `test`.`t1` mapped to number 32
|
# at 846
|
#200224 18:32:01 server id 1 end_log_pos 888 CRC32 0x2da8ac3f Write_rows: table id 32 flags: STMT_END_F
|
### INSERT INTO `test`.`t1`
|
### SET
|
### @1=2
|
### @2=20
|
# Number of rows: 1
|
# at 888
|
#200224 18:32:01 server id 1 end_log_pos 919 CRC32 0xea50f89a Xid = 26
|
COMMIT/*!*/;
|
Now, if we extend the test case a little bit, adding a third field to the table:
|
Test case 2 - Unexpected non-full image upon INSERT
|
--source include/have_binlog_format_mixed.inc
|
|
CREATE TABLE t1 (
|
a INT,
|
b INT,
|
c INT,
|
UNIQUE (a),
|
UNIQUE (b)
|
);
|
|
INSERT INTO t1 (a,b) VALUES (1,10);
|
INSERT INTO t1 (a,b) VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30;
|
|
# Cleanup
|
DROP TABLE t1;
|
The warning is still produced, but on top of that, the row event in the binary log contains a non-full image, even though binlog_row_image=FULL is still used by default (the third column is missing):
#Q> INSERT INTO t1 (a,b) VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
|
#200224 18:35:37 server id 1 end_log_pos 866 CRC32 0x2f651082 Table_map: `test`.`t1` mapped to number 32
|
# at 866
|
#200224 18:35:37 server id 1 end_log_pos 908 CRC32 0x776ec7d7 Write_rows: table id 32 flags: STMT_END_F
|
### INSERT INTO `test`.`t1`
|
### SET
|
### @1=2
|
### @2=20
|
# Number of rows: 1
|
If we make the INSERT.. ON DUPLICATE KEY UPDATE actually hit a duplicate key and be converted into an UPDATE, we'll see a mix of full/minimal image:
|
Test case 3 - Unexpected non-full image upon UPDATE
|
--source include/have_binlog_format_mixed.inc
|
|
CREATE TABLE t1 (
|
a INT,
|
b INT,
|
c INT,
|
UNIQUE (a),
|
UNIQUE (b)
|
);
|
|
INSERT INTO t1 (a,b) VALUES (1,10);
|
INSERT INTO t1 (a,b) VALUES (1,20) ON DUPLICATE KEY UPDATE b = 30;
|
|
# Cleanup
|
DROP TABLE t1;
|
#Q> INSERT INTO t1 (a,b) VALUES (1,20) ON DUPLICATE KEY UPDATE b = 30
|
#200224 18:38:04 server id 1 end_log_pos 866 CRC32 0xda8e71b8 Table_map: `test`.`t1` mapped to number 32
|
# at 866
|
#200224 18:38:04 server id 1 end_log_pos 918 CRC32 0x672a1460 Update_rows: table id 32 flags: STMT_END_F
|
### UPDATE `test`.`t1`
|
### WHERE
|
### @1=1
|
### @2=10
|
### @3=NULL
|
### SET
|
### @1=1
|
### @2=30
|
# Number of rows: 1
|
Reproducible on 10.1-10.5.
Not reproducible on MySQL 5.6.
|