Bug #71479 sort_buffer_size is limited to 4GB-1 on 64-bit Windows
Submitted: 25 Jan 2014 15:32 Modified: 27 Jan 2014 15:14
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6 OS:Microsoft Windows (Win64)
Assigned to: CPU Architecture:Any
Tags: sort_buffer_size

[25 Jan 2014 15:32] Valeriy Kravchuk
Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sort_buffer_siz...) says:

"The maximum permissible setting for sort_buffer_size is 4GB. Values larger than 4GB are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning)."

But in fact (on 5.6.15 running on 64-bit Windows 7 at least) the limit is 4*1024*1024*1024-1 (4GB-1):

mysql> set session sort_buffer_size=4*1024*1024*1024-1;
Query OK, 0 rows affected (0.06 sec)

mysql> set session sort_buffer_size=4*1024*1024*1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect sort_buffer_size value: '4294967296'
1 row in set (0.00 sec)

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.15-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql> show variables like 'sort_buffer_size';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| sort_buffer_size | 4294967295 |
+------------------+------------+
1 row in set (0.00 sec)

How to repeat:
On 64-bit Windows try these:

set session sort_buffer_size=4*1024*1024*1024-1;
show variables like 'sort_buffer_size';
set session sort_buffer_size=4*1024*1024*1024;
show warnings\G
show variables like 'sort_buffer_size';

show variables like 'version%';

Suggested fix:
Document the limit properly in the manual.
[27 Jan 2014 10:10] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[27 Jan 2014 11:57] MySQL Verification Team
but why does this limit apply to windows and not linux?  shouldn't the limit rather be removed instead of document it?
[27 Jan 2014 15:14] Valeriy Kravchuk
Good question, Shane... I suspect some (wrong?) data type is used for the related variable on Windows for some historical reason (like "nobody cared to ask"). 

I don't mind to see this re-classified as a server bug.
[27 Jan 2014 17:42] Paul DuBois
Perhaps there are two issues here:

1) The off-by-one error in the description in the docs (4GB rather than 4GB-1). I can deal with this.

2) The capping of the value for Windows 64-bit, as opposed to other 64-bit systems.

Similar capping applies to join_buffer_size, key_buffer_size, myisam_sort_buffer_size, where the limit was lifted for non-Windows 64-bit systems in 5.1.23. The limit on Windows was lifted for key_buffer_size in 5.1.31; not sure why it wouldn't have been lifted for the others. Maybe it could be, in which case this might better be considered a server bug, or feature request.
[30 Jan 2014 13:58] Paul DuBois
Off-by-one aspect of the docs description has been fixed.
[30 Jan 2014 14:23] Paul DuBois
Unassigning from myself; no longer a docs bug.