diff options
27 files changed, 3578 insertions, 267 deletions
diff --git a/include/m_ctype.h b/include/m_ctype.h index 9025507ce15..b85894e94f5 100644 --- a/include/m_ctype.h +++ b/include/m_ctype.h @@ -282,6 +282,7 @@ typedef struct charset_info_st MY_COLLATION_HANDLER *coll; } CHARSET_INFO; +#define ILLEGAL_CHARSET_INFO_NUMBER (~0U) extern CHARSET_INFO my_charset_bin; diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index 9bd11bf33c1..38c123295fb 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -341,6 +341,14 @@ drop function if exists f1; drop procedure if exists p_verify_status_increment; --enable_warnings +# Save binlog_format in a user variable. References to system +# variables are "unsafe", meaning they are written as rows instead of +# as statements to the binlog, if the loggging mode is 'mixed'. But +# we don't want p_verify_status_increment to affect the logging mode. +# Hence, we save binlog_format in a user variable (which is not +# unsafe) and use that inside p_verify_status_increment. +set @binlog_format=@@global.binlog_format; + set sql_mode=no_engine_substitution; create table t1 (a int unique); create table t2 (a int) engine=myisam; @@ -367,7 +375,7 @@ begin where variable_name='Handler_commit' or variable_name='Handler_prepare' order by variable_name; - if @@global.binlog_format = 'ROW' then + if @binlog_format = 'ROW' then set commit_inc= commit_inc_row; set prepare_inc= prepare_inc_row; else diff --git a/mysql-test/include/diff_tables.inc b/mysql-test/include/diff_tables.inc new file mode 100644 index 00000000000..d15dd56b35d --- /dev/null +++ b/mysql-test/include/diff_tables.inc @@ -0,0 +1,122 @@ +# ==== Purpose ==== +# +# Check if the two given tables (possibly residing on different +# master/slave servers) are equal. +# +# ==== Usage ==== +# +# The tables to check are given by the test language variables +# $diff_table_1 and $diff_table_2. They must be of the +# following form: +# +# [master:|slave:]database.table +# +# I.e., both database and table must be speicified. Optionally, you +# can prefix the name with 'master:' (to read the table on master) or +# with 'slave:' (to read the table on slave). If no prefix is given, +# reads the table from the current connection. If one of these +# variables has a prefix, both should have a prefix. +# +# ==== Side effects ==== +# +# - Prints "Comparing tables $diff_table_1 and $diff_tables_2". +# +# - If the tables are different, prints the difference in a +# system-specific format (unified diff if supported) and generates +# an error. +# +# - If $diff_table_1 or $diff_table_2 begins with 'master:' or +# 'slave:', it will stay connected to one of those hosts after +# execution. The host is only guaranteed to remain unchanged if +# none of $diff_table_1 or $diff_table_2 begins with 'master:' or +# 'slave:'. +# +# ==== Bugs ==== +# +# - It is currently not possible to use this for tables that are +# supposed to be different, because if the files are different: +# - 'diff' produces system-dependent output, +# - the output includes the absolute path of the compared files, +# - the output includes a timestamp. +# To fix that, we'd probably have to use SQL to compute the +# symmetric difference between the tables. I'm not sure how to do +# that efficiently. If we implement this, it would be nice to +# compare the table definitions too. +# +# - It actually compares the result of "SELECT * FROM table ORDER BY +# col1, col2, ..., colN INTO OUTFILE 'file'". Hence, it is assumed +# that the comparison orders for both tables are equal and that two +# rows that are equal in the comparison order cannot differ, e.g., +# by character case. + + +# ==== Save both tables to file ==== + +--echo Comparing tables $diff_table_1 and $diff_table_2 +disable_query_log; + +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1 +--error 0,1 +--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2 + +let $_diff_table=$diff_table_2; +let $_diff_i=2; +while ($_diff_i) { + + # Parse out any leading "master:" or "slave:" from the table + # specification and connect the appropriate server. + let $_diff_conn_master=`SELECT SUBSTR('$_diff_table', 1, 7) = 'master:'`; + if ($_diff_conn_master) { + let $_diff_table=`SELECT SUBSTR('$_diff_table', 8)`; + connection master; + } + let $_diff_conn_slave=`SELECT SUBSTR('$_diff_table', 1, 6) = 'slave:'`; + if ($_diff_conn_slave) { + let $_diff_table=`SELECT SUBSTR('$_diff_table', 7)`; + connection slave; + } + + # Sanity-check the input. + let $_diff_error= `SELECT '$_diff_table' NOT LIKE '_%._%'`; + if ($_diff_error) { + --echo !!!ERROR IN TEST: \$diff_table_$_diff_i='$_diff_table' is not in the form database.table + exit; + } + + # We need the output files to be sorted (so that diff_files does not + # think the files are different just because they are differently + # ordered). To this end, we first generate a query that sorts the + # table by all columns. Since ORDER BY accept column indices, we + # just generate a comma-separated list of all numbers from 1 to the + # number of columns in the table. + let $_diff_column_index=`SELECT MAX(ordinal_position) + FROM information_schema.columns + WHERE CONCAT(table_schema, '.', table_name) = + '$_diff_table'`; + let $_diff_column_list=$_diff_column_index; + dec $_diff_column_index; + while ($_diff_column_index) { + let $_diff_column_list=$_diff_column_index, $_diff_column_list; + dec $_diff_column_index; + } + + # Now that we have the comma-separated list of columns, we can write + # the table to a file. + eval SELECT * FROM $_diff_table ORDER BY $_diff_column_list + INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/diff_table_$_diff_i'; + + # Do the same for $diff_table_1. + dec $_diff_i; + let $_diff_table=$diff_table_1; +} + + +# ==== Compare the generated files ==== + +diff_files $MYSQLTEST_VARDIR/tmp/diff_table_1 $MYSQLTEST_VARDIR/tmp/diff_table_2; + +--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1 +--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2 + +enable_query_log; diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 8598e231861..76eee3874d6 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -331,6 +331,7 @@ drop table if exists t1; drop table if exists t2; drop function if exists f1; drop procedure if exists p_verify_status_increment; +set @binlog_format=@@global.binlog_format; set sql_mode=no_engine_substitution; create table t1 (a int unique); create table t2 (a int) engine=myisam; @@ -355,7 +356,7 @@ select variable_value from information_schema.session_status where variable_name='Handler_commit' or variable_name='Handler_prepare' order by variable_name; -if @@global.binlog_format = 'ROW' then +if @binlog_format = 'ROW' then set commit_inc= commit_inc_row; set prepare_inc= prepare_inc_row; else diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 4ffcb7cdeeb..7c5fdf5f3d0 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -19,10 +19,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; drop table if exists t1,t2,t3,t4,t5,t03,t04 /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -61,10 +65,14 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") /*!*/; DELIMITER ; @@ -89,10 +97,14 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") /*!*/; DELIMITER ; @@ -107,10 +119,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; drop table if exists t1,t2,t3,t4,t5,t03,t04 /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -149,10 +165,14 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") /*!*/; DELIMITER ; @@ -177,10 +197,14 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values ("Alas") /*!*/; DELIMITER ; @@ -195,6 +219,10 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1108844556/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET TIMESTAMP=1108844555/*!*/; @@ -209,6 +237,10 @@ ROLLBACK /* added by mysqlbinlog */; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1108844556/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; SET TIMESTAMP=1108844555/*!*/; @@ -265,10 +297,14 @@ ERROR 42000: PROCEDURE test.p1 does not exist DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; CREATE DEFINER=`root`@`localhost` procedure p1() begin select 1; @@ -311,10 +347,14 @@ flush logs; DELIMITER /*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a varchar(64) character set utf8) /*!*/; SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result index 0c9ba34fc61..89fc4b87d29 100644 --- a/mysql-test/r/mysqlbinlog2.result +++ b/mysql-test/r/mysqlbinlog2.result @@ -18,61 +18,39 @@ insert into t1 values(null, "f"); /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Start: binlog v #, server v ## created {yymmdd} {HH:MM:SS} at startup ROLLBACK/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Intvar SET INSERT_ID=1/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "a") /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Intvar SET INSERT_ID=2/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 SET TIMESTAMP=1579609942/*!*/; insert into t1 values(null, "b") /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Intvar SET INSERT_ID=3/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 SET TIMESTAMP=1579609944/*!*/; insert into t1 values(null, "c") /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Intvar SET INSERT_ID=4/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 SET TIMESTAMP=1579609946/*!*/; insert into t1 values(null, "d") /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Intvar SET INSERT_ID=5/*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Query thread_id={integer} exec_time={integer} error_code=0 SET TIMESTAMP=1579609946/*!*/; insert into t1 values(null, "e") /*!*/; -# at {pos} -#{yymmdd} {HH:MM:SS} server id 1 end_log_pos {pos} Rotate to master-bin.000002 pos: {pos} DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -86,10 +64,14 @@ ROLLBACK/*!*/; SET INSERT_ID=1/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "a") /*!*/; SET INSERT_ID=2/*!*/; @@ -121,10 +103,14 @@ ROLLBACK/*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; SET INSERT_ID=5/*!*/; @@ -143,10 +129,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -174,10 +164,14 @@ ROLLBACK/*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; DELIMITER ; @@ -193,10 +187,14 @@ ROLLBACK/*!*/; SET INSERT_ID=3/*!*/; use test/*!*/; SET TIMESTAMP=1579609944/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "c") /*!*/; SET INSERT_ID=4/*!*/; @@ -219,10 +217,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -246,10 +248,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -277,10 +283,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -296,10 +306,14 @@ ROLLBACK/*!*/; SET INSERT_ID=1/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "a") /*!*/; SET INSERT_ID=2/*!*/; @@ -323,10 +337,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -342,10 +360,14 @@ ROLLBACK/*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; SET INSERT_ID=5/*!*/; @@ -357,10 +379,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -375,10 +401,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -417,10 +447,14 @@ ROLLBACK/*!*/; SET INSERT_ID=3/*!*/; use test/*!*/; SET TIMESTAMP=1579609944/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "c") /*!*/; SET INSERT_ID=4/*!*/; @@ -436,10 +470,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -454,10 +492,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -480,10 +522,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -519,10 +565,14 @@ ROLLBACK/*!*/; SET INSERT_ID=1/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "a") /*!*/; SET INSERT_ID=2/*!*/; @@ -553,10 +603,14 @@ DELIMITER /*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; SET INSERT_ID=5/*!*/; @@ -575,10 +629,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -605,10 +663,14 @@ DELIMITER /*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; DELIMITER ; @@ -624,10 +686,14 @@ ROLLBACK/*!*/; SET INSERT_ID=3/*!*/; use test/*!*/; SET TIMESTAMP=1579609944/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "c") /*!*/; SET INSERT_ID=4/*!*/; @@ -650,10 +716,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -676,10 +746,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -707,10 +781,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -726,10 +804,14 @@ ROLLBACK/*!*/; SET INSERT_ID=1/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "a") /*!*/; SET INSERT_ID=2/*!*/; @@ -753,10 +835,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -771,10 +857,14 @@ DELIMITER /*!*/; SET INSERT_ID=4/*!*/; use test/*!*/; SET TIMESTAMP=1579609946/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "d") /*!*/; SET INSERT_ID=5/*!*/; @@ -786,10 +876,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -804,10 +898,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -846,10 +944,14 @@ ROLLBACK/*!*/; SET INSERT_ID=3/*!*/; use test/*!*/; SET TIMESTAMP=1579609944/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "c") /*!*/; SET INSERT_ID=4/*!*/; @@ -865,10 +967,14 @@ DELIMITER /*!*/; SET INSERT_ID=6/*!*/; use test/*!*/; SET TIMESTAMP=1579609943/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t1 values(null, "f") /*!*/; DELIMITER ; @@ -883,10 +989,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; @@ -909,10 +1019,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1579609942/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int auto_increment not null primary key, b char(3)) /*!*/; SET INSERT_ID=1/*!*/; diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result index 44fcfc0c478..3a7d59798ae 100644 --- a/mysql-test/r/user_var-binlog.result +++ b/mysql-test/r/user_var-binlog.result @@ -21,10 +21,14 @@ ROLLBACK/*!*/; SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/; use test/*!*/; SET TIMESTAMP=10000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; INSERT INTO t1 VALUES(@`a b`) /*!*/; SET @`var1`:=_latin1 0x273B616161 COLLATE `latin1_swedish_ci`/*!*/; diff --git a/mysql-test/suite/binlog/r/binlog_base64_flag.result b/mysql-test/suite/binlog/r/binlog_base64_flag.result index 8e5d7def823..557f584fcc1 100644 --- a/mysql-test/suite/binlog/r/binlog_base64_flag.result +++ b/mysql-test/suite/binlog/r/binlog_base64_flag.result @@ -32,14 +32,17 @@ a /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 -#ROLLBACK/*!*/; +<#>ROLLBACK/*!*/; # at 102 -#use test/*!*/; +<#>use test/*!*/; SET TIMESTAMP=1196959712/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; +<#>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; create table t1 (a int) engine= myisam /*!*/; # at 203 diff --git a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result index 3030af7bd68..1b14686a97d 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result +++ b/mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result @@ -15,10 +15,14 @@ ROLLBACK/*!*/; SET @`v`:=_ucs2 0x006100620063 COLLATE `ucs2_general_ci`/*!*/; use test/*!*/; SET TIMESTAMP=10000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; insert into t2 values (@v) /*!*/; DELIMITER ; diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 8467a18aa6b..1c155cd0876 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -1,14 +1,182 @@ -SET BINLOG_FORMAT=STATEMENT; -CREATE TABLE t1 (a CHAR(40)); -CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); -CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; -INSERT INTO t1 SELECT UUID(); -Warnings: -Warning 1592 Statement is not safe to log in statement format. -SHOW WARNINGS; -Level Warning -Code 1592 -Message Statement is not safe to log in statement format. -DROP TABLE t1,t2,t3; +==== Setup tables ==== +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a CHAR(40)); +CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE trigger_table (a CHAR(7)); +CREATE TABLE trigger_table2 (a INT); +==== Non-deterministic statements ==== +INSERT DELAYED INTO t1 VALUES (5); +==== Some variables that *should* be unsafe ==== +---- Insert directly ---- +INSERT INTO t1 VALUES (@@global.sync_binlog); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +INSERT INTO t1 VALUES (@@session.insert_id); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +INSERT INTO t1 VALUES (@@global.auto_increment_increment); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +INSERT INTO t2 SELECT UUID(); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +INSERT INTO t2 VALUES (@@global.init_slave); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +INSERT INTO t2 VALUES (@@hostname); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +---- Insert from stored procedure ---- +CREATE PROCEDURE proc() +BEGIN +INSERT INTO t1 VALUES (@@global.sync_binlog); +INSERT INTO t1 VALUES (@@session.insert_id); +INSERT INTO t1 VALUES (@@global.auto_increment_increment); +INSERT INTO t2 SELECT UUID(); +INSERT INTO t2 VALUES (@@global.init_slave); +INSERT INTO t2 VALUES (@@hostname); +END| +CALL proc(); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +---- Insert from stored function ---- +CREATE FUNCTION func() +RETURNS INT +BEGIN +INSERT INTO t1 VALUES (@@global.sync_binlog); +INSERT INTO t1 VALUES (@@session.insert_id); +INSERT INTO t1 VALUES (@@global.auto_increment_increment); +INSERT INTO t2 SELECT UUID(); +INSERT INTO t2 VALUES (@@global.init_slave); +INSERT INTO t2 VALUES (@@hostname); +RETURN 0; +END| +SELECT func(); +func() +0 +Warnings: +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +---- Insert from trigger ---- +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN +INSERT INTO t1 VALUES (@@global.sync_binlog); +INSERT INTO t1 VALUES (@@session.insert_id); +INSERT INTO t1 VALUES (@@global.auto_increment_increment); +INSERT INTO t2 SELECT UUID(); +INSERT INTO t2 VALUES (@@global.init_slave); +INSERT INTO t2 VALUES (@@hostname); +END| +INSERT INTO trigger_table VALUES ('bye.'); +Warnings: +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +---- Insert from prepared statement ---- +PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)'; +PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)'; +PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)'; +PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()'; +PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)'; +PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@hostname)'; +EXECUTE p1; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +EXECUTE p2; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +EXECUTE p3; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +EXECUTE p4; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +EXECUTE p5; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +EXECUTE p6; +Warnings: +Warning 1592 Statement is not safe to log in statement format. +---- Insert from nested call of triggers / functions / procedures ---- +CREATE PROCEDURE proc1() +INSERT INTO trigger_table VALUES ('ha!')| +CREATE FUNCTION func2() +RETURNS INT +BEGIN +CALL proc1(); +RETURN 0; +END| +CREATE TRIGGER trig3 +BEFORE INSERT ON trigger_table2 +FOR EACH ROW +BEGIN +DECLARE tmp INT; +SELECT func2() INTO tmp; +END| +CREATE PROCEDURE proc4() +INSERT INTO trigger_table2 VALUES (1)| +CREATE FUNCTION func5() +RETURNS INT +BEGIN +CALL proc4; +RETURN 0; +END| +PREPARE prep6 FROM 'SELECT func5()'| +EXECUTE prep6; +func5() +0 +Warnings: +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. +==== Variables that should *not* be unsafe ==== +INSERT INTO t1 VALUES (@@session.pseudo_thread_id); +INSERT INTO t1 VALUES (@@session.pseudo_thread_id); +INSERT INTO t1 VALUES (@@session.foreign_key_checks); +INSERT INTO t1 VALUES (@@session.sql_auto_is_null); +INSERT INTO t1 VALUES (@@session.unique_checks); +INSERT INTO t2 VALUES (@@session.sql_mode); +INSERT INTO t1 VALUES (@@session.auto_increment_increment); +INSERT INTO t1 VALUES (@@session.auto_increment_offset); +INSERT INTO t2 VALUES (@@session.character_set_client); +INSERT INTO t2 VALUES (@@session.collation_connection); +INSERT INTO t2 VALUES (@@session.collation_server); +INSERT INTO t2 VALUES (@@session.time_zone); +INSERT INTO t2 VALUES (@@session.lc_time_names); +INSERT INTO t2 VALUES (@@session.collation_database); +INSERT INTO t2 VALUES (@@session.timestamp); +INSERT INTO t2 VALUES (@@session.last_insert_id); +SET @my_var= 4711; +INSERT INTO t1 VALUES (@my_var); +SET insert_id=12; +INSERT INTO t3 VALUES (NULL); +==== Clean up ==== +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP PROCEDURE proc1; +DROP FUNCTION func2; +DROP TRIGGER trig3; +DROP PROCEDURE proc4; +DROP FUNCTION func5; +DROP PREPARE prep6; +DROP TABLE t1, t2, t3, trigger_table, trigger_table2; DROP VIEW v1; diff --git a/mysql-test/suite/binlog/t/binlog_base64_flag.test b/mysql-test/suite/binlog/t/binlog_base64_flag.test index 01f98b8a134..a25c6b68420 100644 --- a/mysql-test/suite/binlog/t/binlog_base64_flag.test +++ b/mysql-test/suite/binlog/t/binlog_base64_flag.test @@ -70,7 +70,7 @@ select * from t1; --echo ==== Test --base64-output=never on a binlog with row events ==== # mysqlbinlog should fail ---replace_regex /#[0-9][0-9][0-9][0-9][0-9][0-9] .*/#/ +--replace_regex /#[0-9][0-9][0-9][0-9][0-9][0-9] .*/<#>/ /SET \@\@session.pseudo_thread_id.*/<#>/ error 1; exec $MYSQL_BINLOG --base64-output=never suite/binlog/std_data/bug32407.001; # the above line should output the query log event and then stop diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index fa2a5eac128..5aa20a6947d 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -1,18 +1,233 @@ -# Test to check that a warning is generated for unsafe statements -# executed under statement mode logging. +# ==== Purpose ==== +# +# Some statements can not be written to the binlog in a safe manner +# with statement-based replication, either because they rely on +# features that are local to the server they are replicated from +# (e.g., @@variables), or because they include nondeterministic +# queries (e.g., LIMIT), or because the time at which the query is +# executed cannot be determined (e.g., INSERT DELAYED). Such +# statements should be marked unsafe. All unsafe statements should +# give a warning. +# +# This test verifies that a warning is generated for statements that +# should be unsafe, when they are executed under statement mode +# logging. +# +# All variables should be unsafe, with some exceptions. Therefore, +# this test also verifies that the exceptions do *not* generare a +# warning. +# +# +# ==== Method ==== +# +# We try an INSERT DELAYED statement and verify that a warning is +# issued. +# +# We try to insert unsafe variables into a table in several ways: +# directly with an INSERT statement, from a stored procedure, from a +# stored function, from a trigger, from a prepared statement, and from +# a complicated nesting of triggers, functions, procedures, and +# prepared statements. In all cases, a warning should be issued. +# +# We try to insert the variables that should not be unsafe into a +# table, and verify that *no* warning is issued. +# +# +# ==== Related bugs and worklogs ==== +# +# WL#3339: Issue warnings when statement-based replication may fail +# BUG#31168: @@hostname does not replicate +# BUG#34732: mysqlbinlog does not print default values for auto_increment variables +# BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed +# +# +# ==== Related test cases ==== +# +# rpl.rpl_variables verifies that variables which cannot be replicated +# safely in statement mode are replicated correctly in mixed or row +# mode. +# +# rpl.rpl_variables_stm tests the small subset of variables that +# actually can be replicated safely in statement mode. +# +# +# ==== Todo ==== +# +# There are several other ways to create unsafe statements: see, e.g., +# WL#3339, BUG#34768. source include/have_log_bin.inc; +source include/have_binlog_format_statement.inc; -SET BINLOG_FORMAT=STATEMENT; +--echo ==== Setup tables ==== -CREATE TABLE t1 (a CHAR(40)); -CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY); -CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); -CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (a CHAR(40)); +CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE trigger_table (a CHAR(7)); +CREATE TABLE trigger_table2 (a INT); -INSERT INTO t1 SELECT UUID(); -query_vertical SHOW WARNINGS; -DROP TABLE t1,t2,t3; -DROP VIEW v1; +--echo ==== Non-deterministic statements ==== +INSERT DELAYED INTO t1 VALUES (5); + + +--echo ==== Some variables that *should* be unsafe ==== + +--echo ---- Insert directly ---- + +INSERT INTO t1 VALUES (@@global.sync_binlog); +INSERT INTO t1 VALUES (@@session.insert_id); +INSERT INTO t1 VALUES (@@global.auto_increment_increment); +INSERT INTO t2 SELECT UUID(); +INSERT INTO t2 VALUES (@@global.init_slave); +INSERT INTO t2 VALUES (@@hostname); + +--echo ---- Insert from stored procedure ---- + +DELIMITER |; +CREATE PROCEDURE proc() +BEGIN + INSERT INTO t1 VALUES (@@global.sync_binlog); + INSERT INTO t1 VALUES (@@session.insert_id); + INSERT INTO t1 VALUES (@@global.auto_increment_increment); + INSERT INTO t2 SELECT UUID(); + INSERT INTO t2 VALUES (@@global.init_slave); + INSERT INTO t2 VALUES (@@hostname); +END| +DELIMITER ;| + +CALL proc(); + +--echo ---- Insert from stored function ---- + +DELIMITER |; +CREATE FUNCTION func() +RETURNS INT +BEGIN + INSERT INTO t1 VALUES (@@global.sync_binlog); + INSERT INTO t1 VALUES (@@session.insert_id); + INSERT INTO t1 VALUES (@@global.auto_increment_increment); + INSERT INTO t2 SELECT UUID(); + INSERT INTO t2 VALUES (@@global.init_slave); + INSERT INTO t2 VALUES (@@hostname); + RETURN 0; +END| +DELIMITER ;| + +SELECT func(); + +--echo ---- Insert from trigger ---- + +DELIMITER |; +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN + INSERT INTO t1 VALUES (@@global.sync_binlog); + INSERT INTO t1 VALUES (@@session.insert_id); + INSERT INTO t1 VALUES (@@global.auto_increment_increment); + INSERT INTO t2 SELECT UUID(); + INSERT INTO t2 VALUES (@@global.init_slave); + INSERT INTO t2 VALUES (@@hostname); +END| +DELIMITER ;| + +INSERT INTO trigger_table VALUES ('bye.'); + +--echo ---- Insert from prepared statement ---- + +PREPARE p1 FROM 'INSERT INTO t1 VALUES (@@global.sync_binlog)'; +PREPARE p2 FROM 'INSERT INTO t1 VALUES (@@session.insert_id)'; +PREPARE p3 FROM 'INSERT INTO t1 VALUES (@@global.auto_increment_increment)'; +PREPARE p4 FROM 'INSERT INTO t2 SELECT UUID()'; +PREPARE p5 FROM 'INSERT INTO t2 VALUES (@@global.init_slave)'; +PREPARE p6 FROM 'INSERT INTO t2 VALUES (@@hostname)'; + +EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6; + +--echo ---- Insert from nested call of triggers / functions / procedures ---- + +DELIMITER |; + +# proc1: cause trigger 'trig' above to be triggered. +CREATE PROCEDURE proc1() + INSERT INTO trigger_table VALUES ('ha!')| + +# func2: call proc1 above. +CREATE FUNCTION func2() +RETURNS INT +BEGIN + CALL proc1(); + RETURN 0; +END| + +# trig3: call func2 above +CREATE TRIGGER trig3 +BEFORE INSERT ON trigger_table2 +FOR EACH ROW +BEGIN + DECLARE tmp INT; + SELECT func2() INTO tmp; +END| + +# proc4: cause trig3 above to be triggered. +CREATE PROCEDURE proc4() + INSERT INTO trigger_table2 VALUES (1)| + +# func5: call proc4 above. +CREATE FUNCTION func5() +RETURNS INT +BEGIN + CALL proc4; + RETURN 0; +END| + +# prep6: call func5() above. +PREPARE prep6 FROM 'SELECT func5()'| + +DELIMITER ;| + +# try a complicated call path to trigger 'trig'. +EXECUTE prep6; + + +--echo ==== Variables that should *not* be unsafe ==== + +INSERT INTO t1 VALUES (@@session.pseudo_thread_id); +INSERT INTO t1 VALUES (@@session.pseudo_thread_id); +INSERT INTO t1 VALUES (@@session.foreign_key_checks); +INSERT INTO t1 VALUES (@@session.sql_auto_is_null); +INSERT INTO t1 VALUES (@@session.unique_checks); +INSERT INTO t2 VALUES (@@session.sql_mode); +INSERT INTO t1 VALUES (@@session.auto_increment_increment); +INSERT INTO t1 VALUES (@@session.auto_increment_offset); +INSERT INTO t2 VALUES (@@session.character_set_client); +INSERT INTO t2 VALUES (@@session.collation_connection); +INSERT INTO t2 VALUES (@@session.collation_server); +INSERT INTO t2 VALUES (@@session.time_zone); +INSERT INTO t2 VALUES (@@session.lc_time_names); +INSERT INTO t2 VALUES (@@session.collation_database); +INSERT INTO t2 VALUES (@@session.timestamp); +INSERT INTO t2 VALUES (@@session.last_insert_id); +SET @my_var= 4711; +INSERT INTO t1 VALUES (@my_var); + +# using insert_id implicitly should be ok. +SET insert_id=12; +INSERT INTO t3 VALUES (NULL); + + +--echo ==== Clean up ==== + +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP PROCEDURE proc1; +DROP FUNCTION func2; +DROP TRIGGER trig3; +DROP PROCEDURE proc4; +DROP FUNCTION func5; +DROP PREPARE prep6; +DROP TABLE t1, t2, t3, trigger_table, trigger_table2; diff --git a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result index 1d5b118d822..2d9d801047b 100644 --- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result +++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result @@ -158,10 +158,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT) /*!*/; DELIMITER ; @@ -176,10 +180,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE IF EXISTS t1,t2,t3 /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -281,10 +289,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; DROP TABLE IF EXISTS t1,t2,t3 /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -308,10 +320,14 @@ DELIMITER /*!*/; ROLLBACK/*!*/; use test/*!*/; SET TIMESTAMP=1000000000/*!*/; +SET @@session.pseudo_thread_id=999999999/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT) /*!*/; DELIMITER ; diff --git a/mysql-test/suite/rpl/r/rpl_skip_error.result b/mysql-test/suite/rpl/r/rpl_skip_error.result index aacc89f7a47..0421df81b42 100644 --- a/mysql-test/suite/rpl/r/rpl_skip_error.result +++ b/mysql-test/suite/rpl/r/rpl_skip_error.result @@ -4,31 +4,33 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +==== Test Without sql_mode=strict_trans_tables ==== +[on master] create table t1 (n int not null primary key); +[on slave] insert into t1 values (1); +[on master] insert into t1 values (1); insert into t1 values (2),(3); +[on slave] select * from t1 ORDER BY n; n 1 2 3 -drop table t1; -create table t1(a int primary key); -insert into t1 values (1),(2); -delete from t1 where @@server_id=1; +==== Test With sql_mode=strict_trans_tables ==== +insert into t1 values (7), (8); +[on master] set sql_mode=strict_trans_tables; -select @@server_id; -@@server_id -1 -insert into t1 values (1),(2),(3); -select @@server_id; -@@server_id -2 +insert into t1 values (7), (8), (9); +[on slave] select * from t1; -a +n 1 2 +3 +7 +8 SHOW SLAVE STATUS; Slave_IO_State # Master_Host 127.0.0.1 @@ -68,4 +70,5 @@ Last_IO_Errno # Last_IO_Error # Last_SQL_Errno 0 Last_SQL_Error +==== Clean Up ==== drop table t1; diff --git a/mysql-test/suite/rpl/r/rpl_variables.result b/mysql-test/suite/rpl/r/rpl_variables.result new file mode 100644 index 00000000000..73354666b7a --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_variables.result @@ -0,0 +1,584 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +==== Initialization ==== +[on master] +SET @m_default_week_format= @@global.default_week_format; +SET @m_init_slave= @@global.init_slave; +SET @m_lc_time_names= @@global.lc_time_names; +SET @m_low_priority_updates= @@global.low_priority_updates; +SET @m_relay_log_purge= @@global.relay_log_purge; +SET @m_slave_exec_mode= @@global.slave_exec_mode; +SET @m_sql_mode= @@global.sql_mode; +SET @m_sync_binlog= @@global.sync_binlog; +[on slave] +SET @s_default_week_format= @@global.default_week_format; +SET @s_init_slave= @@global.init_slave; +SET @s_lc_time_names= @@global.lc_time_names; +SET @s_low_priority_updates= @@global.low_priority_updates; +SET @s_relay_log_purge= @@global.relay_log_purge; +SET @s_slave_exec_mode= @@global.slave_exec_mode; +SET @s_sql_mode= @@global.sql_mode; +SET @s_sync_binlog= @@global.sync_binlog; +SET @@global.relay_log_purge = OFF; +SET @@global.sync_binlog = 1000000; +SET @@global.slave_exec_mode = 'STRICT'; +SET @@sql_big_selects = OFF; +SET @@last_insert_id = 10; +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = OFF; +SET @@global.default_week_format = 1; +SET @@local.default_week_format = 2; +SET @@global.lc_time_names = 'zh_HK'; +SET @@local.lc_time_names = 'zh_TW'; +SET @@global.sql_mode = 'ALLOW_INVALID_DATES'; +SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE'; +SET @user_num = 10; +SET @user_text = 'Alunda'; +[on master] +**** Resetting master and slave **** +STOP SLAVE; +RESET SLAVE; +RESET MASTER; +START SLAVE; +[on slave] +SET @@global.init_slave = 'ant'; +[on master] +CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY, +truth BOOLEAN, +num INT, +text VARCHAR(100)); +CREATE TABLE tproc LIKE tstmt; +CREATE TABLE tfunc LIKE tstmt; +CREATE TABLE ttrig LIKE tstmt; +CREATE TABLE tprep LIKE tstmt; +CREATE TABLE trigger_table (text CHAR(4)); +==== Insert variables directly ==== +---- global variables ---- +SET @@global.relay_log_purge = ON; +INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); +SET @@global.relay_log_purge = OFF; +INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); +SET @@global.sync_binlog = 2000000; +INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); +SET @@global.sync_binlog = 3000000; +INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); +SET @@global.init_slave = 'bison'; +INSERT INTO tstmt(text) VALUES (@@global.init_slave); +SET @@global.init_slave = 'cat'; +INSERT INTO tstmt(text) VALUES (@@global.init_slave); +SET @@global.slave_exec_mode = 'IDEMPOTENT'; +INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); +SET @@global.slave_exec_mode = 'STRICT'; +INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); +---- session variables ---- +SET @@sql_big_selects = ON; +INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); +SET @@sql_big_selects = OFF; +INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); +SET @@last_insert_id = 20; +INSERT INTO tstmt(num) VALUES (@@last_insert_id); +SET @@last_insert_id = 30; +INSERT INTO tstmt(num) VALUES (@@last_insert_id); +---- global and session variables ---- +SET @@global.low_priority_updates = ON; +SET @@local.low_priority_updates = OFF; +INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = ON; +INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); +SET @@global.default_week_format = 3; +SET @@local.default_week_format = 4; +INSERT INTO tstmt(num) VALUES (@@global.default_week_format); +INSERT INTO tstmt(num) VALUES (@@local.default_week_format); +SET @@global.default_week_format = 5; +SET @@local.default_week_format = 6; +INSERT INTO tstmt(num) VALUES (@@global.default_week_format); +INSERT INTO tstmt(num) VALUES (@@local.default_week_format); +SET @@global.lc_time_names = 'sv_SE'; +SET @@local.lc_time_names = 'sv_FI'; +INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); +INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); +SET @@global.lc_time_names = 'ar_TN'; +SET @@local.lc_time_names = 'ar_IQ'; +INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); +INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); +SET @@global.sql_mode = ''; +SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; +INSERT INTO tstmt(text) VALUES (@@global.sql_mode); +INSERT INTO tstmt(text) VALUES (@@local.sql_mode); +SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; +SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; +INSERT INTO tstmt(text) VALUES (@@global.sql_mode); +INSERT INTO tstmt(text) VALUES (@@local.sql_mode); +---- user variables ---- +SET @user_num = 20; +INSERT INTO tstmt(num) VALUES (@user_num); +SET @user_num = 30; +INSERT INTO tstmt(num) VALUES (@user_num); +SET @user_text = 'Bergsbrunna'; +INSERT INTO tstmt(text) VALUES (@user_text); +SET @user_text = 'Centrum'; +INSERT INTO tstmt(text) VALUES (@user_text); +==== Insert variables from a stored procedure ==== +CREATE PROCEDURE proc() +BEGIN +# GLOBAL +# boolean +SET @@global.relay_log_purge = ON; +INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); +SET @@global.relay_log_purge = OFF; +INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); +# numeric +SET @@global.sync_binlog = 2000000; +INSERT INTO tproc(num) VALUES (@@global.sync_binlog); +SET @@global.sync_binlog = 3000000; +INSERT INTO tproc(num) VALUES (@@global.sync_binlog); +# string +SET @@global.init_slave = 'bison'; +INSERT INTO tproc(text) VALUES (@@global.init_slave); +SET @@global.init_slave = 'cat'; +INSERT INTO tproc(text) VALUES (@@global.init_slave); +# enumeration +SET @@global.slave_exec_mode = 'IDEMPOTENT'; +INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); +SET @@global.slave_exec_mode = 'STRICT'; +INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); +# SESSION +# boolean +SET @@sql_big_selects = ON; +INSERT INTO tproc(truth) VALUES (@@sql_big_selects); +SET @@sql_big_selects = OFF; +INSERT INTO tproc(truth) VALUES (@@sql_big_selects); +# numeric +SET @@last_insert_id = 20; +INSERT INTO tproc(num) VALUES (@@last_insert_id); +SET @@last_insert_id = 30; +INSERT INTO tproc(num) VALUES (@@last_insert_id); +# BOTH +# boolean +SET @@global.low_priority_updates = ON; +SET @@local.low_priority_updates = OFF; +INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = ON; +INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); +# numeric +SET @@global.default_week_format = 3; +SET @@local.default_week_format = 4; +INSERT INTO tproc(num) VALUES (@@global.default_week_format); +INSERT INTO tproc(num) VALUES (@@local.default_week_format); +SET @@global.default_week_format = 5; +SET @@local.default_week_format = 6; +INSERT INTO tproc(num) VALUES (@@global.default_week_format); +INSERT INTO tproc(num) VALUES (@@local.default_week_format); +# text +SET @@global.lc_time_names = 'sv_SE'; +SET @@local.lc_time_names = 'sv_FI'; +INSERT INTO tproc(text) VALUES (@@global.lc_time_names); +INSERT INTO tproc(text) VALUES (@@local.lc_time_names); +SET @@global.lc_time_names = 'ar_TN'; +SET @@local.lc_time_names = 'ar_IQ'; +INSERT INTO tproc(text) VALUES (@@global.lc_time_names); +INSERT INTO tproc(text) VALUES (@@local.lc_time_names); +# enum +SET @@global.sql_mode = ''; +SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; +INSERT INTO tproc(text) VALUES (@@global.sql_mode); +INSERT INTO tproc(text) VALUES (@@local.sql_mode); +SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; +SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; +INSERT INTO tproc(text) VALUES (@@global.sql_mode); +INSERT INTO tproc(text) VALUES (@@local.sql_mode); +# USER +# numeric +SET @user_num = 20; +INSERT INTO tproc(num) VALUES (@user_num); +SET @user_num = 30; +INSERT INTO tproc(num) VALUES (@user_num); +# string +SET @user_text = 'Bergsbrunna'; +INSERT INTO tproc(text) VALUES (@user_text); +SET @user_text = 'Centrum'; +INSERT INTO tproc(text) VALUES (@user_text); +END| +CALL proc(); +==== Insert variables from a stored function ==== +CREATE FUNCTION func() +RETURNS INT +BEGIN +# GLOBAL +# boolean +SET @@global.relay_log_purge = ON; +INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); +SET @@global.relay_log_purge = OFF; +INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); +# numeric +SET @@global.sync_binlog = 2000000; +INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); +SET @@global.sync_binlog = 3000000; +INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); +# string +SET @@global.init_slave = 'bison'; +INSERT INTO tfunc(text) VALUES (@@global.init_slave); +SET @@global.init_slave = 'cat'; +INSERT INTO tfunc(text) VALUES (@@global.init_slave); +# enumeration +SET @@global.slave_exec_mode = 'IDEMPOTENT'; +INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); +SET @@global.slave_exec_mode = 'STRICT'; +INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); +# SESSION +# boolean +SET @@sql_big_selects = ON; +INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); +SET @@sql_big_selects = OFF; +INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); +# numeric +SET @@last_insert_id = 20; +INSERT INTO tfunc(num) VALUES (@@last_insert_id); +SET @@last_insert_id = 30; +INSERT INTO tfunc(num) VALUES (@@last_insert_id); +# BOTH +# boolean +SET @@global.low_priority_updates = ON; +SET @@local.low_priority_updates = OFF; +INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = ON; +INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); +# numeric +SET @@global.default_week_format = 3; +SET @@local.default_week_format = 4; +INSERT INTO tfunc(num) VALUES (@@global.default_week_format); +INSERT INTO tfunc(num) VALUES (@@local.default_week_format); +SET @@global.default_week_format = 5; +SET @@local.default_week_format = 6; +INSERT INTO tfunc(num) VALUES (@@global.default_week_format); +INSERT INTO tfunc(num) VALUES (@@local.default_week_format); +# text +SET @@global.lc_time_names = 'sv_SE'; +SET @@local.lc_time_names = 'sv_FI'; +INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); +INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); +SET @@global.lc_time_names = 'ar_TN'; +SET @@local.lc_time_names = 'ar_IQ'; +INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); +INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); +# enum +SET @@global.sql_mode = ''; +SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; +INSERT INTO tfunc(text) VALUES (@@global.sql_mode); +INSERT INTO tfunc(text) VALUES (@@local.sql_mode); +SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; +SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; +INSERT INTO tfunc(text) VALUES (@@global.sql_mode); +INSERT INTO tfunc(text) VALUES (@@local.sql_mode); +# USER +# numeric +SET @user_num = 20; +INSERT INTO tfunc(num) VALUES (@user_num); +SET @user_num = 30; +INSERT INTO tfunc(num) VALUES (@user_num); +# string +SET @user_text = 'Bergsbrunna'; +INSERT INTO tfunc(text) VALUES (@user_text); +SET @user_text = 'Centrum'; +INSERT INTO tfunc(text) VALUES (@user_text); +RETURN 0; +END| +SELECT func(); +func() +0 +==== Insert variables from a trigger ==== +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN +# GLOBAL +# boolean +SET @@global.relay_log_purge = ON; +INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); +SET @@global.relay_log_purge = OFF; +INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); +# numeric +SET @@global.sync_binlog = 2000000; +INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); +SET @@global.sync_binlog = 3000000; +INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); +# string +SET @@global.init_slave = 'bison'; +INSERT INTO ttrig(text) VALUES (@@global.init_slave); +SET @@global.init_slave = 'cat'; +INSERT INTO ttrig(text) VALUES (@@global.init_slave); +# enumeration +SET @@global.slave_exec_mode = 'IDEMPOTENT'; +INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); +SET @@global.slave_exec_mode = 'STRICT'; +INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); +# SESSION +# boolean +SET @@sql_big_selects = ON; +INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); +SET @@sql_big_selects = OFF; +INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); +# numeric +SET @@last_insert_id = 20; +INSERT INTO ttrig(num) VALUES (@@last_insert_id); +SET @@last_insert_id = 30; +INSERT INTO ttrig(num) VALUES (@@last_insert_id); +# BOTH +# boolean +SET @@global.low_priority_updates = ON; +SET @@local.low_priority_updates = OFF; +INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); +INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = ON; +INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); +INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); +# numeric +SET @@global.default_week_format = 3; +SET @@local.default_week_format = 4; +INSERT INTO ttrig(num) VALUES (@@global.default_week_format); +INSERT INTO ttrig(num) VALUES (@@local.default_week_format); +SET @@global.default_week_format = 5; +SET @@local.default_week_format = 6; +INSERT INTO ttrig(num) VALUES (@@global.default_week_format); +INSERT INTO ttrig(num) VALUES (@@local.default_week_format); +# text +SET @@global.lc_time_names = 'sv_SE'; +SET @@local.lc_time_names = 'sv_FI'; +INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); +INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); +SET @@global.lc_time_names = 'ar_TN'; +SET @@local.lc_time_names = 'ar_IQ'; +INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); +INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); +# enum +SET @@global.sql_mode = ''; +SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; +INSERT INTO ttrig(text) VALUES (@@global.sql_mode); +INSERT INTO ttrig(text) VALUES (@@local.sql_mode); +SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; +SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; +INSERT INTO ttrig(text) VALUES (@@global.sql_mode); +INSERT INTO ttrig(text) VALUES (@@local.sql_mode); +# USER +# numeric +SET @user_num = 20; +INSERT INTO ttrig(num) VALUES (@user_num); +SET @user_num = 30; +INSERT INTO ttrig(num) VALUES (@user_num); +# string +SET @user_text = 'Bergsbrunna'; +INSERT INTO ttrig(text) VALUES (@user_text); +SET @user_text = 'Centrum'; +INSERT INTO ttrig(text) VALUES (@user_text); +END| +INSERT INTO trigger_table VALUES ('bye.'); +==== Insert variables from a prepared statement ==== +PREPARE p1 FROM 'SET @@global.relay_log_purge = ON'; +PREPARE p2 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; +PREPARE p3 FROM 'SET @@global.relay_log_purge = OFF'; +PREPARE p4 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; +PREPARE p5 FROM 'SET @@global.sync_binlog = 2000000'; +PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; +PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000'; +PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; +PREPARE p9 FROM 'SET @@global.init_slave = \'bison\''; +PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; +PREPARE p11 FROM 'SET @@global.init_slave = \'cat\''; +PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; +PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\''; +PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; +PREPARE p15 FROM 'SET @@global.slave_exec_mode = \'STRICT\''; +PREPARE p16 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; +PREPARE p17 FROM 'SET @@sql_big_selects = ON'; +PREPARE p18 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; +PREPARE p19 FROM 'SET @@sql_big_selects = OFF'; +PREPARE p20 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; +PREPARE p21 FROM 'SET @@last_insert_id = 20'; +PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; +PREPARE p23 FROM 'SET @@last_insert_id = 30'; +PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; +PREPARE p25 FROM 'SET @@global.low_priority_updates = ON'; +PREPARE p26 FROM 'SET @@local.low_priority_updates = OFF'; +PREPARE p27 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; +PREPARE p28 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; +PREPARE p29 FROM 'SET @@global.low_priority_updates = OFF'; +PREPARE p30 FROM 'SET @@local.low_priority_updates = ON'; +PREPARE p31 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; +PREPARE p32 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; +PREPARE p33 FROM 'SET @@global.default_week_format = 3'; +PREPARE p34 FROM 'SET @@local.default_week_format = 4'; +PREPARE p35 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; +PREPARE p36 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; +PREPARE p37 FROM 'SET @@global.default_week_format = 5'; +PREPARE p38 FROM 'SET @@local.default_week_format = 6'; +PREPARE p39 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; +PREPARE p40 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; +PREPARE p41 FROM 'SET @@global.lc_time_names = \'sv_SE\''; +PREPARE p42 FROM 'SET @@local.lc_time_names = \'sv_FI\''; +PREPARE p43 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; +PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; +PREPARE p45 FROM 'SET @@global.lc_time_names = \'ar_TN\''; +PREPARE p46 FROM 'SET @@local.lc_time_names = \'ar_IQ\''; +PREPARE p47 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; +PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; +PREPARE p49 FROM 'SET @@global.sql_mode = \'\''; +PREPARE p50 FROM 'SET @@local.sql_mode = \'IGNORE_SPACE,NO_AUTO_CREATE_USER\''; +PREPARE p51 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; +PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; +PREPARE p53 FROM 'SET @@global.sql_mode = \'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION\''; +PREPARE p54 FROM 'SET @@local.sql_mode = \'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS\''; +PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; +PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; +PREPARE p57 FROM 'SET @user_num = 20'; +PREPARE p58 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; +PREPARE p59 FROM 'SET @user_num = 30'; +PREPARE p60 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; +PREPARE p61 FROM 'SET @user_text = \'Bergsbrunna\''; +PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; +PREPARE p63 FROM 'SET @user_text = \'Centrum\''; +PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; +EXECUTE p1; +EXECUTE p2; +EXECUTE p3; +EXECUTE p4; +EXECUTE p5; +EXECUTE p6; +EXECUTE p7; +EXECUTE p8; +EXECUTE p9; +EXECUTE p10; +EXECUTE p11; +EXECUTE p12; +EXECUTE p13; +EXECUTE p14; +EXECUTE p15; +EXECUTE p16; +EXECUTE p17; +EXECUTE p18; +EXECUTE p19; +EXECUTE p20; +EXECUTE p21; +EXECUTE p22; +EXECUTE p23; +EXECUTE p24; +EXECUTE p25; +EXECUTE p26; +EXECUTE p27; +EXECUTE p28; +EXECUTE p29; +EXECUTE p30; +EXECUTE p31; +EXECUTE p32; +EXECUTE p33; +EXECUTE p34; +EXECUTE p35; +EXECUTE p36; +EXECUTE p37; +EXECUTE p38; +EXECUTE p39; +EXECUTE p40; +EXECUTE p41; +EXECUTE p42; +EXECUTE p43; +EXECUTE p44; +EXECUTE p45; +EXECUTE p46; +EXECUTE p47; +EXECUTE p48; +EXECUTE p49; +EXECUTE p50; +EXECUTE p51; +EXECUTE p52; +EXECUTE p53; +EXECUTE p54; +EXECUTE p55; +EXECUTE p56; +EXECUTE p57; +EXECUTE p58; +EXECUTE p59; +EXECUTE p60; +EXECUTE p61; +EXECUTE p62; +EXECUTE p63; +EXECUTE p64; +==== Results ==== +SELECT * FROM tstmt ORDER BY id; +id truth num text +1 1 NULL NULL +2 0 NULL NULL +3 NULL 2000000 NULL +4 NULL 3000000 NULL +5 NULL NULL bison +6 NULL NULL cat +7 NULL NULL IDEMPOTENT +8 NULL NULL STRICT +9 1 NULL NULL +10 0 NULL NULL +11 NULL 20 NULL +12 NULL 30 NULL +13 1 NULL NULL +14 0 NULL NULL +15 0 NULL NULL +16 1 NULL NULL +17 NULL 3 NULL +18 NULL 4 NULL +19 NULL 5 NULL +20 NULL 6 NULL +21 NULL NULL sv_SE +22 NULL NULL sv_FI +23 NULL NULL ar_TN +24 NULL NULL ar_IQ +25 NULL NULL +26 NULL NULL IGNORE_SPACE,NO_AUTO_CREATE_USER +27 NULL NULL NO_DIR_IN_CREATE,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_ENGINE_SUBSTITUTION +28 NULL NULL NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS +29 NULL 20 NULL +30 NULL 30 NULL +31 NULL NULL Bergsbrunna +32 NULL NULL Centrum +Comparing tables master:test.tstmt and master:test.tproc +Comparing tables master:test.tstmt and master:test.tfunc +Comparing tables master:test.tstmt and master:test.ttrig +Comparing tables master:test.tstmt and master:test.tprep +Comparing tables master:test.tstmt and slave:test.tstmt +Comparing tables master:test.tstmt and slave:test.tproc +Comparing tables master:test.tstmt and slave:test.tfunc +Comparing tables master:test.tstmt and slave:test.ttrig +Comparing tables master:test.tstmt and slave:test.tprep +==== Clean up ==== +[on master] +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table; +SET @@global.default_week_format= @m_default_week_format; +SET @@global.init_slave= @m_init_slave; +SET @@global.lc_time_names= @m_lc_time_names; +SET @@global.low_priority_updates= @m_low_priority_updates; +SET @@global.relay_log_purge= @m_relay_log_purge; +SET @@global.slave_exec_mode= @m_slave_exec_mode; +SET @@global.sql_mode= @m_sql_mode; +SET @@global.sync_binlog= @m_sync_binlog; +[on slave] +SET @@global.default_week_format= @s_default_week_format; +SET @@global.init_slave= @s_init_slave; +SET @@global.lc_time_names= @s_lc_time_names; +SET @@global.low_priority_updates= @s_low_priority_updates; +SET @@global.relay_log_purge= @s_relay_log_purge; +SET @@global.slave_exec_mode= @s_slave_exec_mode; +SET @@global.sql_mode= @s_sql_mode; +SET @@global.sync_binlog= @s_sync_binlog; diff --git a/mysql-test/suite/rpl/r/rpl_variables_stm.result b/mysql-test/suite/rpl/r/rpl_variables_stm.result new file mode 100644 index 00000000000..18b90b1264d --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_variables_stm.result @@ -0,0 +1,526 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +==== Initialization ==== +[on master] +SET @m_pseudo_thread_id= @@global.pseudo_thread_id; +SET @m_auto_increment_increment= @@global.auto_increment_increment; +SET @m_auto_increment_offset= @@global.auto_increment_offset; +SET @m_character_set_client= @@global.character_set_client; +SET @m_collation_connection= @@global.collation_connection; +SET @m_collation_server= @@global.collation_server; +SET @m_time_zone= @@global.time_zone; +SET @m_lc_time_names= @@global.lc_time_names; +SET @m_collation_database= @@global.collation_database; +[on slave] +SET @s_pseudo_thread_id= @@global.pseudo_thread_id; +SET @s_auto_increment_increment= @@global.auto_increment_increment; +SET @s_auto_increment_offset= @@global.auto_increment_offset; +SET @s_character_set_client= @@global.character_set_client; +SET @s_collation_connection= @@global.collation_connection; +SET @s_collation_server= @@global.collation_server; +SET @s_time_zone= @@global.time_zone; +SET @s_lc_time_names= @@global.lc_time_names; +SET @s_collation_database= @@global.collation_database; +SET @@global.pseudo_thread_id= 4711; +SET @@global.auto_increment_increment=19; +SET @@global.auto_increment_offset=4; +SET @@global.character_set_client='eucjpms'; +SET @@global.collation_connection='eucjpms_bin'; +SET @@global.collation_server='geostd8_general_ci'; +SET @@global.time_zone='Japan'; +SET @@global.lc_time_names='sv_SE'; +SET @@global.collation_database='geostd8_bin'; +[on master] +CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY, +num INT, +text VARCHAR(100)); +CREATE TABLE tproc LIKE tstmt; +CREATE TABLE tfunc LIKE tstmt; +CREATE TABLE ttrig LIKE tstmt; +CREATE TABLE tprep LIKE tstmt; +CREATE TABLE trigger_table (text CHAR(4)); +==== Insert variables directly ==== +SET @@pseudo_thread_id= 4712; +INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id); +SET @@pseudo_thread_id= 4713; +INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id); +SET @@foreign_key_checks= 0; +INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks); +SET @@foreign_key_checks= 1; +INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks); +SET @@sql_auto_is_null= 0; +INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null); +SET @@sql_auto_is_null= 1; +INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null); +SET @@unique_checks= 0; +INSERT INTO tstmt(num) VALUES (@@session.unique_checks); +SET @@unique_checks= 1; +INSERT INTO tstmt(num) VALUES (@@session.unique_checks); +SET @@auto_increment_increment= 11; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_increment= 19; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_offset= 13; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_offset= 17; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_increment= 1; +SET @@auto_increment_offset= 1; +SET @@character_set_client= 'cp1257'; +INSERT INTO tstmt(text) VALUES (@@session.character_set_client); +SET @@character_set_client= 'cp1256'; +INSERT INTO tstmt(text) VALUES (@@session.character_set_client); +SET @@collation_connection= 'cp1251_ukrainian_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_connection); +INSERT INTO tstmt(text) VALUES (@@session.character_set_connection); +SET @@collation_connection= 'cp1251_bulgarian_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_connection); +INSERT INTO tstmt(text) VALUES (@@session.character_set_connection); +SET @@collation_server= 'latin7_bin'; +INSERT INTO tstmt(text) VALUES (@@session.collation_server); +INSERT INTO tstmt(text) VALUES (@@session.character_set_server); +SET @@collation_server= 'latin7_general_cs'; +INSERT INTO tstmt(text) VALUES (@@session.collation_server); +INSERT INTO tstmt(text) VALUES (@@session.character_set_server); +SET @@time_zone= 'Europe/Moscow'; +INSERT INTO tstmt(text) VALUES (@@session.time_zone); +SET @@time_zone= 'Universal'; +INSERT INTO tstmt(text) VALUES (@@session.time_zone); +SET @@lc_time_names= 'sv_FI'; +INSERT INTO tstmt(text) VALUES (@@session.lc_time_names); +SET @@lc_time_names= 'no_NO'; +INSERT INTO tstmt(text) VALUES (@@session.lc_time_names); +SET @@collation_database= 'latin7_general_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_database); +INSERT INTO tstmt(text) VALUES (@@session.character_set_database); +SET @@collation_database= 'latin7_estonian_cs'; +INSERT INTO tstmt(text) VALUES (@@session.collation_database); +INSERT INTO tstmt(text) VALUES (@@session.character_set_database); +SET @@timestamp= 47114711; +INSERT INTO tstmt(text) VALUES (@@session.timestamp); +SET @@timestamp= 47124712; +INSERT INTO tstmt(text) VALUES (@@session.timestamp); +SET @@last_insert_id= 1616; +INSERT INTO tstmt(text) VALUES (@@session.last_insert_id); +SET @@last_insert_id= 1717; +INSERT INTO tstmt(text) VALUES (@@session.last_insert_id); +==== Insert variables from a stored procedure ==== +CREATE PROCEDURE proc() +BEGIN +SET @@pseudo_thread_id= 4712; +INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id); +SET @@pseudo_thread_id= 4713; +INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id); +SET @@foreign_key_checks= 0; +INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks); +SET @@foreign_key_checks= 1; +INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks); +SET @@sql_auto_is_null= 0; +INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null); +SET @@sql_auto_is_null= 1; +INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null); +SET @@unique_checks= 0; +INSERT INTO tproc(num) VALUES (@@session.unique_checks); +SET @@unique_checks= 1; +INSERT INTO tproc(num) VALUES (@@session.unique_checks); +SET @@auto_increment_increment= 11; +INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_increment= 19; +INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_offset= 13; +INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_offset= 17; +INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset); +# reset these as they affect the index column +SET @@auto_increment_increment= 1; +SET @@auto_increment_offset= 1; +SET @@character_set_client= 'cp1257'; +INSERT INTO tproc(text) VALUES (@@session.character_set_client); +SET @@character_set_client= 'cp1256'; +INSERT INTO tproc(text) VALUES (@@session.character_set_client); +SET @@collation_connection= 'cp1251_ukrainian_ci'; +INSERT INTO tproc(text) VALUES (@@session.collation_connection); +INSERT INTO tproc(text) VALUES (@@session.character_set_connection); +SET @@collation_connection= 'cp1251_bulgarian_ci'; +INSERT INTO tproc(text) VALUES (@@session.collation_connection); +INSERT INTO tproc(text) VALUES (@@session.character_set_connection); +SET @@collation_server= 'latin7_bin'; +INSERT INTO tproc(text) VALUES (@@session.collation_server); +INSERT INTO tproc(text) VALUES (@@session.character_set_server); +SET @@collation_server= 'latin7_general_cs'; +INSERT INTO tproc(text) VALUES (@@session.collation_server); +INSERT INTO tproc(text) VALUES (@@session.character_set_server); +SET @@time_zone= 'Europe/Moscow'; +INSERT INTO tproc(text) VALUES (@@session.time_zone); +SET @@time_zone= 'Universal'; +INSERT INTO tproc(text) VALUES (@@session.time_zone); +SET @@lc_time_names= 'sv_FI'; +INSERT INTO tproc(text) VALUES (@@session.lc_time_names); +SET @@lc_time_names= 'no_NO'; +INSERT INTO tproc(text) VALUES (@@session.lc_time_names); +SET @@collation_database= 'latin7_general_ci'; +INSERT INTO tproc(text) VALUES (@@session.collation_database); +INSERT INTO tproc(text) VALUES (@@session.character_set_database); +SET @@collation_database= 'latin7_estonian_cs'; +INSERT INTO tproc(text) VALUES (@@session.collation_database); +INSERT INTO tproc(text) VALUES (@@session.character_set_database); +SET @@timestamp= 47114711; +INSERT INTO tproc(text) VALUES (@@session.timestamp); +SET @@timestamp= 47124712; +INSERT INTO tproc(text) VALUES (@@session.timestamp); +SET @@last_insert_id= 1616; +INSERT INTO tproc(text) VALUES (@@session.last_insert_id); +SET @@last_insert_id= 1717; +INSERT INTO tproc(text) VALUES (@@session.last_insert_id); +END| +CALL proc(); +==== Insert variables from a stored function ==== +CREATE FUNCTION func() +RETURNS INT +BEGIN +SET @@pseudo_thread_id= 4712; +INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id); +SET @@pseudo_thread_id= 4713; +INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id); +SET @@foreign_key_checks= 0; +INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks); +SET @@foreign_key_checks= 1; +INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks); +SET @@sql_auto_is_null= 0; +INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null); +SET @@sql_auto_is_null= 1; +INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null); +SET @@unique_checks= 0; +INSERT INTO tfunc(num) VALUES (@@session.unique_checks); +SET @@unique_checks= 1; +INSERT INTO tfunc(num) VALUES (@@session.unique_checks); +SET @@auto_increment_increment= 11; +INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_increment= 19; +INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_offset= 13; +INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_offset= 17; +INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset); +# reset these as they affect the index column +SET @@auto_increment_increment= 1; +SET @@auto_increment_offset= 1; +SET @@character_set_client= 'cp1257'; +INSERT INTO tfunc(text) VALUES (@@session.character_set_client); +SET @@character_set_client= 'cp1256'; +INSERT INTO tfunc(text) VALUES (@@session.character_set_client); +SET @@collation_connection= 'cp1251_ukrainian_ci'; +INSERT INTO tfunc(text) VALUES (@@session.collation_connection); +INSERT INTO tfunc(text) VALUES (@@session.character_set_connection); +SET @@collation_connection= 'cp1251_bulgarian_ci'; +INSERT INTO tfunc(text) VALUES (@@session.collation_connection); +INSERT INTO tfunc(text) VALUES (@@session.character_set_connection); +SET @@collation_server= 'latin7_bin'; +INSERT INTO tfunc(text) VALUES (@@session.collation_server); +INSERT INTO tfunc(text) VALUES (@@session.character_set_server); +SET @@collation_server= 'latin7_general_cs'; +INSERT INTO tfunc(text) VALUES (@@session.collation_server); +INSERT INTO tfunc(text) VALUES (@@session.character_set_server); +SET @@time_zone= 'Europe/Moscow'; +INSERT INTO tfunc(text) VALUES (@@session.time_zone); +SET @@time_zone= 'Universal'; +INSERT INTO tfunc(text) VALUES (@@session.time_zone); +SET @@lc_time_names= 'sv_FI'; +INSERT INTO tfunc(text) VALUES (@@session.lc_time_names); +SET @@lc_time_names= 'no_NO'; +INSERT INTO tfunc(text) VALUES (@@session.lc_time_names); +SET @@collation_database= 'latin7_general_ci'; +INSERT INTO tfunc(text) VALUES (@@session.collation_database); +INSERT INTO tfunc(text) VALUES (@@session.character_set_database); +SET @@collation_database= 'latin7_estonian_cs'; +INSERT INTO tfunc(text) VALUES (@@session.collation_database); +INSERT INTO tfunc(text) VALUES (@@session.character_set_database); +SET @@timestamp= 47114711; +INSERT INTO tfunc(text) VALUES (@@session.timestamp); +SET @@timestamp= 47124712; +INSERT INTO tfunc(text) VALUES (@@session.timestamp); +SET @@last_insert_id= 1616; +INSERT INTO tfunc(text) VALUES (@@session.last_insert_id); +SET @@last_insert_id= 1717; +INSERT INTO tfunc(text) VALUES (@@session.last_insert_id); +RETURN 0; +END| +SELECT func(); +func() +0 +==== Insert variables from a trigger ==== +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN +SET @@pseudo_thread_id= 4712; +INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id); +SET @@pseudo_thread_id= 4713; +INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id); +SET @@foreign_key_checks= 0; +INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks); +SET @@foreign_key_checks= 1; +INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks); +SET @@sql_auto_is_null= 0; +INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null); +SET @@sql_auto_is_null= 1; +INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null); +SET @@unique_checks= 0; +INSERT INTO ttrig(num) VALUES (@@session.unique_checks); +SET @@unique_checks= 1; +INSERT INTO ttrig(num) VALUES (@@session.unique_checks); +SET @@auto_increment_increment= 11; +INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_increment= 19; +INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_offset= 13; +INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_offset= 17; +INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset); +# reset these as they affect the index column +SET @@auto_increment_increment= 1; +SET @@auto_increment_offset= 1; +SET @@character_set_client= 'cp1257'; +INSERT INTO ttrig(text) VALUES (@@session.character_set_client); +SET @@character_set_client= 'cp1256'; +INSERT INTO ttrig(text) VALUES (@@session.character_set_client); +SET @@collation_connection= 'cp1251_ukrainian_ci'; +INSERT INTO ttrig(text) VALUES (@@session.collation_connection); +INSERT INTO ttrig(text) VALUES (@@session.character_set_connection); +SET @@collation_connection= 'cp1251_bulgarian_ci'; +INSERT INTO ttrig(text) VALUES (@@session.collation_connection); +INSERT INTO ttrig(text) VALUES (@@session.character_set_connection); +SET @@collation_server= 'latin7_bin'; +INSERT INTO ttrig(text) VALUES (@@session.collation_server); +INSERT INTO ttrig(text) VALUES (@@session.character_set_server); +SET @@collation_server= 'latin7_general_cs'; +INSERT INTO ttrig(text) VALUES (@@session.collation_server); +INSERT INTO ttrig(text) VALUES (@@session.character_set_server); +SET @@time_zone= 'Europe/Moscow'; +INSERT INTO ttrig(text) VALUES (@@session.time_zone); +SET @@time_zone= 'Universal'; +INSERT INTO ttrig(text) VALUES (@@session.time_zone); +SET @@lc_time_names= 'sv_FI'; +INSERT INTO ttrig(text) VALUES (@@session.lc_time_names); +SET @@lc_time_names= 'no_NO'; +INSERT INTO ttrig(text) VALUES (@@session.lc_time_names); +SET @@collation_database= 'latin7_general_ci'; +INSERT INTO ttrig(text) VALUES (@@session.collation_database); +INSERT INTO ttrig(text) VALUES (@@session.character_set_database); +SET @@collation_database= 'latin7_estonian_cs'; +INSERT INTO ttrig(text) VALUES (@@session.collation_database); +INSERT INTO ttrig(text) VALUES (@@session.character_set_database); +SET @@timestamp= 47114711; +INSERT INTO ttrig(text) VALUES (@@session.timestamp); +SET @@timestamp= 47124712; +INSERT INTO ttrig(text) VALUES (@@session.timestamp); +SET @@last_insert_id= 1616; +INSERT INTO ttrig(text) VALUES (@@session.last_insert_id); +SET @@last_insert_id= 1717; +INSERT INTO ttrig(text) VALUES (@@session.last_insert_id); +END| +INSERT INTO trigger_table VALUES ('bye.'); +==== Insert variables from a prepared statement ==== +PREPARE p1 FROM 'SET @@pseudo_thread_id= 4712'; +PREPARE p2 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)'; +PREPARE p3 FROM 'SET @@pseudo_thread_id= 4713'; +PREPARE p4 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)'; +PREPARE p5 FROM 'SET @@foreign_key_checks= 0'; +PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)'; +PREPARE p7 FROM 'SET @@foreign_key_checks= 1'; +PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)'; +PREPARE p9 FROM 'SET @@sql_auto_is_null= 0'; +PREPARE p10 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)'; +PREPARE p11 FROM 'SET @@sql_auto_is_null= 1'; +PREPARE p12 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)'; +PREPARE p13 FROM 'SET @@unique_checks= 0'; +PREPARE p14 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)'; +PREPARE p15 FROM 'SET @@unique_checks= 1'; +PREPARE p16 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)'; +PREPARE p17 FROM 'SET @@auto_increment_increment= 11'; +PREPARE p18 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)'; +PREPARE p19 FROM 'SET @@auto_increment_increment= 19'; +PREPARE p20 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)'; +PREPARE p21 FROM 'SET @@auto_increment_offset= 13'; +PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)'; +PREPARE p23 FROM 'SET @@auto_increment_offset= 17'; +PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)'; +PREPARE p25 FROM 'SET @@auto_increment_increment= 1'; +PREPARE p26 FROM 'SET @@auto_increment_offset= 1'; +PREPARE p27 FROM 'SET @@character_set_client= \'cp1257\''; +PREPARE p28 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)'; +PREPARE p29 FROM 'SET @@character_set_client= \'cp1256\''; +PREPARE p30 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)'; +PREPARE p31 FROM 'SET @@collation_connection= \'cp1251_ukrainian_ci\''; +PREPARE p32 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)'; +PREPARE p33 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)'; +PREPARE p34 FROM 'SET @@collation_connection= \'cp1251_bulgarian_ci\''; +PREPARE p35 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)'; +PREPARE p36 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)'; +PREPARE p37 FROM 'SET @@collation_server= \'latin7_bin\''; +PREPARE p38 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)'; +PREPARE p39 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)'; +PREPARE p40 FROM 'SET @@collation_server= \'latin7_general_cs\''; +PREPARE p41 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)'; +PREPARE p42 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)'; +PREPARE p43 FROM 'SET @@time_zone= \'Europe/Moscow\''; +PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)'; +PREPARE p45 FROM 'SET @@time_zone= \'Universal\''; +PREPARE p46 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)'; +PREPARE p47 FROM 'SET @@lc_time_names= \'sv_FI\''; +PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)'; +PREPARE p49 FROM 'SET @@lc_time_names= \'no_NO\''; +PREPARE p50 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)'; +PREPARE p51 FROM 'SET @@collation_database= \'latin7_general_ci\''; +PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)'; +PREPARE p53 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)'; +PREPARE p54 FROM 'SET @@collation_database= \'latin7_estonian_cs\''; +PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)'; +PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)'; +PREPARE p57 FROM 'SET @@timestamp= 47114711'; +PREPARE p58 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)'; +PREPARE p59 FROM 'SET @@timestamp= 47124712'; +PREPARE p60 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)'; +PREPARE p61 FROM 'SET @@last_insert_id= 1616'; +PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)'; +PREPARE p63 FROM 'SET @@last_insert_id= 1717'; +PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)'; +EXECUTE p1; +EXECUTE p2; +EXECUTE p3; +EXECUTE p4; +EXECUTE p5; +EXECUTE p6; +EXECUTE p7; +EXECUTE p8; +EXECUTE p9; +EXECUTE p10; +EXECUTE p11; +EXECUTE p12; +EXECUTE p13; +EXECUTE p14; +EXECUTE p15; +EXECUTE p16; +EXECUTE p17; +EXECUTE p18; +EXECUTE p19; +EXECUTE p20; +EXECUTE p21; +EXECUTE p22; +EXECUTE p23; +EXECUTE p24; +EXECUTE p25; +EXECUTE p26; +EXECUTE p27; +EXECUTE p28; +EXECUTE p29; +EXECUTE p30; +EXECUTE p31; +EXECUTE p32; +EXECUTE p33; +EXECUTE p34; +EXECUTE p35; +EXECUTE p36; +EXECUTE p37; +EXECUTE p38; +EXECUTE p39; +EXECUTE p40; +EXECUTE p41; +EXECUTE p42; +EXECUTE p43; +EXECUTE p44; +EXECUTE p45; +EXECUTE p46; +EXECUTE p47; +EXECUTE p48; +EXECUTE p49; +EXECUTE p50; +EXECUTE p51; +EXECUTE p52; +EXECUTE p53; +EXECUTE p54; +EXECUTE p55; +EXECUTE p56; +EXECUTE p57; +EXECUTE p58; +EXECUTE p59; +EXECUTE p60; +EXECUTE p61; +EXECUTE p62; +EXECUTE p63; +EXECUTE p64; +==== Results ==== +SELECT * FROM tstmt ORDER BY id; +id num text +1 4712 NULL +2 4713 NULL +3 0 NULL +4 1 NULL +5 0 NULL +6 1 NULL +7 0 NULL +8 1 NULL +12 11 NULL +20 19 NULL +32 13 NULL +36 17 NULL +37 NULL cp1257 +38 NULL cp1256 +39 NULL cp1251_ukrainian_ci +40 NULL cp1251 +41 NULL cp1251_bulgarian_ci +42 NULL cp1251 +43 NULL latin7_bin +44 NULL latin7 +45 NULL latin7_general_cs +46 NULL latin7 +47 NULL Europe/Moscow +48 NULL Universal +49 NULL sv_FI +50 NULL no_NO +51 NULL latin7_general_ci +52 NULL latin7 +53 NULL latin7_estonian_cs +54 NULL latin7 +55 NULL 47114711 +56 NULL 47124712 +57 NULL 1616 +58 NULL 1717 +Comparing tables master:test.tstmt and master:test.tproc +Comparing tables master:test.tstmt and master:test.tfunc +Comparing tables master:test.tstmt and master:test.ttrig +Comparing tables master:test.tstmt and master:test.tprep +Comparing tables master:test.tstmt and slave:test.tstmt +Comparing tables master:test.tstmt and slave:test.tproc +Comparing tables master:test.tstmt and slave:test.tfunc +Comparing tables master:test.tstmt and slave:test.ttrig +Comparing tables master:test.tstmt and slave:test.tprep +==== Clean up ==== +[on master] +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table; +SET @@global.pseudo_thread_id= @m_pseudo_thread_id; +SET @@global.auto_increment_increment= @m_auto_increment_increment; +SET @@global.auto_increment_offset= @m_auto_increment_offset; +SET @@global.character_set_client= @m_character_set_client; +SET @@global.collation_connection= @m_collation_connection; +SET @@global.collation_server= @m_collation_server; +SET @@global.time_zone= @m_time_zone; +SET @@global.lc_time_names= @m_lc_time_names; +SET @@global.collation_database= @m_collation_database; +[on slave] +SET @@global.pseudo_thread_id= @s_pseudo_thread_id; +SET @@global.auto_increment_increment= @s_auto_increment_increment; +SET @@global.auto_increment_offset= @s_auto_increment_offset; +SET @@global.character_set_client= @s_character_set_client; +SET @@global.collation_connection= @s_collation_connection; +SET @@global.collation_server= @s_collation_server; +SET @@global.time_zone= @s_time_zone; +SET @@global.lc_time_names= @s_lc_time_names; +SET @@global.collation_database= @s_collation_database; diff --git a/mysql-test/suite/rpl/t/rpl_skip_error.test b/mysql-test/suite/rpl/t/rpl_skip_error.test index efd46f4fa09..8129cdec25a 100644 --- a/mysql-test/suite/rpl/t/rpl_skip_error.test +++ b/mysql-test/suite/rpl/t/rpl_skip_error.test @@ -1,57 +1,66 @@ -########################################## -# 2006-02-07 By JBM: Added order by -######################################### -# Note that errors are ignored by opt file. +# ==== Purpose ==== +# +# Verify that --slave-skip-errors works correctly. The error messages +# specified by --slave-skip-errors on slave should be ignored. If +# such errors occur, they should not be reported and not cause the +# slave to stop. +# +# ==== Method ==== +# +# We run the slave with --slave-skip-errors=1062 (the code for +# duplicate key). On slave, we insert value 1 in a table, and then, +# on master, we insert value 1 in the table. The error should be +# ignored on slave. +# +# ==== Related bugs ==== +# +# BUG#28839: Errors in strict mode silently stop SQL thread if --slave-skip-errors exists +# bug in this test: BUG#30594: rpl.rpl_skip_error is nondeterministic + source include/master-slave.inc; source include/have_binlog_format_mixed_or_statement.inc; -# -# Bug #30594 -# Skipping error due to applying Row-based repliation events -# should be checked with another test file -# consider names like rpl_row_skip_error -# +--echo ==== Test Without sql_mode=strict_trans_tables ==== + +--echo [on master] create table t1 (n int not null primary key); -save_master_pos; -connection slave; -sync_with_master; + +--echo [on slave] +sync_slave_with_master; insert into t1 values (1); + +--echo [on master] connection master; # Here we expect (ignored) error, since 1 is already in slave table insert into t1 values (1); - # These should work fine insert into t1 values (2),(3); -save_master_pos; -connection slave; -sync_with_master; +--echo [on slave] +sync_slave_with_master; select * from t1 ORDER BY n; -# Cleanup -connection master; -drop table t1; -sync_slave_with_master; -# End of 4.1 tests +--echo ==== Test With sql_mode=strict_trans_tables ==== -# -# #28839 Errors in strict mode silently stop SQL thread if --slave-skip-errors exists -# +insert into t1 values (7), (8); + +--echo [on master] connection master; -create table t1(a int primary key); -insert into t1 values (1),(2); -delete from t1 where @@server_id=1; set sql_mode=strict_trans_tables; -select @@server_id; -insert into t1 values (1),(2),(3); +insert into t1 values (7), (8), (9); + +--echo [on slave] sync_slave_with_master; -connection slave; -select @@server_id; select * from t1; source include/show_slave_status2.inc; + + +--echo ==== Clean Up ==== + connection master; drop table t1; -sync_with_master; +sync_slave_with_master; + # End of 5.0 tests diff --git a/mysql-test/suite/rpl/t/rpl_variables.test b/mysql-test/suite/rpl/t/rpl_variables.test new file mode 100644 index 00000000000..3669e14c5df --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_variables.test @@ -0,0 +1,739 @@ +# ==== Purpose ==== +# +# Test that queries referencing variables are replicated correctly in +# mixed and row-based logging mode. +# +# +# ==== Method ==== +# +# The test simply does a lot of "INSERT INTO t1 VALUES (@@variable)" +# and checks the result on the slave. +# +# Statements referencing a variable only replicate correctly in mixed +# and row mode: in row mode, the values inserted are replicated. In +# mixed mode, statements referencing a variable are marked as unsafe, +# meaning they will be replicated by row. In statement mode, the +# slave's value will be used and replication will break. (Except in a +# small number of special cases: random seeds, insert_id, and +# auto_increment are replicated). +# +# We test the following variable scopes: +# - server system variables +# - server session variables +# - server "both" variables +# - user variables +# +# For each scope, we use variables of the following types if they +# exist: +# - boolean +# - numeric +# - string +# - enumeration variables +# +# We use these types of variables in the following contexts: +# - directly +# - from a stored procedure +# - from a stored function +# - from a trigger +# - from a prepared statement +# +# For all variables where it is possible, we set the variable to one +# value on slave, and insert it on the master with two distinct +# values. +# +# The same insertions are made in four different tables using direct +# insert, stored procedure, stored function, or trigger. Then all +# eight resulting tables on master and slave are compared. +# +# +# ==== Related bugs ==== +# +# BUG#31168: @@hostname does not replicate +# +# +# ==== Related test cases ==== +# +# binlog.binlog_unsafe tests that a warning is issued if system +# variables are replicated in statement mode. +# +# rpl.rpl_variables_stm tests the small subset of variables that +# actually can be replicated safely in statement mode. + + +source include/master-slave.inc; +source include/have_binlog_format_mixed_or_row.inc; + + +--echo ==== Initialization ==== + +# Backup the values of global variables so that they can be restored +# later. +--echo [on master] +connection master; +SET @m_default_week_format= @@global.default_week_format; +SET @m_init_slave= @@global.init_slave; +SET @m_lc_time_names= @@global.lc_time_names; +SET @m_low_priority_updates= @@global.low_priority_updates; +SET @m_relay_log_purge= @@global.relay_log_purge; +SET @m_slave_exec_mode= @@global.slave_exec_mode; +SET @m_sql_mode= @@global.sql_mode; +SET @m_sync_binlog= @@global.sync_binlog; + +--echo [on slave] +connection slave; +SET @s_default_week_format= @@global.default_week_format; +SET @s_init_slave= @@global.init_slave; +SET @s_lc_time_names= @@global.lc_time_names; +SET @s_low_priority_updates= @@global.low_priority_updates; +SET @s_relay_log_purge= @@global.relay_log_purge; +SET @s_slave_exec_mode= @@global.slave_exec_mode; +SET @s_sql_mode= @@global.sql_mode; +SET @s_sync_binlog= @@global.sync_binlog; + +# Set global variables on slave to something different than on master. +SET @@global.relay_log_purge = OFF; +SET @@global.sync_binlog = 1000000; +SET @@global.slave_exec_mode = 'STRICT'; +SET @@sql_big_selects = OFF; +SET @@last_insert_id = 10; +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = OFF; +SET @@global.default_week_format = 1; +SET @@local.default_week_format = 2; +SET @@global.lc_time_names = 'zh_HK'; +SET @@local.lc_time_names = 'zh_TW'; +SET @@global.sql_mode = 'ALLOW_INVALID_DATES'; +SET @@local.sql_mode = 'ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE'; +SET @user_num = 10; +SET @user_text = 'Alunda'; + +# Stop slave so that we get a fresh sql thread, reading the slave's +# global values of variables into its local copies. +--echo [on master] +connection master; +source include/reset_master_and_slave.inc; + +# We would have wanted to set this together with the other variables +# above, but can't because it affects how the slave works. +--echo [on slave] +connection slave; +SET @@global.init_slave = 'ant'; + + +--echo [on master] +connection master; + +# Tables where everything happens. +CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY, + truth BOOLEAN, + num INT, + text VARCHAR(100)); +CREATE TABLE tproc LIKE tstmt; +CREATE TABLE tfunc LIKE tstmt; +CREATE TABLE ttrig LIKE tstmt; +CREATE TABLE tprep LIKE tstmt; + +# Table on which we put a trigger. +CREATE TABLE trigger_table (text CHAR(4)); + + +--echo ==== Insert variables directly ==== + +--echo ---- global variables ---- + +# boolean +SET @@global.relay_log_purge = ON; +INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); +SET @@global.relay_log_purge = OFF; +INSERT INTO tstmt(truth) VALUES (@@global.relay_log_purge); + +# numeric +SET @@global.sync_binlog = 2000000; +INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); +SET @@global.sync_binlog = 3000000; +INSERT INTO tstmt(num) VALUES (@@global.sync_binlog); + +# string +SET @@global.init_slave = 'bison'; +INSERT INTO tstmt(text) VALUES (@@global.init_slave); +SET @@global.init_slave = 'cat'; +INSERT INTO tstmt(text) VALUES (@@global.init_slave); + +# enumeration +SET @@global.slave_exec_mode = 'IDEMPOTENT'; +INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); +SET @@global.slave_exec_mode = 'STRICT'; +INSERT INTO tstmt(text) VALUES (@@global.slave_exec_mode); + + +--echo ---- session variables ---- + +# boolean +SET @@sql_big_selects = ON; +INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); +SET @@sql_big_selects = OFF; +INSERT INTO tstmt(truth) VALUES (@@sql_big_selects); + +# numeric +SET @@last_insert_id = 20; +INSERT INTO tstmt(num) VALUES (@@last_insert_id); +SET @@last_insert_id = 30; +INSERT INTO tstmt(num) VALUES (@@last_insert_id); + +--echo ---- global and session variables ---- + +# boolean +SET @@global.low_priority_updates = ON; +SET @@local.low_priority_updates = OFF; +INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); +SET @@global.low_priority_updates = OFF; +SET @@local.low_priority_updates = ON; +INSERT INTO tstmt(truth) VALUES (@@global.low_priority_updates); +INSERT INTO tstmt(truth) VALUES (@@local.low_priority_updates); + +# numeric +SET @@global.default_week_format = 3; +SET @@local.default_week_format = 4; +INSERT INTO tstmt(num) VALUES (@@global.default_week_format); +INSERT INTO tstmt(num) VALUES (@@local.default_week_format); +SET @@global.default_week_format = 5; +SET @@local.default_week_format = 6; +INSERT INTO tstmt(num) VALUES (@@global.default_week_format); +INSERT INTO tstmt(num) VALUES (@@local.default_week_format); + +# string +SET @@global.lc_time_names = 'sv_SE'; +SET @@local.lc_time_names = 'sv_FI'; +INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); +INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); +SET @@global.lc_time_names = 'ar_TN'; +SET @@local.lc_time_names = 'ar_IQ'; +INSERT INTO tstmt(text) VALUES (@@global.lc_time_names); +INSERT INTO tstmt(text) VALUES (@@local.lc_time_names); + +# enum +SET @@global.sql_mode = ''; +SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; +INSERT INTO tstmt(text) VALUES (@@global.sql_mode); +INSERT INTO tstmt(text) VALUES (@@local.sql_mode); +SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; +SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; +INSERT INTO tstmt(text) VALUES (@@global.sql_mode); +INSERT INTO tstmt(text) VALUES (@@local.sql_mode); + +--echo ---- user variables ---- + +# numeric +SET @user_num = 20; +INSERT INTO tstmt(num) VALUES (@user_num); +SET @user_num = 30; +INSERT INTO tstmt(num) VALUES (@user_num); + +# string +SET @user_text = 'Bergsbrunna'; +INSERT INTO tstmt(text) VALUES (@user_text); +SET @user_text = 'Centrum'; +INSERT INTO tstmt(text) VALUES (@user_text); + + +--echo ==== Insert variables from a stored procedure ==== + +DELIMITER |; +CREATE PROCEDURE proc() +BEGIN + + # GLOBAL + + # boolean + SET @@global.relay_log_purge = ON; + INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); + SET @@global.relay_log_purge = OFF; + INSERT INTO tproc(truth) VALUES (@@global.relay_log_purge); + + # numeric + SET @@global.sync_binlog = 2000000; + INSERT INTO tproc(num) VALUES (@@global.sync_binlog); + SET @@global.sync_binlog = 3000000; + INSERT INTO tproc(num) VALUES (@@global.sync_binlog); + + # string + SET @@global.init_slave = 'bison'; + INSERT INTO tproc(text) VALUES (@@global.init_slave); + SET @@global.init_slave = 'cat'; + INSERT INTO tproc(text) VALUES (@@global.init_slave); + + # enumeration + SET @@global.slave_exec_mode = 'IDEMPOTENT'; + INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); + SET @@global.slave_exec_mode = 'STRICT'; + INSERT INTO tproc(text) VALUES (@@global.slave_exec_mode); + + # SESSION + + # boolean + SET @@sql_big_selects = ON; + INSERT INTO tproc(truth) VALUES (@@sql_big_selects); + SET @@sql_big_selects = OFF; + INSERT INTO tproc(truth) VALUES (@@sql_big_selects); + + # numeric + SET @@last_insert_id = 20; + INSERT INTO tproc(num) VALUES (@@last_insert_id); + SET @@last_insert_id = 30; + INSERT INTO tproc(num) VALUES (@@last_insert_id); + + # BOTH + + # boolean + SET @@global.low_priority_updates = ON; + SET @@local.low_priority_updates = OFF; + INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); + INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); + SET @@global.low_priority_updates = OFF; + SET @@local.low_priority_updates = ON; + INSERT INTO tproc(truth) VALUES (@@global.low_priority_updates); + INSERT INTO tproc(truth) VALUES (@@local.low_priority_updates); + + # numeric + SET @@global.default_week_format = 3; + SET @@local.default_week_format = 4; + INSERT INTO tproc(num) VALUES (@@global.default_week_format); + INSERT INTO tproc(num) VALUES (@@local.default_week_format); + SET @@global.default_week_format = 5; + SET @@local.default_week_format = 6; + INSERT INTO tproc(num) VALUES (@@global.default_week_format); + INSERT INTO tproc(num) VALUES (@@local.default_week_format); + + # text + SET @@global.lc_time_names = 'sv_SE'; + SET @@local.lc_time_names = 'sv_FI'; + INSERT INTO tproc(text) VALUES (@@global.lc_time_names); + INSERT INTO tproc(text) VALUES (@@local.lc_time_names); + SET @@global.lc_time_names = 'ar_TN'; + SET @@local.lc_time_names = 'ar_IQ'; + INSERT INTO tproc(text) VALUES (@@global.lc_time_names); + INSERT INTO tproc(text) VALUES (@@local.lc_time_names); + + # enum + SET @@global.sql_mode = ''; + SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; + INSERT INTO tproc(text) VALUES (@@global.sql_mode); + INSERT INTO tproc(text) VALUES (@@local.sql_mode); + SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; + SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; + INSERT INTO tproc(text) VALUES (@@global.sql_mode); + INSERT INTO tproc(text) VALUES (@@local.sql_mode); + + # USER + + # numeric + SET @user_num = 20; + INSERT INTO tproc(num) VALUES (@user_num); + SET @user_num = 30; + INSERT INTO tproc(num) VALUES (@user_num); + + # string + SET @user_text = 'Bergsbrunna'; + INSERT INTO tproc(text) VALUES (@user_text); + SET @user_text = 'Centrum'; + INSERT INTO tproc(text) VALUES (@user_text); + +END| +DELIMITER ;| + +CALL proc(); + + +--echo ==== Insert variables from a stored function ==== + +DELIMITER |; +CREATE FUNCTION func() +RETURNS INT +BEGIN + + # GLOBAL + + # boolean + SET @@global.relay_log_purge = ON; + INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); + SET @@global.relay_log_purge = OFF; + INSERT INTO tfunc(truth) VALUES (@@global.relay_log_purge); + + # numeric + SET @@global.sync_binlog = 2000000; + INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); + SET @@global.sync_binlog = 3000000; + INSERT INTO tfunc(num) VALUES (@@global.sync_binlog); + + # string + SET @@global.init_slave = 'bison'; + INSERT INTO tfunc(text) VALUES (@@global.init_slave); + SET @@global.init_slave = 'cat'; + INSERT INTO tfunc(text) VALUES (@@global.init_slave); + + # enumeration + SET @@global.slave_exec_mode = 'IDEMPOTENT'; + INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); + SET @@global.slave_exec_mode = 'STRICT'; + INSERT INTO tfunc(text) VALUES (@@global.slave_exec_mode); + + # SESSION + + # boolean + SET @@sql_big_selects = ON; + INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); + SET @@sql_big_selects = OFF; + INSERT INTO tfunc(truth) VALUES (@@sql_big_selects); + + # numeric + SET @@last_insert_id = 20; + INSERT INTO tfunc(num) VALUES (@@last_insert_id); + SET @@last_insert_id = 30; + INSERT INTO tfunc(num) VALUES (@@last_insert_id); + + # BOTH + + # boolean + SET @@global.low_priority_updates = ON; + SET @@local.low_priority_updates = OFF; + INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); + INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); + SET @@global.low_priority_updates = OFF; + SET @@local.low_priority_updates = ON; + INSERT INTO tfunc(truth) VALUES (@@global.low_priority_updates); + INSERT INTO tfunc(truth) VALUES (@@local.low_priority_updates); + + # numeric + SET @@global.default_week_format = 3; + SET @@local.default_week_format = 4; + INSERT INTO tfunc(num) VALUES (@@global.default_week_format); + INSERT INTO tfunc(num) VALUES (@@local.default_week_format); + SET @@global.default_week_format = 5; + SET @@local.default_week_format = 6; + INSERT INTO tfunc(num) VALUES (@@global.default_week_format); + INSERT INTO tfunc(num) VALUES (@@local.default_week_format); + + # text + SET @@global.lc_time_names = 'sv_SE'; + SET @@local.lc_time_names = 'sv_FI'; + INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); + INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); + SET @@global.lc_time_names = 'ar_TN'; + SET @@local.lc_time_names = 'ar_IQ'; + INSERT INTO tfunc(text) VALUES (@@global.lc_time_names); + INSERT INTO tfunc(text) VALUES (@@local.lc_time_names); + + # enum + SET @@global.sql_mode = ''; + SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; + INSERT INTO tfunc(text) VALUES (@@global.sql_mode); + INSERT INTO tfunc(text) VALUES (@@local.sql_mode); + SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; + SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; + INSERT INTO tfunc(text) VALUES (@@global.sql_mode); + INSERT INTO tfunc(text) VALUES (@@local.sql_mode); + + # USER + + # numeric + SET @user_num = 20; + INSERT INTO tfunc(num) VALUES (@user_num); + SET @user_num = 30; + INSERT INTO tfunc(num) VALUES (@user_num); + + # string + SET @user_text = 'Bergsbrunna'; + INSERT INTO tfunc(text) VALUES (@user_text); + SET @user_text = 'Centrum'; + INSERT INTO tfunc(text) VALUES (@user_text); + + RETURN 0; +END| +DELIMITER ;| + +SELECT func(); + + +--echo ==== Insert variables from a trigger ==== + +DELIMITER |; +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN + + # GLOBAL + + # boolean + SET @@global.relay_log_purge = ON; + INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); + SET @@global.relay_log_purge = OFF; + INSERT INTO ttrig(truth) VALUES (@@global.relay_log_purge); + + # numeric + SET @@global.sync_binlog = 2000000; + INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); + SET @@global.sync_binlog = 3000000; + INSERT INTO ttrig(num) VALUES (@@global.sync_binlog); + + # string + SET @@global.init_slave = 'bison'; + INSERT INTO ttrig(text) VALUES (@@global.init_slave); + SET @@global.init_slave = 'cat'; + INSERT INTO ttrig(text) VALUES (@@global.init_slave); + + # enumeration + SET @@global.slave_exec_mode = 'IDEMPOTENT'; + INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); + SET @@global.slave_exec_mode = 'STRICT'; + INSERT INTO ttrig(text) VALUES (@@global.slave_exec_mode); + + # SESSION + + # boolean + SET @@sql_big_selects = ON; + INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); + SET @@sql_big_selects = OFF; + INSERT INTO ttrig(truth) VALUES (@@sql_big_selects); + + # numeric + SET @@last_insert_id = 20; + INSERT INTO ttrig(num) VALUES (@@last_insert_id); + SET @@last_insert_id = 30; + INSERT INTO ttrig(num) VALUES (@@last_insert_id); + + # BOTH + + # boolean + SET @@global.low_priority_updates = ON; + SET @@local.low_priority_updates = OFF; + INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); + INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); + SET @@global.low_priority_updates = OFF; + SET @@local.low_priority_updates = ON; + INSERT INTO ttrig(truth) VALUES (@@global.low_priority_updates); + INSERT INTO ttrig(truth) VALUES (@@local.low_priority_updates); + + # numeric + SET @@global.default_week_format = 3; + SET @@local.default_week_format = 4; + INSERT INTO ttrig(num) VALUES (@@global.default_week_format); + INSERT INTO ttrig(num) VALUES (@@local.default_week_format); + SET @@global.default_week_format = 5; + SET @@local.default_week_format = 6; + INSERT INTO ttrig(num) VALUES (@@global.default_week_format); + INSERT INTO ttrig(num) VALUES (@@local.default_week_format); + + # text + SET @@global.lc_time_names = 'sv_SE'; + SET @@local.lc_time_names = 'sv_FI'; + INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); + INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); + SET @@global.lc_time_names = 'ar_TN'; + SET @@local.lc_time_names = 'ar_IQ'; + INSERT INTO ttrig(text) VALUES (@@global.lc_time_names); + INSERT INTO ttrig(text) VALUES (@@local.lc_time_names); + + # enum + SET @@global.sql_mode = ''; + SET @@local.sql_mode = 'IGNORE_SPACE,NO_AUTO_CREATE_USER'; + INSERT INTO ttrig(text) VALUES (@@global.sql_mode); + INSERT INTO ttrig(text) VALUES (@@local.sql_mode); + SET @@global.sql_mode = 'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION'; + SET @@local.sql_mode = 'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS'; + INSERT INTO ttrig(text) VALUES (@@global.sql_mode); + INSERT INTO ttrig(text) VALUES (@@local.sql_mode); + + # USER + + # numeric + SET @user_num = 20; + INSERT INTO ttrig(num) VALUES (@user_num); + SET @user_num = 30; + INSERT INTO ttrig(num) VALUES (@user_num); + + # string + SET @user_text = 'Bergsbrunna'; + INSERT INTO ttrig(text) VALUES (@user_text); + SET @user_text = 'Centrum'; + INSERT INTO ttrig(text) VALUES (@user_text); +END| +DELIMITER ;| + +INSERT INTO trigger_table VALUES ('bye.'); + + +--echo ==== Insert variables from a prepared statement ==== + +# GLOBAL + +# boolean +PREPARE p1 FROM 'SET @@global.relay_log_purge = ON'; +PREPARE p2 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; +PREPARE p3 FROM 'SET @@global.relay_log_purge = OFF'; +PREPARE p4 FROM 'INSERT INTO tprep(truth) VALUES (@@global.relay_log_purge)'; + +# numeric +PREPARE p5 FROM 'SET @@global.sync_binlog = 2000000'; +PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; +PREPARE p7 FROM 'SET @@global.sync_binlog = 3000000'; +PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@global.sync_binlog)'; + +# string +PREPARE p9 FROM 'SET @@global.init_slave = \'bison\''; +PREPARE p10 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; +PREPARE p11 FROM 'SET @@global.init_slave = \'cat\''; +PREPARE p12 FROM 'INSERT INTO tprep(text) VALUES (@@global.init_slave)'; + +# enumeration +PREPARE p13 FROM 'SET @@global.slave_exec_mode = \'IDEMPOTENT\''; +PREPARE p14 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; +PREPARE p15 FROM 'SET @@global.slave_exec_mode = \'STRICT\''; +PREPARE p16 FROM 'INSERT INTO tprep(text) VALUES (@@global.slave_exec_mode)'; + +# SESSION + +# boolean +PREPARE p17 FROM 'SET @@sql_big_selects = ON'; +PREPARE p18 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; +PREPARE p19 FROM 'SET @@sql_big_selects = OFF'; +PREPARE p20 FROM 'INSERT INTO tprep(truth) VALUES (@@sql_big_selects)'; + +# numeric +PREPARE p21 FROM 'SET @@last_insert_id = 20'; +PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; +PREPARE p23 FROM 'SET @@last_insert_id = 30'; +PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@last_insert_id)'; + +# BOTH + +# boolean +PREPARE p25 FROM 'SET @@global.low_priority_updates = ON'; +PREPARE p26 FROM 'SET @@local.low_priority_updates = OFF'; +PREPARE p27 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; +PREPARE p28 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; +PREPARE p29 FROM 'SET @@global.low_priority_updates = OFF'; +PREPARE p30 FROM 'SET @@local.low_priority_updates = ON'; +PREPARE p31 FROM 'INSERT INTO tprep(truth) VALUES (@@global.low_priority_updates)'; +PREPARE p32 FROM 'INSERT INTO tprep(truth) VALUES (@@local.low_priority_updates)'; + +# numeric +PREPARE p33 FROM 'SET @@global.default_week_format = 3'; +PREPARE p34 FROM 'SET @@local.default_week_format = 4'; +PREPARE p35 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; +PREPARE p36 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; +PREPARE p37 FROM 'SET @@global.default_week_format = 5'; +PREPARE p38 FROM 'SET @@local.default_week_format = 6'; +PREPARE p39 FROM 'INSERT INTO tprep(num) VALUES (@@global.default_week_format)'; +PREPARE p40 FROM 'INSERT INTO tprep(num) VALUES (@@local.default_week_format)'; + +# text +PREPARE p41 FROM 'SET @@global.lc_time_names = \'sv_SE\''; +PREPARE p42 FROM 'SET @@local.lc_time_names = \'sv_FI\''; +PREPARE p43 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; +PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; +PREPARE p45 FROM 'SET @@global.lc_time_names = \'ar_TN\''; +PREPARE p46 FROM 'SET @@local.lc_time_names = \'ar_IQ\''; +PREPARE p47 FROM 'INSERT INTO tprep(text) VALUES (@@global.lc_time_names)'; +PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@local.lc_time_names)'; + +# enum +PREPARE p49 FROM 'SET @@global.sql_mode = \'\''; +PREPARE p50 FROM 'SET @@local.sql_mode = \'IGNORE_SPACE,NO_AUTO_CREATE_USER\''; +PREPARE p51 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; +PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; +PREPARE p53 FROM 'SET @@global.sql_mode = \'NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION\''; +PREPARE p54 FROM 'SET @@local.sql_mode = \'NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS\''; +PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@global.sql_mode)'; +PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@local.sql_mode)'; + +# USER + +# numeric +PREPARE p57 FROM 'SET @user_num = 20'; +PREPARE p58 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; +PREPARE p59 FROM 'SET @user_num = 30'; +PREPARE p60 FROM 'INSERT INTO tprep(num) VALUES (@user_num)'; + +# string +PREPARE p61 FROM 'SET @user_text = \'Bergsbrunna\''; +PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; +PREPARE p63 FROM 'SET @user_text = \'Centrum\''; +PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@user_text)'; + +EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6; +EXECUTE p7; EXECUTE p8; EXECUTE p9; EXECUTE p10; EXECUTE p11; EXECUTE p12; +EXECUTE p13; EXECUTE p14; EXECUTE p15; EXECUTE p16; EXECUTE p17; EXECUTE p18; +EXECUTE p19; EXECUTE p20; EXECUTE p21; EXECUTE p22; EXECUTE p23; EXECUTE p24; +EXECUTE p25; EXECUTE p26; EXECUTE p27; EXECUTE p28; EXECUTE p29; EXECUTE p30; +EXECUTE p31; EXECUTE p32; EXECUTE p33; EXECUTE p34; EXECUTE p35; EXECUTE p36; +EXECUTE p37; EXECUTE p38; EXECUTE p39; EXECUTE p40; EXECUTE p41; EXECUTE p42; +EXECUTE p43; EXECUTE p44; EXECUTE p45; EXECUTE p46; EXECUTE p47; EXECUTE p48; +EXECUTE p49; EXECUTE p50; EXECUTE p51; EXECUTE p52; EXECUTE p53; EXECUTE p54; +EXECUTE p55; EXECUTE p56; EXECUTE p57; EXECUTE p58; EXECUTE p59; EXECUTE p60; +EXECUTE p61; EXECUTE p62; EXECUTE p63; EXECUTE p64; + + +--echo ==== Results ==== + +# Show the result in table test.tstmt on master... +SELECT * FROM tstmt ORDER BY id; +let $diff_table_1=master:test.tstmt; + +# ... then compare test.tstmt on master to the other tables on master... +let $diff_table_2=master:test.tproc; +source include/diff_tables.inc; +let $diff_table_2=master:test.tfunc; +source include/diff_tables.inc; +let $diff_table_2=master:test.ttrig; +source include/diff_tables.inc; +let $diff_table_2=master:test.tprep; +source include/diff_tables.inc; + +# ... and to all tables on slave. +connection master; +sync_slave_with_master; +let $diff_table_2=slave:test.tstmt; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tproc; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tfunc; +source include/diff_tables.inc; +let $diff_table_2=slave:test.ttrig; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tprep; +source include/diff_tables.inc; + + +--echo ==== Clean up ==== + +--echo [on master] +connection master; +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table; + +SET @@global.default_week_format= @m_default_week_format; +SET @@global.init_slave= @m_init_slave; +SET @@global.lc_time_names= @m_lc_time_names; +SET @@global.low_priority_updates= @m_low_priority_updates; +SET @@global.relay_log_purge= @m_relay_log_purge; +SET @@global.slave_exec_mode= @m_slave_exec_mode; +SET @@global.sql_mode= @m_sql_mode; +SET @@global.sync_binlog= @m_sync_binlog; + +--echo [on slave] +connection slave; +SET @@global.default_week_format= @s_default_week_format; +SET @@global.init_slave= @s_init_slave; +SET @@global.lc_time_names= @s_lc_time_names; +SET @@global.low_priority_updates= @s_low_priority_updates; +SET @@global.relay_log_purge= @s_relay_log_purge; +SET @@global.slave_exec_mode= @s_slave_exec_mode; +SET @@global.sql_mode= @s_sql_mode; +SET @@global.sync_binlog= @s_sync_binlog; + +connection master; +sync_slave_with_master; diff --git a/mysql-test/suite/rpl/t/rpl_variables_stm.test b/mysql-test/suite/rpl/t/rpl_variables_stm.test new file mode 100644 index 00000000000..6548b9e90e3 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_variables_stm.test @@ -0,0 +1,636 @@ +# ==== Purpose ==== +# +# In general, queries referencing @@system_variables are not +# considered safe to write to the binlog in statement-based logging +# mode. However, a few special cases are supported. +# +# This test verifies that queries referencing these specially +# supported variables are replicated correctly in statement mode. +# +# +# ==== Method ==== +# +# The test simply does a lot of "INSERT INTO t1 VALUES (@@variable)" +# and checks the result on the slave. +# +# Statements referencing a variable only replicate correctly in mixed +# and row mode: in row mode, the values inserted are replicated. In +# mixed mode, statements referencing a variable are marked as unsafe, +# meaning they will be replicated by row. In statement mode, the +# slave's value will be used and replication will break. (Except in a +# small number of special cases: random seeds, insert_id, and +# auto_increment are replicated). +# +# We test all replicated variables, from each of the following +# contexts: +# - directly +# - from a stored procedure +# - from a stored function +# - from a trigger +# - from a prepared statement +# +# For all variables where it is possible, we set the variable to one +# value on slave, and insert it on the master with two distinct +# values. +# +# The same insertions are made in four different tables using direct +# insert, stored procedure, stored function, or trigger. Then all +# eight resulting tables on master and slave are compared. +# +# +# ==== Related bugs ==== +# +# BUG#31168: @@hostname does not replicate +# +# +# ==== Related test cases ==== +# +# binlog.binlog_unsafe tests that a warning is issued if system +# variables are replicated in statement mode. +# +# rpl.rpl_variables verifies that variables which cannot be replicated +# safely in statement mode are replicated correctly in mixed or row +# mode. + +source include/master-slave.inc; +source include/have_binlog_format_mixed_or_statement.inc; + + +--echo ==== Initialization ==== + +# Backup the values of global variables so that they can be restored +# later. + +--echo [on master] +connection master; +SET @m_pseudo_thread_id= @@global.pseudo_thread_id; +SET @m_auto_increment_increment= @@global.auto_increment_increment; +SET @m_auto_increment_offset= @@global.auto_increment_offset; +SET @m_character_set_client= @@global.character_set_client; +SET @m_collation_connection= @@global.collation_connection; +SET @m_collation_server= @@global.collation_server; +SET @m_time_zone= @@global.time_zone; +SET @m_lc_time_names= @@global.lc_time_names; +SET @m_collation_database= @@global.collation_database; + +--echo [on slave] +connection slave; +SET @s_pseudo_thread_id= @@global.pseudo_thread_id; +SET @s_auto_increment_increment= @@global.auto_increment_increment; +SET @s_auto_increment_offset= @@global.auto_increment_offset; +SET @s_character_set_client= @@global.character_set_client; +SET @s_collation_connection= @@global.collation_connection; +SET @s_collation_server= @@global.collation_server; +SET @s_time_zone= @@global.time_zone; +SET @s_lc_time_names= @@global.lc_time_names; +SET @s_collation_database= @@global.collation_database; + +SET @@global.pseudo_thread_id= 4711; +SET @@global.auto_increment_increment=19; +SET @@global.auto_increment_offset=4; +SET @@global.character_set_client='eucjpms'; +SET @@global.collation_connection='eucjpms_bin'; +SET @@global.collation_server='geostd8_general_ci'; +SET @@global.time_zone='Japan'; +SET @@global.lc_time_names='sv_SE'; +SET @@global.collation_database='geostd8_bin'; + +--echo [on master] +connection master; + +# Tables where everything happens. +CREATE TABLE tstmt (id INT AUTO_INCREMENT PRIMARY KEY, + num INT, + text VARCHAR(100)); +CREATE TABLE tproc LIKE tstmt; +CREATE TABLE tfunc LIKE tstmt; +CREATE TABLE ttrig LIKE tstmt; +CREATE TABLE tprep LIKE tstmt; + +# Table on which we put a trigger. +CREATE TABLE trigger_table (text CHAR(4)); + + +--echo ==== Insert variables directly ==== + +SET @@pseudo_thread_id= 4712; +INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id); +SET @@pseudo_thread_id= 4713; +INSERT INTO tstmt(num) VALUES (@@session.pseudo_thread_id); + +SET @@foreign_key_checks= 0; +INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks); +SET @@foreign_key_checks= 1; +INSERT INTO tstmt(num) VALUES (@@session.foreign_key_checks); + +SET @@sql_auto_is_null= 0; +INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null); +SET @@sql_auto_is_null= 1; +INSERT INTO tstmt(num) VALUES (@@session.sql_auto_is_null); + +SET @@unique_checks= 0; +INSERT INTO tstmt(num) VALUES (@@session.unique_checks); +SET @@unique_checks= 1; +INSERT INTO tstmt(num) VALUES (@@session.unique_checks); + +SET @@auto_increment_increment= 11; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment); +SET @@auto_increment_increment= 19; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_increment); + +SET @@auto_increment_offset= 13; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset); +SET @@auto_increment_offset= 17; +INSERT INTO tstmt(num) VALUES (@@session.auto_increment_offset); + +# reset these as they affect the index column +SET @@auto_increment_increment= 1; +SET @@auto_increment_offset= 1; + +SET @@character_set_client= 'cp1257'; +INSERT INTO tstmt(text) VALUES (@@session.character_set_client); +SET @@character_set_client= 'cp1256'; +INSERT INTO tstmt(text) VALUES (@@session.character_set_client); + +SET @@collation_connection= 'cp1251_ukrainian_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_connection); +INSERT INTO tstmt(text) VALUES (@@session.character_set_connection); +SET @@collation_connection= 'cp1251_bulgarian_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_connection); +INSERT INTO tstmt(text) VALUES (@@session.character_set_connection); + +SET @@collation_server= 'latin7_bin'; +INSERT INTO tstmt(text) VALUES (@@session.collation_server); +INSERT INTO tstmt(text) VALUES (@@session.character_set_server); +SET @@collation_server= 'latin7_general_cs'; +INSERT INTO tstmt(text) VALUES (@@session.collation_server); +INSERT INTO tstmt(text) VALUES (@@session.character_set_server); + +SET @@time_zone= 'Europe/Moscow'; +INSERT INTO tstmt(text) VALUES (@@session.time_zone); +SET @@time_zone= 'Universal'; +INSERT INTO tstmt(text) VALUES (@@session.time_zone); + +SET @@lc_time_names= 'sv_FI'; +INSERT INTO tstmt(text) VALUES (@@session.lc_time_names); +SET @@lc_time_names= 'no_NO'; +INSERT INTO tstmt(text) VALUES (@@session.lc_time_names); + +SET @@collation_database= 'latin7_general_ci'; +INSERT INTO tstmt(text) VALUES (@@session.collation_database); +INSERT INTO tstmt(text) VALUES (@@session.character_set_database); +SET @@collation_database= 'latin7_estonian_cs'; +INSERT INTO tstmt(text) VALUES (@@session.collation_database); +INSERT INTO tstmt(text) VALUES (@@session.character_set_database); + +SET @@timestamp= 47114711; +INSERT INTO tstmt(text) VALUES (@@session.timestamp); +SET @@timestamp= 47124712; +INSERT INTO tstmt(text) VALUES (@@session.timestamp); + +SET @@last_insert_id= 1616; +INSERT INTO tstmt(text) VALUES (@@session.last_insert_id); +SET @@last_insert_id= 1717; +INSERT INTO tstmt(text) VALUES (@@session.last_insert_id); + + +--echo ==== Insert variables from a stored procedure ==== + +DELIMITER |; +CREATE PROCEDURE proc() +BEGIN + + SET @@pseudo_thread_id= 4712; + INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id); + SET @@pseudo_thread_id= 4713; + INSERT INTO tproc(num) VALUES (@@session.pseudo_thread_id); + + SET @@foreign_key_checks= 0; + INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks); + SET @@foreign_key_checks= 1; + INSERT INTO tproc(num) VALUES (@@session.foreign_key_checks); + + SET @@sql_auto_is_null= 0; + INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null); + SET @@sql_auto_is_null= 1; + INSERT INTO tproc(num) VALUES (@@session.sql_auto_is_null); + + SET @@unique_checks= 0; + INSERT INTO tproc(num) VALUES (@@session.unique_checks); + SET @@unique_checks= 1; + INSERT INTO tproc(num) VALUES (@@session.unique_checks); + + SET @@auto_increment_increment= 11; + INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment); + SET @@auto_increment_increment= 19; + INSERT INTO tproc(num) VALUES (@@session.auto_increment_increment); + + SET @@auto_increment_offset= 13; + INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset); + SET @@auto_increment_offset= 17; + INSERT INTO tproc(num) VALUES (@@session.auto_increment_offset); + + # reset these as they affect the index column + SET @@auto_increment_increment= 1; + SET @@auto_increment_offset= 1; + + SET @@character_set_client= 'cp1257'; + INSERT INTO tproc(text) VALUES (@@session.character_set_client); + SET @@character_set_client= 'cp1256'; + INSERT INTO tproc(text) VALUES (@@session.character_set_client); + + SET @@collation_connection= 'cp1251_ukrainian_ci'; + INSERT INTO tproc(text) VALUES (@@session.collation_connection); + INSERT INTO tproc(text) VALUES (@@session.character_set_connection); + SET @@collation_connection= 'cp1251_bulgarian_ci'; + INSERT INTO tproc(text) VALUES (@@session.collation_connection); + INSERT INTO tproc(text) VALUES (@@session.character_set_connection); + + SET @@collation_server= 'latin7_bin'; + INSERT INTO tproc(text) VALUES (@@session.collation_server); + INSERT INTO tproc(text) VALUES (@@session.character_set_server); + SET @@collation_server= 'latin7_general_cs'; + INSERT INTO tproc(text) VALUES (@@session.collation_server); + INSERT INTO tproc(text) VALUES (@@session.character_set_server); + + SET @@time_zone= 'Europe/Moscow'; + INSERT INTO tproc(text) VALUES (@@session.time_zone); + SET @@time_zone= 'Universal'; + INSERT INTO tproc(text) VALUES (@@session.time_zone); + + SET @@lc_time_names= 'sv_FI'; + INSERT INTO tproc(text) VALUES (@@session.lc_time_names); + SET @@lc_time_names= 'no_NO'; + INSERT INTO tproc(text) VALUES (@@session.lc_time_names); + + SET @@collation_database= 'latin7_general_ci'; + INSERT INTO tproc(text) VALUES (@@session.collation_database); + INSERT INTO tproc(text) VALUES (@@session.character_set_database); + SET @@collation_database= 'latin7_estonian_cs'; + INSERT INTO tproc(text) VALUES (@@session.collation_database); + INSERT INTO tproc(text) VALUES (@@session.character_set_database); + + SET @@timestamp= 47114711; + INSERT INTO tproc(text) VALUES (@@session.timestamp); + SET @@timestamp= 47124712; + INSERT INTO tproc(text) VALUES (@@session.timestamp); + + SET @@last_insert_id= 1616; + INSERT INTO tproc(text) VALUES (@@session.last_insert_id); + SET @@last_insert_id= 1717; + INSERT INTO tproc(text) VALUES (@@session.last_insert_id); + +END| +DELIMITER ;| + +CALL proc(); + + +--echo ==== Insert variables from a stored function ==== + +DELIMITER |; +CREATE FUNCTION func() +RETURNS INT +BEGIN + + SET @@pseudo_thread_id= 4712; + INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id); + SET @@pseudo_thread_id= 4713; + INSERT INTO tfunc(num) VALUES (@@session.pseudo_thread_id); + + SET @@foreign_key_checks= 0; + INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks); + SET @@foreign_key_checks= 1; + INSERT INTO tfunc(num) VALUES (@@session.foreign_key_checks); + + SET @@sql_auto_is_null= 0; + INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null); + SET @@sql_auto_is_null= 1; + INSERT INTO tfunc(num) VALUES (@@session.sql_auto_is_null); + + SET @@unique_checks= 0; + INSERT INTO tfunc(num) VALUES (@@session.unique_checks); + SET @@unique_checks= 1; + INSERT INTO tfunc(num) VALUES (@@session.unique_checks); + + SET @@auto_increment_increment= 11; + INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment); + SET @@auto_increment_increment= 19; + INSERT INTO tfunc(num) VALUES (@@session.auto_increment_increment); + + SET @@auto_increment_offset= 13; + INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset); + SET @@auto_increment_offset= 17; + INSERT INTO tfunc(num) VALUES (@@session.auto_increment_offset); + + # reset these as they affect the index column + SET @@auto_increment_increment= 1; + SET @@auto_increment_offset= 1; + + SET @@character_set_client= 'cp1257'; + INSERT INTO tfunc(text) VALUES (@@session.character_set_client); + SET @@character_set_client= 'cp1256'; + INSERT INTO tfunc(text) VALUES (@@session.character_set_client); + + SET @@collation_connection= 'cp1251_ukrainian_ci'; + INSERT INTO tfunc(text) VALUES (@@session.collation_connection); + INSERT INTO tfunc(text) VALUES (@@session.character_set_connection); + SET @@collation_connection= 'cp1251_bulgarian_ci'; + INSERT INTO tfunc(text) VALUES (@@session.collation_connection); + INSERT INTO tfunc(text) VALUES (@@session.character_set_connection); + + SET @@collation_server= 'latin7_bin'; + INSERT INTO tfunc(text) VALUES (@@session.collation_server); + INSERT INTO tfunc(text) VALUES (@@session.character_set_server); + SET @@collation_server= 'latin7_general_cs'; + INSERT INTO tfunc(text) VALUES (@@session.collation_server); + INSERT INTO tfunc(text) VALUES (@@session.character_set_server); + + SET @@time_zone= 'Europe/Moscow'; + INSERT INTO tfunc(text) VALUES (@@session.time_zone); + SET @@time_zone= 'Universal'; + INSERT INTO tfunc(text) VALUES (@@session.time_zone); + + SET @@lc_time_names= 'sv_FI'; + INSERT INTO tfunc(text) VALUES (@@session.lc_time_names); + SET @@lc_time_names= 'no_NO'; + INSERT INTO tfunc(text) VALUES (@@session.lc_time_names); + + SET @@collation_database= 'latin7_general_ci'; + INSERT INTO tfunc(text) VALUES (@@session.collation_database); + INSERT INTO tfunc(text) VALUES (@@session.character_set_database); + SET @@collation_database= 'latin7_estonian_cs'; + INSERT INTO tfunc(text) VALUES (@@session.collation_database); + INSERT INTO tfunc(text) VALUES (@@session.character_set_database); + + SET @@timestamp= 47114711; + INSERT INTO tfunc(text) VALUES (@@session.timestamp); + SET @@timestamp= 47124712; + INSERT INTO tfunc(text) VALUES (@@session.timestamp); + + SET @@last_insert_id= 1616; + INSERT INTO tfunc(text) VALUES (@@session.last_insert_id); + SET @@last_insert_id= 1717; + INSERT INTO tfunc(text) VALUES (@@session.last_insert_id); + + RETURN 0; +END| +DELIMITER ;| + +SELECT func(); + + +--echo ==== Insert variables from a trigger ==== + +DELIMITER |; +CREATE TRIGGER trig +BEFORE INSERT ON trigger_table +FOR EACH ROW +BEGIN + + SET @@pseudo_thread_id= 4712; + INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id); + SET @@pseudo_thread_id= 4713; + INSERT INTO ttrig(num) VALUES (@@session.pseudo_thread_id); + + SET @@foreign_key_checks= 0; + INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks); + SET @@foreign_key_checks= 1; + INSERT INTO ttrig(num) VALUES (@@session.foreign_key_checks); + + SET @@sql_auto_is_null= 0; + INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null); + SET @@sql_auto_is_null= 1; + INSERT INTO ttrig(num) VALUES (@@session.sql_auto_is_null); + + SET @@unique_checks= 0; + INSERT INTO ttrig(num) VALUES (@@session.unique_checks); + SET @@unique_checks= 1; + INSERT INTO ttrig(num) VALUES (@@session.unique_checks); + + SET @@auto_increment_increment= 11; + INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment); + SET @@auto_increment_increment= 19; + INSERT INTO ttrig(num) VALUES (@@session.auto_increment_increment); + + SET @@auto_increment_offset= 13; + INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset); + SET @@auto_increment_offset= 17; + INSERT INTO ttrig(num) VALUES (@@session.auto_increment_offset); + + # reset these as they affect the index column + SET @@auto_increment_increment= 1; + SET @@auto_increment_offset= 1; + + SET @@character_set_client= 'cp1257'; + INSERT INTO ttrig(text) VALUES (@@session.character_set_client); + SET @@character_set_client= 'cp1256'; + INSERT INTO ttrig(text) VALUES (@@session.character_set_client); + + SET @@collation_connection= 'cp1251_ukrainian_ci'; + INSERT INTO ttrig(text) VALUES (@@session.collation_connection); + INSERT INTO ttrig(text) VALUES (@@session.character_set_connection); + SET @@collation_connection= 'cp1251_bulgarian_ci'; + INSERT INTO ttrig(text) VALUES (@@session.collation_connection); + INSERT INTO ttrig(text) VALUES (@@session.character_set_connection); + + SET @@collation_server= 'latin7_bin'; + INSERT INTO ttrig(text) VALUES (@@session.collation_server); + INSERT INTO ttrig(text) VALUES (@@session.character_set_server); + SET @@collation_server= 'latin7_general_cs'; + INSERT INTO ttrig(text) VALUES (@@session.collation_server); + INSERT INTO ttrig(text) VALUES (@@session.character_set_server); + + SET @@time_zone= 'Europe/Moscow'; + INSERT INTO ttrig(text) VALUES (@@session.time_zone); + SET @@time_zone= 'Universal'; + INSERT INTO ttrig(text) VALUES (@@session.time_zone); + + SET @@lc_time_names= 'sv_FI'; + INSERT INTO ttrig(text) VALUES (@@session.lc_time_names); + SET @@lc_time_names= 'no_NO'; + INSERT INTO ttrig(text) VALUES (@@session.lc_time_names); + + SET @@collation_database= 'latin7_general_ci'; + INSERT INTO ttrig(text) VALUES (@@session.collation_database); + INSERT INTO ttrig(text) VALUES (@@session.character_set_database); + SET @@collation_database= 'latin7_estonian_cs'; + INSERT INTO ttrig(text) VALUES (@@session.collation_database); + INSERT INTO ttrig(text) VALUES (@@session.character_set_database); + + SET @@timestamp= 47114711; + INSERT INTO ttrig(text) VALUES (@@session.timestamp); + SET @@timestamp= 47124712; + INSERT INTO ttrig(text) VALUES (@@session.timestamp); + + SET @@last_insert_id= 1616; + INSERT INTO ttrig(text) VALUES (@@session.last_insert_id); + SET @@last_insert_id= 1717; + INSERT INTO ttrig(text) VALUES (@@session.last_insert_id); + +END| +DELIMITER ;| + +INSERT INTO trigger_table VALUES ('bye.'); + + +--echo ==== Insert variables from a prepared statement ==== + +# GLOBAL + +PREPARE p1 FROM 'SET @@pseudo_thread_id= 4712'; +PREPARE p2 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)'; +PREPARE p3 FROM 'SET @@pseudo_thread_id= 4713'; +PREPARE p4 FROM 'INSERT INTO tprep(num) VALUES (@@session.pseudo_thread_id)'; + +PREPARE p5 FROM 'SET @@foreign_key_checks= 0'; +PREPARE p6 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)'; +PREPARE p7 FROM 'SET @@foreign_key_checks= 1'; +PREPARE p8 FROM 'INSERT INTO tprep(num) VALUES (@@session.foreign_key_checks)'; + +PREPARE p9 FROM 'SET @@sql_auto_is_null= 0'; +PREPARE p10 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)'; +PREPARE p11 FROM 'SET @@sql_auto_is_null= 1'; +PREPARE p12 FROM 'INSERT INTO tprep(num) VALUES (@@session.sql_auto_is_null)'; + +PREPARE p13 FROM 'SET @@unique_checks= 0'; +PREPARE p14 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)'; +PREPARE p15 FROM 'SET @@unique_checks= 1'; +PREPARE p16 FROM 'INSERT INTO tprep(num) VALUES (@@session.unique_checks)'; + +PREPARE p17 FROM 'SET @@auto_increment_increment= 11'; +PREPARE p18 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)'; +PREPARE p19 FROM 'SET @@auto_increment_increment= 19'; +PREPARE p20 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_increment)'; + +PREPARE p21 FROM 'SET @@auto_increment_offset= 13'; +PREPARE p22 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)'; +PREPARE p23 FROM 'SET @@auto_increment_offset= 17'; +PREPARE p24 FROM 'INSERT INTO tprep(num) VALUES (@@session.auto_increment_offset)'; + +# reset these as they affect the index column +PREPARE p25 FROM 'SET @@auto_increment_increment= 1'; +PREPARE p26 FROM 'SET @@auto_increment_offset= 1'; + +PREPARE p27 FROM 'SET @@character_set_client= \'cp1257\''; +PREPARE p28 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)'; +PREPARE p29 FROM 'SET @@character_set_client= \'cp1256\''; +PREPARE p30 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_client)'; + +PREPARE p31 FROM 'SET @@collation_connection= \'cp1251_ukrainian_ci\''; +PREPARE p32 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)'; +PREPARE p33 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)'; +PREPARE p34 FROM 'SET @@collation_connection= \'cp1251_bulgarian_ci\''; +PREPARE p35 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_connection)'; +PREPARE p36 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_connection)'; + +PREPARE p37 FROM 'SET @@collation_server= \'latin7_bin\''; +PREPARE p38 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)'; +PREPARE p39 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)'; +PREPARE p40 FROM 'SET @@collation_server= \'latin7_general_cs\''; +PREPARE p41 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_server)'; +PREPARE p42 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_server)'; + +PREPARE p43 FROM 'SET @@time_zone= \'Europe/Moscow\''; +PREPARE p44 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)'; +PREPARE p45 FROM 'SET @@time_zone= \'Universal\''; +PREPARE p46 FROM 'INSERT INTO tprep(text) VALUES (@@session.time_zone)'; + +PREPARE p47 FROM 'SET @@lc_time_names= \'sv_FI\''; +PREPARE p48 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)'; +PREPARE p49 FROM 'SET @@lc_time_names= \'no_NO\''; +PREPARE p50 FROM 'INSERT INTO tprep(text) VALUES (@@session.lc_time_names)'; + +PREPARE p51 FROM 'SET @@collation_database= \'latin7_general_ci\''; +PREPARE p52 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)'; +PREPARE p53 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)'; +PREPARE p54 FROM 'SET @@collation_database= \'latin7_estonian_cs\''; +PREPARE p55 FROM 'INSERT INTO tprep(text) VALUES (@@session.collation_database)'; +PREPARE p56 FROM 'INSERT INTO tprep(text) VALUES (@@session.character_set_database)'; + +PREPARE p57 FROM 'SET @@timestamp= 47114711'; +PREPARE p58 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)'; +PREPARE p59 FROM 'SET @@timestamp= 47124712'; +PREPARE p60 FROM 'INSERT INTO tprep(text) VALUES (@@session.timestamp)'; + +PREPARE p61 FROM 'SET @@last_insert_id= 1616'; +PREPARE p62 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)'; +PREPARE p63 FROM 'SET @@last_insert_id= 1717'; +PREPARE p64 FROM 'INSERT INTO tprep(text) VALUES (@@session.last_insert_id)'; + +EXECUTE p1; EXECUTE p2; EXECUTE p3; EXECUTE p4; EXECUTE p5; EXECUTE p6; +EXECUTE p7; EXECUTE p8; EXECUTE p9; EXECUTE p10; EXECUTE p11; EXECUTE p12; +EXECUTE p13; EXECUTE p14; EXECUTE p15; EXECUTE p16; EXECUTE p17; EXECUTE p18; +EXECUTE p19; EXECUTE p20; EXECUTE p21; EXECUTE p22; EXECUTE p23; EXECUTE p24; +EXECUTE p25; EXECUTE p26; EXECUTE p27; EXECUTE p28; EXECUTE p29; EXECUTE p30; +EXECUTE p31; EXECUTE p32; EXECUTE p33; EXECUTE p34; EXECUTE p35; EXECUTE p36; +EXECUTE p37; EXECUTE p38; EXECUTE p39; EXECUTE p40; EXECUTE p41; EXECUTE p42; +EXECUTE p43; EXECUTE p44; EXECUTE p45; EXECUTE p46; EXECUTE p47; EXECUTE p48; +EXECUTE p49; EXECUTE p50; EXECUTE p51; EXECUTE p52; EXECUTE p53; EXECUTE p54; +EXECUTE p55; EXECUTE p56; EXECUTE p57; EXECUTE p58; EXECUTE p59; EXECUTE p60; +EXECUTE p61; EXECUTE p62; EXECUTE p63; EXECUTE p64; + + +--echo ==== Results ==== + +# Show the result in table test.tstmt on master... +SELECT * FROM tstmt ORDER BY id; +let $diff_table_1=master:test.tstmt; + +# ... then compare test.tstmt on master to the other tables on master... +let $diff_table_2=master:test.tproc; +source include/diff_tables.inc; +let $diff_table_2=master:test.tfunc; +source include/diff_tables.inc; +let $diff_table_2=master:test.ttrig; +source include/diff_tables.inc; +let $diff_table_2=master:test.tprep; +source include/diff_tables.inc; + +# ... and to all tables on slave. +sync_slave_with_master; +let $diff_table_2=slave:test.tstmt; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tproc; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tfunc; +source include/diff_tables.inc; +let $diff_table_2=slave:test.ttrig; +source include/diff_tables.inc; +let $diff_table_2=slave:test.tprep; +source include/diff_tables.inc; + + +--echo ==== Clean up ==== + +--echo [on master] +connection master; +DROP PROCEDURE proc; +DROP FUNCTION func; +DROP TRIGGER trig; +DROP TABLE tstmt, tproc, tfunc, ttrig, tprep, trigger_table; + +SET @@global.pseudo_thread_id= @m_pseudo_thread_id; +SET @@global.auto_increment_increment= @m_auto_increment_increment; +SET @@global.auto_increment_offset= @m_auto_increment_offset; +SET @@global.character_set_client= @m_character_set_client; +SET @@global.collation_connection= @m_collation_connection; +SET @@global.collation_server= @m_collation_server; +SET @@global.time_zone= @m_time_zone; +SET @@global.lc_time_names= @m_lc_time_names; +SET @@global.collation_database= @m_collation_database; + +--echo [on slave] +connection slave; +SET @@global.pseudo_thread_id= @s_pseudo_thread_id; +SET @@global.auto_increment_increment= @s_auto_increment_increment; +SET @@global.auto_increment_offset= @s_auto_increment_offset; +SET @@global.character_set_client= @s_character_set_client; +SET @@global.collation_connection= @s_collation_connection; +SET @@global.collation_server= @s_collation_server; +SET @@global.time_zone= @s_time_zone; +SET @@global.lc_time_names= @s_lc_time_names; +SET @@global.collation_database= @s_collation_database; + +connection master; +sync_slave_with_master; diff --git a/mysql-test/t/mysqlbinlog2.test b/mysql-test/t/mysqlbinlog2.test index 2ac16f4c590..59017b09c54 100644 --- a/mysql-test/t/mysqlbinlog2.test +++ b/mysql-test/t/mysqlbinlog2.test @@ -41,8 +41,7 @@ select "--- Local --" as ""; # be time dependent (the Start events). Better than nothing. # ---replace_regex /[[:<:]][0-9]{6} [0-9 ][0-9]:[0-9]{2}:[0-9]{2}[[:>:]]/{yymmdd} {HH:MM:SS}/ /=[0-9]+ /={integer} / /# at [0-9]+/# at {pos}/ /(pos:?) [0-9]+/\1 {pos}/ /binlog v [0-9]+, server v [^ ]* created/binlog v #, server v ## created/ ---exec $MYSQL_BINLOG --base64-output=never $MYSQLTEST_VARDIR/log/master-bin.000001 +--exec $MYSQL_BINLOG --short-form --base64-output=never $MYSQLTEST_VARDIR/log/master-bin.000001 --disable_query_log select "--- offset --" as ""; diff --git a/sql/item_func.cc b/sql/item_func.cc index 984fa9038b7..12bb0999ffc 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -4789,6 +4789,12 @@ Item_func_get_system_var::fix_fields(THD *thd, Item **ref) } +bool Item_func_get_system_var::is_written_to_binlog() +{ + return var->is_written_to_binlog(var_type); +} + + longlong Item_func_inet_aton::val_int() { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_func.h b/sql/item_func.h index f8eb7ff6200..9ab30a2cf93 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -1435,6 +1435,15 @@ public: void fix_length_and_dec() { DBUG_ASSERT(0); } /* TODO: fix to support views */ const char *func_name() const { return "get_system_var"; } + /** + Indicates whether this system variable is written to the binlog or not. + + Variables are written to the binlog as part of "status_vars" in + Query_log_event, as an Intvar_log_event, or a Rand_log_event. + + @return true if the variable is written to the binlog, false otherwise. + */ + bool is_written_to_binlog(); }; diff --git a/sql/log_event.cc b/sql/log_event.cc index af802f52611..15f1a957149 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -14,7 +14,11 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ -#ifndef MYSQL_CLIENT +#ifdef MYSQL_CLIENT + +#include "mysql_priv.h" + +#else #ifdef USE_PRAGMA_IMPLEMENTATION #pragma implementation // gcc: Class implementation @@ -28,7 +32,9 @@ #include "rpl_utility.h" #include "rpl_record.h" #include <my_dir.h> + #endif /* MYSQL_CLIENT */ + #include <base64.h> #include <my_bitmap.h> @@ -1589,7 +1595,7 @@ bool Query_log_event::write(IO_CACHE* file) recognize Q_CATALOG_CODE and have no problem. */ } - if (auto_increment_increment != 1) + if (auto_increment_increment != 1 || auto_increment_offset != 1) { *start++= Q_AUTO_INCREMENT; int2store(start, auto_increment_increment); @@ -2102,9 +2108,17 @@ void Query_log_event::print_query_header(IO_CACHE* file, end= strmov(end, print_event_info->delimiter); *end++='\n'; my_b_write(file, (uchar*) buff, (uint) (end-buff)); - if (flags & LOG_EVENT_THREAD_SPECIFIC_F) + if ((!print_event_info->thread_id_printed || + ((flags & LOG_EVENT_THREAD_SPECIFIC_F) && + thread_id != print_event_info->thread_id))) + { + // If --short-form, print deterministic value instead of pseudo_thread_id. my_b_printf(file,"SET @@session.pseudo_thread_id=%lu%s\n", - (ulong)thread_id, print_event_info->delimiter); + short_form ? 999999999 : (ulong)thread_id, + print_event_info->delimiter); + print_event_info->thread_id= thread_id; + print_event_info->thread_id_printed= 1; + } /* If flags2_inited==0, this is an event from 3.23 or 4.0; nothing to @@ -2151,20 +2165,14 @@ void Query_log_event::print_query_header(IO_CACHE* file, gracefully). So this code should always be good. */ - if (likely(sql_mode_inited)) + if (likely(sql_mode_inited) && + (unlikely(print_event_info->sql_mode != sql_mode || + !print_event_info->sql_mode_inited))) { - if (unlikely(!print_event_info->sql_mode_inited)) /* first Query event */ - { - print_event_info->sql_mode_inited= 1; - /* force a difference to force write */ - print_event_info->sql_mode= ~sql_mode; - } - if (unlikely(print_event_info->sql_mode != sql_mode)) - { - my_b_printf(file,"SET @@session.sql_mode=%lu%s\n", - (ulong)sql_mode, print_event_info->delimiter); - print_event_info->sql_mode= sql_mode; - } + my_b_printf(file,"SET @@session.sql_mode=%lu%s\n", + (ulong)sql_mode, print_event_info->delimiter); + print_event_info->sql_mode= sql_mode; + print_event_info->sql_mode_inited= 1; } if (print_event_info->auto_increment_increment != auto_increment_increment || print_event_info->auto_increment_offset != auto_increment_offset) @@ -2178,33 +2186,28 @@ void Query_log_event::print_query_header(IO_CACHE* file, /* TODO: print the catalog when we feature SET CATALOG */ - if (likely(charset_inited)) + if (likely(charset_inited) && + (unlikely(!print_event_info->charset_inited || + bcmp((uchar*) print_event_info->charset, (uchar*) charset, 6)))) { - if (unlikely(!print_event_info->charset_inited)) /* first Query event */ + CHARSET_INFO *cs_info= get_charset(uint2korr(charset), MYF(MY_WME)); + if (cs_info) { - print_event_info->charset_inited= 1; - print_event_info->charset[0]= ~charset[0]; // force a difference to force write - } - if (unlikely(bcmp((uchar*) print_event_info->charset, (uchar*) charset, 6))) - { - CHARSET_INFO *cs_info= get_charset(uint2korr(charset), MYF(MY_WME)); - if (cs_info) - { - /* for mysql client */ - my_b_printf(file, "/*!\\C %s */%s\n", - cs_info->csname, print_event_info->delimiter); - } - my_b_printf(file,"SET " - "@@session.character_set_client=%d," - "@@session.collation_connection=%d," - "@@session.collation_server=%d" - "%s\n", - uint2korr(charset), - uint2korr(charset+2), - uint2korr(charset+4), - print_event_info->delimiter); - memcpy(print_event_info->charset, charset, 6); + /* for mysql client */ + my_b_printf(file, "/*!\\C %s */%s\n", + cs_info->csname, print_event_info->delimiter); } + my_b_printf(file,"SET " + "@@session.character_set_client=%d," + "@@session.collation_connection=%d," + "@@session.collation_server=%d" + "%s\n", + uint2korr(charset), + uint2korr(charset+2), + uint2korr(charset+4), + print_event_info->delimiter); + memcpy(print_event_info->charset, charset, 6); + print_event_info->charset_inited= 1; } if (time_zone_len) { @@ -8631,3 +8634,34 @@ Incident_log_event::write_data_body(IO_CACHE *file) DBUG_ENTER("Incident_log_event::write_data_body"); DBUG_RETURN(write_str(file, m_message.str, m_message.length)); } + + +#ifdef MYSQL_CLIENT +/** + The default values for these variables should be values that are + *incorrect*, i.e., values that cannot occur in an event. This way, + they will always be printed for the first event. +*/ +st_print_event_info::st_print_event_info() + :flags2_inited(0), sql_mode_inited(0), + auto_increment_increment(0),auto_increment_offset(0), charset_inited(0), + lc_time_names_number(~0), + charset_database_number(ILLEGAL_CHARSET_INFO_NUMBER), + thread_id(0), thread_id_printed(false), + base64_output_mode(BASE64_OUTPUT_UNSPEC), printed_fd_event(FALSE) +{ + /* + Currently we only use static PRINT_EVENT_INFO objects, so zeroed at + program's startup, but these explicit bzero() is for the day someone + creates dynamic instances. + */ + bzero(db, sizeof(db)); + bzero(charset, sizeof(charset)); + bzero(time_zone_str, sizeof(time_zone_str)); + delimiter[0]= ';'; + delimiter[1]= 0; + myf const flags = MYF(MY_WME | MY_NABP); + open_cached_file(&head_cache, NULL, NULL, 0, flags); + open_cached_file(&body_cache, NULL, NULL, 0, flags); +} +#endif diff --git a/sql/log_event.h b/sql/log_event.h index c46827253a3..2cf69e975f4 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -592,8 +592,9 @@ typedef struct st_print_event_info { /* Settings for database, sql_mode etc that comes from the last event - that was printed. - */ + that was printed. We cache these so that we don't have to print + them if they are unchanged. + */ // TODO: have the last catalog here ?? char db[FN_REFLEN+1]; // TODO: make this a LEX_STRING when thd->db is bool flags2_inited; @@ -606,26 +607,10 @@ typedef struct st_print_event_info char time_zone_str[MAX_TIME_ZONE_NAME_LENGTH]; uint lc_time_names_number; uint charset_database_number; - st_print_event_info() - :flags2_inited(0), sql_mode_inited(0), - auto_increment_increment(1),auto_increment_offset(1), charset_inited(0), - lc_time_names_number(0), charset_database_number(0), - base64_output_mode(BASE64_OUTPUT_UNSPEC), printed_fd_event(FALSE) - { - /* - Currently we only use static PRINT_EVENT_INFO objects, so zeroed at - program's startup, but these explicit bzero() is for the day someone - creates dynamic instances. - */ - bzero(db, sizeof(db)); - bzero(charset, sizeof(charset)); - bzero(time_zone_str, sizeof(time_zone_str)); - delimiter[0]= ';'; - delimiter[1]= 0; - myf const flags = MYF(MY_WME | MY_NABP); - open_cached_file(&head_cache, NULL, NULL, 0, flags); - open_cached_file(&body_cache, NULL, NULL, 0, flags); - } + uint thread_id; + bool thread_id_printed; + + st_print_event_info(); ~st_print_event_info() { close_cached_file(&head_cache); diff --git a/sql/set_var.cc b/sql/set_var.cc index 71131df0ce3..0d1d8b92d3b 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -161,10 +161,14 @@ static void sys_default_slow_log_path(THD *thd, enum_var_type type); static sys_var_chain vars = { NULL, NULL }; -static sys_var_thd_ulong sys_auto_increment_increment(&vars, "auto_increment_increment", - &SV::auto_increment_increment); -static sys_var_thd_ulong sys_auto_increment_offset(&vars, "auto_increment_offset", - &SV::auto_increment_offset); +static sys_var_thd_ulong +sys_auto_increment_increment(&vars, "auto_increment_increment", + &SV::auto_increment_increment, NULL, NULL, + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_thd_ulong +sys_auto_increment_offset(&vars, "auto_increment_offset", + &SV::auto_increment_offset, NULL, NULL, + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_bool_ptr sys_automatic_sp_privileges(&vars, "automatic_sp_privileges", &sp_automatic_privileges); @@ -176,19 +180,25 @@ static sys_var_thd_binlog_format sys_binlog_format(&vars, "binlog_format", &SV::binlog_format); static sys_var_thd_ulong sys_bulk_insert_buff_size(&vars, "bulk_insert_buffer_size", &SV::bulk_insert_buff_size); -static sys_var_character_set_sv sys_character_set_server(&vars, "character_set_server", - &SV::collation_server, - &default_charset_info); +static sys_var_character_set_sv +sys_character_set_server(&vars, "character_set_server", + &SV::collation_server, &default_charset_info, 0, + sys_var::SESSION_VARIABLE_IN_BINLOG); sys_var_const_str sys_charset_system(&vars, "character_set_system", (char *)my_charset_utf8_general_ci.name); -static sys_var_character_set_database sys_character_set_database(&vars, "character_set_database"); -static sys_var_character_set_client sys_character_set_client(&vars, - "character_set_client", - &SV::character_set_client, - &default_charset_info); -static sys_var_character_set_sv sys_character_set_connection(&vars, "character_set_connection", - &SV::collation_connection, - &default_charset_info); +static sys_var_character_set_database +sys_character_set_database(&vars, "character_set_database", + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_character_set_client +sys_character_set_client(&vars, "character_set_client", + &SV::character_set_client, + &default_charset_info, + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_character_set_sv +sys_character_set_connection(&vars, "character_set_connection", + &SV::collation_connection, + &default_charset_info, 0, + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_character_set_sv sys_character_set_results(&vars, "character_set_results", &SV::character_set_results, &default_charset_info, true); @@ -199,15 +209,18 @@ static sys_var_thd_ulong sys_completion_type(&vars, "completion_type", &SV::completion_type, check_completion_type, fix_completion_type); -static sys_var_collation_sv sys_collation_connection(&vars, "collation_connection", - &SV::collation_connection, - &default_charset_info); -static sys_var_collation_sv sys_collation_database(&vars, "collation_database", - &SV::collation_database, - &default_charset_info); -static sys_var_collation_sv sys_collation_server(&vars, "collation_server", - &SV::collation_server, - &default_charset_info); +static sys_var_collation_sv +sys_collation_connection(&vars, "collation_connection", + &SV::collation_connection, &default_charset_info, + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_collation_sv +sys_collation_database(&vars, "collation_database", &SV::collation_database, + &default_charset_info, + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_collation_sv +sys_collation_server(&vars, "collation_server", &SV::collation_server, + &default_charset_info, + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_long_ptr sys_concurrent_insert(&vars, "concurrent_insert", &myisam_concurrent_insert); static sys_var_long_ptr sys_connect_timeout(&vars, "connect_timeout", @@ -303,9 +316,10 @@ static sys_var_thd_ulong sys_max_error_count(&vars, "max_error_count", &SV::max_error_count); static sys_var_thd_ulonglong sys_max_heap_table_size(&vars, "max_heap_table_size", &SV::max_heap_table_size); -static sys_var_thd_ulong sys_pseudo_thread_id(&vars, "pseudo_thread_id", - &SV::pseudo_thread_id, - check_pseudo_thread_id, 0); +static sys_var_thd_ulong sys_pseudo_thread_id(&vars, "pseudo_thread_id", + &SV::pseudo_thread_id, + check_pseudo_thread_id, 0, + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_thd_ha_rows sys_max_join_size(&vars, "max_join_size", &SV::max_join_size, fix_max_join_size); @@ -436,8 +450,14 @@ static sys_var_long_ptr sys_slow_launch_time(&vars, "slow_launch_time", &slow_launch_time); static sys_var_thd_ulong sys_sort_buffer(&vars, "sort_buffer_size", &SV::sortbuff_size); +/* + sql_mode should *not* have binlog_mode=SESSION_VARIABLE_IN_BINLOG: + even though it is written to the binlog, the slave ignores the + MODE_NO_DIR_IN_CREATE variable, so slave's value differs from + master's (see log_event.cc: Query_log_event::do_apply_event()). +*/ static sys_var_thd_sql_mode sys_sql_mode(&vars, "sql_mode", - &SV::sql_mode); + &SV::sql_mode); #ifdef HAVE_OPENSSL extern char *opt_ssl_ca, *opt_ssl_capath, *opt_ssl_cert, *opt_ssl_cipher, *opt_ssl_key; @@ -588,7 +608,8 @@ static sys_var_thd_bit sys_sql_notes(&vars, "sql_notes", 0, OPTION_SQL_NOTES); static sys_var_thd_bit sys_auto_is_null(&vars, "sql_auto_is_null", 0, set_option_bit, - OPTION_AUTO_IS_NULL); + OPTION_AUTO_IS_NULL, 0, + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_thd_bit sys_safe_updates(&vars, "sql_safe_updates", 0, set_option_bit, OPTION_SAFE_UPDATES); @@ -601,11 +622,12 @@ static sys_var_thd_bit sys_quote_show_create(&vars, "sql_quote_show_create", 0, static sys_var_thd_bit sys_foreign_key_checks(&vars, "foreign_key_checks", 0, set_option_bit, OPTION_NO_FOREIGN_KEY_CHECKS, - 1); + 1, sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_thd_bit sys_unique_checks(&vars, "unique_checks", 0, set_option_bit, OPTION_RELAXED_UNIQUE_CHECKS, - 1); + 1, + sys_var::SESSION_VARIABLE_IN_BINLOG); #if defined(ENABLED_PROFILING) && defined(COMMUNITY_SERVER) static sys_var_thd_bit sys_profiling(&vars, "profiling", NULL, set_option_bit, @@ -618,13 +640,41 @@ static sys_var_thd_ulong sys_profiling_history_size(&vars, "profiling_history_si static sys_var_thd_ha_rows sys_select_limit(&vars, "sql_select_limit", &SV::select_limit); -static sys_var_timestamp sys_timestamp(&vars, "timestamp"); -static sys_var_last_insert_id sys_last_insert_id(&vars, "last_insert_id"); -static sys_var_last_insert_id sys_identity(&vars, "identity"); +static sys_var_timestamp sys_timestamp(&vars, "timestamp", + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_last_insert_id +sys_last_insert_id(&vars, "last_insert_id", + sys_var::SESSION_VARIABLE_IN_BINLOG); +/* + identity is an alias for last_insert_id(), so that we are compatible + with Sybase +*/ +static sys_var_last_insert_id +sys_identity(&vars, "identity", sys_var::SESSION_VARIABLE_IN_BINLOG); -static sys_var_thd_lc_time_names sys_lc_time_names(&vars, "lc_time_names"); +static sys_var_thd_lc_time_names +sys_lc_time_names(&vars, "lc_time_names", sys_var::SESSION_VARIABLE_IN_BINLOG); -static sys_var_insert_id sys_insert_id(&vars, "insert_id"); +/* + insert_id should *not* be marked as written to the binlog (i.e., it + should *not* have binlog_status==SESSION_VARIABLE_IN_BINLOG), + because we want any statement that refers to insert_id explicitly to + be unsafe. (By "explicitly", we mean using @@session.insert_id, + whereas insert_id is used "implicitly" when NULL value is inserted + into an auto_increment column). + + We want statements referring explicitly to @@session.insert_id to be + unsafe, because insert_id is modified internally by the slave sql + thread when NULL values are inserted in an AUTO_INCREMENT column. + This modification interfers with the value of the + @@session.insert_id variable if @@session.insert_id is referred + explicitly by an insert statement (as is seen by executing "SET + @@session.insert_id=0; CREATE TABLE t (a INT, b INT KEY + AUTO_INCREMENT); INSERT INTO t(a) VALUES (@@session.insert_id);" in + statement-based logging mode: t will be different on master and + slave). +*/ +static sys_var_insert_id sys_insert_id(&vars, "insert_id"); static sys_var_readonly sys_error_count(&vars, "error_count", OPT_SESSION, SHOW_LONG, @@ -634,9 +684,10 @@ static sys_var_readonly sys_warning_count(&vars, "warning_count", SHOW_LONG, get_warning_count); -/* alias for last_insert_id() to be compatible with Sybase */ -static sys_var_rand_seed1 sys_rand_seed1(&vars, "rand_seed1"); -static sys_var_rand_seed2 sys_rand_seed2(&vars, "rand_seed2"); +static sys_var_rand_seed1 sys_rand_seed1(&vars, "rand_seed1", + sys_var::SESSION_VARIABLE_IN_BINLOG); +static sys_var_rand_seed2 sys_rand_seed2(&vars, "rand_seed2", + sys_var::SESSION_VARIABLE_IN_BINLOG); static sys_var_thd_ulong sys_default_week_format(&vars, "default_week_format", &SV::default_week_format); @@ -644,7 +695,8 @@ static sys_var_thd_ulong sys_default_week_format(&vars, "default_week_for sys_var_thd_ulong sys_group_concat_max_len(&vars, "group_concat_max_len", &SV::group_concat_max_len); -sys_var_thd_time_zone sys_time_zone(&vars, "time_zone"); +sys_var_thd_time_zone sys_time_zone(&vars, "time_zone", + sys_var::SESSION_VARIABLE_IN_BINLOG); /* Global read-only variable containing hostname */ static sys_var_const_str sys_hostname(&vars, "hostname", glob_hostname); diff --git a/sql/set_var.h b/sql/set_var.h index 171158fcf1e..46fe7755baf 100644 --- a/sql/set_var.h +++ b/sql/set_var.h @@ -48,6 +48,22 @@ struct sys_var_chain class sys_var { public: + + /** + Enumeration type to indicate for a system variable whether it will be written to the binlog or not. + */ + enum Binlog_status_enum + { + /* The variable value is not in the binlog. */ + NOT_IN_BINLOG, + /* The value of the @@session variable is in the binlog. */ + SESSION_VARIABLE_IN_BINLOG + /* + Currently, no @@global variable is ever in the binlog, so we + don't need an enumeration value for that. + */ + }; + sys_var *next; struct my_option *option_limits; /* Updated by by set_var_init() */ uint name_length; /* Updated by by set_var_init() */ @@ -55,8 +71,9 @@ public: sys_after_update_func after_update; bool no_support_one_shot; - sys_var(const char *name_arg,sys_after_update_func func= NULL) - :name(name_arg), after_update(func) + sys_var(const char *name_arg, sys_after_update_func func= NULL, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :name(name_arg), after_update(func), binlog_status(binlog_status_arg) , no_support_one_shot(1) {} virtual ~sys_var() {} @@ -71,6 +88,11 @@ public: virtual bool check(THD *thd, set_var *var); bool check_enum(THD *thd, set_var *var, const TYPELIB *enum_names); bool check_set(THD *thd, set_var *var, TYPELIB *enum_names); + bool is_written_to_binlog(enum_var_type type) + { + return (type == OPT_SESSION || type == OPT_DEFAULT) && + (binlog_status == SESSION_VARIABLE_IN_BINLOG); + } virtual bool update(THD *thd, set_var *var)=0; virtual void set_default(THD *thd_arg, enum_var_type type) {} virtual SHOW_TYPE show_type() { return SHOW_UNDEF; } @@ -86,6 +108,9 @@ public: virtual bool is_struct() { return 0; } virtual bool is_readonly() const { return 0; } virtual sys_var_pluginvar *cast_pluginvar() { return 0; } + +private: + const Binlog_status_enum binlog_status; }; @@ -232,8 +257,9 @@ class sys_var_const_str :public sys_var { public: char *value; // Pointer to const value - sys_var_const_str(sys_var_chain *chain, const char *name_arg, const char *value_arg) - :sys_var(name_arg),value((char*) value_arg) + sys_var_const_str(sys_var_chain *chain, const char *name_arg, + const char *value_arg) + :sys_var(name_arg), value((char*) value_arg) { chain_sys_var(chain); } bool check(THD *thd, set_var *var) { @@ -328,8 +354,9 @@ class sys_var_thd :public sys_var { public: sys_var_thd(const char *name_arg, - sys_after_update_func func= NULL) - :sys_var(name_arg,func) + sys_after_update_func func= NULL, + Binlog_status_enum binlog_status= NOT_IN_BINLOG) + :sys_var(name_arg, func, binlog_status) {} bool check_type(enum_var_type type) { return 0; } bool check_default(enum_var_type type) @@ -344,12 +371,13 @@ class sys_var_thd_ulong :public sys_var_thd sys_check_func check_func; public: ulong SV::*offset; - sys_var_thd_ulong(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg) - :sys_var_thd(name_arg), check_func(0), offset(offset_arg) - { chain_sys_var(chain); } - sys_var_thd_ulong(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg, - sys_check_func c_func, sys_after_update_func au_func) - :sys_var_thd(name_arg,au_func), check_func(c_func), offset(offset_arg) + sys_var_thd_ulong(sys_var_chain *chain, const char *name_arg, + ulong SV::*offset_arg, + sys_check_func c_func= NULL, + sys_after_update_func au_func= NULL, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_thd(name_arg, au_func, binlog_status_arg), check_func(c_func), + offset(offset_arg) { chain_sys_var(chain); } bool check(THD *thd, set_var *var); bool update(THD *thd, set_var *var); @@ -440,22 +468,12 @@ protected: TYPELIB *enum_names; sys_check_func check_func; public: - sys_var_thd_enum(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg, - TYPELIB *typelib) - :sys_var_thd(name_arg), offset(offset_arg), enum_names(typelib), - check_func(0) - { chain_sys_var(chain); } - sys_var_thd_enum(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg, - TYPELIB *typelib, - sys_after_update_func func) - :sys_var_thd(name_arg,func), offset(offset_arg), enum_names(typelib), - check_func(0) - { chain_sys_var(chain); } - sys_var_thd_enum(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg, - TYPELIB *typelib, sys_after_update_func func, - sys_check_func check) - :sys_var_thd(name_arg,func), offset(offset_arg), enum_names(typelib), - check_func(check) + sys_var_thd_enum(sys_var_chain *chain, const char *name_arg, + ulong SV::*offset_arg, TYPELIB *typelib, + sys_after_update_func func= NULL, + sys_check_func check= NULL) + :sys_var_thd(name_arg, func), offset(offset_arg), + enum_names(typelib), check_func(check) { chain_sys_var(chain); } bool check(THD *thd, set_var *var) { @@ -480,7 +498,7 @@ public: sys_var_thd_sql_mode(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg) :sys_var_thd_enum(chain, name_arg, offset_arg, &sql_mode_typelib, - fix_sql_mode_var) + fix_sql_mode_var) {} bool check(THD *thd, set_var *var) { @@ -534,9 +552,10 @@ public: bool reverse; sys_var_thd_bit(sys_var_chain *chain, const char *name_arg, sys_check_func c_func, sys_update_func u_func, - ulonglong bit, bool reverse_arg=0) - :sys_var_thd(name_arg), check_func(c_func), update_func(u_func), - bit_flag(bit), reverse(reverse_arg) + ulonglong bit, bool reverse_arg=0, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_thd(name_arg, NULL, binlog_status_arg), check_func(c_func), + update_func(u_func), bit_flag(bit), reverse(reverse_arg) { chain_sys_var(chain); } bool check(THD *thd, set_var *var); bool update(THD *thd, set_var *var); @@ -567,8 +586,9 @@ public: class sys_var_timestamp :public sys_var { public: - sys_var_timestamp(sys_var_chain *chain, const char *name_arg) - :sys_var(name_arg) + sys_var_timestamp(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var(name_arg, NULL, binlog_status_arg) { chain_sys_var(chain); } bool update(THD *thd, set_var *var); void set_default(THD *thd, enum_var_type type); @@ -582,8 +602,9 @@ public: class sys_var_last_insert_id :public sys_var { public: - sys_var_last_insert_id(sys_var_chain *chain, const char *name_arg) - :sys_var(name_arg) + sys_var_last_insert_id(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var(name_arg, NULL, binlog_status_arg) { chain_sys_var(chain); } bool update(THD *thd, set_var *var); bool check_type(enum_var_type type) { return type == OPT_GLOBAL; } @@ -608,8 +629,9 @@ public: class sys_var_rand_seed1 :public sys_var { public: - sys_var_rand_seed1(sys_var_chain *chain, const char *name_arg) - :sys_var(name_arg) + sys_var_rand_seed1(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var(name_arg, NULL, binlog_status_arg) { chain_sys_var(chain); } bool update(THD *thd, set_var *var); bool check_type(enum_var_type type) { return type == OPT_GLOBAL; } @@ -618,8 +640,9 @@ public: class sys_var_rand_seed2 :public sys_var { public: - sys_var_rand_seed2(sys_var_chain *chain, const char *name_arg) - :sys_var(name_arg) + sys_var_rand_seed2(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var(name_arg, NULL, binlog_status_arg) { chain_sys_var(chain); } bool update(THD *thd, set_var *var); bool check_type(enum_var_type type) { return type == OPT_GLOBAL; } @@ -629,11 +652,12 @@ public: class sys_var_collation :public sys_var_thd { public: - sys_var_collation(const char *name_arg) - :sys_var_thd(name_arg) - { + sys_var_collation(const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_thd(name_arg, NULL, binlog_status_arg) + { no_support_one_shot= 0; - } + } bool check(THD *thd, set_var *var); SHOW_TYPE show_type() { return SHOW_CHAR; } bool check_update_type(Item_result type) @@ -648,8 +672,9 @@ class sys_var_character_set :public sys_var_thd { public: bool nullable; - sys_var_character_set(const char *name_arg, bool is_nullable= 0) : - sys_var_thd(name_arg), nullable(is_nullable) + sys_var_character_set(const char *name_arg, bool is_nullable= 0, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_thd(name_arg, NULL, binlog_status_arg), nullable(is_nullable) { /* In fact only almost all variables derived from sys_var_character_set @@ -678,8 +703,9 @@ public: sys_var_character_set_sv(sys_var_chain *chain, const char *name_arg, CHARSET_INFO *SV::*offset_arg, CHARSET_INFO **global_default_arg, - bool is_nullable= 0) - : sys_var_character_set(name_arg, is_nullable), + bool is_nullable= 0, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + : sys_var_character_set(name_arg, is_nullable, binlog_status_arg), offset(offset_arg), global_default(global_default_arg) { chain_sys_var(chain); } void set_default(THD *thd, enum_var_type type); @@ -693,9 +719,9 @@ public: sys_var_character_set_client(sys_var_chain *chain, const char *name_arg, CHARSET_INFO *SV::*offset_arg, CHARSET_INFO **global_default_arg, - bool is_nullable= 0) + Binlog_status_enum binlog_status_arg) : sys_var_character_set_sv(chain, name_arg, offset_arg, global_default_arg, - is_nullable) + 0, binlog_status_arg) { } bool check(THD *thd, set_var *var); }; @@ -704,8 +730,10 @@ public: class sys_var_character_set_database :public sys_var_character_set { public: - sys_var_character_set_database(sys_var_chain *chain, const char *name_arg) : - sys_var_character_set(name_arg) + sys_var_character_set_database(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= + NOT_IN_BINLOG) + : sys_var_character_set(name_arg, 0, binlog_status_arg) { chain_sys_var(chain); } void set_default(THD *thd, enum_var_type type); CHARSET_INFO **ci_ptr(THD *thd, enum_var_type type); @@ -718,8 +746,9 @@ class sys_var_collation_sv :public sys_var_collation public: sys_var_collation_sv(sys_var_chain *chain, const char *name_arg, CHARSET_INFO *SV::*offset_arg, - CHARSET_INFO **global_default_arg) - :sys_var_collation(name_arg), + CHARSET_INFO **global_default_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_collation(name_arg, binlog_status_arg), offset(offset_arg), global_default(global_default_arg) { chain_sys_var(chain); @@ -946,8 +975,9 @@ public: class sys_var_thd_time_zone :public sys_var_thd { public: - sys_var_thd_time_zone(sys_var_chain *chain, const char *name_arg): - sys_var_thd(name_arg) + sys_var_thd_time_zone(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + :sys_var_thd(name_arg, NULL, binlog_status_arg) { no_support_one_shot= 0; chain_sys_var(chain); @@ -1034,8 +1064,9 @@ public: class sys_var_thd_lc_time_names :public sys_var_thd { public: - sys_var_thd_lc_time_names(sys_var_chain *chain, const char *name_arg): - sys_var_thd(name_arg) + sys_var_thd_lc_time_names(sys_var_chain *chain, const char *name_arg, + Binlog_status_enum binlog_status_arg= NOT_IN_BINLOG) + : sys_var_thd(name_arg, NULL, binlog_status_arg) { #if MYSQL_VERSION_ID < 50000 no_support_one_shot= 0; @@ -1079,9 +1110,8 @@ public: sys_var_thd_binlog_format(sys_var_chain *chain, const char *name_arg, ulong SV::*offset_arg) :sys_var_thd_enum(chain, name_arg, offset_arg, - &binlog_format_typelib - , fix_binlog_format_after_update - ) + &binlog_format_typelib, + fix_binlog_format_after_update) {}; bool is_readonly() const; }; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 1bd90e74f47..98434d15d87 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -7389,6 +7389,7 @@ variable_aux: } | '@' opt_var_ident_type ident_or_text opt_component { + /* disallow "SELECT @@global.global.variable" */ if ($3.str && $4.str && check_reserved_words(&$3)) { my_parse_error(ER(ER_SYNTAX_ERROR)); @@ -7396,6 +7397,8 @@ variable_aux: } if (!($$= get_system_var(YYTHD, $2, $3, $4))) MYSQL_YYABORT; + if (!((Item_func_get_system_var*) $$)->is_written_to_binlog()) + Lex->set_stmt_unsafe(); } ; |