421 lines
13 KiB
PHP
421 lines
13 KiB
PHP
# ==== Requirements ====
|
|
#
|
|
# For a given scenario specified by the caller, execute the scenario
|
|
# and verify the following requirements:
|
|
#
|
|
# - Replication should not fail.
|
|
# - The servers should have the same table data.
|
|
# - The servers should write the same to their binary logs, if
|
|
# they use the same setting for binlog_row_image.
|
|
# - The servers should have the same binary representations of JSON
|
|
# objects (the actual representation is not accessible through
|
|
# SQL, but we check the sizes using JSON_STORAGE_SIZE).
|
|
# - The script also prints decoded rows to the result file so they
|
|
# can be sanity-checked by a human.
|
|
#
|
|
# ==== Implementation ====
|
|
#
|
|
# Does the following:
|
|
# 1. Execute one or several statements on MASTER
|
|
# 2. Replicate to slave_1, slave_2, slave_3, slave_4
|
|
# 3. Run mysqlbinlog -v on master. Convert the pseudo-SQL output to
|
|
# real SQL, and apply on 'decoded'. Take the BINLOG statements and
|
|
# apply to 'base64'.
|
|
# 4. Run the same mysqlbinlog -v on all servers
|
|
# 5. Verify that all mysqlbinlog pseudo-SQL was identical
|
|
# 6. Verify that all table data is identical
|
|
# 7. Verify that the sizes of JSON objects are identical
|
|
#
|
|
# ==== Usage ====
|
|
#
|
|
# --let $master_con= CONNECTION
|
|
# --let $slave_1_con= CONNECTION
|
|
# --let $slave_2_con= CONNECTION
|
|
# --let $slave_3_con= CONNECTION
|
|
# --let $slave_4_con= CONNECTION
|
|
# --let $slave_5_con= CONNECTION
|
|
# --let $base64_con= CONNECTION
|
|
# --let $decoded_con= CONNECTION
|
|
# [--let $rows= (comma-separated-values)[, (comma-separated-values)[, (...)]]
|
|
# [--let $stmt_pre= PREFIX]
|
|
# [--let $stmt_post= SUFFIX]
|
|
# --let $stmt= STATEMENT
|
|
# --let $column_def= DEFINITION
|
|
# --let $table= DATABASE.TABLE
|
|
# --let $compare_size_columns= COLUMN[, COLUMN[, COLUMN...]]
|
|
# [--let $echo_stmt= 1]
|
|
# [--let $echo_select= COLUMNS]
|
|
# [--let $echo_decoded_rows= 1]
|
|
# [--let $first_case= N]
|
|
# --source include/check_mysqlbinlog_decoded_rows.inc
|
|
#
|
|
# Assumptions:
|
|
# Replication should be running from server_1 to server_2.
|
|
# server_3 should be configured but not replicating.
|
|
#
|
|
# Parameters:
|
|
# $master_con, $slave_[1|2|3|4|5]_con, $base64_con, $decoded_con
|
|
# Connections: statement will originally be executed on
|
|
# $master_con, then replicated to $slave_1_con, $slave_2_con,
|
|
# $slave_3_con, $slave_4_con, and $slave_5_con.
|
|
# mysqlbinlog's base64 output will be applied on $base64_con and
|
|
# the decoded pseudo-SQL will be applied on $decoded_con
|
|
#
|
|
# $column_def
|
|
# Whatever should be between the open parenthesis and closing
|
|
# parenthesis in CREATE TABLE (...). If not given, assumes the
|
|
# invoker has already created the table.
|
|
#
|
|
# $stmt
|
|
# The statement(s) to execute.
|
|
#
|
|
# $stmt_pre
|
|
# This is prepended to $stmt
|
|
#
|
|
# $stmt_post
|
|
# This is appended to $stmt
|
|
#
|
|
# $table
|
|
# The qualified name of the table that will be compared on
|
|
# server_1, server_2, and server_3 using include/diff_tables.inc
|
|
#
|
|
# $echo_stmt
|
|
# Print the statement before executing it.
|
|
#
|
|
# $echo_select
|
|
# If set, issue SELECT $echo_select FROM $table at the beginning
|
|
# and end of the script. Examples: To select all columns, set
|
|
# $echo_select=*; to select columns 'i' and 'j', set
|
|
# $echo_select=i,j.
|
|
#
|
|
# $echo_decoded_rows
|
|
# If set to 1, print the generated SQL statement after decoding it
|
|
# using mysqlbinlog, on master, slave_2, and slave_4 (the output
|
|
# is expected to differ on these, because of the different
|
|
# binlog_row_image). If set to 2, print it only on master (useful
|
|
# when the full format is too big).
|
|
#
|
|
# $compare_size_columns
|
|
# Comma-separated list of names of columns. The sizes of the listed
|
|
# columns will be compared on all servers.
|
|
#
|
|
# $first_case
|
|
# For debugging: If set to a number N>=1, skip all invocations of
|
|
# this script before the Nth.
|
|
|
|
|
|
# Print header
|
|
if (!$first_case)
|
|
{
|
|
--let $first_case= 1
|
|
}
|
|
if (!$rpl_apply_decoded_rows_case_counter)
|
|
{
|
|
--let $rpl_apply_decoded_rows_case_counter= 0
|
|
}
|
|
--inc $rpl_apply_decoded_rows_case_counter
|
|
|
|
if ($rpl_apply_decoded_rows_case_counter >= $first_case)
|
|
{
|
|
|
|
--echo ---- $rpl_apply_decoded_rows_case_counter. $desc ----
|
|
|
|
--disable_query_log
|
|
|
|
--let $include_filename= rpl_row_jsondiff_scenario.inc
|
|
--source include/begin_include_file.inc
|
|
|
|
# Save binlog positions on all servers
|
|
--let $save_binlog_position_in_sql= 1
|
|
--connection $master_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $slave_1_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $slave_2_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $slave_3_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $slave_4_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $slave_5_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $base64_con
|
|
--source include/save_binlog_position.inc
|
|
--connection $decoded_con
|
|
--source include/save_binlog_position.inc
|
|
|
|
if ($column_def)
|
|
{
|
|
if ($column_def != $_last_column_def)
|
|
{
|
|
--echo * CREATE TABLE $table ($column_def)
|
|
--let $rpl_sql= SET SESSION SQL_LOG_BIN = 0; DROP TABLE IF EXISTS $table; CREATE TABLE $table ($column_def); SET SESSION SQL_LOG_BIN = 1;
|
|
--source include/rpl_for_each_server_stmt.inc
|
|
}
|
|
}
|
|
--let $_last_column_def= $column_def
|
|
|
|
if ($rows)
|
|
{
|
|
--let $rpl_sql= SET SQL_LOG_BIN = 0; TRUNCATE $table; INSERT INTO $table VALUES $rows; SET SQL_LOG_BIN = 1;
|
|
--source include/rpl_for_each_server_stmt.inc
|
|
}
|
|
if ($echo_select)
|
|
{
|
|
--echo # Before update
|
|
eval SELECT $echo_select FROM $table;
|
|
}
|
|
|
|
# Run query on MASTER and sync to slaves
|
|
--connection $master_con
|
|
if ($echo_stmt)
|
|
{
|
|
--echo $stmt_pre $stmt $stmt_post
|
|
}
|
|
eval $stmt_pre $stmt $stmt_post;
|
|
|
|
if ($echo_select)
|
|
{
|
|
--echo # After update
|
|
eval SELECT $echo_select FROM $table;
|
|
}
|
|
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Syncing to three slaves
|
|
}
|
|
--source include/rpl_sync.inc
|
|
|
|
# Prepare for get_mysqlbinlog_decoded_rows
|
|
SET @@SESSION.GROUP_CONCAT_MAX_LEN= 65536;
|
|
--let $columns= `SELECT GROUP_CONCAT(CONCAT(COLUMN_NAME, '/', COLUMN_TYPE) ORDER BY ORDINAL_POSITION SEPARATOR '; ') FROM INFORMATION_SCHEMA.COLUMNS WHERE CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) = '$table'`
|
|
if ($rpl_debug)
|
|
{
|
|
--echo columns=$columns
|
|
}
|
|
--let $mysqlbinlog_positions_from_sql_variables= 1
|
|
--let $mysqlbinlog_options= --force-if-open
|
|
|
|
# Get decoded rows on MASTER
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_master= $decoded_sql
|
|
|
|
if ($echo_decoded_rows)
|
|
{
|
|
--echo # Decoded rows
|
|
--echo $decoded_sql_master
|
|
}
|
|
|
|
# Run converted pseudo-SQL on $decoded_con (unless nothing was logged)
|
|
if ($decoded_sql_master)
|
|
{
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Applying decoded SQL
|
|
}
|
|
--connection $decoded_con
|
|
eval $decoded_sql_master;
|
|
}
|
|
|
|
# Run BINLOG base64 on $base64_con
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Applying base64
|
|
}
|
|
--connection $base64_con
|
|
--let $base64_port= `SELECT @@GLOBAL.PORT`
|
|
--exec $MYSQL --user=root --host=127.0.0.1 --port=$base64_port < $output_file
|
|
--remove_file $output_file
|
|
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Comparing decoded output
|
|
}
|
|
|
|
# Get mysqlbinlog output on all other servers too.
|
|
|
|
--connection $slave_1_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_slave_1= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $slave_2_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_slave_2= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $slave_3_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_slave_3= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $slave_4_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_slave_4= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $slave_5_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_slave_5= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $base64_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_base64= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
--connection $decoded_con
|
|
--source include/get_mysqlbinlog_decoded_rows.inc
|
|
--let $decoded_sql_decoded= $decoded_sql
|
|
--remove_file $output_file
|
|
|
|
if ($echo_decoded_rows == 1)
|
|
{
|
|
--echo # Decoded rows, full image
|
|
--echo $decoded_sql_slave_2
|
|
|
|
--echo # Decoded rows, minimal image when master has full image
|
|
--echo $decoded_sql_slave_4
|
|
}
|
|
|
|
# Verify that mysqlbinlog has the same output on all servers
|
|
--let $mismatch=
|
|
|
|
# When using no primary key, master will generate a full BI but a
|
|
# minimal AI. Due to BUG#86554, slave will then generate a full AI,
|
|
# so it will be different from the master. Hence disable comparison
|
|
# with master in the no-pk case.
|
|
if ($key)
|
|
{
|
|
if ($decoded_sql_slave_1 != $decoded_sql_master)
|
|
{
|
|
--let $mismatch= slave_1 vs master;
|
|
}
|
|
}
|
|
# Since slave_2 and slave_3 use binlog_row_image=FULL, they are
|
|
# expected to have different decoded SQL statements from the master,
|
|
# and will only be compared with each other.
|
|
if ($decoded_sql_slave_3 != $decoded_sql_slave_2)
|
|
{
|
|
--let $mismatch= $mismatch slave_3 vs slave_2;
|
|
}
|
|
# Due to BUG#26258329, don't compare slave_4/slave_5 with master,
|
|
# only with each other.
|
|
#if ($decoded_sql_slave_4 != $decoded_sql_master)
|
|
#{
|
|
# --let $mismatch= slave_4 vs master;
|
|
#}
|
|
if ($decoded_sql_slave_4 != $decoded_sql_slave_5)
|
|
{
|
|
--let $mismatch= $mismatch slave_4 vs slave_5;
|
|
}
|
|
# Must compare with slave_1 and not master due to BUG#86554
|
|
if ($decoded_sql_base64 != $decoded_sql_slave_1)
|
|
{
|
|
--let $mismatch= $mismatch base64 vs slave_1;
|
|
}
|
|
if ($decoded_sql_decoded != $decoded_sql_master)
|
|
{
|
|
--let $mismatch= $mismatch decoded vs master;
|
|
}
|
|
if ($mismatch)
|
|
{
|
|
--source include/show_rpl_debug_info.inc
|
|
--echo FAILURE: output on master: <<<$decoded_sql_master>>>
|
|
--echo FAILURE: output on slave_1: <<<$decoded_sql_slave_1>>>
|
|
--echo FAILURE: output on slave_2: <<<$decoded_sql_slave_2>>>
|
|
--echo FAILURE: output on slave_3: <<<$decoded_sql_slave_3>>>
|
|
--echo FAILURE: output on slave_4: <<<$decoded_sql_slave_4>>>
|
|
--echo FAILURE: output on slave_5: <<<$decoded_sql_slave_5>>>
|
|
--echo FAILURE: output on base64: <<<$decoded_sql_base64>>>
|
|
--echo FAILURE: output on decoded: <<<$decoded_sql_decoded>>>
|
|
--echo FAILURE: The following decoded rows are different: $mismatch
|
|
--echo FAILURE while executing case #$rpl_apply_decoded_rows_case_counter
|
|
--die Decoded rows look different on the different hosts
|
|
}
|
|
|
|
# Verify that tables are the same on all servers
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Comparing tables
|
|
}
|
|
--let $diff_tables= $master_con:$table, $slave_1_con:$table, $slave_2_con:$table, $slave_3_con:$table, $slave_4_con:$table, $slave_5_con:$table, $base64_con:$table, $decoded_con:$table
|
|
--source include/diff_tables.inc
|
|
|
|
if ($compare_size_columns)
|
|
{
|
|
if ($echo_progress)
|
|
{
|
|
--echo # Comparing sizes of binary representations
|
|
}
|
|
--let $size_query= `SELECT REPLACE('$compare_size_columns', ', ', '), "+", JSON_STORAGE_SIZE(')`
|
|
--let $size_query= SELECT CONCAT(JSON_STORAGE_SIZE($size_query)) FROM $table
|
|
|
|
--connection $master_con
|
|
--let $size_master= `$size_query`
|
|
--connection $slave_1_con
|
|
--let $size_slave_1= `$size_query`
|
|
--connection $slave_2_con
|
|
--let $size_slave_2= `$size_query`
|
|
--connection $slave_3_con
|
|
--let $size_slave_3= `$size_query`
|
|
--connection $slave_4_con
|
|
--let $size_slave_4= `$size_query`
|
|
--connection $slave_5_con
|
|
--let $size_slave_5= `$size_query`
|
|
--connection $base64_con
|
|
--let $size_base64= `$size_query`
|
|
--connection $decoded_con
|
|
--let $size_decoded= `$size_query`
|
|
|
|
# Compare binary sizes
|
|
--let $mismatch=
|
|
if ($size_slave_1 != $size_master)
|
|
{
|
|
--let $mismatch= slave_1
|
|
}
|
|
if ($size_slave_2 != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch slave_2
|
|
}
|
|
if ($size_slave_3 != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch slave_3
|
|
}
|
|
if ($size_slave_4 != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch slave_4
|
|
}
|
|
if ($size_slave_5 != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch slave_5
|
|
}
|
|
if ($size_base64 != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch base64
|
|
}
|
|
if ($size_decoded != $size_master)
|
|
{
|
|
--let $mismatch= $mismatch decoded
|
|
}
|
|
if ($mismatch)
|
|
{
|
|
--source include/show_rpl_debug_info.inc
|
|
--echo FAILURE: sizes on master: <<<$size_master>>>
|
|
--echo FAILURE: sizes on slave_1: <<<$size_slave_1>>>
|
|
--echo FAILURE: sizes on slave_2: <<<$size_slave_2>>>
|
|
--echo FAILURE: sizes on slave_3: <<<$size_slave_3>>>
|
|
--echo FAILURE: sizes on slave_4: <<<$size_slave_4>>>
|
|
--echo FAILURE: sizes on slave_5: <<<$size_slave_5>>>
|
|
--echo FAILURE: sizes on base64: <<<$size_base64>>>
|
|
--echo FAILURE: sizes on decoded: <<<$size_decoded>>>
|
|
--echo FAILURE: The following are different from master: $mismatch
|
|
--echo FAILURE while executing case #$rpl_apply_decoded_rows_case_counter
|
|
--die Sizes of binary representations are different on the different hosts
|
|
}
|
|
}
|
|
|
|
--let $include_filename= rpl_row_jsondiff_scenario.inc
|
|
--source include/end_include_file.inc
|
|
}
|