Debian Codegraph @

mysql-test/suite/binlog_encryption/encrypted_master.result

raw
1 #################
2 # Initialization
3 #################
4 include/rpl_init.inc [topology=1->2]
5 connection server_2;
6 include/stop_slave_sql.inc
7 connection server_1;
8 SET @binlog_annotate_row_events.save= @@global.binlog_annotate_row_events;
9 SET @binlog_checksum.save= @@global.binlog_checksum;
10 SET @master_verify_checksum.save= @@global.master_verify_checksum;
11 SET @binlog_row_image.save= @@global.binlog_row_image;
12 ####################################################
13 # Test 1: simple binlog, no checksum, no annotation
14 ####################################################
15 connection server_1;
16 SET binlog_annotate_row_events= 0;
17 SET GLOBAL binlog_annotate_row_events= 0;
18 SET GLOBAL binlog_checksum= NONE;
19 SET GLOBAL master_verify_checksum= 0;
20 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
21 CREATE DATABASE database_name_to_encrypt;
22 USE database_name_to_encrypt;
23 CREATE USER user_name_to_encrypt;
24 GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
25 SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
26 CREATE TABLE innodb_table_name_to_encrypt (
27 int_column_name_to_encrypt INT PRIMARY KEY,
28 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
29 blob_column_name_to_encrypt BLOB,
30 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
31 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
32 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
33 ) ENGINE=InnoDB
34 PARTITION BY RANGE (int_column_name_to_encrypt)
35 SUBPARTITION BY KEY (int_column_name_to_encrypt)
36 SUBPARTITIONS 2 (
37 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
38 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
39 )
40 ;
41 CREATE TABLE myisam_table_name_to_encrypt (
42 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
43 char_column_name_to_encrypt VARCHAR(255),
44 datetime_column_name_to_encrypt DATETIME,
45 text_column_name_to_encrypt TEXT
46 ) ENGINE=MyISAM;
47 CREATE TABLE aria_table_name_to_encrypt (
48 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
49 varchar_column_name_to_encrypt VARCHAR(1024),
50 enum_column_name_to_encrypt ENUM(
51 'enum_value1_to_encrypt',
52 'enum_value2_to_encrypt'
53 ),
54 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
55 blob_column_name_to_encrypt BLOB
56 ) ENGINE=Aria;
57 CREATE TRIGGER trigger_name_to_encrypt
58 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
59 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
60 VALUES (NEW.char_column_name_to_encrypt);
61 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
62 AS SELECT * FROM innodb_table_name_to_encrypt;
63 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
64 RETURNS VARCHAR(64)
65 RETURN 'func_result_to_encrypt';
66 CREATE PROCEDURE proc_name_to_encrypt (
67 IN proc_in_parameter_to_encrypt CHAR(32),
68 OUT proc_out_parameter_to_encrypt INT
69 )
70 BEGIN
71 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
72 DECLARE cursor_name_to_encrypt CURSOR FOR
73 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
74 DECLARE EXIT HANDLER FOR NOT FOUND
75 BEGIN
76 SET @stmt_var_to_encrypt = CONCAT(
77 "SELECT
78 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
79 FROM innodb_table_name_to_encrypt
80 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
81 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
82 EXECUTE stmt_to_encrypt;
83 DEALLOCATE PREPARE stmt_to_encrypt;
84 END;
85 OPEN cursor_name_to_encrypt;
86 proc_label_to_encrypt: LOOP
87 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
88 END LOOP;
89 CLOSE cursor_name_to_encrypt;
90 END $$
91 CREATE SERVER server_name_to_encrypt
92 FOREIGN DATA WRAPPER mysql
93 OPTIONS (HOST 'host_name_to_encrypt');
94 connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
95 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
96 float_column_name_to_encrypt FLOAT,
97 binary_column_name_to_encrypt BINARY(64)
98 );
99 disconnect con1;
100 connection server_1;
101 CREATE INDEX index_name_to_encrypt
102 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
103 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
104 ALTER TABLE innodb_table_name_to_encrypt
105 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
106 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
107 ;
108 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
109 AS SELECT * FROM innodb_table_name_to_encrypt;
110 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
111 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
112 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
113 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
114 INSERT INTO view_name_to_encrypt VALUES
115 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
116 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
117 ;
118 BEGIN NOT ATOMIC
119 DECLARE counter_name_to_encrypt INT DEFAULT 0;
120 START TRANSACTION;
121 WHILE counter_name_to_encrypt<12 DO
122 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
123 INSERT INTO innodb_table_name_to_encrypt
124 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
125 FROM innodb_table_name_to_encrypt
126 ORDER BY int_column_name_to_encrypt;
127 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
128 END WHILE;
129 COMMIT;
130 END
131 $$
132 INSERT INTO myisam_table_name_to_encrypt
133 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
134 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
135 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
136 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
137 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
138 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
139 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
140 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
141 TRUNCATE TABLE aria_table_name_to_encrypt;
142 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
143 (enum_column_name_to_encrypt);
144 LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
145 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
146 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
147 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
148 ;
149 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
150 ANALYZE TABLE myisam_table_name_to_encrypt;
151 CHECK TABLE aria_table_name_to_encrypt;
152 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
153 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
154 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
155 DROP DATABASE database_name_to_encrypt;
156 DROP USER new_user_name_to_encrypt;
157 DROP SERVER server_name_to_encrypt;
158 ####################################################
159 # Test 2: binlog with checksum, no annotated events
160 ####################################################
161 connection server_1;
162 SET binlog_annotate_row_events= 0;
163 SET GLOBAL binlog_annotate_row_events= 0;
164 SET GLOBAL binlog_checksum= CRC32;
165 SET GLOBAL master_verify_checksum= 1;
166 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
167 CREATE DATABASE database_name_to_encrypt;
168 USE database_name_to_encrypt;
169 CREATE USER user_name_to_encrypt;
170 GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
171 SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
172 CREATE TABLE innodb_table_name_to_encrypt (
173 int_column_name_to_encrypt INT PRIMARY KEY,
174 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
175 blob_column_name_to_encrypt BLOB,
176 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
177 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
178 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
179 ) ENGINE=InnoDB
180 PARTITION BY RANGE (int_column_name_to_encrypt)
181 SUBPARTITION BY KEY (int_column_name_to_encrypt)
182 SUBPARTITIONS 2 (
183 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
184 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
185 )
186 ;
187 CREATE TABLE myisam_table_name_to_encrypt (
188 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
189 char_column_name_to_encrypt VARCHAR(255),
190 datetime_column_name_to_encrypt DATETIME,
191 text_column_name_to_encrypt TEXT
192 ) ENGINE=MyISAM;
193 CREATE TABLE aria_table_name_to_encrypt (
194 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
195 varchar_column_name_to_encrypt VARCHAR(1024),
196 enum_column_name_to_encrypt ENUM(
197 'enum_value1_to_encrypt',
198 'enum_value2_to_encrypt'
199 ),
200 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
201 blob_column_name_to_encrypt BLOB
202 ) ENGINE=Aria;
203 CREATE TRIGGER trigger_name_to_encrypt
204 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
205 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
206 VALUES (NEW.char_column_name_to_encrypt);
207 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
208 AS SELECT * FROM innodb_table_name_to_encrypt;
209 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
210 RETURNS VARCHAR(64)
211 RETURN 'func_result_to_encrypt';
212 CREATE PROCEDURE proc_name_to_encrypt (
213 IN proc_in_parameter_to_encrypt CHAR(32),
214 OUT proc_out_parameter_to_encrypt INT
215 )
216 BEGIN
217 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
218 DECLARE cursor_name_to_encrypt CURSOR FOR
219 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
220 DECLARE EXIT HANDLER FOR NOT FOUND
221 BEGIN
222 SET @stmt_var_to_encrypt = CONCAT(
223 "SELECT
224 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
225 FROM innodb_table_name_to_encrypt
226 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
227 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
228 EXECUTE stmt_to_encrypt;
229 DEALLOCATE PREPARE stmt_to_encrypt;
230 END;
231 OPEN cursor_name_to_encrypt;
232 proc_label_to_encrypt: LOOP
233 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
234 END LOOP;
235 CLOSE cursor_name_to_encrypt;
236 END $$
237 CREATE SERVER server_name_to_encrypt
238 FOREIGN DATA WRAPPER mysql
239 OPTIONS (HOST 'host_name_to_encrypt');
240 connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
241 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
242 float_column_name_to_encrypt FLOAT,
243 binary_column_name_to_encrypt BINARY(64)
244 );
245 disconnect con1;
246 connection server_1;
247 CREATE INDEX index_name_to_encrypt
248 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
249 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
250 ALTER TABLE innodb_table_name_to_encrypt
251 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
252 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
253 ;
254 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
255 AS SELECT * FROM innodb_table_name_to_encrypt;
256 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
257 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
258 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
259 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
260 INSERT INTO view_name_to_encrypt VALUES
261 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
262 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
263 ;
264 BEGIN NOT ATOMIC
265 DECLARE counter_name_to_encrypt INT DEFAULT 0;
266 START TRANSACTION;
267 WHILE counter_name_to_encrypt<12 DO
268 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
269 INSERT INTO innodb_table_name_to_encrypt
270 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
271 FROM innodb_table_name_to_encrypt
272 ORDER BY int_column_name_to_encrypt;
273 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
274 END WHILE;
275 COMMIT;
276 END
277 $$
278 INSERT INTO myisam_table_name_to_encrypt
279 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
280 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
281 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
282 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
283 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
284 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
285 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
286 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
287 TRUNCATE TABLE aria_table_name_to_encrypt;
288 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
289 (enum_column_name_to_encrypt);
290 LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
291 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
292 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
293 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
294 ;
295 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
296 ANALYZE TABLE myisam_table_name_to_encrypt;
297 CHECK TABLE aria_table_name_to_encrypt;
298 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
299 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
300 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
301 DROP DATABASE database_name_to_encrypt;
302 DROP USER new_user_name_to_encrypt;
303 DROP SERVER server_name_to_encrypt;
304 ####################################################
305 # Test 3: binlog with checksum and annotated events
306 ####################################################
307 connection server_1;
308 SET binlog_annotate_row_events= 1;
309 SET GLOBAL binlog_annotate_row_events= 1;
310 SET GLOBAL binlog_checksum= CRC32;
311 SET GLOBAL master_verify_checksum= 1;
312 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
313 CREATE DATABASE database_name_to_encrypt;
314 USE database_name_to_encrypt;
315 CREATE USER user_name_to_encrypt;
316 GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
317 SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
318 CREATE TABLE innodb_table_name_to_encrypt (
319 int_column_name_to_encrypt INT PRIMARY KEY,
320 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
321 blob_column_name_to_encrypt BLOB,
322 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
323 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
324 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
325 ) ENGINE=InnoDB
326 PARTITION BY RANGE (int_column_name_to_encrypt)
327 SUBPARTITION BY KEY (int_column_name_to_encrypt)
328 SUBPARTITIONS 2 (
329 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
330 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
331 )
332 ;
333 CREATE TABLE myisam_table_name_to_encrypt (
334 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
335 char_column_name_to_encrypt VARCHAR(255),
336 datetime_column_name_to_encrypt DATETIME,
337 text_column_name_to_encrypt TEXT
338 ) ENGINE=MyISAM;
339 CREATE TABLE aria_table_name_to_encrypt (
340 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
341 varchar_column_name_to_encrypt VARCHAR(1024),
342 enum_column_name_to_encrypt ENUM(
343 'enum_value1_to_encrypt',
344 'enum_value2_to_encrypt'
345 ),
346 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
347 blob_column_name_to_encrypt BLOB
348 ) ENGINE=Aria;
349 CREATE TRIGGER trigger_name_to_encrypt
350 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
351 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
352 VALUES (NEW.char_column_name_to_encrypt);
353 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
354 AS SELECT * FROM innodb_table_name_to_encrypt;
355 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
356 RETURNS VARCHAR(64)
357 RETURN 'func_result_to_encrypt';
358 CREATE PROCEDURE proc_name_to_encrypt (
359 IN proc_in_parameter_to_encrypt CHAR(32),
360 OUT proc_out_parameter_to_encrypt INT
361 )
362 BEGIN
363 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
364 DECLARE cursor_name_to_encrypt CURSOR FOR
365 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
366 DECLARE EXIT HANDLER FOR NOT FOUND
367 BEGIN
368 SET @stmt_var_to_encrypt = CONCAT(
369 "SELECT
370 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
371 FROM innodb_table_name_to_encrypt
372 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
373 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
374 EXECUTE stmt_to_encrypt;
375 DEALLOCATE PREPARE stmt_to_encrypt;
376 END;
377 OPEN cursor_name_to_encrypt;
378 proc_label_to_encrypt: LOOP
379 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
380 END LOOP;
381 CLOSE cursor_name_to_encrypt;
382 END $$
383 CREATE SERVER server_name_to_encrypt
384 FOREIGN DATA WRAPPER mysql
385 OPTIONS (HOST 'host_name_to_encrypt');
386 connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
387 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
388 float_column_name_to_encrypt FLOAT,
389 binary_column_name_to_encrypt BINARY(64)
390 );
391 disconnect con1;
392 connection server_1;
393 CREATE INDEX index_name_to_encrypt
394 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
395 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
396 ALTER TABLE innodb_table_name_to_encrypt
397 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
398 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
399 ;
400 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
401 AS SELECT * FROM innodb_table_name_to_encrypt;
402 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
403 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
404 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
405 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
406 INSERT INTO view_name_to_encrypt VALUES
407 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
408 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
409 ;
410 BEGIN NOT ATOMIC
411 DECLARE counter_name_to_encrypt INT DEFAULT 0;
412 START TRANSACTION;
413 WHILE counter_name_to_encrypt<12 DO
414 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
415 INSERT INTO innodb_table_name_to_encrypt
416 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
417 FROM innodb_table_name_to_encrypt
418 ORDER BY int_column_name_to_encrypt;
419 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
420 END WHILE;
421 COMMIT;
422 END
423 $$
424 INSERT INTO myisam_table_name_to_encrypt
425 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
426 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
427 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
428 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
429 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
430 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
431 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
432 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
433 TRUNCATE TABLE aria_table_name_to_encrypt;
434 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
435 (enum_column_name_to_encrypt);
436 LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
437 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
438 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
439 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
440 ;
441 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
442 ANALYZE TABLE myisam_table_name_to_encrypt;
443 CHECK TABLE aria_table_name_to_encrypt;
444 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
445 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
446 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
447 DROP DATABASE database_name_to_encrypt;
448 DROP USER new_user_name_to_encrypt;
449 DROP SERVER server_name_to_encrypt;
450 ####################################################
451 # Test 4: binlog with annotated events and binlog_row_image=minimal
452 ####################################################
453 connection server_1;
454 SET binlog_annotate_row_events= 1;
455 SET GLOBAL binlog_annotate_row_events= 1;
456 SET GLOBAL binlog_checksum= NONE;
457 SET GLOBAL master_verify_checksum= 0;
458 SET GLOBAL binlog_row_image= MINIMAL;
459 SET binlog_row_image= MINIMAL;
460 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
461 CREATE DATABASE database_name_to_encrypt;
462 USE database_name_to_encrypt;
463 CREATE USER user_name_to_encrypt;
464 GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
465 SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
466 CREATE TABLE innodb_table_name_to_encrypt (
467 int_column_name_to_encrypt INT PRIMARY KEY,
468 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
469 blob_column_name_to_encrypt BLOB,
470 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
471 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
472 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
473 ) ENGINE=InnoDB
474 PARTITION BY RANGE (int_column_name_to_encrypt)
475 SUBPARTITION BY KEY (int_column_name_to_encrypt)
476 SUBPARTITIONS 2 (
477 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
478 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
479 )
480 ;
481 CREATE TABLE myisam_table_name_to_encrypt (
482 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
483 char_column_name_to_encrypt VARCHAR(255),
484 datetime_column_name_to_encrypt DATETIME,
485 text_column_name_to_encrypt TEXT
486 ) ENGINE=MyISAM;
487 CREATE TABLE aria_table_name_to_encrypt (
488 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
489 varchar_column_name_to_encrypt VARCHAR(1024),
490 enum_column_name_to_encrypt ENUM(
491 'enum_value1_to_encrypt',
492 'enum_value2_to_encrypt'
493 ),
494 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
495 blob_column_name_to_encrypt BLOB
496 ) ENGINE=Aria;
497 CREATE TRIGGER trigger_name_to_encrypt
498 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
499 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
500 VALUES (NEW.char_column_name_to_encrypt);
501 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
502 AS SELECT * FROM innodb_table_name_to_encrypt;
503 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
504 RETURNS VARCHAR(64)
505 RETURN 'func_result_to_encrypt';
506 CREATE PROCEDURE proc_name_to_encrypt (
507 IN proc_in_parameter_to_encrypt CHAR(32),
508 OUT proc_out_parameter_to_encrypt INT
509 )
510 BEGIN
511 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
512 DECLARE cursor_name_to_encrypt CURSOR FOR
513 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
514 DECLARE EXIT HANDLER FOR NOT FOUND
515 BEGIN
516 SET @stmt_var_to_encrypt = CONCAT(
517 "SELECT
518 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
519 FROM innodb_table_name_to_encrypt
520 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
521 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
522 EXECUTE stmt_to_encrypt;
523 DEALLOCATE PREPARE stmt_to_encrypt;
524 END;
525 OPEN cursor_name_to_encrypt;
526 proc_label_to_encrypt: LOOP
527 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
528 END LOOP;
529 CLOSE cursor_name_to_encrypt;
530 END $$
531 CREATE SERVER server_name_to_encrypt
532 FOREIGN DATA WRAPPER mysql
533 OPTIONS (HOST 'host_name_to_encrypt');
534 connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
535 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
536 float_column_name_to_encrypt FLOAT,
537 binary_column_name_to_encrypt BINARY(64)
538 );
539 disconnect con1;
540 connection server_1;
541 CREATE INDEX index_name_to_encrypt
542 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
543 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
544 ALTER TABLE innodb_table_name_to_encrypt
545 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
546 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
547 ;
548 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
549 AS SELECT * FROM innodb_table_name_to_encrypt;
550 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
551 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
552 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
553 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
554 INSERT INTO view_name_to_encrypt VALUES
555 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
556 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
557 ;
558 BEGIN NOT ATOMIC
559 DECLARE counter_name_to_encrypt INT DEFAULT 0;
560 START TRANSACTION;
561 WHILE counter_name_to_encrypt<12 DO
562 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
563 INSERT INTO innodb_table_name_to_encrypt
564 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
565 FROM innodb_table_name_to_encrypt
566 ORDER BY int_column_name_to_encrypt;
567 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
568 END WHILE;
569 COMMIT;
570 END
571 $$
572 INSERT INTO myisam_table_name_to_encrypt
573 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
574 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
575 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
576 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
577 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
578 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
579 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
580 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
581 TRUNCATE TABLE aria_table_name_to_encrypt;
582 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
583 (enum_column_name_to_encrypt);
584 LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
585 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
586 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
587 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
588 ;
589 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
590 ANALYZE TABLE myisam_table_name_to_encrypt;
591 CHECK TABLE aria_table_name_to_encrypt;
592 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
593 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
594 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
595 DROP DATABASE database_name_to_encrypt;
596 DROP USER new_user_name_to_encrypt;
597 DROP SERVER server_name_to_encrypt;
598 #############################
599 # Final checks for the master
600 #############################
601 NOT FOUND /_to_encrypt.*/ in master-bin.0*
602 NOT FOUND /COMMIT.*/ in master-bin.0*
603 NOT FOUND /TIMESTAMP.*/ in master-bin.0*
604 include/save_master_pos.inc
605 #############################
606 # Final checks for the slave
607 #############################
608 connection server_2;
609 include/sync_io_with_master.inc
610 FOUND 1 /_to_encrypt.*/ in slave-relay-bin.0*
611 FOUND 1 /COMMIT.*/ in slave-relay-bin.0*
612 FOUND 1 /TIMESTAMP.*/ in slave-relay-bin.0*
613 include/start_slave.inc
614 include/sync_slave_sql_with_io.inc
615 FOUND 1 /_to_encrypt.*/ in slave-bin.0*
616 FOUND 1 /COMMIT.*/ in slave-bin.0*
617 FOUND 1 /TIMESTAMP.*/ in slave-bin.0*
618 ##########
619 # Cleanup
620 ##########
621 connection server_1;
622 SET GLOBAL binlog_annotate_row_events= @binlog_annotate_row_events.save;
623 SET GLOBAL binlog_checksum= @binlog_checksum.save;
624 SET GLOBAL master_verify_checksum= @master_verify_checksum.save;
625 SET GLOBAL binlog_row_image= @binlog_row_image.save;
626 include/rpl_end.inc