[#MDEV-4774] Strangeness with max_binlog_stmt_cache

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

I also see the behavior in MySQL 5.5.32, so perhaps the fix stems from there, but I wanted to file it in both places. Here is the MySQL bug report: http://bugs.mysql.com/bug.php?id=69704

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.

set @@global.max_binlog_stmt_cache_size=18446744073709547520;

select @@global.max_binlog_stmt_cache_size;

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.

[MDEV-4774] Strangeness with max_binlog_stmt_cache_size Settings Created: 2013-07-10 Updated: 2021-12-18 Resolved: 2017-01-22
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	10.0.3, 5.5.31, 5.5, 10.0
Fix Version/s:	10.0.30, 10.1.22

[MDEV-4774] Strangeness with max_binlog_stmt_cache_size Settings Created: 2013-07-10 Updated: 2021-12-18 Resolved: 2017-01-22