1# ==== Purpose ====
2#
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
5# different databases.
6#
7#
8# ==== Usage ====
9#
10# --let $diff_tables= [con1:][db1.]t1, [con2:][db2.]t2, ... , [conN:][dbN.]tN
11# [--let $rpl_debug= 1]
12# --source include/diff_tables.inc
13#
14# Parameters:
15#   $diff_tables
16#     Comma-separated list of tables to compare. Each table has the form
17#
18#       [CONNECTION:][DATABASE.]table
19#
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.
26#
27#   $rpl_debug
28#     See include/rpl_init.inc
29#
30#
31# ==== Side effects ====
32#
33# - Prints "include/diff_tables.inc [$diff_tables]".
34#
35# - If the tables are different, prints the difference in a
36#   system-specific format (unified diff if supported) and generates
37#   an error.
38#
39#
40# ==== Bugs ====
41#
42# - It is currently not possible to use this for tables that are
43#   supposed to be different, because if the files are different:
44#    - 'diff' produces system-dependent output,
45#    - the output includes the absolute path of the compared files,
46#    - the output includes a timestamp.
47#   To fix that, we'd probably have to use SQL to compute the
48#   symmetric difference between the tables.  I'm not sure how to do
49#   that efficiently.  If we implement this, it would be nice to
50#   compare the table definitions too.
51#
52# - It actually compares the result of "SELECT * FROM table ORDER BY
53#   col1, col2, ..., colN INTO OUTFILE 'file'".  Hence, it is assumed
54#   that the comparison orders for both tables are equal and that two
55#   rows that are equal in the comparison order cannot differ, e.g.,
56#   by character case.
57
58
59--let $include_filename= diff_tables.inc [$diff_tables]
60--source include/begin_include_file.inc
61
62
63if (!$rpl_debug)
64{
65  --disable_query_log
66}
67
68
69# Check sanity
70if (`SELECT LOCATE(',', '$diff_tables') = 0`)
71{
72  --die ERROR IN TEST: $diff_tables must contain at least two tables (separated by comma)
73}
74
75
76# ==== Save both tables to file ====
77
78# Trim off whitespace
79--let $_dt_tables= `SELECT REPLACE('$diff_tables', ' ', '')`
80
81# Iterate over all tables
82--let $_dt_outfile=
83--let $_dt_prev_outfile=
84while ($_dt_tables)
85{
86  --let $_dt_table= `SELECT SUBSTRING_INDEX('$_dt_tables', ',', 1)`
87  --let $_dt_tables= `SELECT SUBSTRING('$_dt_tables', LENGTH('$_dt_table') + 2)`
88
89  # Parse connection, if any
90  --let $_dt_colon_index= `SELECT LOCATE(':', '$_dt_table')`
91  if ($_dt_colon_index)
92  {
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
97  }
98
99  # Parse database name, if any
100  --let $_dt_database_index= `SELECT LOCATE('.', '$_dt_table')`
101  if ($_dt_database_index)
102  {
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)`
105  }
106  if (!$_dt_database_index)
107  {
108    --let $_dt_database= `SELECT DATABASE()`
109  }
110
111  if ($rpl_debug)
112  {
113    --echo con='$_dt_connection' db='$_dt_database' table='$_dt_table'
114    --echo rest of tables='$_dt_tables'
115  }
116
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.
128
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)
134  {
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?
137  }
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`)
141  {
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;
144    if ($rpl_debug)
145    {
146      --echo # increasing group_concat_max_len from $_dt_old_group_concat_max_len to $_dt_order_by_length
147    }
148  }
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)
158  {
159    --let $_dt_dummy= `SET SESSION group_concat_max_len = $_dt_old_group_concat_max_len
160  }
161  if ($rpl_debug)
162  {
163    --echo using ORDER BY clause '`$_dt_column_list`'
164  }
165
166  # Now that we have the comma-separated list of columns, we can write
167  # the table to a file.
168  --let $_dt_outfile= `SELECT @@datadir`
169  --let $_dt_outfile= $_dt_outfile/diff_table-$_dt_connection-$_dt_database-$_dt_table
170  eval SELECT * INTO OUTFILE '$_dt_outfile' FROM $_dt_database.$_dt_table ORDER BY `$_dt_column_list`;
171
172  # Compare files.
173  if ($_dt_prev_outfile)
174  {
175    if ($rpl_debug)
176    {
177      --echo # diffing $_dt_prev_outfile vs $_dt_outfile
178    }
179    --diff_files $_dt_prev_outfile $_dt_outfile
180    # Remove previous outfile. Keep current file for comparison with next table.
181    --disable_warnings
182    --remove_file $_dt_prev_outfile
183    --enable_warnings
184  }
185  --let $_dt_prev_outfile= $_dt_outfile
186}
187
188--disable_warnings
189--remove_file $_dt_prev_outfile
190--enable_warnings
191
192--let $include_filename= diff_tables.inc [$diff_tables]
193--source include/end_include_file.inc
194