Description:
I experienced a situation that a compound unique index allowed duplicates without null values.
The situation is like the following description.
## isolation-level
- read-committed
## the related table
CREATE TABLE `coupon_use_log` (
`coupon_use_log_seq` bigint(20) NOT NULL AUTO_INCREMENT ,
`coupon_type` varchar(1) NOT NULL ,
`coupon_id` varchar(100) NOT NULL ,
`use_cnt` int(11) NOT NULL ,
`promotion_seq` int(11) NOT NULL ,
`event_seq` int(11) NOT NULL ,
`print_seq` int(11) NOT NULL ,
`game_ssn` int(11) NOT NULL ,
`game_world` varchar(100) NOT NULL,
`game_usn` varchar(30) NOT NULL ,
`platform_type` int(11) NOT NULL DEFAULT '0',
`platform_usn` varchar(50) NOT NULL,
`reg_date` datetime NOT NULL ,
PRIMARY KEY (`coupon_use_log_seq`),
UNIQUE KEY `idx_COUPON_USE_LOG_01` (`coupon_id`,`use_cnt`,`event_seq`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
## three transactions on the same unique key record of the same table
1) An administrator's transaction to modify a column value of a record.
start at : 160917 12:17:17
end at : 160917 12:17:34
### UPDATE `coupondb`.`coupon_use_log`
### WHERE
### @1=739218 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
### @4=26547 /* INT meta=0 nullable=0 is_null=0 */
2) service transaction 1 ( insert to same table at 1) )
start at : 160917 12:17:25
end at : 160917 12:17:34
INSERT INTO `coupondb`.`coupon_use_log` ( coupon_use_log_seq = 745661 )
## binlog
#160917 12:17:25 server id 2801 end_log_pos 452822143 CRC32 0xca82151b Write_rows: table id 224 flags: STMT_END_F
BINLOG '
xbXcVxPxCgAAVQAAABSE/RoAAOAAAAAAAAEACGNvdXBvbmRiAA5jb3Vwb25fdXNlX2xvZwANCA8P
AwMDAwMPDwMPEgsDACwBLAFaAJYAAAAAlUJATg==
xbXcVx7xCgAAawAAAH+E/RoAAOAAAAAAAAEAAgAN/x8A4L1gCwAAAAAAAVMQAGp1c3Rvbmx5MW1z
bDA5MDe0ZwAApwEAAAQEAAABAAAA7gMAAAEAMQYxOTMxMTgEAAAAATCZmmLEWBsVgso=
'/*!*/;
### INSERT INTO `coupondb`.`coupon_use_log`
### SET
### @1=745661 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='S' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
### @3='abcd0907' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @4=26548 /* INT meta=0 nullable=0 is_null=0 */
### @5=423 /* INT meta=0 nullable=0 is_null=0 */
### @6=1028 /* INT meta=0 nullable=0 is_null=0 */
### @7=1 /* INT meta=0 nullable=0 is_null=0 */
### @8=1006 /* INT meta=0 nullable=0 is_null=0 */
### @9='1' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @10='193118' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### @11=4 /* INT meta=0 nullable=0 is_null=0 */
### @12='0' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### @13='2016-09-17 12:17:24' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 452822143
#160917 12:17:34 server id 2801 end_log_pos 452822174 CRC32 0x553d9dbc Xid = 2001479752
COMMIT/*!*/;
3) service transaction 2 ( insert to same table at 1) and 2) )
start at : 160917 12:17:34
end at : 160917 12:17:34
INSERT INTO `coupondb`.`coupon_use_log` ( coupon_use_log_seq = 745663 )
## binlog
#160917 12:17:34 server id 2801 end_log_pos 452822575 CRC32 0xc1c78e2e Write_rows: table id 224 flags: STMT_END_F
BINLOG '
zrXcVxPxCgAAVQAAAMOF/RoAAOAAAAAAAAEACGNvdXBvbmRiAA5jb3Vwb25fdXNlX2xvZwANCA8P
AwMDAwMPDwMPEgsDACwBLAFaAJYAAAAAsizyzQ==
zrXcVx7xCgAAbAAAAC+G/RoAAOAAAAAAAAEAAgAN/x8A4L9gCwAAAAAAAVMQAGp1c3Rvbmx5MW1z
bDA5MDe0ZwAApwEAAAQEAAABAAAA7gMAAAEAMQcxNDA3OTc1BwAAAAEwmZpixGEujsfB
'/*!*/;
### INSERT INTO `coupondb`.`coupon_use_log`
### SET
### @1=745663 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='S' /* VARSTRING(3) meta=3 nullable=0 is_null=0 */
### @3='abcd0907' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @4=26548 /* INT meta=0 nullable=0 is_null=0 */
### @5=423 /* INT meta=0 nullable=0 is_null=0 */
### @6=1028 /* INT meta=0 nullable=0 is_null=0 */
### @7=1 /* INT meta=0 nullable=0 is_null=0 */
### @8=1006 /* INT meta=0 nullable=0 is_null=0 */
### @9='1' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @10='1407975' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### @11=7 /* INT meta=0 nullable=0 is_null=0 */
### @12='0' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### @13='2016-09-17 12:17:33' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
# at 452822575
#160917 12:17:34 server id 2801 end_log_pos 452822606 CRC32 0x05b83287 Xid = 2001482217
COMMIT/*!*/;
## Detail description for the above situation
1) transaction was started earlier than 2) and 3) and tried to update a record of the table coupon_use_log
update coupon_use_log set use_cnd = 26547
where coupon_id = 'abcd0907' and use_cnt=26548 and event_seq=1028;
(`coupon_id`,`use_cnt`,`event_seq`) is a unique key of the table coupon_use_log
before the first transaction was committed. the second transaction was started to insert a row into a table coupon_use_log with following values for unique index columns => coupon_id = 'abcd0907' , use_cnt=26548, event_seq=1028;
( you can see the complete insert statment by above binlog )
this transaction had to wait to get the lock that the first transaction was holding exclusive for update.
when the first transaction was committed ( the second transaction is automatically committed right after the first is done ), at the same time the third transaction was started to insert a row into a table coupon_use_log with following values for unique index columns => coupon_id = 'abcd0907' , use_cnt=26548, event_seq=1028;
( you can see the complete insert statment by above binlog )
In normal situation, the third transaction must fail because it tried insert statement with same unique key values with transaction 2 insert statement.
but the transaction 2) and 3) were successfully done.
And the slave was stopped with an unique key duplicate error.
it must not happen in any situation. :(
How to repeat:
I can't repeat.
