| Bug #74728 | binlog-row-event-max-size value is not shown in SHOW VARIABLES | ||
|---|---|---|---|
| Submitted: | 7 Nov 2014 10:04 | Modified: | 29 Jan 2019 15:16 |
| Reporter: | Hartmut Holzgraefe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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
[7 Nov 2014 10:13]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
// 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]
MySQL Verification Team
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]
MySQL Verification Team
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 2019 17:11]
MySQL Verification Team
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 2019 15:16]
Luis Soares
This was fixed in MySQL 8.0.14, in: https://dev.mysql.com/worklog/task/?id=12385 .
[29 Jan 2019 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.
