diff options
| author | Alfranio Correia <alfranio.correia@oracle.com> | 2010-11-05 17:42:37 +0000 |
|---|---|---|
| committer | Alfranio Correia <alfranio.correia@oracle.com> | 2010-11-05 17:42:37 +0000 |
| commit | bf2c66d4a7281c1d7dbf49cbad315c7041762670 (patch) | |
| tree | 9b04bc2cae8d25cb3b3334712ad98c4006f16431 | |
| parent | babe727bb4158113f63f1ad3f6d4384230c30492 (diff) | |
| download | mariadb-git-bf2c66d4a7281c1d7dbf49cbad315c7041762670.tar.gz | |
BUG#57275 binlog_cache_size affects trx- and stmt-cache and gets twice the expected memory
After the WL#2687, the binlog_cache_size and max_binlog_cache_size affect both the
stmt-cache and the trx-cache. This means that the resource used is twice the amount
expected/defined by the user.
The binlog_cache_use is incremented when the stmt-cache or the trx-cache is used
and binlog_cache_disk_use is incremented when the disk space from the stmt-cache or the
trx-cache is used. This behavior does not allow to distinguish which cache may be harming
performance due to the extra disk accesses and needs to have its in-memory cache
increased.
To fix the problem, we introduced two new options and status variables related to the
stmt-cache:
Options:
. binlog_stmt_cache_size
. max_binlog_stmt_cache_size
Status Variables:
. binlog_stmt_cache_use
. binlog_stmt_cache_disk_use
So there are
. binlog_cache_size that defines the size of the transactional cache for
updates to transactional engines for the binary log.
. binlog_stmt_cache_size that defines the size of the statement cache for
updates to non-transactional engines for the binary log.
. max_binlog_cache_size that sets the total size of the transactional
cache.
. max_binlog_stmt_cache_size that sets the total size of the statement
cache.
. binlog_cache_use that identifies the number of transactions that used the
temporary transactional binary log cache.
. binlog_cache_disk_use that identifies the number of transactions that used
the temporary transactional binary log cache but that exceeded the value of
binlog_cache_size.
. binlog_stmt_cache_use that identifies the number of statements that used the
temporary non-transactional binary log cache.
. binlog_stmt_cache_disk_use that identifies the number of statements that used
the temporary non-transactional binary log cache but that exceeded the value of
binlog_stmt_cache_size.
24 files changed, 1108 insertions, 164 deletions
diff --git a/include/my_sys.h b/include/my_sys.h index 23c9b2da55f..c50f014b88c 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -456,7 +456,8 @@ typedef struct st_io_cache /* Used when cacheing files */ IO_CACHE_CALLBACK pre_close; /* Counts the number of times, when we were forced to use disk. We use it to - increase the binlog_cache_disk_use status variable. + increase the binlog_cache_disk_use and binlog_stmt_cache_disk_use status + variables. */ ulong disk_writes; void* arg; /* for use by pre/post_read */ diff --git a/mysql-test/extra/binlog_tests/binlog_cache_stat.test b/mysql-test/extra/binlog_tests/binlog_cache_stat.test index f82bc9c40b4..a602b098201 100644 --- a/mysql-test/extra/binlog_tests/binlog_cache_stat.test +++ b/mysql-test/extra/binlog_tests/binlog_cache_stat.test @@ -18,23 +18,28 @@ create table t2 (a int) engine=myisam; # # Checking commit. # ---echo **** Preparing the enviroment to check commit and its effect on ---echo **** the binlog_cache_use and binlog_cache_disk_use. +--echo **** Preparing the enviroment to check commit and its effect on status variables. --echo **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; let $exp_cache= 0; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 0; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } ---echo **** Now we are going to create transactional changes which are long enough so ---echo **** they will be flushed to disk... +--echo **** Transactional changes which are long enough so they will be flushed to disk... --echo **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. let $1=2000; disable_query_log; begin; @@ -49,15 +54,21 @@ let $exp_cache= 1; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Transactional changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. --echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); commit; @@ -65,65 +76,87 @@ let $exp_cache= 2; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Non-Transactional changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. ---echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); commit; -let $exp_cache= 3; +let $exp_cache= 2; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 1; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Mixed changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. ---echo **** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); commit; -let $exp_cache= 5; +let $exp_cache= 3; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 2; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } # # Checking abort. # ---echo **** Preparing the enviroment to check abort and its effect on ---echo **** the binlog_cache_use and binlog_cache_disk_use +--echo **** Preparing the enviroment to check abort and its effect on the status variables. --echo **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; let $exp_cache= 0; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 0; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } ---echo **** Now we are going to create transactional changes which are long enough so ---echo **** they will be flushed to disk... +--echo **** Transactional changes which are long enough so they will be flushed to disk... --echo **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. let $1=2000; disable_query_log; begin; @@ -138,15 +171,21 @@ let $exp_cache= 1; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Transactional changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. --echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); rollback; @@ -154,42 +193,59 @@ let $exp_cache= 2; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 0; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Non-Transactional changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. ---echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); rollback; -let $exp_cache= 3; +let $exp_cache= 2; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 1; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } --echo **** Mixed changes which should not be flushed to disk and so should not ---echo **** increase binlog_cache_disk_use. ---echo **** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +--echo **** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +--echo **** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +--echo **** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); rollback; -let $exp_cache= 5; +let $exp_cache= 3; let $got_cache= query_get_value(show status like "binlog_cache_use", Value, 1); let $exp_disk= 1; let $got_disk= query_get_value(show status like "binlog_cache_disk_use", Value, 1); -if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk`) +let $exp_stmt_cache= 2; +let $got_stmt_cache= query_get_value(show status like "binlog_stmt_cache_use", Value, 1); +let $exp_stmt_disk= 0; +let $got_stmt_disk= query_get_value(show status like "binlog_stmt_cache_disk_use", Value, 1); +if (`SELECT $got_cache <> $exp_cache || $got_disk <> $exp_disk || $got_stmt_cache <> $exp_stmt_cache || $got_stmt_disk <> $exp_stmt_disk`) { - -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk but" + -- echo "Expected: binlog_cache_use = $exp_cache, binlog_cache_disk_use = $exp_disk but got binlog_cache_use = $got_cache, binlog_cache_disk_use = $got_disk" + -- echo "Expected: binlog_stmt_cache_use = $exp_stmt_cache, binlog_stmt_cache_disk_use = $exp_stmt_disk but got binlog_stmt_cache_use = $got_stmt_cache, binlog_stmt_cache_disk_use = $got_stmt_disk" -- die } drop table t1, t2; diff --git a/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test b/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test index 3d97ad10d17..385a82baa77 100644 --- a/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test +++ b/mysql-test/extra/rpl_tests/rpl_binlog_max_cache_size.test @@ -25,11 +25,13 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state let $old_max_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_cache_size", Value, 1); let $old_binlog_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_cache_size", Value, 1); +let $old_max_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "max_binlog_stmt_cache_size", Value, 1); +let $old_binlog_stmt_cache_size= query_get_value(SHOW VARIABLES LIKE "binlog_stmt_cache_size", Value, 1); SET GLOBAL max_binlog_cache_size = 4096; -# Becuase of bug#55377, we have to set binlog_cache_size until the bug is -# fixed. SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; disconnect master; connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); @@ -47,14 +49,14 @@ connection master; --echo *** Single statement on transactional table *** --disable_query_log ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE eval INSERT INTO t1 (a, data) VALUES (1, CONCAT($data, $data, $data, $data, $data)); --enable_query_log --echo *** Single statement on non-transactional table *** --disable_query_log ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE eval INSERT INTO t2 (a, data) VALUES (2, CONCAT($data, $data, $data, $data, $data, $data)); --enable_query_log @@ -74,7 +76,7 @@ eval INSERT INTO t2 (a, data) VALUES (5, $data); --echo *** Single statement on both transactional and non-transactional tables. *** --disable_query_log ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE eval UPDATE t2, t1 SET t2.data = CONCAT($data, $data, $data, $data), t1.data = CONCAT($data, $data, $data, $data); --enable_query_log @@ -103,11 +105,11 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (1, $data); --eval INSERT INTO t1 (a, data) VALUES (2, $data); --eval INSERT INTO t1 (a, data) VALUES (3, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (4, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (5, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (6, $data); --eval INSERT INTO t1 (a, data) VALUES (7, 's'); --eval INSERT INTO t2 (a, data) VALUES (8, 's'); @@ -130,9 +132,9 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (14, $data); --eval INSERT INTO t1 (a, data) VALUES (15, $data); --eval INSERT INTO t1 (a, data) VALUES (16, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (17, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (18, $data); --eval INSERT INTO t1 (a, data) VALUES (19, 's'); --eval INSERT INTO t2 (a, data) VALUES (20, 's'); @@ -148,7 +150,7 @@ if (`SELECT @@binlog_format = 'STATEMENT' || @@binlog_format = 'MIXED'`) if (`SELECT @@binlog_format = 'ROW'`) { --disable_query_log - --error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE + --error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE CREATE TABLE t4 SELECT * FROM t1; --enable_query_log } @@ -162,9 +164,9 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (22, $data); --eval INSERT INTO t1 (a, data) VALUES (23, $data); --eval INSERT INTO t1 (a, data) VALUES (24, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (25, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (26, $data); --eval INSERT INTO t1 (a, data) VALUES (27, 's'); --eval INSERT INTO t2 (a, data) VALUES (28, 's'); @@ -192,11 +194,11 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (1, $data); --eval INSERT INTO t1 (a, data) VALUES (2, $data); --eval INSERT INTO t1 (a, data) VALUES (3, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (4, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (5, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (6, $data); --eval INSERT INTO t1 (a, data) VALUES (7, 's'); --eval INSERT INTO t2 (a, data) VALUES (8, 's'); @@ -221,11 +223,11 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (1, $data); --eval INSERT INTO t1 (a, data) VALUES (2, $data); --eval INSERT INTO t1 (a, data) VALUES (3, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (4, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (5, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (6, $data); --eval INSERT INTO t1 (a, data) VALUES (7, 's'); --eval INSERT INTO t2 (a, data) VALUES (8, 's'); @@ -268,7 +270,7 @@ TRUNCATE TABLE t1; BEGIN; --disable_query_log ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE eval CALL p1($data); --enable_query_log COMMIT; @@ -277,7 +279,7 @@ TRUNCATE TABLE t1; BEGIN; --disable_query_log ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE eval CALL p1($data); --enable_query_log ROLLBACK; @@ -299,12 +301,12 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (1, $data); --eval INSERT INTO t1 (a, data) VALUES (2, $data); --eval INSERT INTO t1 (a, data) VALUES (3, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (4, $data); SAVEPOINT sv; ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (5, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (6, $data); --eval INSERT INTO t1 (a, data) VALUES (7, 's'); --eval INSERT INTO t2 (a, data) VALUES (8, 's'); @@ -331,11 +333,11 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (2, $data); --eval INSERT INTO t2 (a, data) VALUES (3, $data); --eval INSERT INTO t1 (a, data) VALUES (4, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (5, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (6, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (7, $data); --eval UPDATE t2 SET data= CONCAT($data, $data); --eval INSERT INTO t1 (a, data) VALUES (8, 's'); @@ -351,7 +353,7 @@ BEGIN; --eval INSERT INTO t1 (a, data) VALUES (16, $data); --eval INSERT INTO t2 (a, data) VALUES (17, $data); --eval INSERT INTO t1 (a, data) VALUES (18, $data); ---error ER_TRANS_CACHE_FULL, ER_ERROR_ON_WRITE +--error ER_TRANS_CACHE_FULL, ER_STMT_CACHE_FULL, ER_ERROR_ON_WRITE --eval INSERT INTO t1 (a, data) VALUES (19, $data); --enable_query_log COMMIT; @@ -367,10 +369,13 @@ let $diff_statement= SELECT * FROM t1; --echo # [ On Slave ] SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; source include/stop_slave.inc; source include/start_slave.inc; CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*"); +CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*"); CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*"); connection master; @@ -385,6 +390,10 @@ connection master; --eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size --replace_result $old_binlog_cache_size ORIGINAL_VALUE --eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size +--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE +--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size +--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE +--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size disconnect master; connect (master,127.0.0.1,root,,test,$MASTER_MYPORT,); @@ -415,6 +424,10 @@ source include/show_binlog_events.inc; --eval SET GLOBAL max_binlog_cache_size= $old_max_binlog_cache_size --replace_result $old_binlog_cache_size ORIGINAL_VALUE --eval SET GLOBAL binlog_cache_size= $old_binlog_cache_size +--replace_result $old_max_binlog_stmt_cache_size ORIGINAL_VALUE +--eval SET GLOBAL max_binlog_stmt_cache_size= $old_max_binlog_stmt_cache_size +--replace_result $old_binlog_stmt_cache_size ORIGINAL_VALUE +--eval SET GLOBAL binlog_stmt_cache_size= $old_binlog_stmt_cache_size source include/stop_slave.inc; source include/start_slave.inc; diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index 63e17e1c188..2d2a3cb919a 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -31,10 +31,10 @@ The following options may be given as the first argument: file (Solves most 'table full' errors) --bind-address=name IP address to bind to. --binlog-cache-size=# - The size of the cache to hold the SQL statements for the - binary log during a transaction. If you often use big, - multi-statement transactions you can increase this to get - more performance + The size of the transactional cache for updates to + transactional engines for the binary log. If you often + use transactions containing many statements, you can + increase this to get more performance --binlog-direct-non-transactional-updates Causes updates to non-transactional engines using statement format to be written directly to binary log. @@ -65,6 +65,11 @@ The following options may be given as the first argument: The maximum size of a row-based binary log event in bytes. Rows will be grouped into events smaller than this size if possible. The value has to be a multiple of 256. + --binlog-stmt-cache-size=# + The size of the statement cache for updates to + non-transactional engines for the binary log. If you + often use statements updating a great number of rows, you + can increase this to get more performance --bootstrap Used by mysql installation scripts. --bulk-insert-buffer-size=# Size of tree cache used in bulk insert optimisation. Note @@ -277,14 +282,15 @@ The following options may be given as the first argument: --max-allowed-packet=# Max packet length to send to or receive from the server --max-binlog-cache-size=# - Can be used to restrict the total size used to cache a - multi-transaction query + Sets the total size of the transactional cache --max-binlog-dump-events=# Option used by mysql-test for debugging and testing of replication. --max-binlog-size=# Binary log will be rotated automatically when the size exceeds this value. Will also apply to relay logs if max_relay_log_size is 0 + --max-binlog-stmt-cache-size=# + Sets the total size of the statement cache --max-connect-errors=# If there is more than this number of interrupted connections from a host this host will be blocked from @@ -733,6 +739,7 @@ binlog-cache-size 32768 binlog-direct-non-transactional-updates FALSE binlog-format STATEMENT binlog-row-event-max-size 1024 +binlog-stmt-cache-size 32768 bulk-insert-buffer-size 8388608 character-set-client-handshake TRUE character-set-filesystem binary @@ -812,6 +819,7 @@ max-allowed-packet 1048576 max-binlog-cache-size 18446744073709547520 max-binlog-dump-events 0 max-binlog-size 1073741824 +max-binlog-stmt-cache-size 18446744073709547520 max-connect-errors 10 max-connections 151 max-delayed-threads 20 diff --git a/mysql-test/suite/binlog/r/binlog_mixed_cache_stat.result b/mysql-test/suite/binlog/r/binlog_mixed_cache_stat.result index e9c344e4f7d..09cdab8414a 100644 --- a/mysql-test/suite/binlog/r/binlog_mixed_cache_stat.result +++ b/mysql-test/suite/binlog/r/binlog_mixed_cache_stat.result @@ -1,56 +1,62 @@ drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; -**** Preparing the enviroment to check commit and its effect on -**** the binlog_cache_use and binlog_cache_disk_use. +**** Preparing the enviroment to check commit and its effect on status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); commit; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); commit; **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); commit; -**** Preparing the enviroment to check abort and its effect on -**** the binlog_cache_use and binlog_cache_disk_use +**** Preparing the enviroment to check abort and its effect on the status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); rollback; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); diff --git a/mysql-test/suite/binlog/r/binlog_row_cache_stat.result b/mysql-test/suite/binlog/r/binlog_row_cache_stat.result index e9c344e4f7d..09cdab8414a 100644 --- a/mysql-test/suite/binlog/r/binlog_row_cache_stat.result +++ b/mysql-test/suite/binlog/r/binlog_row_cache_stat.result @@ -1,56 +1,62 @@ drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; -**** Preparing the enviroment to check commit and its effect on -**** the binlog_cache_use and binlog_cache_disk_use. +**** Preparing the enviroment to check commit and its effect on status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); commit; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); commit; **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); commit; -**** Preparing the enviroment to check abort and its effect on -**** the binlog_cache_use and binlog_cache_disk_use +**** Preparing the enviroment to check abort and its effect on the status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); rollback; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); diff --git a/mysql-test/suite/binlog/r/binlog_stm_cache_stat.result b/mysql-test/suite/binlog/r/binlog_stm_cache_stat.result index e9c344e4f7d..09cdab8414a 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_cache_stat.result +++ b/mysql-test/suite/binlog/r/binlog_stm_cache_stat.result @@ -1,56 +1,62 @@ drop table if exists t1, t2; create table t1 (a int) engine=innodb; create table t2 (a int) engine=myisam; -**** Preparing the enviroment to check commit and its effect on -**** the binlog_cache_use and binlog_cache_disk_use. +**** Preparing the enviroment to check commit and its effect on status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); commit; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); commit; **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); commit; -**** Preparing the enviroment to check abort and its effect on -**** the binlog_cache_use and binlog_cache_disk_use +**** Preparing the enviroment to check abort and its effect on the status variables. **** Expected: binlog_cache_use = 0, binlog_cache_disk_use = 0. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. flush status; -**** Now we are going to create transactional changes which are long enough so -**** they will be flushed to disk... +**** Transactional changes which are long enough so they will be flushed to disk... **** Expected: binlog_cache_use = 1, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. **** Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. **** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 0, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); rollback; **** Non-Transactional changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 2, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 1, binlog_stmt_cache_disk_use = 0. begin; insert into t2 values( 1 ); rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back **** Mixed changes which should not be flushed to disk and so should not -**** increase binlog_cache_disk_use. -**** Expected: binlog_cache_use = 5, binlog_cache_disk_use = 1. +**** increase either binlog_cache_disk_use or binlog_stmt_cache_disk_use. +**** Expected: binlog_cache_use = 3, binlog_cache_disk_use = 1. +**** Expected: binlog_stmt_cache_use = 2, binlog_stmt_cache_disk_use = 0. begin; insert into t1 values( 1 ); insert into t2 values( 1 ); diff --git a/mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result b/mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result index 479caed8e6b..c7f2ffab47a 100644 --- a/mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result +++ b/mysql-test/suite/rpl/r/rpl_mixed_binlog_max_cache_size.result @@ -7,6 +7,8 @@ start slave; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam; CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; @@ -129,13 +131,18 @@ source include/diff_master_slave.inc; # [ On Slave ] SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; include/stop_slave.inc include/start_slave.inc CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*"); +CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*"); CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*"); TRUNCATE t1; SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; BEGIN; Repeat statement 'INSERT INTO t1 VALUES($n, repeat("a", 32))' 128 times COMMIT; @@ -146,6 +153,8 @@ show binlog events in 'slave-bin.000001' from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; include/stop_slave.inc include/start_slave.inc SELECT count(*) FROM t1; diff --git a/mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result b/mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result index 9c1dfebebaf..f04c229a9b6 100644 --- a/mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result +++ b/mysql-test/suite/rpl/r/rpl_row_binlog_max_cache_size.result @@ -7,6 +7,8 @@ start slave; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam; CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; @@ -130,13 +132,18 @@ source include/diff_master_slave.inc; # [ On Slave ] SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; include/stop_slave.inc include/start_slave.inc CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*"); +CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*"); CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*"); TRUNCATE t1; SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; BEGIN; Repeat statement 'INSERT INTO t1 VALUES($n, repeat("a", 32))' 128 times COMMIT; @@ -147,6 +154,8 @@ show binlog events in 'slave-bin.000001' from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; include/stop_slave.inc include/start_slave.inc SELECT count(*) FROM t1; diff --git a/mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result b/mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result index 479caed8e6b..c7f2ffab47a 100644 --- a/mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result +++ b/mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result @@ -7,6 +7,8 @@ start slave; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; CREATE TABLE t1(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; CREATE TABLE t2(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=MyIsam; CREATE TABLE t3(a INT PRIMARY KEY, data VARCHAR(30000)) ENGINE=Innodb; @@ -129,13 +131,18 @@ source include/diff_master_slave.inc; # [ On Slave ] SET GLOBAL max_binlog_cache_size = 4096; SET GLOBAL binlog_cache_size = 4096; +SET GLOBAL max_binlog_stmt_cache_size = 4096; +SET GLOBAL binlog_stmt_cache_size = 4096; include/stop_slave.inc include/start_slave.inc CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage.*"); +CALL mtr.add_suppression("Multi-statement transaction required more than 'max_binlog_stmt_cache_size' bytes of storage.*"); CALL mtr.add_suppression("Writing one row to the row-based binary log failed.*"); TRUNCATE t1; SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; BEGIN; Repeat statement 'INSERT INTO t1 VALUES($n, repeat("a", 32))' 128 times COMMIT; @@ -146,6 +153,8 @@ show binlog events in 'slave-bin.000001' from <binlog_start>; Log_name Pos Event_type Server_id End_log_pos Info SET GLOBAL max_binlog_cache_size= ORIGINAL_VALUE; SET GLOBAL binlog_cache_size= ORIGINAL_VALUE; +SET GLOBAL max_binlog_stmt_cache_size= ORIGINAL_VALUE; +SET GLOBAL binlog_stmt_cache_size= ORIGINAL_VALUE; include/stop_slave.inc include/start_slave.inc SELECT count(*) FROM t1; diff --git a/mysql-test/suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc b/mysql-test/suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc new file mode 100644 index 00000000000..f5df54b7acd --- /dev/null +++ b/mysql-test/suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc @@ -0,0 +1,154 @@ +################ mysql-test\t\binlog_stmt_cache_size_basic.test ################ +# # +# Variable Name: binlog_stmt_cache_size # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: Numeric # +# Default Value: 32768 # +# Range: 4096 - 4294967295 # +# # +# # +# Creation Date: 2010-10-12 # +# Author: Alfranio Correia # +# # +# Description: Test Cases of Dynamic System Variable "binlog_stmt_cache_size" # +# that checks behavior of this variable in the following ways # +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity . # +# # +# Reference: http://dev.mysql.com/doc/refman/5.5/en/ # +# server-system-variables.html#option_mysqld_binlog_stmt_cache_size # +# # +################################################################################ + +################################################################# +# START OF binlog_stmt_cache_size TESTS # +################################################################# + +######################################################################### +# Saving initial value of binlog_stmt_cache_size in a temporary variable # +######################################################################### + +SET @start_value = @@global.binlog_stmt_cache_size; +SELECT @start_value; + +--echo '#--------------------FN_DYNVARS_006_01------------------------#' +######################################################################### +# Display the DEFAULT value of binlog_stmt_cache_size # +######################################################################### + +SET @@global.binlog_stmt_cache_size = 100; +SET @@global.binlog_stmt_cache_size = DEFAULT; +SELECT @@global.binlog_stmt_cache_size; + + +--echo '#---------------------FN_DYNVARS_006_02-------------------------#' +############################################### +# Verify default value of variable # +############################################### + +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size = 32768; + + +--echo '#--------------------FN_DYNVARS_006_03------------------------#' +######################################################################### +# Change the value of binlog_stmt_cache_size to a valid value # +######################################################################### + +SET @@global.binlog_stmt_cache_size = 4096; +SELECT @@global.binlog_stmt_cache_size; +SET @@global.binlog_stmt_cache_size = 4294967295; +SELECT @@global.binlog_stmt_cache_size; +SET @@global.binlog_stmt_cache_size = 10000; +SELECT @@global.binlog_stmt_cache_size; +SET @@global.binlog_stmt_cache_size = 21221204; +SELECT @@global.binlog_stmt_cache_size; +echo 'Bug: Invalid values are coming in variable on assigning valid values'; + + +--echo '#--------------------FN_DYNVARS_006_04-------------------------#' +############################################################################ +# Change the value of binlog_stmt_cache_size to invalid value # +############################################################################ + +SET @@global.binlog_stmt_cache_size = 1024; +SELECT @@global.binlog_stmt_cache_size; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.binlog_stmt_cache_size = 10000.01; +SET @@global.binlog_stmt_cache_size = -1024; +SELECT @@global.binlog_stmt_cache_size; +SET @@global.binlog_stmt_cache_size = 42949672950; +SELECT @@global.binlog_stmt_cache_size; +echo 'Bug: Errors are not coming on assigning invalid values to variable'; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.binlog_stmt_cache_size = ON; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.binlog_stmt_cache_size = 'test'; + + +--echo '#-------------------FN_DYNVARS_006_05----------------------------#' +############################################################################ +# Test if accessing session binlog_stmt_cache_size gives error # +############################################################################ + +--Error ER_GLOBAL_VARIABLE +SET @@session.binlog_stmt_cache_size = 0; + + +--echo '#----------------------FN_DYNVARS_006_06------------------------#' +############################################################################## +# Check if the value in GLOBAL Tables matches values in variable # +############################################################################## + +SELECT @@global.binlog_stmt_cache_size = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='binlog_stmt_cache_size'; + +--echo '#---------------------FN_DYNVARS_006_07----------------------#' +################################################################### +# Check if TRUE and FALSE values can be used on variable # +################################################################### + +SET @@global.binlog_stmt_cache_size = TRUE; +SELECT @@global.binlog_stmt_cache_size; +SET @@global.binlog_stmt_cache_size = FALSE; +SELECT @@global.binlog_stmt_cache_size; +echo 'Bug: Errors are not coming on assigning TRUE/FALSE to variable'; + +--echo '#---------------------FN_DYNVARS_006_08----------------------#' +############################################################################### +# Check if accessing variable without SCOPE points to same global variable # +############################################################################### + +SET @@global.binlog_stmt_cache_size = 1; +SELECT @@binlog_stmt_cache_size = @@global.binlog_stmt_cache_size; + +--echo '#---------------------FN_DYNVARS_006_09----------------------#' +########################################################################### +# Check if binlog_stmt_cache_size can be accessed with and without @@ sign# +########################################################################### + +--Error ER_GLOBAL_VARIABLE +SET binlog_stmt_cache_size = 1; +--Error ER_PARSE_ERROR +SET global.binlog_stmt_cache_size = 1; +--Error ER_UNKNOWN_TABLE +SELECT global.binlog_stmt_cache_size; +--Error ER_BAD_FIELD_ERROR +SELECT binlog_stmt_cache_size = @@session.binlog_stmt_cache_size; + + +############################## +# Restore initial value # +############################## + +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size; + + +########################################################### +# END OF binlog_stmt_cache_size TESTS # +########################################################### diff --git a/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_32.result b/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_32.result new file mode 100644 index 00000000000..604f671d5a4 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_32.result @@ -0,0 +1,108 @@ +SET @start_value = @@global.binlog_stmt_cache_size; +SELECT @start_value; +@start_value +32768 +'#--------------------FN_DYNVARS_006_01------------------------#' +SET @@global.binlog_stmt_cache_size = 100; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '100' +SET @@global.binlog_stmt_cache_size = DEFAULT; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +32768 +'#---------------------FN_DYNVARS_006_02-------------------------#' +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size = 32768; +@@global.binlog_stmt_cache_size = 32768 +1 +'#--------------------FN_DYNVARS_006_03------------------------#' +SET @@global.binlog_stmt_cache_size = 4096; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 4294967295; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '4294967295' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4294963200 +SET @@global.binlog_stmt_cache_size = 10000; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '10000' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +8192 +SET @@global.binlog_stmt_cache_size = 21221204; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '21221204' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +21217280 +'Bug: Invalid values are coming in variable on assigning valid values' +'#--------------------FN_DYNVARS_006_04-------------------------#' +SET @@global.binlog_stmt_cache_size = 1024; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1024' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 10000.01; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +SET @@global.binlog_stmt_cache_size = -1024; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '-1024' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 42949672950; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '42949672950' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4294963200 +'Bug: Errors are not coming on assigning invalid values to variable' +SET @@global.binlog_stmt_cache_size = ON; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +SET @@global.binlog_stmt_cache_size = 'test'; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +'#-------------------FN_DYNVARS_006_05----------------------------#' +SET @@session.binlog_stmt_cache_size = 0; +ERROR HY000: Variable 'binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +'#----------------------FN_DYNVARS_006_06------------------------#' +SELECT @@global.binlog_stmt_cache_size = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='binlog_stmt_cache_size'; +@@global.binlog_stmt_cache_size = VARIABLE_VALUE +1 +'#---------------------FN_DYNVARS_006_07----------------------#' +SET @@global.binlog_stmt_cache_size = TRUE; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = FALSE; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '0' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +'Bug: Errors are not coming on assigning TRUE/FALSE to variable' +'#---------------------FN_DYNVARS_006_08----------------------#' +SET @@global.binlog_stmt_cache_size = 1; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1' +SELECT @@binlog_stmt_cache_size = @@global.binlog_stmt_cache_size; +@@binlog_stmt_cache_size = @@global.binlog_stmt_cache_size +1 +'#---------------------FN_DYNVARS_006_09----------------------#' +SET binlog_stmt_cache_size = 1; +ERROR HY000: Variable 'binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +SET global.binlog_stmt_cache_size = 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binlog_stmt_cache_size = 1' at line 1 +SELECT global.binlog_stmt_cache_size; +ERROR 42S02: Unknown table 'global' in field list +SELECT binlog_stmt_cache_size = @@session.binlog_stmt_cache_size; +ERROR 42S22: Unknown column 'binlog_stmt_cache_size' in 'field list' +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +32768 diff --git a/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_64.result b/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_64.result new file mode 100644 index 00000000000..604f671d5a4 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/binlog_stmt_cache_size_basic_64.result @@ -0,0 +1,108 @@ +SET @start_value = @@global.binlog_stmt_cache_size; +SELECT @start_value; +@start_value +32768 +'#--------------------FN_DYNVARS_006_01------------------------#' +SET @@global.binlog_stmt_cache_size = 100; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '100' +SET @@global.binlog_stmt_cache_size = DEFAULT; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +32768 +'#---------------------FN_DYNVARS_006_02-------------------------#' +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size = 32768; +@@global.binlog_stmt_cache_size = 32768 +1 +'#--------------------FN_DYNVARS_006_03------------------------#' +SET @@global.binlog_stmt_cache_size = 4096; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 4294967295; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '4294967295' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4294963200 +SET @@global.binlog_stmt_cache_size = 10000; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '10000' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +8192 +SET @@global.binlog_stmt_cache_size = 21221204; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '21221204' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +21217280 +'Bug: Invalid values are coming in variable on assigning valid values' +'#--------------------FN_DYNVARS_006_04-------------------------#' +SET @@global.binlog_stmt_cache_size = 1024; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1024' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 10000.01; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +SET @@global.binlog_stmt_cache_size = -1024; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '-1024' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = 42949672950; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '42949672950' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4294963200 +'Bug: Errors are not coming on assigning invalid values to variable' +SET @@global.binlog_stmt_cache_size = ON; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +SET @@global.binlog_stmt_cache_size = 'test'; +ERROR 42000: Incorrect argument type to variable 'binlog_stmt_cache_size' +'#-------------------FN_DYNVARS_006_05----------------------------#' +SET @@session.binlog_stmt_cache_size = 0; +ERROR HY000: Variable 'binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +'#----------------------FN_DYNVARS_006_06------------------------#' +SELECT @@global.binlog_stmt_cache_size = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='binlog_stmt_cache_size'; +@@global.binlog_stmt_cache_size = VARIABLE_VALUE +1 +'#---------------------FN_DYNVARS_006_07----------------------#' +SET @@global.binlog_stmt_cache_size = TRUE; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +SET @@global.binlog_stmt_cache_size = FALSE; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '0' +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +4096 +'Bug: Errors are not coming on assigning TRUE/FALSE to variable' +'#---------------------FN_DYNVARS_006_08----------------------#' +SET @@global.binlog_stmt_cache_size = 1; +Warnings: +Warning 1292 Truncated incorrect binlog_stmt_cache_size value: '1' +SELECT @@binlog_stmt_cache_size = @@global.binlog_stmt_cache_size; +@@binlog_stmt_cache_size = @@global.binlog_stmt_cache_size +1 +'#---------------------FN_DYNVARS_006_09----------------------#' +SET binlog_stmt_cache_size = 1; +ERROR HY000: Variable 'binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +SET global.binlog_stmt_cache_size = 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binlog_stmt_cache_size = 1' at line 1 +SELECT global.binlog_stmt_cache_size; +ERROR 42S02: Unknown table 'global' in field list +SELECT binlog_stmt_cache_size = @@session.binlog_stmt_cache_size; +ERROR 42S22: Unknown column 'binlog_stmt_cache_size' in 'field list' +SET @@global.binlog_stmt_cache_size = @start_value; +SELECT @@global.binlog_stmt_cache_size; +@@global.binlog_stmt_cache_size +32768 diff --git a/mysql-test/suite/sys_vars/r/max_binlog_stmt_cache_size_basic.result b/mysql-test/suite/sys_vars/r/max_binlog_stmt_cache_size_basic.result new file mode 100644 index 00000000000..f2229a0090b --- /dev/null +++ b/mysql-test/suite/sys_vars/r/max_binlog_stmt_cache_size_basic.result @@ -0,0 +1,152 @@ +SET @start_value = @@global.max_binlog_stmt_cache_size; +SELECT @start_value; +@start_value +18446744073709547520 +'#--------------------FN_DYNVARS_072_01------------------------#' +SET @@global.max_binlog_stmt_cache_size = 5000; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '5000' +SET @@global.max_binlog_stmt_cache_size = DEFAULT; +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +18446744073709547520 +'#---------------------FN_DYNVARS_072_02-------------------------#' +SET @@global.max_binlog_stmt_cache_size = @start_value; +SELECT @@global.max_binlog_stmt_cache_size = 4294967295; +@@global.max_binlog_stmt_cache_size = 4294967295 +0 +'#--------------------FN_DYNVARS_072_03------------------------#' +SET @@global.max_binlog_stmt_cache_size = 4096; +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 4294967295; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '4294967295' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4294963200 +SET @@global.max_binlog_stmt_cache_size = 4294967294; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '4294967294' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4294963200 +SET @@global.max_binlog_stmt_cache_size = 4097; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '4097' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 65535; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '65535' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +61440 +'#--------------------FN_DYNVARS_072_04-------------------------#' +SET @@global.max_binlog_stmt_cache_size = -1; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '-1' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 100000000000; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '100000000000' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +99999997952 +SET @@global.max_binlog_stmt_cache_size = 10000.01; +ERROR 42000: Incorrect argument type to variable 'max_binlog_stmt_cache_size' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +99999997952 +SET @@global.max_binlog_stmt_cache_size = -1024; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '-1024' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 1024; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '1024' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 4294967296; +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4294967296 +SET @@global.max_binlog_stmt_cache_size = 4095; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '4095' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = ON; +ERROR 42000: Incorrect argument type to variable 'max_binlog_stmt_cache_size' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = 'test'; +ERROR 42000: Incorrect argument type to variable 'max_binlog_stmt_cache_size' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +'#-------------------FN_DYNVARS_072_05----------------------------#' +SET @@session.max_binlog_stmt_cache_size = 4096; +ERROR HY000: Variable 'max_binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@session.max_binlog_stmt_cache_size; +ERROR HY000: Variable 'max_binlog_stmt_cache_size' is a GLOBAL variable +'#----------------------FN_DYNVARS_072_06------------------------#' +SELECT @@global.max_binlog_stmt_cache_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='max_binlog_stmt_cache_size'; +@@global.max_binlog_stmt_cache_size = VARIABLE_VALUE +1 +SELECT @@max_binlog_stmt_cache_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='max_binlog_stmt_cache_size'; +@@max_binlog_stmt_cache_size = VARIABLE_VALUE +1 +'#---------------------FN_DYNVARS_072_07----------------------#' +SET @@global.max_binlog_stmt_cache_size = TRUE; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '1' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +SET @@global.max_binlog_stmt_cache_size = FALSE; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '0' +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +4096 +'#---------------------FN_DYNVARS_072_08----------------------#' +SET @@global.max_binlog_stmt_cache_size = 5000; +Warnings: +Warning 1292 Truncated incorrect max_binlog_stmt_cache_size value: '5000' +SELECT @@max_binlog_stmt_cache_size = @@global.max_binlog_stmt_cache_size; +@@max_binlog_stmt_cache_size = @@global.max_binlog_stmt_cache_size +1 +'#---------------------FN_DYNVARS_072_09----------------------#' +SET max_binlog_stmt_cache_size = 6000; +ERROR HY000: Variable 'max_binlog_stmt_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +SELECT @@max_binlog_stmt_cache_size; +@@max_binlog_stmt_cache_size +4096 +SET local.max_binlog_stmt_cache_size = 7000; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_binlog_stmt_cache_size = 7000' at line 1 +SELECT local.max_binlog_stmt_cache_size; +ERROR 42S02: Unknown table 'local' in field list +SET global.max_binlog_stmt_cache_size = 8000; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_binlog_stmt_cache_size = 8000' at line 1 +SELECT global.max_binlog_stmt_cache_size; +ERROR 42S02: Unknown table 'global' in field list +SELECT max_binlog_stmt_cache_size = @@session.max_binlog_stmt_cache_size; +ERROR 42S22: Unknown column 'max_binlog_stmt_cache_size' in 'field list' +SET @@global.max_binlog_stmt_cache_size = @start_value; +SELECT @@global.max_binlog_stmt_cache_size; +@@global.max_binlog_stmt_cache_size +18446744073709547520 diff --git a/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_32.test b/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_32.test new file mode 100644 index 00000000000..fe8f89ccc16 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_32.test @@ -0,0 +1,7 @@ +################################################################################ +# Wrapper for 32 bit machines # +################################################################################ + +--source include/have_32bit.inc +--source suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc + diff --git a/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_64.test b/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_64.test new file mode 100644 index 00000000000..c4a2c95d42b --- /dev/null +++ b/mysql-test/suite/sys_vars/t/binlog_stmt_cache_size_basic_64.test @@ -0,0 +1,7 @@ +################################################################################ +# Wrapper for 64 bit machines # +################################################################################ + +--source include/have_64bit.inc +--source suite/sys_vars/inc/binlog_stmt_cache_size_basic.inc + diff --git a/mysql-test/suite/sys_vars/t/max_binlog_cache_size_func-master.opt b/mysql-test/suite/sys_vars/t/max_binlog_cache_size_func-master.opt deleted file mode 100644 index 6e00d7157d6..00000000000 --- a/mysql-test/suite/sys_vars/t/max_binlog_cache_size_func-master.opt +++ /dev/null @@ -1,2 +0,0 @@ ---log-bin ---innodb diff --git a/mysql-test/suite/sys_vars/t/max_binlog_stmt_cache_size_basic.test b/mysql-test/suite/sys_vars/t/max_binlog_stmt_cache_size_basic.test new file mode 100644 index 00000000000..07a030c35a7 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/max_binlog_stmt_cache_size_basic.test @@ -0,0 +1,184 @@ +############ mysql-test\t\max_binlog_stmt_cache_size_basic.test ############### +# # +# Variable Name: max_binlog_stmt_cache_size # +# Scope: GLOBAL # +# Access Type: Dynamic # +# Data Type: numeric # +# Default Value:4294967295 # +# Range: 4096-4294967295 # +# # +# # +# # +# # +# # +# Creation Date: 2010-11-05 # +# Author: Alfranio # +# # +# Description: Test Cases of Dynamic System Variable # +# max_binlog_stmt_cache_size that checks # +# the behavior of this variable in the following ways # +# * Default Value # +# * Valid & Invalid values # +# * Scope & Access method # +# * Data Integrity # +# # +# Reference: http://dev.mysql.com/doc/refman/5.5/en/ # +# server-system-variables.html # +# # +############################################################################### + +--source include/load_sysvars.inc + +########################################################################## +# START OF max_binlog_stmt_cache_size TESTS # +########################################################################## + + +############################################################################## +# Saving initial value of max_binlog_stmt_cache_size in a temporary variable # +############################################################################## + +SET @start_value = @@global.max_binlog_stmt_cache_size; +SELECT @start_value; + + +--echo '#--------------------FN_DYNVARS_072_01------------------------#' +######################################################################## +# Display the DEFAULT value of max_binlog_stmt_cache_size # +######################################################################## + +SET @@global.max_binlog_stmt_cache_size = 5000; +SET @@global.max_binlog_stmt_cache_size = DEFAULT; +SELECT @@global.max_binlog_stmt_cache_size; + + +--echo '#---------------------FN_DYNVARS_072_02-------------------------#' +############################################### +# Verify default value of variable # +############################################### + +SET @@global.max_binlog_stmt_cache_size = @start_value; +SELECT @@global.max_binlog_stmt_cache_size = 4294967295; + +--echo '#--------------------FN_DYNVARS_072_03------------------------#' +######################################################################## +# Change the value of max_binlog_stmt_cache_size to a valid value # +######################################################################## + +SET @@global.max_binlog_stmt_cache_size = 4096; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 4294967295; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 4294967294; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 4097; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 65535; +SELECT @@global.max_binlog_stmt_cache_size; + + +--echo '#--------------------FN_DYNVARS_072_04-------------------------#' +########################################################################### +# Change the value of max_binlog_stmt_cache_size to invalid value # +########################################################################### + +SET @@global.max_binlog_stmt_cache_size = -1; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 100000000000; +SELECT @@global.max_binlog_stmt_cache_size; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.max_binlog_stmt_cache_size = 10000.01; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = -1024; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 1024; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 4294967296; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = 4095; +SELECT @@global.max_binlog_stmt_cache_size; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.max_binlog_stmt_cache_size = ON; +SELECT @@global.max_binlog_stmt_cache_size; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.max_binlog_stmt_cache_size = 'test'; +SELECT @@global.max_binlog_stmt_cache_size; + + +--echo '#-------------------FN_DYNVARS_072_05----------------------------#' +########################################################################### +# Test if accessing session max_binlog_stmt_cache_size gives error # +########################################################################### + +--Error ER_GLOBAL_VARIABLE +SET @@session.max_binlog_stmt_cache_size = 4096; +--Error ER_INCORRECT_GLOBAL_LOCAL_VAR +SELECT @@session.max_binlog_stmt_cache_size; + + +--echo '#----------------------FN_DYNVARS_072_06------------------------#' +############################################################################## +# Check if the value in GLOBAL & SESSION Tables matches values in variable # +############################################################################## + +SELECT @@global.max_binlog_stmt_cache_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='max_binlog_stmt_cache_size'; + +SELECT @@max_binlog_stmt_cache_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='max_binlog_stmt_cache_size'; + + +--echo '#---------------------FN_DYNVARS_072_07----------------------#' +################################################################### +# Check if TRUE and FALSE values can be used on variable # +################################################################### + +SET @@global.max_binlog_stmt_cache_size = TRUE; +SELECT @@global.max_binlog_stmt_cache_size; +SET @@global.max_binlog_stmt_cache_size = FALSE; +SELECT @@global.max_binlog_stmt_cache_size; + + +--echo '#---------------------FN_DYNVARS_072_08----------------------#' +######################################################################################################## +# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # +######################################################################################################## + +SET @@global.max_binlog_stmt_cache_size = 5000; +SELECT @@max_binlog_stmt_cache_size = @@global.max_binlog_stmt_cache_size; + + +--echo '#---------------------FN_DYNVARS_072_09----------------------#' +################################################################################ +# Check if max_binlog_stmt_cache_size can be accessed with and without @@ sign # +################################################################################ + +--Error ER_GLOBAL_VARIABLE +SET max_binlog_stmt_cache_size = 6000; +SELECT @@max_binlog_stmt_cache_size; +--Error ER_PARSE_ERROR +SET local.max_binlog_stmt_cache_size = 7000; +--Error ER_UNKNOWN_TABLE +SELECT local.max_binlog_stmt_cache_size; +--Error ER_PARSE_ERROR +SET global.max_binlog_stmt_cache_size = 8000; +--Error ER_UNKNOWN_TABLE +SELECT global.max_binlog_stmt_cache_size; +--Error ER_BAD_FIELD_ERROR +SELECT max_binlog_stmt_cache_size = @@session.max_binlog_stmt_cache_size; + + +############################## +# Restore initial value # +############################## + +SET @@global.max_binlog_stmt_cache_size = @start_value; +SELECT @@global.max_binlog_stmt_cache_size; + + +######################################################################## +# END OF max_binlog_stmt_cache_size TESTS # +######################################################################## diff --git a/sql/log.cc b/sql/log.cc index f483ae74d64..53746ec02f8 100644 --- a/sql/log.cc +++ b/sql/log.cc @@ -209,11 +209,11 @@ class binlog_cache_data { public: binlog_cache_data(): m_pending(0), before_stmt_pos(MY_OFF_T_UNDEF), - incident(FALSE), changes_to_non_trans_temp_table_flag(FALSE) - { - cache_log.end_of_file= max_binlog_cache_size; - } - + incident(FALSE), changes_to_non_trans_temp_table_flag(FALSE), + saved_max_binlog_cache_size(0), ptr_binlog_cache_use(0), + ptr_binlog_cache_disk_use(0) + { } + ~binlog_cache_data() { DBUG_ASSERT(empty()); @@ -262,10 +262,9 @@ public: changes_to_non_trans_temp_table_flag= FALSE; incident= FALSE; before_stmt_pos= MY_OFF_T_UNDEF; - cache_log.end_of_file= max_binlog_cache_size; /* The truncate function calls reinit_io_cache that calls my_b_flush_io_cache - which may increase disk_write. This breaks the "disk_writes"' use by the + which may increase disk_writes. This breaks the disk_writes use by the binary log which aims to compute the ratio between in-memory cache usage and disk cache usage. To avoid this undesirable behavior, we reset the variable after truncating the cache. @@ -301,6 +300,36 @@ public: before_stmt_pos= MY_OFF_T_UNDEF; } + void set_binlog_cache_info(ulong param_max_binlog_cache_size, + ulong *param_ptr_binlog_cache_use, + ulong *param_ptr_binlog_cache_disk_use) + { + /* + The assertions guarantee that the set_binlog_cache_info is + called just once and information passed as parameters are + never zero. + + This is done while calling the constructor binlog_cache_mngr. + We cannot set informaton in the constructor binlog_cache_data + because the space for binlog_cache_mngr is allocated through + a placement new. + + In the future, we can refactor this and change it to avoid + the set_binlog_info. + */ + DBUG_ASSERT(saved_max_binlog_cache_size == 0 && + param_max_binlog_cache_size != 0 && + ptr_binlog_cache_use == 0 && + param_ptr_binlog_cache_use != 0 && + ptr_binlog_cache_disk_use == 0 && + param_ptr_binlog_cache_disk_use != 0); + + saved_max_binlog_cache_size= param_max_binlog_cache_size; + ptr_binlog_cache_use= param_ptr_binlog_cache_use; + ptr_binlog_cache_disk_use= param_ptr_binlog_cache_disk_use; + cache_log.end_of_file= saved_max_binlog_cache_size; + } + /* Cache to store data before copying it to the binary log. */ @@ -332,21 +361,39 @@ private: /** This function computes binlog cache and disk usage. - - @param cache_data Pointer to the cache where data is - stored. */ void compute_statistics() { if (!empty()) { - statistic_increment(binlog_cache_use, &LOCK_status); + statistic_increment(*ptr_binlog_cache_use, &LOCK_status); if (cache_log.disk_writes != 0) - statistic_increment(binlog_cache_disk_use, &LOCK_status); + statistic_increment(*ptr_binlog_cache_disk_use, &LOCK_status); } } /* + Stores the values of maximum size of the cache allowed when this cache + is configured. This corresponds to either + . max_binlog_cache_size or max_binlog_stmt_cache_size. + */ + ulong saved_max_binlog_cache_size; + + /* + Stores a pointer to the status variable that keeps track of the in-memory + cache usage. This corresponds to either + . binlog_cache_use or binlog_stmt_cache_use. + */ + ulong *ptr_binlog_cache_use; + + /* + Stores a pointer to the status variable that keeps track of the disk + cache usage. This corresponds to either + . binlog_cache_disk_use or binlog_stmt_cache_disk_use. + */ + ulong *ptr_binlog_cache_disk_use; + + /* It truncates the cache to a certain position. This includes deleting the pending event. */ @@ -359,7 +406,7 @@ private: set_pending(0); } reinit_io_cache(&cache_log, WRITE_CACHE, pos, 0, 0); - cache_log.end_of_file= max_binlog_cache_size; + cache_log.end_of_file= saved_max_binlog_cache_size; } binlog_cache_data& operator=(const binlog_cache_data& info); @@ -368,7 +415,20 @@ private: class binlog_cache_mngr { public: - binlog_cache_mngr() {} + binlog_cache_mngr(ulong param_max_binlog_stmt_cache_size, + ulong param_max_binlog_cache_size, + ulong *param_ptr_binlog_stmt_cache_use, + ulong *param_ptr_binlog_stmt_cache_disk_use, + ulong *param_ptr_binlog_cache_use, + ulong *param_ptr_binlog_cache_disk_use) + { + stmt_cache.set_binlog_cache_info(param_max_binlog_stmt_cache_size, + param_ptr_binlog_stmt_cache_use, + param_ptr_binlog_stmt_cache_disk_use); + trx_cache.set_binlog_cache_info(param_max_binlog_cache_size, + param_ptr_binlog_cache_use, + param_ptr_binlog_cache_disk_use); + } void reset_cache(binlog_cache_data* cache_data) { @@ -1877,7 +1937,7 @@ static int binlog_rollback(handlerton *hton, THD *thd, bool all) DBUG_RETURN(error); } -void MYSQL_BIN_LOG::set_write_error(THD *thd) +void MYSQL_BIN_LOG::set_write_error(THD *thd, bool is_transactional) { DBUG_ENTER("MYSQL_BIN_LOG::set_write_error"); @@ -1887,9 +1947,20 @@ void MYSQL_BIN_LOG::set_write_error(THD *thd) DBUG_VOID_RETURN; if (my_errno == EFBIG) - my_message(ER_TRANS_CACHE_FULL, ER(ER_TRANS_CACHE_FULL), MYF(MY_WME)); + { + if (is_transactional) + { + my_message(ER_TRANS_CACHE_FULL, ER(ER_TRANS_CACHE_FULL), MYF(MY_WME)); + } + else + { + my_message(ER_STMT_CACHE_FULL, ER(ER_STMT_CACHE_FULL), MYF(MY_WME)); + } + } else + { my_error(ER_ERROR_ON_WRITE, MYF(MY_WME), name, errno); + } DBUG_VOID_RETURN; } @@ -1906,6 +1977,7 @@ bool MYSQL_BIN_LOG::check_write_error(THD *thd) switch (thd->stmt_da->sql_errno()) { case ER_TRANS_CACHE_FULL: + case ER_STMT_CACHE_FULL: case ER_ERROR_ON_WRITE: case ER_BINLOG_LOGGING_IMPOSSIBLE: checked= TRUE; @@ -4398,7 +4470,7 @@ int THD::binlog_setup_trx_data() cache_mngr= (binlog_cache_mngr*) my_malloc(sizeof(binlog_cache_mngr), MYF(MY_ZEROFILL)); if (!cache_mngr || open_cached_file(&cache_mngr->stmt_cache.cache_log, mysql_tmpdir, - LOG_PREFIX, binlog_cache_size, MYF(MY_WME)) || + LOG_PREFIX, binlog_stmt_cache_size, MYF(MY_WME)) || open_cached_file(&cache_mngr->trx_cache.cache_log, mysql_tmpdir, LOG_PREFIX, binlog_cache_size, MYF(MY_WME))) { @@ -4407,8 +4479,13 @@ int THD::binlog_setup_trx_data() } thd_set_ha_data(this, binlog_hton, cache_mngr); - cache_mngr= new (thd_get_ha_data(this, binlog_hton)) binlog_cache_mngr; - + cache_mngr= new (thd_get_ha_data(this, binlog_hton)) + binlog_cache_mngr(max_binlog_stmt_cache_size, + max_binlog_cache_size, + &binlog_stmt_cache_use, + &binlog_stmt_cache_disk_use, + &binlog_cache_use, + &binlog_cache_disk_use); DBUG_RETURN(0); } @@ -4660,7 +4737,7 @@ MYSQL_BIN_LOG::flush_and_set_pending_rows_event(THD *thd, */ if (pending->write(file)) { - set_write_error(thd); + set_write_error(thd, is_transactional); if (check_write_error(thd) && cache_data && stmt_has_updated_non_trans_table(thd)) cache_data->set_incident(); @@ -4685,6 +4762,7 @@ bool MYSQL_BIN_LOG::write(Log_event *event_info) bool error= 1; DBUG_ENTER("MYSQL_BIN_LOG::write(Log_event *)"); binlog_cache_data *cache_data= 0; + bool is_trans_cache= FALSE; if (thd->binlog_evt_union.do_union) { @@ -4745,7 +4823,7 @@ bool MYSQL_BIN_LOG::write(Log_event *event_info) binlog_cache_mngr *const cache_mngr= (binlog_cache_mngr*) thd_get_ha_data(thd, binlog_hton); - bool is_trans_cache= use_trans_cache(thd, event_info->use_trans_cache()); + is_trans_cache= use_trans_cache(thd, event_info->use_trans_cache()); file= cache_mngr->get_binlog_cache_log(is_trans_cache); cache_data= cache_mngr->get_binlog_cache_data(is_trans_cache); @@ -4851,7 +4929,7 @@ unlock: if (error) { - set_write_error(thd); + set_write_error(thd, is_trans_cache); if (check_write_error(thd) && cache_data && stmt_has_updated_non_trans_table(thd)) cache_data->set_incident(); diff --git a/sql/log.h b/sql/log.h index 89b3594cd1e..df7ed61a473 100644 --- a/sql/log.h +++ b/sql/log.h @@ -399,7 +399,7 @@ public: bool write_incident(THD *thd, bool lock); int write_cache(IO_CACHE *cache, bool lock_log, bool flush_and_sync); - void set_write_error(THD *thd); + void set_write_error(THD *thd, bool is_transactional); bool check_write_error(THD *thd); void start_union_events(THD *thd, query_id_t query_id_param); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index df4f0f95b8c..86013dace31 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -460,6 +460,8 @@ ulonglong slave_type_conversions_options; ulong thread_cache_size=0; ulong binlog_cache_size=0; ulonglong max_binlog_cache_size=0; +ulong binlog_stmt_cache_size=0; +ulonglong max_binlog_stmt_cache_size=0; ulong query_cache_size=0; ulong refresh_version; /* Increments on each reload */ query_id_t global_query_id; @@ -471,6 +473,7 @@ ulong delayed_insert_threads, delayed_insert_writes, delayed_rows_in_use; ulong delayed_insert_errors,flush_time; ulong specialflag=0; ulong binlog_cache_use= 0, binlog_cache_disk_use= 0; +ulong binlog_stmt_cache_use= 0, binlog_stmt_cache_disk_use= 0; ulong max_connections, max_connect_errors; /** Limit of the total number of prepared statements in the server. @@ -6406,6 +6409,8 @@ SHOW_VAR status_vars[]= { {"Aborted_connects", (char*) &aborted_connects, SHOW_LONG}, {"Binlog_cache_disk_use", (char*) &binlog_cache_disk_use, SHOW_LONG}, {"Binlog_cache_use", (char*) &binlog_cache_use, SHOW_LONG}, + {"Binlog_stmt_cache_disk_use",(char*) &binlog_stmt_cache_disk_use, SHOW_LONG}, + {"Binlog_stmt_cache_use", (char*) &binlog_stmt_cache_use, SHOW_LONG}, {"Bytes_received", (char*) offsetof(STATUS_VAR, bytes_received), SHOW_LONGLONG_STATUS}, {"Bytes_sent", (char*) offsetof(STATUS_VAR, bytes_sent), SHOW_LONGLONG_STATUS}, {"Com", (char*) com_status_vars, SHOW_ARRAY}, diff --git a/sql/mysqld.h b/sql/mysqld.h index 6e81c240f7d..4988248e936 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -152,6 +152,7 @@ extern ulonglong keybuff_size; extern ulonglong thd_startup_options; extern ulong thread_id; extern ulong binlog_cache_use, binlog_cache_disk_use; +extern ulong binlog_stmt_cache_use, binlog_stmt_cache_disk_use; extern ulong aborted_threads,aborted_connects; extern ulong delayed_insert_timeout; extern ulong delayed_insert_limit, delayed_queue_size; @@ -171,8 +172,9 @@ extern uint slave_net_timeout; extern uint max_user_connections; extern ulong what_to_log,flush_time; extern ulong max_prepared_stmt_count, prepared_stmt_count; -extern ulong binlog_cache_size, open_files_limit; -extern ulonglong max_binlog_cache_size; +extern ulong open_files_limit; +extern ulong binlog_cache_size, binlog_stmt_cache_size; +extern ulonglong max_binlog_cache_size, max_binlog_stmt_cache_size; extern ulong max_binlog_size, max_relay_log_size; extern ulong opt_binlog_rows_event_max_size; extern ulong rpl_recovery_rank, thread_cache_size; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index be97afe055a..b0dc4d9195b 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -6392,3 +6392,5 @@ ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MIN ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MAX eng "The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds. A sensible value for the period should be less than the timeout." +ER_STMT_CACHE_FULL + eng "Multi-row statements required more than 'max_binlog_stmt_cache_size' bytes of storage; increase this mysqld variable and try again" diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 5c9df82ddac..ed2c44fb03f 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -236,14 +236,23 @@ static Sys_var_charptr Sys_basedir( IN_FS_CHARSET, DEFAULT(0)); static Sys_var_ulong Sys_binlog_cache_size( - "binlog_cache_size", "The size of the cache to " - "hold the SQL statements for the binary log during a " - "transaction. If you often use big, multi-statement " - "transactions you can increase this to get more performance", + "binlog_cache_size", "The size of the transactional cache for " + "updates to transactional engines for the binary log. " + "If you often use transactions containing many statements, " + "you can increase this to get more performance", GLOBAL_VAR(binlog_cache_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(IO_SIZE, ULONG_MAX), DEFAULT(32768), BLOCK_SIZE(IO_SIZE)); +static Sys_var_ulong Sys_binlog_stmt_cache_size( + "binlog_stmt_cache_size", "The size of the statement cache for " + "updates to non-transactional engines for the binary log. " + "If you often use statements updating a great number of rows, " + "you can increase this to get more performance", + GLOBAL_VAR(binlog_stmt_cache_size), + CMD_LINE(REQUIRED_ARG), + VALID_RANGE(IO_SIZE, ULONG_MAX), DEFAULT(32768), BLOCK_SIZE(IO_SIZE)); + static bool check_has_super(sys_var *self, THD *thd, set_var *var) { DBUG_ASSERT(self->scope() != sys_var::GLOBAL);// don't abuse check_has_super() @@ -1031,13 +1040,20 @@ static Sys_var_ulong Sys_max_allowed_packet( static Sys_var_ulonglong Sys_max_binlog_cache_size( "max_binlog_cache_size", - "Can be used to restrict the total size used to cache a " - "multi-transaction query", + "Sets the total size of the transactional cache", GLOBAL_VAR(max_binlog_cache_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(IO_SIZE, ULONGLONG_MAX), DEFAULT((ULONGLONG_MAX/IO_SIZE)*IO_SIZE), BLOCK_SIZE(IO_SIZE)); +static Sys_var_ulonglong Sys_max_binlog_stmt_cache_size( + "max_binlog_stmt_cache_size", + "Sets the total size of the statement cache", + GLOBAL_VAR(max_binlog_stmt_cache_size), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(IO_SIZE, ULONGLONG_MAX), + DEFAULT((ULONGLONG_MAX/IO_SIZE)*IO_SIZE), + BLOCK_SIZE(IO_SIZE)); + static bool fix_max_binlog_size(sys_var *self, THD *thd, enum_var_type type) { mysql_bin_log.set_max_size(max_binlog_size); |