MySQL Bugs: #30384: SQL_BUFFER_RESULT corrups indexes

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:	Windows (XP Professional)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	bfsm_2007_10_18

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 ;

Thank you for the report.

Verified as described.

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.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.

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.

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.

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.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.