BUG#31168: @@hostname does not replicate

Problem: in mixed and statement mode, a query that refers to a
system variable will use the slave's value when replayed on
slave. So if the value of a system variable is inserted into a
table, the slave will differ from the master.
Fix: mark statements that refer to a system variable as "unsafe",
meaning they will be replicated by row in mixed mode and produce a warning
in statement mode. There are some exceptions: some variables are actually
replicated. Those should *not* be marked as unsafe.
BUG#34732: mysqlbinlog does not print default values for auto_increment variables
Problem: mysqlbinlog does not print default values for some variables,
including auto_increment_increment and others. So if a client executing
the output of mysqlbinlog has different default values, replication will
be wrong.
Fix: Always print default values for all variables that are replicated.
I need to fix the two bugs at the same time, because the test cases would
fail if I only fixed one of them.


include/m_ctype.h:
  Added definition of ILLEGAL_CHARSET_INFO_NUMBER. We just need a symbol
  for a number that will never be used by any charset. ~0U should be safe
  since charset numbers are sequential, starting from 0.
mysql-test/include/commit.inc:
  Upated test to avoid making statements unsafe.
mysql-test/r/commit_1innodb.result:
  Updated test needs updated result file.
mysql-test/r/mysqlbinlog.result:
  Updated result file.
mysql-test/r/mysqlbinlog2.result:
  Updated result file.
mysql-test/r/user_var-binlog.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_base64_flag.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result:
  Updated result file.
mysql-test/suite/binlog/r/binlog_unsafe.result:
  Modified test file needs modified result file.
mysql-test/suite/binlog/t/binlog_base64_flag.test:
  Need to filter out pseudo_thread_id from result since it is
  nondeterministic.
mysql-test/suite/binlog/t/binlog_unsafe.test:
  Add tests that using variables is unsafe. The 'CREATE VIEW' tests didn't
  make sense, so I removed them. SHOW WARNINGS is not necessary either,
  because we get warnings for each statement in the result file.
mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result:
  Updated result file.
mysql-test/suite/rpl/r/rpl_skip_error.result:
  Updated result file.
mysql-test/suite/rpl/t/rpl_skip_error.test:
  The test used @@server_id, which is not safe to replicate, so it would
  have given a warning. The way it used @@server_id was hackish (issue a
  query on master that removes rows only on master), so I replaced it by a
  more robust way to do the same thing (connect to slave and insert the
  rows only there).
  Also clarified what the test case does.
mysql-test/t/mysqlbinlog2.test:
  Use --short-form instead of manually filtering out nondeterministic stuff
  from mysqlbinlog (because we added the nondeterministic @@pseudo_thread_id
  to the output).
sql/item_func.cc:
  Added method of Item_func_get_system_var that indicates whether the given
  system variable will be written to the binlog or not.
sql/item_func.h:
  Added method of Item_func_get_system_var that indicates whether the given
  system variable will be written to the binlog or not.
sql/log_event.cc:
   - auto_increment_offset was not written to the binlog if
  auto_increment_increment=1
   - mysqlbinlog did not output default values for some variables
  (BUG#34732). In st_print_event_info, we remember for each variable whether
  it has been printed or not. This is achieved in different ways for
  different variables:
      - For auto_increment_*, lc_time_names, charset_database_number,
        we set the default values in st_print_event_info to something
        illegal, so that it will look like they have changed the first time
        they are seen.
      - For charset, sql_mode, pseudo_thread_id, we add a flag to
        st_print_event_info which indicates whether the variable has been
        printed.
      - Since pseudo_thread_id is now printed more often, and its value is
        not guaranteed to be constant across different runs of the same
        test script, I replaced it by a constant if --short-form is used.
   - Moved st_print_event_info constructor from log_event.h to log_event.cc,
  since it now depends on ILLEGAL_CHARSET_NUMBER, which is defined in
  m_ctype.h, which is better to include from a .cc file than from a header
  file.
sql/log_event.h:
  Added fields to st_print_event_info that indicate whether some of the
  variables have been written or not. Since the initialization of
  charset_database_number now depends on ILLEGAL_CHARSET_INFO_NUMBER, which
  is defined in a header file, which we'd better not include from this
  header file -- I moved the constructor from here to log_event.cc.
sql/set_var.cc:
  System variables now have a flag binlog_status, which indicates if they
  are written to the binlog. If nothing is specified, all variables are
  marked as not written to the binlog (NOT_IN_BINLOG) when created. In this
  file, the variables that are written to the binlog are marked with
  SESSION_VARIABLE_IN_BINLOG.
sql/set_var.h:
  Added flag binlog_status to class sys_var. Added a getter and a
  constructor parameter that sets it.
  Since I had to change sys_var_thd_enum constructor anyways, I simplified
  it to use default values of arguments instead of three copies of the
  constructor.
sql/sql_yacc.yy:
  Mark statements that refer to a system variable as "unsafe",
  meaning they will be replicated by row in mixed mode. Added comment to
  explain strange piece of code just above.
mysql-test/include/diff_tables.inc:
  New auxiliary test file that tests whether two tables (possibly one on
  master and one on slave) differ.
mysql-test/suite/rpl/r/rpl_variables.result:
  New test case needs new result file.
mysql-test/suite/rpl/r/rpl_variables_stm.result:
  New test file needs new result file.
mysql-test/suite/rpl/t/rpl_variables.test:
  Test that INSERT of @@variables is replicated correctly (by switching to
  row-based mode).
mysql-test/suite/rpl/t/rpl_variables_stm.test:
  Test that replication of @@variables which are replicated explicitly works
  as expected in statement mode (without giving warnings).

1 files changed, 92 insertions, 40 deletions

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);