MySQL Bugs: #45702: Impossible to specify myisam_sort

Bug #45702	Impossible to specify myisam_sort_buffer > 4GB on 64 bit machines
Submitted:	24 Jun 2009 12:57	Modified:	3 Feb 2012 16:36
Reporter:	Alexey Stroganov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.x, 5.1.x, 5.4.4, 5.5.17	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
While testing various scenarios of loading of data I've noted that in pretty simple case 'repair with key cache' is used even in case when myisam_sort_buffer was big enough: 8G. 

Further analysis showed that we have inconsistent situation with types for buffer size variables in MyISAM. Some of them like sortbuff_size still 'uint' that makes impossible to pass values more than 32bit.

There is already closed bug#29446 for exact the same issue but unfortunately it
didn't fix the problem.

example of inconsistency:

mysqld.cc
...
  {"myisam_sort_buffer_size", OPT_MYISAM_SORT_BUFFER_SIZE,
   "The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALT
ER TABLE.",
   (uchar**) &global_system_variables.myisam_sort_buff_size,
   (uchar**) &max_system_variables.myisam_sort_buff_size, 0,
   GET_ULONG, REQUIRED_ARG, 8192*1024, 4, (longlong) ULONG_MAX, 0, 1, 0}
...

include/myisam.h

...
typedef struct st_mi_sort_param
{
...
  uint key, key_length,real_key_length,sortbuff_size;
...
} MI_SORT_PARAM;

storage/myisam/sort.c

In mi_check.c we explicitly cast param->sort_buffer_length to uint

mi_repair(...)
{
...
    if (_create_index_by_sort(&sort_param,
                              (my_bool) (!(param->testflag & T_VERBOSE)),
                              (uint) param->sort_buffer_length))
...
}

in the code below we get parameter as  ulong and then cast it again to uint

int _create_index_by_sort(MI_SORT_PARAM *info,my_bool no_messages,
                          ulong sortbuff_size)
{
...
 uint memavl,old_memavl,keys,sort_length;
...
 memavl=max(sortbuff_size,MIN_SORT_MEMORY);
...
}

How to repeat:
1. On x64 box start mysqld under debugger with --myisam_sort_buffer_size= >=4G 
2. alter table disable/enable keys on any myisam table
3. check in debugger values of sort_buffer_size at above points

Observations for the 5.1:

sort_buffer_length is ulong here, that is correct.

include/myisam.h
... 
ulong read_buffer_length,write_buffer_length,
        sort_buffer_length,sort_key_blocks;
...

however later in mi_check.c sort_buffer_length is casted to uint anyway:

...
   if (_create_index_by_sort(&sort_param,
                              (my_bool) (!(param->testflag & T_VERBOSE)),
                              (uint) param->sort_buffer_length))
...

So 5.1 is affected as well.

Observations for 5.0:

in 5.0 we have exactly the same situation as in 5.1, so 5.0 is affected as well.

It should be taken into account that myisam code in 6.0/5.4.4 is a bit different from 5.0/5.1  so there should be two different fixes(quite similar though) one for 5.0/5.1 and another for 5.4.4.

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/89517

2940 Alexey Botchkov	2009-11-05
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
         myisam_sort_buffer_size and related variables became ulonglong.
         The limitation of the buffer size is MAX_SIZE_T as the size_t type
         specifies the parameter for malloc.
         Also the not_partition test fixed as it doesn't actually pass.
      
      per-file comments:
        include/myisam.h
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        mysql-test/r/not_partition.result
          test result updated as the error message changed
        mysql-test/t/not_partition.test
          error numbers changed so the test should be modified
        sql/handler.h
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        sql/mysqld.cc
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        sql/set_var.cc
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        sql/sql_class.h
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        sql/sql_sort.h
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          buffer size variables became ulonglong
        storage/myisam/sort.c
      Bug#45702      Impossibe to specify myisam_sort_buffer > 4GB on 64 bit machines
          variable types changed with the ulonglong

See also bug #59925, probably same issue.

Bug #62827 was marked as a duplicate of this one.

Noted in 5.5.22, 5.6.5 changelogs.

myisam_sort_buffer_size could not be set larger than 4GB on 64-bit
systems.