Bug #74728 binlog-row-event-max-size value is not shown in SHOW VARIABLES
Submitted: 7 Nov 2014 10:04 Modified: 29 Jan 15:16
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:mysql 5.6.20, 5.6.22, 5.7.6, 5.5.41 OS:Any
Assigned to: CPU Architecture:Any

[7 Nov 2014 10:04] Hartmut Holzgraefe
Description:
The value of this option can be seen in "mysqld --help --verbose" output (8192 by default), but not in SHOW VARIABLES output ...

How to repeat:
Check SHOW VARIABLES LIKE 'binlog%' output
[7 Nov 2014 10:13] Miguel Solorzano
C:\dbs>5.6\bin\mysqld --help --verbose | findstr binlog-row-event
2014-11-07 08:10:47 6604 [Note] Plugin 'FEDERATED' is disabled.
  --binlog-row-event-max-size=#
binlog-row-event-max-size                                  8192
2014-11-07 08:10:47 6604 [Note] Binlog end
2014-11-07 08:10:47 6604 [Note] Shutting down plugin 'MyISAM'
2014-11-07 08:10:47 6604 [Note] Shutting down plugin 'CSV'

C:\dbs>5.7\bin\mysqld --help --verbose | findstr binlog-row-event
  --binlog-row-event-max-size=#
binlog-row-event-max-size                                    8192

C:\dbs>net start mysqld57
The MySQLD57 service is starting.
The MySQLD57 service was started successfully.

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution

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 5.7 > SHOW VARIABLES LIKE 'binlog%'
    -> ;
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | IGNORE_ERROR |
| binlog_format                           | STATEMENT    |
| binlog_group_commit_sync_delay          | 0            |
| binlog_group_commit_sync_no_delay_count | 0            |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | FULL         |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
+-----------------------------------------+--------------+
12 rows in set (0.03 sec)

Not found on Documentation too.
[7 Nov 2014 10:27] Umesh Shastry
Hello Hartmut,

Thank you for the bug report.
Also, this seems to be set only at the Command-Line - per http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#option_mysqld_b...

Thanks,
Umesh
[7 Nov 2014 10:28] Umesh Shastry
// 5.6.22

[ushastry@cluster-repo mysql-advanced-5.6.22]$ bin/mysqld --help --verbose|grep binlog-row-event-max-size

  --binlog-row-event-max-size=#
binlog-row-event-max-size                                  8192

mysql> SHOW VARIABLES LIKE 'binlog%';
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | IGNORE_ERROR |
| binlog_format                           | STATEMENT    |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | FULL         |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
| binlogging_impossible_mode              | IGNORE_ERROR |
+-----------------------------------------+--------------+
11 rows in set (0.00 sec)

// 5.7.6

[root@cluster-repo mysql-advanced-5.7.6]# bin/mysqld --help --verbose|grep binlog-row-event-max-size
  --binlog-row-event-max-size=#
binlog-row-event-max-size                                    8192

mysql> SHOW VARIABLES LIKE 'binlog%';
+-----------------------------------------+--------------+
| Variable_name                           | Value        |
+-----------------------------------------+--------------+
| binlog_cache_size                       | 32768        |
| binlog_checksum                         | CRC32        |
| binlog_direct_non_transactional_updates | OFF          |
| binlog_error_action                     | IGNORE_ERROR |
| binlog_format                           | STATEMENT    |
| binlog_group_commit_sync_delay          | 0            |
| binlog_group_commit_sync_no_delay_count | 0            |
| binlog_max_flush_queue_time             | 0            |
| binlog_order_commits                    | ON           |
| binlog_row_image                        | FULL         |
| binlog_rows_query_log_events            | OFF          |
| binlog_stmt_cache_size                  | 32768        |
+-----------------------------------------+--------------+
12 rows in set (0.00 sec)

// 5.5.41

[root@cluster-repo mysql-5.5.41]# bin/mysqld --help --verbose|grep binlog-row-event-max-size                                                                                                      141109 17:29:00 [ERROR] Can't find messagefile '/usr/local/mysql/share/errmsg.sys'
  --binlog-row-event-max-size=#
binlog-row-event-max-size                         1024

mysql> SHOW VARIABLES LIKE 'binlog%';
+-----------------------------------------+-----------+
| Variable_name                           | Value     |
+-----------------------------------------+-----------+
| binlog_cache_size                       | 32768     |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+
4 rows in set (0.01 sec)
[7 Nov 2014 11:20] Hartmut Holzgraefe
It is also settable via my.cnf ... or at least doesn't raise an error

I can't really *verify* what value it is set to though, as it isn't part of SHOW output ...

I don't mind that much whether it is settable at runtime via SET or not, I just want to be able to verify the current setting (esp. in combination with replication changes regarding to >1GB binlog packets as listed in changesets on http://bugs.mysql.com/49932 )
[7 Nov 2014 11:33] Umesh Shastry
I completely agree, should have visibility.
[7 Nov 2014 14:48] Peter Laursen
Well .. there is some consistency.

http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html lists it as an *option* but not a *variable*. As long as this is the case SHOW VARIALES will not show it. 

This does not exclude that it would be useful to have it exposed as a variable as well. I requested the same for "character-set-client-handshake" what is also only an option and not a variable currently.

-- Peter
-- not a MySQL/Oracle person.
[26 Oct 2017 5:39] Shane Bester
Given the number of slave lag SR's we get,  knowning this variable's value is fairly important imho.

mysql> select @@global.binlog_row_event_max_size;
ERROR 1193 (HY000): Unknown system variable 'binlog_row_event_max_size'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0.00 sec)
[23 Jan 17:11] Shane Bester
looks fixed in 8.0.14.
mysql> select @@global.binlog_row_event_max_size;
+------------------------------------+
| @@global.binlog_row_event_max_size |
+------------------------------------+
|                               8192 |
+------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%row_event_max%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| binlog_row_event_max_size | 8192  |
+---------------------------+-------+
1 row in set (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.14    |
+-----------+
1 row in set (0.00 sec)
[29 Jan 15:16] Luis Soares
This was fixed in MySQL 8.0.14, in: https://dev.mysql.com/worklog/task/?id=12385 .
[29 Jan 17:12] Paul Dubois
Posted by developer:
 
Fixed in 8.0.14.

The startup option --binlog-row-event-max-size now has a
corresponding system variable binlog_row_event_max_size. The startup
option and system variable set a soft limit on the maximum size of a
row-based binary log event, with a default setting of 8192 bytes.
Where possible, rows stored in the binary log are grouped into events
with a size not exceeding the value of this setting. If an event
cannot be split, the maximum size can be exceeded.

The binlog_row_event_max_size global system variable is read-only and
can be set only at server startup. Its value can therefore only be
modified by using the PERSIST_ONLY keyword or the @@persist_only
qualifier with the SET statement. The addition of a system variable
means that this setting can be viewed using the Performance Schema
tables or a SHOW VARIABLES or SELECT statement.