Hi Vlad, Just a little optimization: Could you place: err = ha_log_pbxt_thread_error_for_mysql(pb_ignore_dup_key); and if (err == HA_ERR_FOUND_DUPP_KEY ... in { ... } On Mar 19, 2009, at 11:30 AM, Vladimir Kolesnikov wrote: > Vladimir Kolesnikov has proposed merging lp:~vkolesnikov/pbxt/pbxt- > bug-313391 into lp:pbxt. > > Requested reviews: > PBXT Core (pbxt-core) > > test files were also updated to provide resultset sorting for a > previous fix > -- > https://code.launchpad.net/~vkolesnikov/pbxt/pbxt-bug-313391/+merge/ > 4658 > Your team PBXT Core is subscribed to branch lp:pbxt. > === modified file 'ChangeLog' > --- ChangeLog 2009-03-17 16:06:29 +0000 > +++ ChangeLog 2009-03-19 10:20:21 +0000 > @@ -3,6 +3,8 @@ > > ------- 1.0.08 RC - Not yet released > > +RN229: Fixed bug #313391: LOAD DATA..REPLACE broken > + > RN228: Fixed bug #341115: 'Out of memory' error (a bug in key > comparison algorithm) > > RN227: Changed conflict handling to use spin locks and improve > efficiency. > > === modified file 'src/ha_pbxt.cc' > --- src/ha_pbxt.cc 2009-03-11 15:38:29 +0000 > +++ src/ha_pbxt.cc 2009-03-19 10:20:21 +0000 > @@ -2211,6 +2211,15 @@ > if (!xt_tab_new_record(pb_open_tab, (xtWord1 *) buf)) > err = ha_log_pbxt_thread_error_for_mysql(pb_ignore_dup_key); > > + /* > + * This is needed to allow the same row to be updated multiple > times in case of bulk REPLACE. > + * This happens during execution of LOAD DATA...REPLACE MySQL > first tries to INSERT the row > + * and if it gets dup-key error it tries UPDATE, so the same row > can be overwriten multiple > + * times within the same statement > + */ > + if (err == HA_ERR_FOUND_DUPP_KEY && pb_open_tab->ot_thread- > >st_is_update) > + pb_open_tab->ot_thread->st_update_id++; > + > return err; > } > > > === modified file 'test/mysql-test/r/pbxt_bugs.result' > --- test/mysql-test/r/pbxt_bugs.result 2009-03-17 16:06:29 +0000 > +++ test/mysql-test/r/pbxt_bugs.result 2009-03-19 10:20:21 +0000 > @@ -1071,18 +1071,18 @@ > 1 SIMPLE t2 index NULL c1 4 NULL 16 Using index > SELECT c1 FROM t2; > c1 > --2147483648 > +-1 > +-11 > -12 > --11 > +-2 > +-2147483648 > +-3 > +-4 > +-5 > +-6 > +-7 > +-8 > -9 > --8 > --7 > --6 > --5 > --4 > --3 > --2 > --1 > 0 > 10 > 2147483647 > @@ -1090,6 +1090,7 @@ > UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2; > SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2; > c1 c2 c3 > +-12 12 12 > -2147483648 1 1 > -2147483648 2 2 > -3 3 3 > @@ -1099,29 +1100,28 @@ > -7 7 7 > -8 8 8 > -9 9 9 > +0 2147483648 15 > 10 10 10 > --12 12 12 > 2147483647 2147483647 16 > -0 2147483648 15 > 2147483647 4294967295 14 > UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2; > SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC; > c1 c2 c3 > +-12 12 12 > +-2147483648 0 13 > +-2147483648 1 1 > +-2147483648 2 2 > +-2147483648 2147483648 15 > -2147483648 4294967295 14 > --2147483648 2147483648 15 > +-3 3 3 > +-4 4 4 > +-5 5 5 > +-6 6 6 > +-7 7 7 > +-8 8 8 > +-9 9 9 > +10 10 10 > 2147483647 2147483647 16 > --12 12 12 > -10 10 10 > --9 9 9 > --8 8 8 > --7 7 7 > --6 6 6 > --5 5 5 > --4 4 4 > --3 3 3 > --2147483648 2 2 > --2147483648 1 1 > --2147483648 0 13 > UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2; > SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2; > c1 c2 c3 > @@ -1143,8 +1143,8 @@ > UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT > 2; > SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC; > c1 c2 c3 > +-2147483648 8 8 > -2147483648 9 9 > --2147483648 8 8 > UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2; > SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2; > c1 c2 c3 > @@ -1158,19 +1158,19 @@ > UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1; > SELECT * FROM t2 WHERE c1=-2147483648; > c1 c2 c3 > --2147483648 2147483648 15 > --2147483648 4294967295 14 > --2147483648 6 6 > --2147483648 NULL 11 > -2147483648 0 13 > +-2147483648 1 1 > -2147483648 12 12 > --2147483648 7 7 > --2147483648 1 1 > -2147483648 2 2 > +-2147483648 2147483648 15 > -2147483648 4 4 > +-2147483648 4294967295 14 > -2147483648 5 5 > +-2147483648 6 6 > +-2147483648 7 7 > +-2147483648 8 8 > -2147483648 9 9 > --2147483648 8 8 > +-2147483648 NULL 11 > SELECT * FROM t2; > c1 c2 c3 > -2147483648 0 13 > @@ -1210,3 +1210,11 @@ > -3 > 10 > 2147483647 > +DROP TABLE IF EXISTS t1; > +CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 VARCHAR(255)); > +LOAD DATA LOCAL INFILE 't/load_unique_error1.inc' REPLACE INTO > TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@c1,c2) > SET c1 = @c1 % 2; > +SELECT * FROM t1 ORDER BY c1; > +c1 c2 > +0 opq > +1 jkl > +DROP TABLE t1; > > === modified file 'test/mysql-test/t/pbxt_bugs.test' > --- test/mysql-test/t/pbxt_bugs.test 2009-03-17 16:06:29 +0000 > +++ test/mysql-test/t/pbxt_bugs.test 2009-03-19 10:20:21 +0000 > @@ -876,31 +876,52 @@ > > # make sure it uses index scan > EXPLAIN SELECT c1 FROM t2; > - > +--sorted_result > SELECT c1 FROM t2; > > UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC; > UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 > LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT > 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC; > UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 > LIMIT 2; > +--sorted_result > SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2; > UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1; > +--sorted_result > SELECT * FROM t2 WHERE c1=-2147483648; > --sorted_result > SELECT * FROM t2; > > # make sure it uses index scan > EXPLAIN SELECT c1 FROM t2; > - > -SELECT c1 FROM t2; > \ No newline at end of file > +--sorted_result > +SELECT c1 FROM t2; > + > +# bug 313391: LOAD DATA..REPLACE broken > + > +--disable_warnings > +DROP TABLE IF EXISTS t1; > +--enable_warnings > +CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 VARCHAR(255)); > +LOAD DATA LOCAL INFILE 't/load_unique_error1.inc' REPLACE INTO > TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@c1,c2) > SET c1 = @c1 % 2; > +--sorted_result > +SELECT * FROM t1 ORDER BY c1; > +DROP TABLE t1; > + > -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com