1 2--connection slave 3 4SET @saved_slave_type_conversions = @@slave_type_conversions; 5SET GLOBAL SLAVE_TYPE_CONVERSIONS = 'ALL_NON_LOSSY'; 6 7--connection master 8if (!$engine_type) {let $engine_type=`SELECT @@storage_engine`; let $engine_type2=`SELECT @@storage_engine`;} 9 10--disable_warnings 11DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; 12--enable_warnings 13 14echo --------------------------------------------------------------------------------------------------------------; 15echo --------------------------------------------COMPRESSED TO COMPRESSED------------------------------------------; 16echo --------------------------------------------------------------------------------------------------------------; 17 18--sync_slave_with_master 19eval CREATE TABLE t1 (a BLOB COMPRESSED default "1111111111") ENGINE=$engine_type2; 20eval CREATE TABLE t2 (a TINYBLOB COMPRESSED) ENGINE=$engine_type2; 21eval CREATE TABLE t3 (a MEDIUMBLOB COMPRESSED) ENGINE=$engine_type2; 22eval CREATE TABLE t4 (a LONGBLOB COMPRESSED) ENGINE=$engine_type2; 23eval CREATE TABLE t5 (a TEXT COMPRESSED) ENGINE=$engine_type2; 24eval CREATE TABLE t6 (a TINYTEXT COMPRESSED) ENGINE=$engine_type2; 25eval CREATE TABLE t7 (a MEDIUMTEXT COMPRESSED) ENGINE=$engine_type2; 26eval CREATE TABLE t8 (a LONGTEXT COMPRESSED) ENGINE=$engine_type2; 27eval CREATE TABLE t9 (a VARCHAR(10000) COMPRESSED default "0") ENGINE=$engine_type2; 28eval CREATE TABLE t10 (a VARBINARY(10000) COMPRESSED) ENGINE=$engine_type2; 29 30--connection master 31eval CREATE TABLE t1 (a BLOB COMPRESSED default "1111111111") ENGINE=$engine_type; 32eval CREATE TABLE t2 (a TINYBLOB COMPRESSED) ENGINE=$engine_type; 33eval CREATE TABLE t3 (a MEDIUMBLOB COMPRESSED) ENGINE=$engine_type; 34eval CREATE TABLE t4 (a LONGBLOB COMPRESSED) ENGINE=$engine_type; 35eval CREATE TABLE t5 (a TEXT COMPRESSED) ENGINE=$engine_type; 36eval CREATE TABLE t6 (a TINYTEXT COMPRESSED) ENGINE=$engine_type; 37eval CREATE TABLE t7 (a MEDIUMTEXT COMPRESSED) ENGINE=$engine_type; 38eval CREATE TABLE t8 (a LONGTEXT COMPRESSED) ENGINE=$engine_type; 39eval CREATE TABLE t9 (a VARCHAR(10000) COMPRESSED default "0") ENGINE=$engine_type; 40eval CREATE TABLE t10 (a VARBINARY(10000) COMPRESSED) ENGINE=$engine_type; 41 42INSERT INTO t1(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),(default); 43INSERT INTO t2(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 44INSERT INTO t3(a) VALUES(REPEAT('a',100)),(REPEAT('v',2000)),("April"),(NULL); 45INSERT INTO t4(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("00-00-00"); 46INSERT INTO t5(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL); 47INSERT INTO t6(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 48INSERT INTO t7(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',3000)),(NULL); 49INSERT INTO t8(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("qwerty"); 50INSERT INTO t9(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("12-12-2009"),(default); 51INSERT INTO t10(a) VALUES(REPEAT('a',1000)),(REPEAT('v',2000)),(REPEAT('r',300)),(NULL), (""); 52 53echo --------------------------------------------------data on master------------------------------------------------; 54 55let $a= 10; 56while ($a) 57{ 58 --sorted_result 59 eval SELECT LENGTH(a), SUBSTRING(a,1,10), "M,compressed" from t$a; 60 --sorted_result 61 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 62 dec $a; 63} 64SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 65 66echo --------------------------------------------------data on slave-------------------------------------------------; 67 68--sync_slave_with_master 69let $a= 10; 70while ($a) 71{ 72 --sorted_result 73 eval SELECT LENGTH(a), SUBSTRING(a,1,10), "S,compressed" from t$a; 74 --sorted_result 75 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 76 dec $a; 77} 78 79SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 80 81--connection master 82DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; 83FLUSH STATUS; 84 85echo -----------------------------------------------------------------------------------------------------------------; 86echo ---------------------------------------COMPRESSED TO NOT COMPRESSED----------------------------------------------; 87echo -----------------------------------------------------------------------------------------------------------------; 88 89--sync_slave_with_master 90FLUSH STATUS; 91eval CREATE TABLE t1 (a BLOB) ENGINE=$engine_type2; 92eval CREATE TABLE t2 (a TINYBLOB) ENGINE=$engine_type2; 93eval CREATE TABLE t3 (a MEDIUMBLOB) ENGINE=$engine_type2; 94eval CREATE TABLE t4 (a LONGBLOB) ENGINE=$engine_type2; 95eval CREATE TABLE t5 (a TEXT) ENGINE=$engine_type2; 96eval CREATE TABLE t6 (a TINYTEXT) ENGINE=$engine_type2; 97eval CREATE TABLE t7 (a MEDIUMTEXT) ENGINE=$engine_type2; 98eval CREATE TABLE t8 (a LONGTEXT) ENGINE=$engine_type2; 99eval CREATE TABLE t9 (a VARCHAR(10000)) ENGINE=$engine_type2; 100eval CREATE TABLE t10 (a VARBINARY(10000)) ENGINE=$engine_type2; 101 102--connection master 103eval CREATE TABLE IF NOT EXISTS t1 (a BLOB COMPRESSED) ENGINE=$engine_type; 104eval CREATE TABLE IF NOT EXISTS t2 (a TINYBLOB COMPRESSED) ENGINE=$engine_type; 105eval CREATE TABLE IF NOT EXISTS t3 (a MEDIUMBLOB COMPRESSED) ENGINE=$engine_type; 106eval CREATE TABLE IF NOT EXISTS t4 (a LONGBLOB COMPRESSED) ENGINE=$engine_type; 107eval CREATE TABLE IF NOT EXISTS t5 (a TEXT COMPRESSED) ENGINE=$engine_type; 108eval CREATE TABLE IF NOT EXISTS t6 (a TINYTEXT COMPRESSED) ENGINE=$engine_type; 109eval CREATE TABLE IF NOT EXISTS t7 (a MEDIUMTEXT COMPRESSED) ENGINE=$engine_type; 110eval CREATE TABLE IF NOT EXISTS t8 (a LONGTEXT COMPRESSED) ENGINE=$engine_type; 111eval CREATE TABLE IF NOT EXISTS t9 (a VARCHAR(10000) COMPRESSED) ENGINE=$engine_type; 112eval CREATE TABLE IF NOT EXISTS t10 (a VARBINARY(10000) COMPRESSED) ENGINE=$engine_type; 113 114INSERT INTO t1(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL); 115INSERT INTO t2(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 116INSERT INTO t3(a) VALUES(REPEAT('a',100)),(REPEAT('v',2000)),("April"),(NULL); 117INSERT INTO t4(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("00-00-00"); 118INSERT INTO t5(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL); 119INSERT INTO t6(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 120INSERT INTO t7(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',3000)),(NULL); 121INSERT INTO t8(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("qwerty"); 122INSERT INTO t9(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("12-12-2009"); 123INSERT INTO t10(a) VALUES(REPEAT('a',1000)),(REPEAT('v',2000)),(REPEAT('r',300)),(NULL), (""); 124 125echo ---------------------------------------data on master-----------------------------------------------------------; 126 127let $a= 10; 128while ($a) 129{ 130 --sorted_result 131 eval SELECT LENGTH(a),SUBSTRING(a,1,10), "M,compressed" from t$a; 132 --sorted_result 133 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 134 dec $a; 135} 136SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 137 138echo ---------------------------------------data on slave-------------------------------------------------------------; 139 140--sync_slave_with_master 141let $a= 10; 142while ($a) 143{ 144 --sorted_result 145 eval SELECT LENGTH(a), SUBSTRING(a,1,10), "S,not compressed" from t$a; 146 --sorted_result 147 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 148 dec $a; 149} 150 151SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 152--connection master 153DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; 154FLUSH STATUS; 155 156echo -----------------------------------------------------------------------------------------------------------------; 157echo -----------------------------------NOT COMPRESSED to COMPRESSED--------------------------------------------------; 158echo -----------------------------------------------------------------------------------------------------------------; 159 160--sync_slave_with_master 161FLUSH STATUS; 162eval CREATE TABLE t1 (a BLOB COMPRESSED) ENGINE=$engine_type2; 163eval CREATE TABLE t2 (a TINYBLOB COMPRESSED) ENGINE=$engine_type2; 164eval CREATE TABLE t3 (a MEDIUMBLOB COMPRESSED) ENGINE=$engine_type2; 165eval CREATE TABLE t4 (a LONGBLOB COMPRESSED) ENGINE=$engine_type2; 166eval CREATE TABLE t5 (a TEXT COMPRESSED) ENGINE=$engine_type2; 167eval CREATE TABLE t6 (a TINYTEXT COMPRESSED) ENGINE=$engine_type2; 168eval CREATE TABLE t7 (a MEDIUMTEXT COMPRESSED) ENGINE=$engine_type2; 169eval CREATE TABLE t8 (a LONGTEXT COMPRESSED) ENGINE=$engine_type2; 170eval CREATE TABLE t9 (a VARCHAR(10000) COMPRESSED) ENGINE=$engine_type2; 171eval CREATE TABLE t10 (a VARBINARY(10000) COMPRESSED) ENGINE=$engine_type2; 172 173--connection master 174eval CREATE TABLE IF NOT EXISTS t1 (a BLOB) ENGINE=$engine_type; 175eval CREATE TABLE IF NOT EXISTS t2 (a TINYBLOB) ENGINE=$engine_type; 176eval CREATE TABLE IF NOT EXISTS t3 (a MEDIUMBLOB) ENGINE=$engine_type; 177eval CREATE TABLE IF NOT EXISTS t4 (a LONGBLOB) ENGINE=$engine_type; 178eval CREATE TABLE IF NOT EXISTS t5 (a TEXT) ENGINE=$engine_type; 179eval CREATE TABLE IF NOT EXISTS t6 (a TINYTEXT) ENGINE=$engine_type; 180eval CREATE TABLE IF NOT EXISTS t7 (a MEDIUMTEXT) ENGINE=$engine_type; 181eval CREATE TABLE IF NOT EXISTS t8 (a LONGTEXT) ENGINE=$engine_type; 182eval CREATE TABLE IF NOT EXISTS t9 (a VARCHAR(10000)) ENGINE=$engine_type; 183eval CREATE TABLE IF NOT EXISTS t10 (a VARBINARY(10000)) ENGINE=$engine_type; 184 185INSERT INTO t1(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL); 186INSERT INTO t2(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 187INSERT INTO t3(a) VALUES(REPEAT('a',100)),(REPEAT('v',2000)),("April"),(NULL); 188INSERT INTO t4(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("00-00-00"); 189INSERT INTO t5(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL); 190INSERT INTO t6(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',254)),(NULL); 191INSERT INTO t7(a) VALUES(REPEAT('a',100)),(REPEAT('v',200)),(REPEAT('r',3000)),(NULL); 192INSERT INTO t8(a) VALUES(REPEAT('a',10000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("qwerty"); 193INSERT INTO t9(a) VALUES(REPEAT('a',1000)),(REPEAT('v',200)),(REPEAT('r',300)),(NULL),("12-12-2009"); 194INSERT INTO t10(a) VALUES(REPEAT('a',1000)),(REPEAT('v',2000)),(REPEAT('r',300)),(NULL), (""); 195 196echo -----------------------------------data on master---------------------------------------------------------------; 197 198let $a= 10; 199while ($a) 200{ 201 --sorted_result 202 eval SELECT LENGTH(a),SUBSTRING(a,1,10), "M,not compressed" from t$a; 203 --sorted_result 204 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 205 dec $a; 206} 207SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 208 209echo -----------------------------------data on slave-----------------------------------------------------------------; 210 211--sync_slave_with_master 212let $a= 10; 213while ($a) 214{ 215 --sorted_result 216 eval SELECT LENGTH(a), SUBSTRING(a,1,10), "S,compressed" from t$a; 217 --sorted_result 218 eval SELECT DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t$a'; 219 dec $a; 220} 221SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN('Column_compressions', 'Column_decompressions'); 222 223--connection master 224DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; 225 226echo -----------------------------------------------------------------------------------------------------------------; 227echo ------varchar(1000)->varchar(999) compressed, varchar(1000) compressed->varchar(999) ----------------------------; 228echo -----------------------------------------------------------------------------------------------------------------; 229 230--sync_slave_with_master 231eval CREATE TABLE t1 (a VARCHAR(999) COMPRESSED) ENGINE=$engine_type2; 232eval CREATE TABLE t2 (a VARCHAR(999)) ENGINE=$engine_type2; 233show warnings; 234call mtr.add_suppression("Slave: Data too long for column 'a' at row 1 Error_code: 1406"); 235call mtr.add_suppression("Slave: Table 't2' already exists Error_code: 1050"); 236 237--connection master 238eval CREATE TABLE IF NOT EXISTS t1 (a VARCHAR(1000))ENGINE=$engine_type; 239eval CREATE TABLE IF NOT EXISTS t2 (a VARCHAR(1000) COMPRESSED)ENGINE=$engine_type; 240 241INSERT INTO t1(a) VALUES(REPEAT('a',1000)); 242--connection slave 243--let $slave_sql_errno= 1406,1677 244--source include/wait_for_slave_sql_error_and_skip.inc 245show warnings; 246 247--connection master 248 INSERT INTO t2(a) VALUES(REPEAT('a',1000)); 249--connection slave 250--let $slave_sql_errno= 1406,1677 251--source include/wait_for_slave_sql_error_and_skip.inc 252show warnings; 253 254SET GLOBAL SLAVE_TYPE_CONVERSIONS = @saved_slave_type_conversions; 255 256--connection master 257DROP TABLE t1,t2; 258--sync_slave_with_master 259 260 261