MySQL Bugs: #74871: max_seeks_for_key default value is invalid when used in cnf

Bug #74871	max_seeks_for_key default value is invalid when used in cnf
Submitted:	14 Nov 2014 18:45	Modified:	15 Nov 2014 5:47
Reporter:	John Cesario	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.5.40, 5.5.42, 5.6.23	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	cnf max_seeks_for_key

Description:
The default values for max_seeks_for_key on a 64-bit platform are invalid when specified in the cnf. 
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_seeks_for_k...

[vagrant@db1 mysql]$ arch
x86_64
[vagrant@db1 mysql]$ grep max_seeks /etc/my.cnf
max_seeks_for_key= 18446744073709551615
[vagrant@db1 mysql]$ sudo /etc/init.d/mysql restart
 ERROR! MySQL PID file could not be found!
Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/db1.pid).

[vagrant@db1 mysql]$ sudo grep ERROR /var/log/mysqld.log
141114 18:35:48 [ERROR] Incorrect integer value: '18446744073709551615'
141114 18:35:48 [ERROR] /usr/sbin/mysqld: Error while setting value '18446744073709551615' to 'max_seeks_for_key'
141114 18:35:48 [ERROR] Aborting

From the CLI this works.

How to repeat:
Set max_seeks_for_key to 64-bit default value (18446744073709551615)  in [mysqld] group in cnf

Suggested fix:
Should not error out on start when using default value.

Hello John Cesario,

Thank you for the report.

Thanks,
Umesh

// Attempts to set it on the command line(or in through conf file) end up with Aborting

[root@cluster-repo mysql-5.5.42]# /data/ushastry/server/mysql-5.5.42/bin/mysqld --basedir=/data/ushastry/server/mysql-5.5.42 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=18446744073709551615 --user=root 2>&1 &
[1] 25191
[root@cluster-repo mysql-5.5.42]# 141117 12:31:30 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.
141117 12:31:30 [ERROR] Incorrect integer value: '18446744073709551615'
141117 12:31:30 [Warning] option 'max_seeks_for_key': unsigned value 0 adjusted to 1
141117 12:31:30 [ERROR] /data/ushastry/server/mysql-5.5.42/bin/mysqld: Error while setting value '18446744073709551615' to 'max_seeks_for_key'
141117 12:31:30 [ERROR] Aborting

[1]+  Exit 1                  /data/ushastry/server/mysql-5.5.42/bin/mysqld --basedir=/data/ushastry/server/mysql-5.5.42 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=18446744073709551615 --user=root 2>&1

// Allows to set dynamically using SET

[root@cluster-repo mysql-5.5.42]# /data/ushastry/server/mysql-5.5.42/bin/mysqld --basedir=/data/ushastry/server/mysql-5.5.42 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=184467440 --user=root 2>&1 &
[1] 25226
[root@cluster-repo mysql-5.5.42]# 141117 12:35:30 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.

[root@cluster-repo mysql-5.5.42]# bin/mysql -u root -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.42                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.42                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> SELECT @@global.max_seeks_for_key;
+----------------------------+
| @@global.max_seeks_for_key |
+----------------------------+
|                  184467440 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SET @@global.max_seeks_for_key = 18446744073709551615;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.max_seeks_for_key;
+----------------------------+
| @@global.max_seeks_for_key |
+----------------------------+
|       18446744073709551615 |
+----------------------------+
1 row in set (0.00 sec)

mysql>

// 5.6.23

[root@cluster-repo mysql-advanced-5.6.23]# /data/ushastry/server/mysql-advanced-5.6.23/bin/mysqld --basedir=/data/ushastry/server/mysql-advanced-5.6.23 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=18446744073709551615 --user=root 2>&1 &
[1] 25329
[root@cluster-repo mysql-advanced-5.6.23]# 2014-11-17 12:40:36 0 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.
2014-11-17 12:40:36 0 [ERROR] Incorrect integer value: '18446744073709551615'
2014-11-17 12:40:36 0 [Warning] option 'max_seeks_for_key': unsigned value 0 adjusted to 1
2014-11-17 12:40:36 0 [ERROR] /data/ushastry/server/mysql-advanced-5.6.23/bin/mysqld: Error while setting value '18446744073709551615' to 'max_seeks_for_key'
2014-11-17 12:40:36 0 [ERROR] Aborting

2014-11-17 12:40:36 0 [Note] Binlog end

[1]+  Exit 1                  /data/ushastry/server/mysql-advanced-5.6.23/bin/mysqld --basedir=/data/ushastry/server/mysql-advanced-5.6.23 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=18446744073709551615 --user=root 2>&1

[root@cluster-repo mysql-advanced-5.6.23]# /data/ushastry/server/mysql-advanced-5.6.23/bin/mysqld --basedir=/data/ushastry/server/mysql-advanced-5.6.23 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --max_seeks_for_key=184467440737095 --user=root 2>&1 &
[1] 25332
[root@cluster-repo mysql-advanced-5.6.23]# 2014-11-17 12:41:07 0 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.
2014-11-17 12:41:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

[root@cluster-repo mysql-advanced-5.6.23]# bin/mysql -u root -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.23-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> SELECT @@global.max_seeks_for_key;
+----------------------------+
| @@global.max_seeks_for_key |
+----------------------------+
|            184467440737095 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SET @@global.max_seeks_for_key = 18446744073709551615;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.max_seeks_for_key;
+----------------------------+
| @@global.max_seeks_for_key |
+----------------------------+
|       18446744073709551615 |
+----------------------------+
1 row in set (0.00 sec)