Bug #38018 Maria: INSERT SELECT ON DUPLICATE KEY UPDATE, LOAD DATA REPLACE wrong lock
Submitted: 10 Jul 2008 14:43 Modified: 11 Jul 2008 15:52
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Maria storage engine Severity:S3 (Non-critical)
Version:5.1-maria OS:Linux
Assigned to: Guilhem Bichot CPU Architecture:Any

[10 Jul 2008 14:43] Guilhem Bichot
Description:
INSERT SELECT ON DUPLICATE KEY UPDATE and LOAD DATA CONCURRENT REPLACE
use TL_WRITE_CONCURRENT_INSERT though they may update/delete a row, as can be seen by running this:

# test INSERT ON DUPLICATE KEY UPDATE
create table t1(id int, s char(1), unique(s)) engine=maria;
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1;
insert into t1 values(1,"a") on duplicate key update t1.id=t1.id+1;
insert into t1 select 1,"a" on duplicate key update t1.id=t1.id+1;
select * from t1;
drop table t1;

# test LOAD DATA INFILE REPLACE
create table t1 (pk int primary key, apk int unique, data int) engine=maria;
insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6);
load data concurrent infile '../std_data_ln/loaddata5.dat' replace into table
t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk);
select * from t1 order by pk;

after adding
DBUG_ASSERT(file->lock.type != TL_WRITE_CONCURRENT_INSERT);
to ha_maria::update_row() and delete_row().
This looks like a bug, because concurrent insert is supposed to work only when there are no updates or deletes.
Though I haven't found a way where this actually breaks, so maybe this is not a bug, or maybe versioning is disabled somewhere...

REPLACE (see sql_yacc.yy), and INSERT VALUES ON DUPLICATE KEY UPDATE
(see upgrade_lock_type()) are already fine.

How to repeat:
modify code and run above test
[10 Jul 2008 14:43] Guilhem Bichot
We don't yet know if it's a real bug
[10 Jul 2008 14:54] Guilhem Bichot
A first try of patch is at http://lists.mysql.com/maria/117
but was rejected.
[11 Jul 2008 14:24] Sergei Golubchik
the same in MyISAM: bug#38046
[11 Jul 2008 14:50] Guilhem Bichot
New patch for Maria http://lists.mysql.com/maria/122
[11 Jul 2008 15:52] Guilhem Bichot
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[14 Sep 2008 5:55] Bugs System
Pushed into 6.0.7-alpha  (revid:guilhem@mysql.com-20080711143347-fm710xuabjecwymz) (version source revid:john.embretsen@sun.com-20080724122511-9c0oudz1xrdrs6y6) (pib:3)