rpl_extraMaster_Col.test 13.4 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
#############################################################
# Purpose: To test having extra columns on the master WL#3915
# engine inspecific sourced part
#############################################################

# TODO: partition specific
# -- source include/have_partition.inc

########### Clean up ################
--disable_warnings
--disable_query_log
DROP TABLE IF EXISTS  t1,t2,t3,t4,t31;

--enable_query_log
--enable_warnings

#
# Setup differently defined tables on master and slave
#

# Def on master: t (f_1 type_m_1,... f_s type_m_s, f_s1, f_m)
# Def on slave:  t (f_1 type_s_1,... f_s type_s_s)
# where type_mi,type_si (0 < i-1 <s1) pairs are compatible types (WL#3228)
# Arbitrary paramaters of the test are:
# 1. the tables type
# 2. the types of the extra master's column f_s1,..., f_m
# 3. the numbers of common columns `s' 
# 4. and  extra columns `m' are par
#
# optionally
#
# 5. vary the common columns type within compatible ranges.

#
# constant size column type:

#BIGINT       
#BLOB         
#DATE         
#DATETIME     
#FLOAT        
#INT, INTEGER 
#LONGBLOB      
#LONGTEXT     
#MEDIUMBLOB   
#MEDIUMINT    
#MEDIUMTEXT   
#REAL         
#SMALLINT     
#TEXT         
#TIME         
#TIMESTAMP    
#TINYBLOB     
#TINYINT      
#TINYTEXT     
#YEAR         

# variable size column types:

#BINARY(M)    
#BIT(M)        
#CHAR(M)      
#DECIMAL(M,D) 
#DOUBLE[P]    
#ENUM         
#FLOAT(p)     
#NUMERIC(M,D) 
#SET           
#VARBINARY(M) 
#VARCHAR(M)    
#


connection master;
    eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
                      /* extra */
                       f5 FLOAT DEFAULT '2.00', 
                       f6 CHAR(4) DEFAULT 'TEST',
		       f7 INT DEFAULT '0',
		       f8 TEXT,
		       f9 LONGBLOB,
           f10 BIT(63),
		       f11 VARBINARY(64))
                      ENGINE=$engine_type;

#connection slave;
   sync_slave_with_master;
   alter table t1 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

connection master;

   INSERT into t1 values (1, 1, 1, 'first', 1.0, 'yksi', 1, 'lounge of happiness', 'very fat blob', b'01010101010101', 0x123456);
   INSERT into t1 values (2, 2, 2, 'second', 2.0, 'kaks', 2, 'got stolen from the paradise', 'very fat blob', b'01010101010101', 0x123456), (3, 3, 3, 'third', 3.0, 'kolm', 3, 'got stolen from the paradise', 'very fat blob', b'01010101010101', 0x123456);
   update t1 set f4= 'next' where f1=1;
   delete from t1 where f1=1;

   select * from t1 order by f3;


#connection slave;
   sync_slave_with_master;
102
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 #
103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375
--query_vertical show slave status;
   select * from t1 order by f3;


### Altering table def scenario

connection master;

   eval CREATE TABLE t2 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
                      /* extra */
                       f5 DOUBLE DEFAULT '2.00', 
                       f6 ENUM('a', 'b', 'c') default 'a',
		       f7 DECIMAL(17,9) default '1000.00',
		       f8 MEDIUMBLOB,
		       f9 NUMERIC(6,4) default '2000.00',
		       f10 VARCHAR(1024),
		       f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
		       f12 SET('a', 'b', 'c') default 'b')
                       ENGINE=$engine_type;

   eval CREATE TABLE t3 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
                      /* extra */
                       f5 DOUBLE DEFAULT '2.00', 
                       f6 ENUM('a', 'b', 'c') default 'a',
		       f8 MEDIUMBLOB,
		       f10 VARCHAR(1024),
		       f11 BINARY(20) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
		       f12 SET('a', 'b', 'c') default 'b')
                       ENGINE=$engine_type;


