MySQL Bugs: #42286: myisam_max_sort_file_size has incorrect initial and DEFAULT values

Bug #42286	myisam_max_sort_file_size has incorrect initial and DEFAULT values
Submitted:	23 Jan 2009 0:37	Modified:	28 May 2009 9:01
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	default value, initial value, myisam_max_sort_file_size, variables

Description:
The global variable myisam_max_sort_file_size has incorrect initial and DEFAULT values (compared to the manual)

NOTE:  These values were produced on Mac OSX 10.5, Intel.  However, these symptoms were produced on other OS's.  See Bug #39369	execution of "variables.test" with "check-testcases" show differences.

SELECT @@GLOBAL.myisam_max_sort_file_size as initial_myisam_max_sort;
initial_myisam_max_sort
2147483647
SET @@global.myisam_max_sort_file_size = default;
SELECT @@global.myisam_max_sort_file_size as default_myisam_max_sort;
default_myisam_max_sort
2146435072
SET @@global.myisam_max_sort_file_size = 2147483648;
SELECT @@global.myisam_max_sort_file_size as should_be_2147483648;
should_be_2147483648
2147483648

How to repeat:
Run the attached test case and observe the values.

See the output of the .test file above in the description.

Suggested fix:
Ensure the initial and DEFAULT values are in sync.

Either update the code or the manual to reflect actual server behavior.

.test file to show bug behavior

Attachment: exp.test (, text), 656 bytes.

.result file for the test case.

Attachment: exp.result (, text), 804 bytes.

NOTE:
This was observed in the 5.1-rpl tree.
The behavior of the -bugteam trees differs:  Initial and DEFAULT values match, but they *do not* match the manual's value:

Observed on -bugteam:
2146435072 for initial and DEFAULT
Manual calls for 2147483648

This is likely a symptom /duplicate of Bug #31177	Server variables can't be set to their current values

presumed fallout from Bug#31177, and therefore correct in -bugteam

to wit:

64-bit
> SELECT @@GLOBAL.myisam_max_sort_file_size as initial_myisam_max_sort;
9223372036853727232
> SET @@global.myisam_max_sort_file_size = default;
> SELECT @@global.myisam_max_sort_file_size as default_myisam_max_sort;
9223372036853727232

32-bit
> SELECT @@GLOBAL.myisam_max_sort_file_size as initial_myisam_max_sort;
2146435072
> SET @@global.myisam_max_sort_file_size = default;
> SELECT @@global.myisam_max_sort_file_size as default_myisam_max_sort;
2146435072

both
> SET @@global.myisam_max_sort_file_size = 2147483648;
> SELECT @@global.myisam_max_sort_file_size;
2147483648

Please update docs, thanks!

Since this is an issue with the documentation and not with the server, I've changed the category to Docs and reassigned to myself.

Maximum and default values are set for myisam_max_sort_file_size in mysqld.cc:

  {"myisam_max_sort_file_size", OPT_MYISAM_MAX_SORT_FILE_SIZE,
   "Don't use the fast sort index method to created index if the temporary file would get bigger than this.",
   (uchar**) &global_system_variables.myisam_max_sort_file_size,
   (uchar**) &max_system_variables.myisam_max_sort_file_size, 0,
   GET_ULL, REQUIRED_ARG, (longlong) LONG_MAX, 0, (ulonglong) MAX_FILE_SIZE,
   0, 1024*1024, 0},

MAX_FILE_SIZE is defined in my_base.h:

#if SYSTEM_SIZEOF_OFF_T == 4
#define MAX_FILE_SIZE	INT_MAX32
#else
#define MAX_FILE_SIZE	LONGLONG_MAX
#endif

LONGLONG_MAX is defined in my_global.h:

#if SIZEOF_LONG_LONG > 4
#define HAVE_LONG_LONG 1
#endif

/*
  Some pre-ANSI-C99 systems like AIX 5.1 and Linux/GCC 2.95 define
  ULONGLONG_MAX, LONGLONG_MIN, LONGLONG_MAX; we use them if they're defined.
  Also on Windows we define these constants by hand in config-win.h.
*/

#if defined(HAVE_LONG_LONG) && !defined(LONGLONG_MIN)
#define LONGLONG_MIN	((long long) 0x8000000000000000LL)
#define LONGLONG_MAX	((long long) 0x7FFFFFFFFFFFFFFFLL)
#endif

Also in my_global.h, we have

#ifdef USE_RAID
/*
  The following is done with a if to not get problems with pre-processors
  with late define evaluation
*/
#if SIZEOF_OFF_T == 4
#define SYSTEM_SIZEOF_OFF_T 4
#else
#define SYSTEM_SIZEOF_OFF_T 8
#endif
#undef  SIZEOF_OFF_T
#define SIZEOF_OFF_T	    8
#else
#define SYSTEM_SIZEOF_OFF_T SIZEOF_OFF_T
#endif /* USE_RAID */

In addition, we have in set_var.cc:

static void
fix_myisam_max_sort_file_size(THD *thd, enum_var_type type)
{
  myisam_max_temp_length=
    (my_off_t) global_system_variables.myisam_max_sort_file_size;
}

Can you tell what all this actually means? As I understand it, the maximum for myisam_max_sort_file_size ought to 0x7fffffff on 32-bit systems and 0x7fffffffffffffff on 64-bit, but this does not seem to be the case.

Also, I'm wondering if the removal of support for RAID in 5.0 didn't cause a change?

If there was a change, and it didn't have anything to do with RAID going away, then we need to determine when it happened, why it happened, and if the change was intentional.

Setting to NDI because I've gone as far as I can with this without assistance from knowledgeable developer.

Tatjana, maybe you can shed some light on this, or suggest someone who can?

This was closed in the course of fixing another Docs issue some time back.