• Jan Lindström's avatar
    MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail · 0c4d11e8
    Jan Lindström authored
    This is caused by following change:
    
    commit 95d29c99f01882ffcc2259f62b3163f9b0e80c75
    Author: Marko Mäkelä <marko.makela@oracle.com>
    Date:   Tue Nov 27 11:12:13 2012 +0200
    
        Bug#15920445 INNODB REPORTS ER_DUP_KEY BEFORE CREATE UNIQUE INDEX COMPLETED
    
        There is a phase during online secondary index creation where the index has
        been internally completed inside InnoDB, but does not 'officially' exist yet.
        We used to report ER_DUP_KEY in these situations, like this:
    
        ERROR 23000: Can't write; duplicate key in table 't1'
    
        What we should do is to let the 'offending' operation complete, but report an
        error to the
        ALTER TABLE t1 ADD UNIQUE KEY (c2):
    
        ERROR HY000: Index c2 is corrupted
        (This misleading error message should be fixed separately:
        Bug#15920713 CREATE UNIQUE INDEX REPORTS ER_INDEX_CORRUPT INSTEAD OF DUPLICATE)
    
        row_ins_sec_index_entry_low(): flag the index corrupted instead of
        reporting a duplicate, in case the index has not been published yet.
    
        rb:1614 approved by Jimmy Yang
    
    Problem is that after we have found duplicate key on primary key
    we continue to get necessary gap locks in secondary indexes to
    block concurrent transactions from inserting the searched records.
    However, search from unique index used in foreign key constraint
    could return DB_NO_REFERENCED_ROW if INSERT .. ON DUPLICATE KEY UPDATE
    does not contain value for foreign key column. In this case
    we should return the original DB_DUPLICATE_KEY error instead
    of DB_NO_REFERENCED_ROW.
    
    Consider as a example following:
    
    create table child(a int not null primary key,
    b int not null,
    c int,
    unique key (b),
    foreign key (b) references
    parent (id)) engine=innodb;
    
    insert into child values (1,1,2);
    
    insert into child(a) values (1) on duplicate key update c = 3;
    
    Now primary key value 1 naturally causes duplicate key error that will be
    stored on node->duplicate. If there was no duplicate key error, we should
    return the actual no referenced row error. As value for column b used in
    both unique key and foreign key is not provided, server uses 0 as a
    search value. This is naturally, not found leading to DB_NO_REFERENCED_ROW.
    But, we should update the row with primay key value 1 anyway as
    requested by on duplicate key update clause.
    0c4d11e8
row0ins.cc 103 KB