Bug #30384 SQL_BUFFER_RESULT corrups indexes
Submitted: 13 Aug 2007 10:07 Modified: 23 Jan 2008 18:36
Reporter: Ilias Karampatsos Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.x , 5.1.x OS:Microsoft Windows (XP Professional)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: bfsm_2007_10_18
Triage: D1 (Critical)

[13 Aug 2007 10:07] Ilias Karampatsos
Description:
Trying to create a table from a SELECT using the SQL_BUFFER_RESULT switch corrupts the index. Removing the SQL_BUFFER_RESULT fixes the problem.
This did not exist on 4.1.x servers.

How to repeat:
create table x (`x1` mediumint , `x2` mediumint , `x3` mediumint ) ;
insert into x  (x1, x2, x3) values (1, 2, 3),(1, 2, 3),(1, 2, 3),(1, 2, 3);
drop table z;
CREATE  TABLE z (KEY (x1,x2)) ENGINE=MyISAM 
select SQL_BUFFER_RESULT * from x;
check table z Extended;
repair  table z USE_FRM ;
[13 Aug 2007 10:22] Sveta Smirnova
Thank you for the report.

Verified as described.
[21 Sep 2007 10:10] 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/34452

ChangeSet@1.2531, 2007-09-21 12:09:00+04:00, evgen@sunlight.local +4 -0
  Bug#30384: Having SQL_BUFFER_RESULT option in the CREATE .. KEY(..) .. SELECT
  led to creating corrupted index.
  
  While execution of the  CREATE .. SELECT SQL_BUFFER_RESULT statement the 
  engine->start_bulk_insert function was called twice. On the first call
  On the first call MyISAM disabled all non-unique indexes and on the second
  call it decides to not re-enable them because all indexes was disabled.
  Due to this no indexes was actually created during CREATE TABLE thus
  producing crashed table.
  
  Now the select_inset class has is_bulk_insert_mode flag which prevents
  calling the start_bulk_insert function twice.
  The flag is set in the select_create::prepare, select_insert::prepare2
  functions and the select_insert class constructor.
  The flag is reset in the select_insert::send_eof function.
[29 Oct 2007 8:42] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:48] Bugs System
Pushed into 6.0.4-alpha
[31 Oct 2007 20:34] 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/36801

ChangeSet@1.2549, 2007-11-01 00:26:10+00:00, evgen@moonbone.local +2 -0
  Bug#30384: Having SQL_BUFFER_RESULT option in the CREATE .. KEY(..) .. SELECT
  led to creating corrupted index.
  
  Corrected fix. The call to the handler::start_bulk_insert function was
  removed from the select_insert::prepare2 function because of its redundancy.
  The is_bulk_insert_mode flag is removed as it is not needed anymore.
[2 Nov 2007 13:53] 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/36990

ChangeSet@1.2549, 2007-11-02 17:48:14+00:00, evgen@moonbone.local +2 -0
  Bug#30384: Having SQL_BUFFER_RESULT option in the CREATE .. KEY(..) .. SELECT
  led to creating corrupted index.
  
  Corrected fix. The new method called prepare2 is added to the select_create
  class. As all preparetions are done by the select_create::prepare function
  it doesn't do anything.
  The is_bulk_insert_mode flag is removed as it is not needed anymore.
[16 Nov 2007 21:01] 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/37982

ChangeSet@1.2549, 2007-11-16 22:53:56+00:00, evgen@moonbone.local +2 -0
  Bug#30384: Having SQL_BUFFER_RESULT option in the CREATE .. KEY(..) .. SELECT
  led to creating corrupted index.
  
  Corrected fix. The new method called prepare2 is added to the select_create
  class. As all preparations are done by the select_create::prepare function
  it doesn't do anything. Slightly changed algorithm of calling the 
  start_bulk_insert function. Now it's called from the select_insert::prepare2
  function when the SQL_BUFFER_RESULT flags is set.
  The is_bulk_insert_mode flag is removed as it is not needed anymore.
[19 Nov 2007 18:26] 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/38090

ChangeSet@1.2549, 2007-11-19 20:20:19+00:00, evgen@moonbone.local +4 -0
  Bug#30384: Having SQL_BUFFER_RESULT option in the CREATE .. KEY(..) .. SELECT
  led to creating corrupted index.
  
  Corrected fix. The new method called prepare2 is added to the select_create
  class. As all preparations are done by the select_create::prepare function
  it doesn't do anything. Slightly changed algorithm of calling the 
  start_bulk_insert function. Now it's called from the select_insert::prepare2
  function when the SQL_BUFFER_RESULT flags is set.
  The is_bulk_insert_mode flag is removed as it is not needed anymore.
[14 Dec 2007 8:14] Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:16] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:19] Bugs System
Pushed into 6.0.5-alpha
[23 Jan 2008 18:36] Paul Dubois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

For CREATE ... SELECT ... FROM, where the resulting table contained
indexes, adding SQL_BUFFER_RESULT to the SELECT part caused index 
corruption in the table.