Bug #69704 Strangeness with max_binlog_stmt_cache_size Settings

• Submitted: 9 Jul 2013 23:26
• Modified: 10 Jul 2013 5:34
• Reporter: Chris Calender
• Status: Verified
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: 5.5.32, 5.6.12
• OS: Any
• CPU Architecture: Any
• Tags: max_binlog_stmt_cache_size

[9 Jul 2013 23:26] Chris Calender

Description:
I'm seeing some strangeness when setting various values for max_binlog_stmt_cache_size in the config file.

1.  If I do not set max_binlog_stmt_cache_size, it defaults to: 18446744073709547520

This is normal and expected:

http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_max_binl...

2. However, if I try to set this in the config file, mysqld fails to start (seeing this on Windows and Linux, 5.5.30 and 5.5.30).

[mysqld]
max_binlog_stmt_cache_size = 18446744073709547520

Start mysqld.  It fails.  On Windows, there is no entry to the error log, at least not when run as a service.  On Linux, seeing an error like:

130704 12:00:00 mysqld_safe Starting mysqld_wrapper daemon with databases from /mysql/data
130704 12:00:00 [ERROR] Incorrect integer value: '18446744073709547520'
130704 12:00:00 [Warning] option 'max_binlog_stmt_cache_size': unsigned value 0 adjusted to 4096
130704 12:00:00 [ERROR] /usr/sbin/mysqld: Error while setting value '18446744073709547520' to 'max_binlog_stmt_cache_size'
130704 12:00:00 [ERROR] Aborting

3. If I set it dynamically to '18446744073709547520', it works as expected:

mysql> set @@global.max_binlog_stmt_cache_size=18446744073709547520;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.max_binlog_stmt_cache_size;
+-------------------------------------+
| @@global.max_binlog_stmt_cache_size |
+-------------------------------------+
|                18446744073709547520 |
+-------------------------------------+
1 row in set (0.00 sec)

4. However, if I set it dynamically to '18446744073709547519', 1 less than the above value, it reports a "warning", and truncates the value somewhat, which is strange since it doesn't do this when setting it to 1 value higher.

mysql> set @@global.max_binlog_stmt_cache_size=18446744073709547519;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect max_binlog_stmt_cache_size value: '18446744073709547519' |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@global.max_binlog_stmt_cache_size;
+-------------------------------------+
| @@global.max_binlog_stmt_cache_size |
+-------------------------------------+
|                18446744073709543424 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.32-log |
+------------+
1 row in set (0.07 sec)

How to repeat:
1. select @@global.max_binlog_stmt_cache_size;

2. Set the following:

[mysqld]
max_binlog_stmt_cache_size = 18446744073709547520

And start mysqld.

3. set @@global.max_binlog_stmt_cache_size=18446744073709547520;
select @@global.max_binlog_stmt_cache_size;

4. set @@global.max_binlog_stmt_cache_size=18446744073709547519;
show warnings;
select @@global.max_binlog_stmt_cache_size;

Suggested fix:
There are several issues here, so I see the fixes as being:

1. Fix so it reads the max_binlog_stmt_cache_size from the config file correctly.  

2. Also, it should be consistent, regardless of what it is set to (i.e., should not be truncated when set to less than the default).

3. Also, the server should not fail to start.

4. Lastly, Windows should log an error of some sort.

[10 Jul 2013 5:34] MySQL Verification Team

Hello Chris,

Thank you for the bug report. 
Verified as described on 5.5.32/5.6.12.

// 5.5.32

130702 18:36:06 [Note] /home/ushastry/mybuilds/mysql-5.5.32/bin/mysqld: Shutdown complete

130702 18:36:06 mysqld_safe mysqld from pid file /tmp/5532/ushastry.pid ended
130710 11:02:10 mysqld_safe Starting mysqld daemon with databases from /tmp/5532
130710 11:02:10 [ERROR] Incorrect integer value: '18446744073709547520'
130710 11:02:10 [Warning] option 'max_binlog_stmt_cache_size': unsigned value 0 adjusted to 4096
130710 11:02:10 [ERROR] /home/ushastry/mybuilds/mysql-5.5.32/bin/mysqld: Error while setting value '18446744073709547520' to 'max_binlog_stmt_cache_size'
130710 11:02:10 [ERROR] Aborting

130710 11:02:10 mysqld_safe mysqld from pid file /tmp/5532/ushastry.pid ended

// 5.6.12

2013-07-09 10:01:05 13020 [Note] /home/ushastry/mybuilds/mysql-5.6.12-release/bin/mysqld: ready for connections.
Version: '5.6.12-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
130710 10:58:23 mysqld_safe Starting mysqld daemon with databases from /tmp/5612
2013-07-10 10:58:23 0 [ERROR] Incorrect integer value: '18446744073709547520'
2013-07-10 10:58:23 0 [Warning] option 'max_binlog_stmt_cache_size': unsigned value 0 adjusted to 4096
2013-07-10 10:58:23 0 [ERROR] /home/ushastry/mybuilds/mysql-5.6.12-release/bin/mysqld: Error while setting value '18446744073709547520' to 'max_binlog_stmt_cache_size'
2013-07-10 10:58:23 0 [ERROR] Aborting

2013-07-10 10:58:23 0 [Note] Binlog end
130710 10:58:23 mysqld_safe mysqld from pid file /tmp/5612/ushastry.pid ended