3
# Check if the two given tables (possibly residing on different
4
# master/slave servers) are equal.
3
# Check if all tables in the given list are equal. The tables may have
4
# different names, exist in different connections, and/or reside in
8
# The tables to check are given by the test language variables
9
# $diff_table_1 and $diff_table_2. They must be of the
12
# [master:|slave:]database.table
14
# I.e., both database and table must be speicified. Optionally, you
15
# can prefix the name with 'master:' (to read the table on master) or
16
# with 'slave:' (to read the table on slave). If no prefix is given,
17
# reads the table from the current connection. If one of these
18
# variables has a prefix, both should have a prefix.
10
# --let $diff_tables= [con1:][db1.]t1, [con2:][db2.]t2, ... , [conN:][dbN.]tN
11
# [--let $rpl_debug= 1]
12
# --source include/diff_tables.inc
16
# Comma-separated list of tables to compare. Each table has the form
18
# [CONNECTION:][DATABASE.]table
20
# If CONNECTION is given, then that connection is used. If
21
# CONNECTION is not given, then the connection of the previous
22
# table is used (or the current connection, if this is the first
23
# table). If DATABASE is given, the table is read in that
24
# database. If DATABASE is not given, the table is read in the
25
# connection's current database.
28
# See include/rpl_init.inc
20
31
# ==== Side effects ====
22
# - Prints "Comparing tables $diff_table_1 and $diff_tables_2".
33
# - Prints "include/diff_tables.inc [$diff_tables]".
24
35
# - If the tables are different, prints the difference in a
25
36
# system-specific format (unified diff if supported) and generates
28
# - If $diff_table_1 or $diff_table_2 begins with 'master:' or
29
# 'slave:', it will stay connected to one of those hosts after
30
# execution. The host is only guaranteed to remain unchanged if
31
# none of $diff_table_1 or $diff_table_2 begins with 'master:' or
50
56
# by character case.
59
--let $include_filename= diff_tables.inc [$diff_tables]
60
--source include/begin_include_file.inc
70
if (`SELECT LOCATE(',', '$diff_tables') = 0`)
72
--die ERROR IN TEST: $diff_tables must contain at least two tables (separated by comma)
53
76
# ==== Save both tables to file ====
55
--echo Comparing tables $diff_table_1 and $diff_table_2
59
--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1
61
--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2
63
let $_diff_table=$diff_table_2;
67
# Parse out any leading "master:" or "slave:" from the table
68
# specification and connect the appropriate server.
69
let $_diff_conn_master=`SELECT SUBSTR('$_diff_table', 1, 7) = 'master:'`;
70
if ($_diff_conn_master) {
71
let $_diff_table=`SELECT SUBSTR('$_diff_table', 8)`;
74
let $_diff_conn_slave=`SELECT SUBSTR('$_diff_table', 1, 6) = 'slave:'`;
75
if ($_diff_conn_slave) {
76
let $_diff_table=`SELECT SUBSTR('$_diff_table', 7)`;
80
# Sanity-check the input.
81
let $_diff_error= `SELECT '$_diff_table' NOT LIKE '_%._%'`;
83
--echo !!!ERROR IN TEST: \$diff_table_$_diff_i='$_diff_table' is not in the form database.table
87
# We need the output files to be sorted (so that diff_files does not
88
# think the files are different just because they are differently
89
# ordered). To this end, we first generate a query that sorts the
90
# table by all columns. Since ORDER BY accept column indices, we
91
# just generate a comma-separated list of all numbers from 1 to the
92
# number of columns in the table.
93
let $_diff_column_index=`SELECT MAX(ordinal_position)
94
FROM information_schema.columns
95
WHERE CONCAT(table_schema, '.', table_name) =
97
let $_diff_column_list=$_diff_column_index;
98
dec $_diff_column_index;
99
while ($_diff_column_index) {
100
let $_diff_column_list=$_diff_column_index, $_diff_column_list;
101
dec $_diff_column_index;
79
--let $_dt_tables= `SELECT REPLACE('$diff_tables', ' ', '')`
81
# Iterate over all tables
83
--let $_dt_prev_outfile=
84
while (`SELECT '$_dt_tables' != ''`)
86
--let $_dt_table= `SELECT SUBSTRING_INDEX('$_dt_tables', ',', 1)`
87
--let $_dt_tables= `SELECT SUBSTRING('$_dt_tables', LENGTH('$_dt_table') + 2)`
89
# Parse connection, if any
90
--let $_dt_colon_index= `SELECT LOCATE(':', '$_dt_table')`
93
--let $_dt_connection= `SELECT SUBSTRING('$_dt_table', 1, $_dt_colon_index - 1)`
94
--let $_dt_table= `SELECT SUBSTRING('$_dt_table', $_dt_colon_index + 1)`
95
--let $rpl_connection_name= $_dt_connection
96
--source include/rpl_connection.inc
99
# Parse database name, if any
100
--let $_dt_database_index= `SELECT LOCATE('.', '$_dt_table')`
101
if ($_dt_database_index)
103
--let $_dt_database= `SELECT SUBSTRING('$_dt_table', 1, $_dt_database_index - 1)`
104
--let $_dt_table= `SELECT SUBSTRING('$_dt_table', $_dt_database_index + 1)`
106
if (!$_dt_database_index)
108
--let $_dt_database= `SELECT DATABASE()`
113
--echo con='$_dt_connection' db='$_dt_database' table='$_dt_table'
114
--echo rest of tables='$_dt_tables'
117
# We need to sort the output files so that diff_files does not think
118
# the tables are different just because the rows are differently
119
# ordered. To this end, we first generate a string containing a
120
# comma-separated list of all column names. This is used in the
121
# ORDER BY clause of the following SELECT statement. We get the
122
# column names from INFORMATION_SCHEMA.COLUMNS, and we concatenate
123
# them with GROUP_CONCAT. Since GROUP_CONCAT is limited by the
124
# @@SESSION.group_concat_max_len, which is only 1024 by default, we
125
# first compute the total size of all columns and then increase this
126
# limit if needed. We restore the limit afterwards so as not to
127
# interfere with the test case.
129
# Compute length of ORDER BY clause.
130
let $_dt_order_by_length=
131
`SELECT SUM(LENGTH(column_name) + 3) FROM information_schema.columns
132
WHERE table_schema = '$_dt_database' AND table_name = '$_dt_table'`;
133
if (!$_dt_order_by_length)
135
--echo ERROR IN TEST: table $_dt_database.$_dt_table not found in INFORMATION_SCHEMA.COLUMNS. Did you misspell it?
136
--die ERROR IN TEST: table not found in INFORMATION_SCHEMA. Did you misspell it?
138
--let $_dt_old_group_concat_max_len=
139
# Increase group_concat_max_len if needed.
140
if (`SELECT $_dt_order_by_length > @@SESSION.group_concat_max_len`)
142
--let $_dt_old_group_concat_max_len= `SELECT @@SESSION.group_concat_max_len`
143
--eval SET SESSION group_concat_max_len = $_dt_order_by_length;
146
--echo # increasing group_concat_max_len from $_dt_old_group_concat_max_len to $_dt_order_by_length
149
# Generate ORDER BY clause.
150
# It would be better to do GROUP_CONCAT(CONCAT('`', column_name, '`')) but
151
# BUG#58087 prevents us from returning strings that begin with backticks.
152
let $_dt_column_list=
153
`SELECT GROUP_CONCAT(column_name ORDER BY ORDINAL_POSITION SEPARATOR '`,`')
154
FROM information_schema.columns
155
WHERE table_schema = '$_dt_database' AND table_name = '$_dt_table'`;
156
# Restore group_concat_max_len.
157
if ($_dt_old_group_concat_max_len)
159
--let $_dt_dummy= `SET SESSION group_concat_max_len = $_dt_old_group_concat_max_len
163
--echo using ORDER BY clause '`$_dt_column_list`'
104
166
# Now that we have the comma-separated list of columns, we can write
105
167
# the table to a file.
106
eval SELECT * FROM $_diff_table ORDER BY $_diff_column_list
107
INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/diff_table_$_diff_i';
168
--let $_dt_outfile= `SELECT @@datadir`
169
--let $_dt_outfile= $_dt_outfile/diff_table-$_dt_connection-$_dt_database-$_dt_table
170
eval SELECT * FROM $_dt_database.$_dt_table ORDER BY `$_dt_column_list` INTO OUTFILE '$_dt_outfile';
109
# Do the same for $diff_table_1.
111
let $_diff_table=$diff_table_1;
173
if ($_dt_prev_outfile)
177
--echo # diffing $_dt_prev_outfile vs $_dt_outfile
179
--diff_files $_dt_prev_outfile $_dt_outfile
180
# Remove previous outfile. Keep current file for comparison with next table.
181
--remove_file $_dt_prev_outfile
183
--let $_dt_prev_outfile= $_dt_outfile
115
# ==== Compare the generated files ====
117
diff_files $MYSQLTEST_VARDIR/tmp/diff_table_1 $MYSQLTEST_VARDIR/tmp/diff_table_2;
119
--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_1
120
--remove_file $MYSQLTEST_VARDIR/tmp/diff_table_2
186
--remove_file $_dt_prev_outfile
189
--let $include_filename= diff_tables.inc [$diff_tables]
190
--source include/end_include_file.inc
added
removed
mysql-test/include/diff_tables.inc
