| Bug #55377 | max_binlog_cache_size does not work as specified | ||
|---|---|---|---|
| Submitted: | 19 Jul 2010 19:01 | Modified: | 16 Nov 2010 15:50 |
| Reporter: | Sven Sandberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.5.x | OS: | Any |
| Assigned to: | Alfranio Tavares Correia Junior | CPU Architecture: | Any |
| Tags: | max_binlog_cache_size | ||
[19 Jul 2010 21:46]
Sveta Smirnova
Thank you for the report.
I get correct error:
mysqltest: At line 23: query 'INSERT INTO t1 VALUES ('8_bytes!123456789012345012345612341234123412345678901234501234561234123412341232345678-$extra_char');' failed: 1197: Multi-statement transaction requi
red more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
Which tree do you use? How do you compile MySQL server?
[20 Jul 2010 8:51]
Sven Sandberg
The bug is that the *first* transaction in the binlog is 32768 bytes long, and it does not produce the error. I added the second transaction just to show that the limit is exactly 32768. So you need to run mysqlbinlog on master-bin.000001, and verify that the position of the first COMMIT is 32768 bigger than the position of the first INSERT. Sorry for the incomplete instructions.
[20 Jul 2010 9:46]
Sveta Smirnova
Thank you for the feedback. Verified as described.
[20 Jul 2010 9:52]
Alfranio Tavares Correia Junior
BINLOG_CACHE_SIZE represents the part of the IO_CACHE that is kept in memory to improve performance and allegedly MAX_BINLOG_CACHE_SIZE is the total size of the IO_CACHE taking into account both memory and what is on disk. However MAX_BINLOG_CACHE_SIZE is being set to 4096 and BINLOG_CACHE_SIZE is 32768 (default value). So, the IO_CACHE considers that the size of the file cannot exceed: . max(BINLOG_CACHE_SIZE, MAX_BINLOG_CACHE_SIZE) In my opinion there is nothing wrong with this behavior. Maybe we just need to update/improve the documentation.
[4 Aug 2010 19:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/115046 3162 Alfranio Correia 2010-08-04 BUG#55377 max_binlog_cache_size does not work as specified The binary log is created with a maximum size defined by max (max_binlog_cache_size, binlog_cache_size) and not by the max_binlog_cache_size as one expects. To fix this behavior, we issue a warning message when the binlog_cache_size is greater than the max_binlog_cache_size and set binlog_cache_size to max_binlog_cache_size. @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test Suppressed the warning message that is issued when the binlog_cache_size is greater than the max_binlog_cache_size. Besides, the binlog_cache_size's value is restored as it is changed when the binary log is openned. @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/t/rpl_mixed_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_row_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_stm_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ sql/log.cc Core of the patch.
[10 Aug 2010 14:00]
Sven Sandberg
OK, Alfranio has convinced me that there is a possible use case for max_binlog_cache_size. It is conceivable that someone implements a slave server that applies events as they arrive, without first writing them to a relay log. In that case, if the master sets max_binlog_cache_size, it is a useful limitation on the resource usage by the slave's replication thread. So I'm fine with keeping the variable.
[10 Aug 2010 14:06]
Sven Sandberg
To documentation team: the manual is currently a bit confusing for max_binlog_cache_size and binlog_cache_size. max_binlog_cache_size is a limit on the size of a transaction, measured in number of bytes used by the binary log. It is not a limit on memory (the transaction may be stored on disk). binlog_cache_size is a limit on the amount of memory used to cache a transaction. (Clarify that it only affects memory usage, not disk).
[28 Aug 2010 11:10]
Andrei Elkin
More about `binlog_cache_size' can be found in docs (6.2.4 The Binary): When a thread that handles the transaction starts, it allocates a buffer of `binlog_cache_size' to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends. The paragraph words on that a transaction is associated with an IO_CACHE whose memory buffer is size of `binlog_cache_size'.
[28 Aug 2010 11:35]
Andrei Elkin
There seems to be an agreement in that
max_binlog_cache_size
is the limit for the total size of IO_CACHE of the transaction.
Therefore enforcing
max_binlog_cache_size >= binlog_cache_size
in server's bootstrap and in the global var update method:s
looks to be the right solution.
[2 Sep 2010 19:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117452 3257 Alfranio Correia 2010-09-02 BUG#55377 max_binlog_cache_size does not work as specified The binary log is created with a maximum size defined by max (max_binlog_cache_size, binlog_cache_size) and not by the max_binlog_cache_size as one expects. To fix this behavior, we issue a warning message when the binlog_cache_size is greater than the max_binlog_cache_size and set binlog_cache_size to max_binlog_cache_size. @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test Suppressed the warning message that is issued when the binlog_cache_size is greater than the max_binlog_cache_size. Besides, the binlog_cache_size's value is restored as it is changed when the binary log is openned. @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/t/rpl_mixed_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_row_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_stm_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size.
[3 Sep 2010 13:05]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117514 3257 Alfranio Correia 2010-09-03 BUG#55377 max_binlog_cache_size does not work as specified The binary log is created with a maximum size defined by max (max_binlog_cache_size, binlog_cache_size) and not by the max_binlog_cache_size as one expects. To fix this behavior, we issue a warning message when the binlog_cache_size is greater than the max_binlog_cache_size and set binlog_cache_size to max_binlog_cache_size. @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test Suppressed the warning message that is issued when the binlog_cache_size is greater than the max_binlog_cache_size. Besides, the binlog_cache_size's value is restored as it is changed when the binary log is openned. @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/t/rpl_mixed_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_row_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_stm_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ sql/binlog.cc Added the body of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/binlog.h Added the definition of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/mysqld.cc Checked if binlog_cache_size <= max_binlog_cache_size during startup. @ sql/sys_vars.cc Checked if binlog_cache_size <= max_binlog_cache_size after calling "SET".
[3 Sep 2010 17:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117534 3257 Alfranio Correia 2010-09-03 BUG#55377 max_binlog_cache_size does not work as specified The binary log is created with a maximum size defined by max (max_binlog_cache_size, binlog_cache_size) and not by the max_binlog_cache_size as one expects. To fix this behavior, we issue a warning message when the binlog_cache_size is greater than the max_binlog_cache_size and set binlog_cache_size to max_binlog_cache_size. @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test Suppressed the warning message that is issued when the binlog_cache_size is greater than the max_binlog_cache_size. Besides, the binlog_cache_size's value is restored as it is changed when the binary log is openned. @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/t/rpl_mixed_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_row_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_stm_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ sql/binlog.cc Added the body of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/binlog.h Added the definition of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/mysqld.cc Checked if binlog_cache_size <= max_binlog_cache_size during startup. @ sql/sys_vars.cc Checked if binlog_cache_size <= max_binlog_cache_size after calling "SET".
[6 Sep 2010 16:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117628 3258 Alfranio Correia 2010-09-06 BUG#55377 max_binlog_cache_size does not work as specified The binary log is created with a maximum size defined by max (max_binlog_cache_size, binlog_cache_size) and not by the max_binlog_cache_size as one expects. To fix this behavior, we issue a warning message when the binlog_cache_size is greater than the max_binlog_cache_size and set binlog_cache_size to max_binlog_cache_size. @ mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test Suppressed the warning message that is issued when the binlog_cache_size is greater than the max_binlog_cache_size. Besides, the binlog_cache_size's value is restored as it is changed when the binary log is openned. @ mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result Updated the result file. @ mysql-test/suite/rpl/t/rpl_mixed_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_row_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ mysql-test/suite/rpl/t/rpl_stm_binlog_max_cache_size-master.opt Set the binlog_cache_size's value to be greater than the max_binlog_cache_size. @ sql/binlog.cc Added the body of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/binlog.h Added the definition of function to check if binlog_cache_size <= max_binlog_cache_size. @ sql/mysqld.cc Checked if binlog_cache_size <= max_binlog_cache_size during startup. @ sql/share/errmsg-utf8.txt Created a new error message. @ sql/sys_vars.cc Checked if binlog_cache_size <= max_binlog_cache_size after calling "SET".
[17 Sep 2010 11:36]
Alfranio Tavares Correia Junior
Patch queued to mysql-next-mr-bugfixing.
[20 Sep 2010 12:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/118582 3294 Alfranio Correia 2010-09-20 BUG#55377 max_binlog_cache_size does not work as specified
[20 Sep 2010 12:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/118583 3294 Alfranio Correia 2010-09-20 BUG#55377 max_binlog_cache_size does not work as specified Post-push fixes.
[2 Oct 2010 18:15]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[13 Nov 2010 16:07]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[15 Nov 2010 13:24]
Jon Stephens
Will this fix go into 5.1? (Looks like it won't, just want to be sure). Thanks!
[16 Nov 2010 15:18]
Alfranio Tavares Correia Junior
The patch is for 5.5.x and will not go into 5.1.
[16 Nov 2010 15:50]
Jon Stephens
No additional changelog entries needed; closing. (Thanks, Sven!)
Description: When setting max_binlog_cache_size=4096, it allows the total size of all binlog events for a transaction to be 32768 bytes, plus the events for BEGIN and COMMIT. The size should be limited to 4096 bytes. In fact, it seems that the code tries to limit the size of transactions by setting IO_CACHE::end_of_file= max_binlog_cache_size (in binlog_trx_data::binlog_trx_data() in log.cc). However, it doesn't seem that end_of_file is intended to be a limit on the size of the IO_CACHE. I'm not going to try to decipher the code in mf_iocache.c ;-) but end_of_file is increased in _my_b_seq_read() and in my_b_append(). If end_of_file was a limit on the file size, I don't think it should not increase when reading or appending to the file. How to repeat: --source include/have_binlog_format_statement.inc --source include/have_innodb.inc CREATE TABLE t1 (a VARCHAR(100)) ENGINE = INNODB; SET GLOBAL MAX_BINLOG_CACHE_SIZE = 4096; --let $extra_char= --let $m= 2 while ($m) { --let $n= 336 BEGIN; --disable_query_log while ($n) { INSERT INTO t1 VALUES ('8 bytes!'); --dec $n } --enable_query_log --eval INSERT INTO t1 VALUES ('8_bytes!123456789012345012345612341234123412345678901234501234561234123412341232345678-$extra_char'); COMMIT; --let $extra_char= 1 --echo # First transaction succeeded! --echo # Now trying with a transaction that is one byte longer. --dec $m }