MySQL Bugs: #71554: Manual does not explain upper limit for innodb_ft_result_search

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

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.

Hello Valeriy,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

// 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>

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.

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

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.