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:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5.x OS:Any
Assigned to: Alfranio Junior CPU Architecture:Any
Tags: max_binlog_cache_size

[19 Jul 2010 19:01] Sven Sandberg
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
}
[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 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 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 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!)