# no ENUM and SET
    eval CREATE TABLE t4 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
                      /* extra */
                       f5 DOUBLE DEFAULT '2.00', 
		       f6 DECIMAL(17,9) default '1000.00',
		       f7 MEDIUMBLOB,
		       f8 NUMERIC(6,4) default '2000.00',
		       f9 VARCHAR(1024),
		       f10 BINARY(20) not null default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
		       f11 CHAR(255))
                       ENGINE=$engine_type;


    eval CREATE TABLE t31 (f1 INT, f2 INT, f3 INT PRIMARY KEY, f4 CHAR(20),
                       
                       /* extra */

                       f5  BIGINT,
                       f6  BLOB,
		       f7  DATE,
		       f8  DATETIME,
		       f9  FLOAT,
		       f10 INT,
		       f11 LONGBLOB,
		       f12 LONGTEXT,
		       f13 MEDIUMBLOB,
		       f14 MEDIUMINT,
		       f15 MEDIUMTEXT,
		       f16 REAL,
		       f17 SMALLINT,
		       f18 TEXT,
		       f19 TIME,
		       f20 TIMESTAMP,
		       f21 TINYBLOB,
		       f22 TINYINT,
		       f23 TINYTEXT,
		       f24 YEAR,
		       f25 BINARY(255),
		       f26 BIT(64),
		       f27 CHAR(255),
		       f28 DECIMAL(30,7),
		       f29 DOUBLE,
		       f30 ENUM ('a','b', 'c') default 'a',
		       f31 FLOAT,
		       f32 NUMERIC(17,9),
		       f33 SET ('a', 'b', 'c') default 'b',
		       f34 VARBINARY(1025),
		       f35 VARCHAR(257)       
                       ) ENGINE=$engine_type;

#connection slave;
    sync_slave_with_master;
    alter table t2 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11, drop
f12;
    alter table t3 drop f5, drop f6, drop f8, drop f10, drop f11, drop f12;
    alter table t4 drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11;

    alter table t31 
       drop f5, drop f6, drop f7, drop f8, drop f9, drop f10, drop f11,
       drop f12, drop f13, drop f14, drop f15, drop f16, drop f17, drop f18,
       drop f19, drop f20, drop f21, drop f22, drop f23, drop f24, drop f25,
       drop f26, drop f27, drop f28, drop f29, drop f30, drop f31, drop f32,
       drop f33, drop f34, drop f35;
                 


connection master;
   INSERT into t2 set f1=1, f2=1, f3=1, f4='first', f8='f8: medium size blob', f10='f10:
some var char';
   INSERT into t2 values (2, 2, 2, 'second',
       2.0, 'b', 2000.0002, 'f8: medium size blob', 2000, 'f10: some var char',
'01234567', 'c'),
                       (3, 3, 3, 'third',
       3.0, 'b', 3000.0003, 'f8: medium size blob', 3000, 'f10: some var char',
'01234567', 'c');
   INSERT into t3 set f1=1, f2=1, f3=1, f4='first', f10='f10: some var char';
   INSERT into t4 set f1=1, f2=1, f3=1, f4='first', f7='f7: medium size blob', f10='f10:
