Bug #71554 Manual does not explain upper limit for innodb_ft_result_search_limit value
Submitted: 1 Feb 2014 18:23 Modified: 14 Feb 2014 13:18
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: fulltext, innodb_ft_result_cache_limit, missing manual

[1 Feb 2014 18:23] Valeriy Kravchuk
Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_ft_result_cach...) says that range of possible values for this variable is:

"Range	1000000 .. unlimited"

This is surely wrong, as there are no unlimited numeric server variables in MySQL. Moreover, limit probably depends on OS (32-bit vs 64-bit, Windows vs Unix etc)

On 64-bit Windows, for example, the limit in 5.6.15 is 4G-1:

mysql> show variables like 'innodb_ft_re%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| innodb_ft_result_cache_limit | 2000000000 |
+------------------------------+------------+
1 row in set (0.00 sec)

mysql> set global innodb_ft_result_cache_limit=4*1024*1024*1024;
Query OK, 0 rows affected, 1 warning (0.05 sec)

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

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

How to repeat:
set global innodb_ft_result_cache_limit=4*1024*1024*1024;
show warnings\G
show variables like 'innodb_ft_re%';

Suggested fix:
Document this limit properly. Also it would be useful to know when memory for this query result cache is allocated: at server startup, when fulltext index is used or at some other moment, is it used by all threads (as variable is global this can be the case) etc.
[4 Feb 2014 9:46] MySQL Verification Team
Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[4 Feb 2014 10:08] MySQL Verification Team
// On OL6

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.17                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.17-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> set global innodb_ft_result_cache_limit=4*18446744073709547520;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(4 * 18446744073709547520)'
mysql> set global innodb_ft_result_cache_limit=2*18446744073709547520;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * 18446744073709547520)'
mysql> set global innodb_ft_result_cache_limit=1*18446744073709547520;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_ft_re%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| innodb_ft_result_cache_limit | 18446744073709547520 |
+------------------------------+----------------------+
1 row in set (0.00 sec)

// On Windows7(64)

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.17                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.17-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Win64                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> set global innodb_ft_result_cache_limit=4*18446744073709547520;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(4 * 18446744073709547520)'
mysql> set global innodb_ft_result_cache_limit=2*18446744073709547520;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(2 * 18446744073709547520)'
mysql> set global innodb_ft_result_cache_limit=1*18446744073709547520;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

mysql>
[14 Feb 2014 13:18] Daniel Price
Posted by developer:
 
Fixed as of 5.6.17, 5.7.4, and here's the changelog entry:

"innodb_ft_result_cache_limit" now has a hardcoded maximum value of
4294967295 bytes or (2**32 -1). The maximum value was previously defined
as the maximum value of "ulong". 

Documentation for innodb_ft_result_cache_limit has been updated to reflect the previous and current maximum value. The revised content will appear soon, with the next published documentation build.
[28 Mar 2014 19:41] Laurynas Biveinis
5.6$ bzr log -r 5813 -n0
------------------------------------------------------------
revno: 5813
committer: Shaohua Wang <shaohua.wang@oracle.com>
branch nick: mysql-5.6-bugfix1
timestamp: Thu 2014-02-13 11:33:41 +0800
message:
  BUG#18180057: MANUAL DOES NOT EXPLAIN UPPER LIMIT FOR 
  INNODB_FT_RESULT_CACHE_LIMIT VALUE
  
  Analysis:
  We set max value of fts_result_cache_limit to ~0UL(max value of ulong),
  but ulong is 4 bytes on windows, the max value is 4294967295(2**32 -1),
  which is not consistent with linux(2**64 -1).
  
  Solution:
  We explicitly set the max value to 4294967295.
  
  rb://4651 approved by Jimmy.Yang
[29 Mar 2014 8:18] Laurynas Biveinis
5.6$ bzr log -r 5827
------------------------------------------------------------
revno: 5827
committer: Shaohua Wang <shaohua.wang@oracle.com>
branch nick: mysql-5.6-bugfix1
timestamp: Wed 2014-02-19 17:03:14 +0800
message:
  Follow up fix for BUG#18180057: MANUAL DOES NOT EXPLAIN UPPER
  LIMIT FOR INNODB_FT_RESULT_CACHE_LIMIT VALUE
  
  Build error in some 32-bit platform handler/ha_innodb.cc:15819:1:
  error: this decimal constant is unsigned only in ISO C90 [-Werror]
  
  Approved by Jimmy.Yang in IM.