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
• Status: Verified
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Windows (Win64)
• 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.