binary data';
   INSERT into t31 set f1=1, f2=1, f3=1, f4='first';
   INSERT into t31 set f1=1, f2=1, f3=2, f4='second',
     f9=2.2,  f10='seven samurai', f28=222.222, f35='222';
   INSERT into t31 values (1, 1, 3, 'third',
      /* f5  BIGINT,  */            333333333333333333333333,
      /* f6  BLOB,  */              '3333333333333333333333',
      /* f7  DATE,  */              '2007-07-18',
      /* f8  DATETIME,  */          "2007-07-18",
      /* f9  FLOAT,  */             3.33333333,
      /* f10 INT,  */               333333333,
      /* f11 LONGBLOB,  */          '3333333333333333333',
      /* f12 LONGTEXT,  */          '3333333333333333333',
      /* f13 MEDIUMBLOB,  */        '3333333333333333333',
      /* f14 MEDIUMINT,  */         33,
      /* f15 MEDIUMTEXT,  */        3.3,
      /* f16 REAL,  */              3.3,
      /* f17 SMALLINT,  */          3,
      /* f18 TEXT,  */              '33',
      /* f19 TIME,  */              '2:59:58.999',
      /* f20 TIMESTAMP,  */         20000303000000,
      /* f21 TINYBLOB,  */          '3333',
      /* f22 TINYINT,  */           3,
      /* f23 TINYTEXT,  */          '3',
      /* f24 YEAR,  */              3000,
      /* f25 BINARY(255),  */       'three_33333',
      /* f26 BIT(64),  */           b'011', 
      /* f27 CHAR(255),  */         'three',
      /* f28 DECIMAL(30,7),  */     3.333,
      /* f29 DOUBLE,  */            3.333333333333333333333333333,
      /* f30 ENUM ('a','b','c')*/   'c',
      /* f31 FLOAT,  */             3.0,
      /* f32 NUMERIC(17,9),  */     3.3333,
      /* f33 SET ('a','b','c'),*/   'c',
      /*f34 VARBINARY(1025),*/      '3333 minus 3',
      /*f35 VARCHAR(257),*/         'three times three'
      );
   
   INSERT into t31 values (1, 1, 4, 'fourth',
       /* f5  BIGINT,  */            333333333333333333333333,
       /* f6  BLOB,  */              '3333333333333333333333',
       /* f7  DATE,  */              '2007-07-18',
       /* f8  DATETIME,  */          "2007-07-18",
       /* f9  FLOAT,  */             3.33333333,
       /* f10 INT,  */               333333333,
       /* f11 LONGBLOB,  */          '3333333333333333333',
       /* f12 LONGTEXT,  */          '3333333333333333333',
       /* f13 MEDIUMBLOB,  */        '3333333333333333333',
       /* f14 MEDIUMINT,  */         33,
       /* f15 MEDIUMTEXT,  */        3.3,
       /* f16 REAL,  */              3.3,
       /* f17 SMALLINT,  */          3,
       /* f18 TEXT,  */              '33',
       /* f19 TIME,  */              '2:59:58.999',
       /* f20 TIMESTAMP,  */         20000303000000,
       /* f21 TINYBLOB,  */          '3333',
       /* f22 TINYINT,  */           3,
       /* f23 TINYTEXT,  */          '3',
       /* f24 YEAR,  */              3000,
       /* f25 BINARY(255),  */       'three_33333',
       /* f26 BIT(64),  */           b'011',
       /* f27 CHAR(255),  */         'three',
       /* f28 DECIMAL(30,7),  */     3.333,
       /* f29 DOUBLE,  */            3.333333333333333333333333333,
       /* f30 ENUM ('a','b','c')*/   'c',
       /* f31 FLOAT,  */             3.0,
       /* f32 NUMERIC(17,9),  */     3.3333,
       /* f33 SET ('a','b','c'),*/   'c',
       /*f34 VARBINARY(1025),*/      '3333 minus 3',
       /*f35 VARCHAR(257),*/         'three times three'
       ),
   (1, 1, 5, 'fifth',
       /* f5  BIGINT,  */            333333333333333333333333,
       /* f6  BLOB,  */              '3333333333333333333333',
       /* f7  DATE,  */              '2007-07-18',
       /* f8  DATETIME,  */          "2007-07-18",
       /* f9  FLOAT,  */             3.33333333,
       /* f10 INT,  */               333333333,
       /* f11 LONGBLOB,  */          '3333333333333333333',
       /* f12 LONGTEXT,  */          '3333333333333333333',
       /* f13 MEDIUMBLOB,  */        '3333333333333333333',
       /* f14 MEDIUMINT,  */         33,
       /* f15 MEDIUMTEXT,  */        3.3,
       /* f16 REAL,  */              3.3,
       /* f17 SMALLINT,  */          3,
       /* f18 TEXT,  */              '33',
       /* f19 TIME,  */              '2:59:58.999',
       /* f20 TIMESTAMP,  */         20000303000000,
       /* f21 TINYBLOB,  */          '3333',
       /* f22 TINYINT,  */           3,
       /* f23 TINYTEXT,  */          '3',
       /* f24 YEAR,  */              3000,
       /* f25 BINARY(255),  */       'three_33333',
       /* f26 BIT(64),  */           b'011',
       /* f27 CHAR(255),  */         'three',
       /* f28 DECIMAL(30,7),  */     3.333,
       /* f29 DOUBLE,  */            3.333333333333333333333333333,
       /* f30 ENUM ('a','b','c')*/   'c',
       /* f31 FLOAT,  */             3.0,
       /* f32 NUMERIC(17,9),  */     3.3333,
       /* f33 SET ('a','b','c'),*/   'c',
       /*f34 VARBINARY(1025),*/      '3333 minus 3',
       /*f35 VARCHAR(257),*/         'three times three'
       ),
   (1, 1, 6, 'sixth',
       /* f5  BIGINT,  */            NULL,
       /* f6  BLOB,  */              '3333333333333333333333',
       /* f7  DATE,  */              '2007-07-18',
       /* f8  DATETIME,  */          "2007-07-18",
       /* f9  FLOAT,  */             3.33333333,
       /* f10 INT,  */               333333333,
       /* f11 LONGBLOB,  */          '3333333333333333333',
       /* f12 LONGTEXT,  */          '3333333333333333333',
       /* f13 MEDIUMBLOB,  */        '3333333333333333333',
       /* f14 MEDIUMINT,  */         33,
       /* f15 MEDIUMTEXT,  */        3.3,
       /* f16 REAL,  */              3.3,
       /* f17 SMALLINT,  */          3,
       /* f18 TEXT,  */              '33',
       /* f19 TIME,  */              '2:59:58.999',
       /* f20 TIMESTAMP,  */         20000303000000,
       /* f21 TINYBLOB,  */          '3333',
       /* f22 TINYINT,  */           3,
       /* f23 TINYTEXT,  */          '3',
       /* f24 YEAR,  */              3000,
       /* f25 BINARY(255),  */       'three_33333',
       /* f26 BIT(64),  */           b'011',
       /* f27 CHAR(255),  */         'three',
       /* f28 DECIMAL(30,7),  */     3.333,
       /* f29 DOUBLE,  */            3.333333333333333333333333333,
       /* f30 ENUM ('a','b','c')*/   'c',
       /* f31 FLOAT,  */             3.0,
       /* f32 NUMERIC(17,9),  */     3.3333,
       /* f33 SET ('a','b','c'),*/   'c',
       /*f34 VARBINARY(1025),*/      '3333 minus 3',
       /*f35 VARCHAR(257),*/         NULL
       );
   
#connection slave;
   sync_slave_with_master;

   select * from t1 order by f3;
   select * from t2 order by f1;
   select * from t3 order by f1;
   select * from t4 order by f1;
   select * from t31 order by f1;
   
connection master;

   update t31 set f5=555555555555555 where f3=6;
   update t31 set f2=2 where f3=2;
   update t31 set f1=NULL where f3=1;
   update t31 set f3=NULL, f27=NULL, f35='f35 new value' where f3=3;

   delete from t1;
   delete from t2;
   delete from t3;
   delete from t4;
   delete from t31;

#connection slave;
   sync_slave_with_master;
   select * from t31;

--replace_result $MASTER_MYPORT MASTER_PORT
376
--replace_column 1 # 4 # 7 # 8 # 9 # 22 # 23 # 33 #
377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393
--query_vertical show slave status;

#### Clean Up ####

connection master;
--disable_warnings
--disable_query_log
  DROP TABLE t1,t2,t3,t4,t31;

#connection slave;
  sync_slave_with_master;
--enable_query_log
--enable_warnings

# END of the tests