Bug #83045 Unique index allows duplicates without null values

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.

Hello peter kim,

Thank you for the report.
This is most likely duplicate of Bug #73170, Please see Bug #73170

Thanks,
Umesh