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
Triage: Triaged: D4 (Minor)

[8 Feb 2008 23:34] Paul Dubois
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).
[9 Feb 2008 12:29] Miguel Solorzano
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';
[13 Jul 2010 17:36] Paul Dubois
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.
[8 Feb 2013 8:55] Valeriy Kravchuk
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)