MySQL Bugs: #34437: myisam_max_sort_file_size: Assigning a value at runtime has no effect

Bug #34437	myisam_max_sort_file_size: Assigning a value at runtime has no effect
Submitted:	8 Feb 2008 23:34	Modified:	9 Feb 2008 12:29
Reporter:	Paul DuBois	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Assigning a value to myisam_max_sort_file_size produces no warning, but also has no effect.

How to repeat:
Server version: 5.1.24-rc-debug-log 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| myisam_max_sort_file_size | 2147483647 | 
+---------------------------+------------+
1 row in set (0.04 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 1024*1024*2;
Query OK, 0 rows affected (0.13 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| myisam_max_sort_file_size | 2147483647 | 
+---------------------------+------------+
1 row in set (0.04 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 0;          
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| myisam_max_sort_file_size | 2147483647 | 
+---------------------------+------------+
1 row in set (0.03 sec)

Suggested fix:
Either the variable should change value when a value is assigned to it, or it should be deemed not settable at runtime and assignments to it should fail.

Earlier bug reports about this variable do not seem to be quite the same as this (Bug#339, Bug#342, Bug#1987, Bug#3754).

Thank you for the bug report. Verified as described.

SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
SET GLOBAL myisam_max_sort_file_size = 1024*1024*2;
SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
SET GLOBAL myisam_max_sort_file_size = 0;
SHOW VARIABLES LIKE 'myisam_max_sort_file_size';

There is no session value of this variable:

mysql> SELECT @@SESSION.myisam_max_sort_file_size;
ERROR 1238 (HY000): Variable 'myisam_max_sort_file_size' is a GLOBAL variable

Anyway, setting the global value should at least be *visible* in the
current session. Further investigation shows that it is visible, sort
of. Depends on whether you use SHOW VARIABLES or SHOW GLOBAL
variables:

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036853727232 |
+---------------------------+---------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036853727232 |
+---------------------------+---------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036853727232 |
+---------------------------+---------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| myisam_max_sort_file_size | 2097152 |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------------------+
| Variable_name             | Value               |
+---------------------------+---------------------+
| myisam_max_sort_file_size | 9223372036853727232 |
+---------------------------+---------------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| myisam_max_sort_file_size | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

It seems to me that for a global-only variable, SHOW VARIABLES
and SHOW GLOBAL VARIABLES should show the same value.
That's what happens for, say, key_buffer_size.

Looks fixed in 5.5.30 at least:

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+--------------+
| Variable_name             | Value        |
+---------------------------+--------------+
| myisam_max_sort_file_size | 107374182400 |
+---------------------------+--------------+
1 row in set (0.20 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 1024*1024*2;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| myisam_max_sort_file_size | 2097152 |
+---------------------------+---------+
1 row in set (0.00 sec)

mysql> SET GLOBAL myisam_max_sort_file_size = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'myisam_max_sort_file_size';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| myisam_max_sort_file_size | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.30    |
+-----------+
1 row in set (0.05 sec)