Debian Codegraph @

mysql-test/suite/binlog_encryption/encrypted_slave.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 #################
8 # Test flow
9 #################
10 connection server_1;
11 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
12 CREATE DATABASE database_name_to_encrypt;
13 USE database_name_to_encrypt;
14 CREATE USER user_name_to_encrypt;
15 GRANT ALL ON database_name_to_encrypt.* TO user_name_to_encrypt;
16 SET PASSWORD FOR user_name_to_encrypt = PASSWORD('password_to_encrypt');
17 CREATE TABLE innodb_table_name_to_encrypt (
18 int_column_name_to_encrypt INT PRIMARY KEY,
19 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
20 blob_column_name_to_encrypt BLOB,
21 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
22 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
23 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
24 ) ENGINE=InnoDB
25 PARTITION BY RANGE (int_column_name_to_encrypt)
26 SUBPARTITION BY KEY (int_column_name_to_encrypt)
27 SUBPARTITIONS 2 (
28 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
29 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
30 )
31 ;
32 CREATE TABLE myisam_table_name_to_encrypt (
33 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
34 char_column_name_to_encrypt VARCHAR(255),
35 datetime_column_name_to_encrypt DATETIME,
36 text_column_name_to_encrypt TEXT
37 ) ENGINE=MyISAM;
38 CREATE TABLE aria_table_name_to_encrypt (
39 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
40 varchar_column_name_to_encrypt VARCHAR(1024),
41 enum_column_name_to_encrypt ENUM(
42 'enum_value1_to_encrypt',
43 'enum_value2_to_encrypt'
44 ),
45 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
46 blob_column_name_to_encrypt BLOB
47 ) ENGINE=Aria;
48 CREATE TRIGGER trigger_name_to_encrypt
49 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
50 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
51 VALUES (NEW.char_column_name_to_encrypt);
52 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
53 AS SELECT * FROM innodb_table_name_to_encrypt;
54 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
55 RETURNS VARCHAR(64)
56 RETURN 'func_result_to_encrypt';
57 CREATE PROCEDURE proc_name_to_encrypt (
58 IN proc_in_parameter_to_encrypt CHAR(32),
59 OUT proc_out_parameter_to_encrypt INT
60 )
61 BEGIN
62 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
63 DECLARE cursor_name_to_encrypt CURSOR FOR
64 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
65 DECLARE EXIT HANDLER FOR NOT FOUND
66 BEGIN
67 SET @stmt_var_to_encrypt = CONCAT(
68 "SELECT
69 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
70 FROM innodb_table_name_to_encrypt
71 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
72 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
73 EXECUTE stmt_to_encrypt;
74 DEALLOCATE PREPARE stmt_to_encrypt;
75 END;
76 OPEN cursor_name_to_encrypt;
77 proc_label_to_encrypt: LOOP
78 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
79 END LOOP;
80 CLOSE cursor_name_to_encrypt;
81 END $$
82 CREATE SERVER server_name_to_encrypt
83 FOREIGN DATA WRAPPER mysql
84 OPTIONS (HOST 'host_name_to_encrypt');
85 connect con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt;
86 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
87 float_column_name_to_encrypt FLOAT,
88 binary_column_name_to_encrypt BINARY(64)
89 );
90 disconnect con1;
91 connection server_1;
92 CREATE INDEX index_name_to_encrypt
93 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
94 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
95 ALTER TABLE innodb_table_name_to_encrypt
96 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
97 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
98 ;
99 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
100 AS SELECT * FROM innodb_table_name_to_encrypt;
101 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
102 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
103 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
104 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
105 INSERT INTO view_name_to_encrypt VALUES
106 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
107 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
108 ;
109 BEGIN NOT ATOMIC
110 DECLARE counter_name_to_encrypt INT DEFAULT 0;
111 START TRANSACTION;
112 WHILE counter_name_to_encrypt<12 DO
113 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
114 INSERT INTO innodb_table_name_to_encrypt
115 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
116 FROM innodb_table_name_to_encrypt
117 ORDER BY int_column_name_to_encrypt;
118 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
119 END WHILE;
120 COMMIT;
121 END
122 $$
123 INSERT INTO myisam_table_name_to_encrypt
124 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
125 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
126 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
127 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
128 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
129 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
130 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
131 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
132 TRUNCATE TABLE aria_table_name_to_encrypt;
133 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
134 (enum_column_name_to_encrypt);
135 LOAD DATA LOCAL INFILE '<DATADIR>/database_name_to_encrypt/file_name_to_encrypt'
136 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
137 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
138 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
139 ;
140 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
141 ANALYZE TABLE myisam_table_name_to_encrypt;
142 CHECK TABLE aria_table_name_to_encrypt;
143 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
144 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
145 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
146 DROP DATABASE database_name_to_encrypt;
147 DROP USER new_user_name_to_encrypt;
148 DROP SERVER server_name_to_encrypt;
149 #################
150 # Master binlog checks
151 #################
152 FOUND 1 /_to_encrypt.*/ in master-bin.0*
153 FOUND 1 /COMMIT.*/ in master-bin.0*
154 FOUND 1 /TIMESTAMP.*/ in master-bin.0*
155 include/save_master_pos.inc
156 #################
157 # Relay log checks
158 #################
159 connection server_2;
160 include/sync_io_with_master.inc
161 NOT FOUND /_to_encrypt/ in slave-relay-bin.0*
162 NOT FOUND /COMMIT/ in slave-relay-bin.0*
163 NOT FOUND /TIMESTAMP/ in slave-relay-bin.0*
164 #################
165 # Slave binlog checks
166 #################
167 include/start_slave.inc
168 include/sync_slave_sql_with_io.inc
169 include/sync_io_with_master.inc
170 NOT FOUND /_to_encrypt/ in slave-bin.0*
171 NOT FOUND /COMMIT/ in slave-bin.0*
172 NOT FOUND /TIMESTAMP/ in slave-bin.0*
173 ##########
174 # Cleanup
175 ##########
176 include/rpl_end.inc