| Bug #46961 | archive engine loses rows during join when results approach join_buffer_size | ||
|---|---|---|---|
| Submitted: | 27 Aug 2009 17:28 | Modified: | 18 Dec 2009 11:43 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Archive storage engine | Severity: | S1 (Critical) |
| Version: | 5.1.37, 5.1.39 | OS: | Any |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
| Tags: | regression | ||
[27 Aug 2009 17:38]
MySQL Verification Team
the select is to blame, not the insert. select t2.a,t2.b,t2.c,t2.d from t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i; where t2 is archive doesn't return all the rows!
[27 Aug 2009 18:09]
Giuseppe Maxia
Note, however, that changing the query this way, it selects all the rows: select t2.a,t2.b,t2.c,t2.d from t2 j, t2 a, t2 b, t2 c, t2 d, t2 e, t2 f, t2 g, t2 h, t2 ; Still a bug, but more interesting :)
[27 Aug 2009 18:52]
MySQL Verification Team
Thank you for the bug report. Verified as described.
c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-Win X64 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 >use test
Database changed
mysql 5.1 >drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.1 >create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)
mysql 5.1 >insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1 >insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
-> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 59049 rows affected (0.68 sec)
Records: 59049 Duplicates: 0 Warnings: 0
mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
| 59052 |
+----------+
1 row in set (0.00 sec)
mysql 5.1 >drop table if exists t2;
Query OK, 0 rows affected (0.01 sec)
mysql 5.1 >create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive;
Query OK, 0 rows affected (0.11 sec)
mysql 5.1 >insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql 5.1 >insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
-> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 29778 rows affected (0.44 sec)
Records: 29778 Duplicates: 0 Warnings: 0
mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
| 29781 |
+----------+
1 row in set (0.04 sec)
mysql 5.1 >
[4 Sep 2009 7:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82413 3109 Sergey Vojtovich 2009-09-04 BUG#46961 - archive engine loses rows during self joining select! SELECT with join (not only self-join) from archive table may return incomplete result set, when result set size exceeds join buffer size. The problem was that archive row counter was initialzed too early, when ha_archive::info() method was called. Later, when optimizer exceeds join buffer, it attempts to reuse handler without calling ha_archive::info() again (which is correct). Fixed by moving row counter initialization from ha_archive::info() to ha_archive::rnd_init(). @ mysql-test/r/archive.result A test case for BUG#46961. @ mysql-test/t/archive.test A test case for BUG#46961. @ storage/archive/ha_archive.cc Since a cursor may get reused without a call to ::info(), move assignment of scan_rows to a proper place, that is ::rnd_init().
[14 Sep 2009 16:05]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[22 Sep 2009 9:41]
Tony Bedford
An entry was added to the 5.4.4 changelog: The Archive storage engine lost records during a bulk insert.
[2 Oct 2009 1:41]
Paul DuBois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[6 Oct 2009 8:59]
Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[18 Dec 2009 10:38]
Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:53]
Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:58]
Tony Bedford
Updated changelog to include versions 5.1.40 and 5.1.41-ndb-7.1.0.
[18 Dec 2009 11:08]
Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:22]
Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[18 Dec 2009 11:43]
Tony Bedford
Updated changelog to include versions 5.1.41-ndb-6.3.31 and 5.1.41-ndb-7.0.11.
Description: In 5.1, archive engine loses records during bulk insert. MyISAM and InnoDB tables both contain 59052 records, whereas archive ends up with 29781. Output of testcase: mysql> drop table if exists t2; Query OK, 0 rows affected (1.97 sec) mysql> create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam; Query OK, 0 rows affected (0.95 sec) mysql> insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d'); Query OK, 3 rows affected (0.66 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i; Query OK, 59049 rows affected (1 min 15.50 sec) Records: 59049 Duplicates: 0 Warnings: 0 mysql> mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 59052 | +----------+ 1 row in set (0.20 sec) mysql> mysql> drop table if exists t2; Query OK, 0 rows affected (0.08 sec) mysql> create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive; Query OK, 0 rows affected (0.27 sec) mysql> insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d'); Query OK, 3 rows affected (0.20 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i; Query OK, 29778 rows affected (51.75 sec) Records: 29778 Duplicates: 0 Warnings: 0 mysql> mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 29781 | +----------+ 1 row in set (0.06 sec) How to repeat: execute these two identical sql (except for table engine): ---- drop table if exists t2; create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam; insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d'); insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i; select count(*) from t2; ----- ----- drop table if exists t2; create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive; insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d'); insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i; select count(*) from t2; ------ now compare the count(*) result. they are wildly different! Suggested fix: insert all the rows!