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