Debian Codegraph @

mysql-test/suite/binlog_encryption/testdata.inc

raw
1 #
2 # This include file creates some basic events which should go to the binary log.
3 # What happens to the binary log depends on the test which calls the file,
4 # and should be checked from the test.
5 #
6 # Names are intentionally long and ugly, to make grepping more reliable.
7 #
8 # Some of events are considered unsafe for SBR (not necessarily correctly,
9 # but here isn't the place to check the logic), so we just suppress the warning.
10 #
11 # For those few queries which produce result sets (e.g. ANALYZE, CHECKSUM etc.),
12 # we don't care about the result, so it will not be printed to the output.
13
14 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
15
16 #
17 # Some DDL
18 #
19
20 CREATE DATABASE database_name_to_encrypt;
21 USE database_name_to_encrypt;
22
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
27 CREATE TABLE innodb_table_name_to_encrypt (
28 int_column_name_to_encrypt INT PRIMARY KEY,
29 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
30 blob_column_name_to_encrypt BLOB,
31 virt_column_name_to_encrypt INT AS (int_column_name_to_encrypt % 10) VIRTUAL,
32 pers_column_name_to_encrypt INT AS (int_column_name_to_encrypt) PERSISTENT,
33 INDEX `index_name_to_encrypt`(`timestamp_column_name_to_encrypt`)
34 ) ENGINE=InnoDB
35 PARTITION BY RANGE (int_column_name_to_encrypt)
36 SUBPARTITION BY KEY (int_column_name_to_encrypt)
37 SUBPARTITIONS 2 (
38 PARTITION partition0_name_to_encrypt VALUES LESS THAN (100),
39 PARTITION partition1_name_to_encrypt VALUES LESS THAN (MAXVALUE)
40 )
41 ;
42
43 CREATE TABLE myisam_table_name_to_encrypt (
44 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
45 char_column_name_to_encrypt VARCHAR(255),
46 datetime_column_name_to_encrypt DATETIME,
47 text_column_name_to_encrypt TEXT
48 ) ENGINE=MyISAM;
49
50 CREATE TABLE aria_table_name_to_encrypt (
51 int_column_name_to_encrypt INT AUTO_INCREMENT PRIMARY KEY,
52 varchar_column_name_to_encrypt VARCHAR(1024),
53 enum_column_name_to_encrypt ENUM(
54 'enum_value1_to_encrypt',
55 'enum_value2_to_encrypt'
56 ),
57 timestamp_column_name_to_encrypt TIMESTAMP(6) NULL,
58 blob_column_name_to_encrypt BLOB
59 ) ENGINE=Aria;
60
61 CREATE TRIGGER trigger_name_to_encrypt
62 AFTER INSERT ON myisam_table_name_to_encrypt FOR EACH ROW
63 INSERT INTO aria_table_name_to_encrypt (varchar_column_name_to_encrypt)
64 VALUES (NEW.char_column_name_to_encrypt);
65
66 CREATE DEFINER=user_name_to_encrypt VIEW view_name_to_encrypt
67 AS SELECT * FROM innodb_table_name_to_encrypt;
68
69 CREATE FUNCTION func_name_to_encrypt (func_parameter_to_encrypt INT)
70 RETURNS VARCHAR(64)
71 RETURN 'func_result_to_encrypt';
72
73 --delimiter $$
74 CREATE PROCEDURE proc_name_to_encrypt (
75 IN proc_in_parameter_to_encrypt CHAR(32),
76 OUT proc_out_parameter_to_encrypt INT
77 )
78 BEGIN
79 DECLARE procvar_name_to_encrypt CHAR(64) DEFAULT 'procvar_val_to_encrypt';
80 DECLARE cursor_name_to_encrypt CURSOR FOR
81 SELECT virt_column_name_to_encrypt FROM innodb_table_name_to_encrypt;
82 DECLARE EXIT HANDLER FOR NOT FOUND
83 BEGIN
84 SET @stmt_var_to_encrypt = CONCAT(
85 "SELECT
86 IF (RAND()>0.5,'enum_value2_to_encrypt','enum_value1_to_encrypt')
87 FROM innodb_table_name_to_encrypt
88 INTO OUTFILE '", proc_in_parameter_to_encrypt, "'");
89 PREPARE stmt_to_encrypt FROM @stmt_var_to_encrypt;
90 EXECUTE stmt_to_encrypt;
91 DEALLOCATE PREPARE stmt_to_encrypt;
92 END;
93 OPEN cursor_name_to_encrypt;
94 proc_label_to_encrypt: LOOP
95 FETCH cursor_name_to_encrypt INTO procvar_name_to_encrypt;
96 END LOOP;
97 CLOSE cursor_name_to_encrypt;
98 END $$
99 --delimiter ;
100
101 CREATE SERVER server_name_to_encrypt
102 FOREIGN DATA WRAPPER mysql
103 OPTIONS (HOST 'host_name_to_encrypt');
104
105 --let $_cur_con= $CURRENT_CONNECTION
106 --connect (con1,localhost,user_name_to_encrypt,password_to_encrypt,database_name_to_encrypt)
107 CREATE TEMPORARY TABLE tmp_table_name_to_encrypt (
108 float_column_name_to_encrypt FLOAT,
109 binary_column_name_to_encrypt BINARY(64)
110 );
111 --disconnect con1
112 --connection $_cur_con
113
114 CREATE INDEX index_name_to_encrypt
115 ON myisam_table_name_to_encrypt (datetime_column_name_to_encrypt);
116
117 ALTER DATABASE database_name_to_encrypt CHARACTER SET utf8;
118
119 ALTER TABLE innodb_table_name_to_encrypt
120 MODIFY timestamp_column_name_to_encrypt TIMESTAMP NOT NULL
121 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
122 ;
123
124 ALTER ALGORITHM=MERGE VIEW view_name_to_encrypt
125 AS SELECT * FROM innodb_table_name_to_encrypt;
126
127 RENAME TABLE innodb_table_name_to_encrypt TO new_table_name_to_encrypt;
128 ALTER TABLE new_table_name_to_encrypt RENAME TO innodb_table_name_to_encrypt;
129
130 #
131 # Some DML
132 #
133
134 --disable_warnings
135
136 set @user_var1_to_encrypt= 'dyncol1_val_to_encrypt';
137 set @user_var2_to_encrypt= 'dyncol2_name_to_encrypt';
138
139 INSERT INTO view_name_to_encrypt VALUES
140 (1, NOW(6), COLUMN_CREATE('dyncol1_name_to_encrypt',@user_var1_to_encrypt), NULL, NULL),
141 (2, NOW(6), COLUMN_CREATE(@user_var2_to_encrypt,'dyncol2_val_to_encrypt'), NULL, NULL)
142 ;
143 --delimiter $$
144 BEGIN NOT ATOMIC
145 DECLARE counter_name_to_encrypt INT DEFAULT 0;
146 START TRANSACTION;
147 WHILE counter_name_to_encrypt<12 DO
148 SELECT COUNT(*) INTO @cnt FROM innodb_table_name_to_encrypt;
149 INSERT INTO innodb_table_name_to_encrypt
150 SELECT int_column_name_to_encrypt+@cnt, NOW(6), blob_column_name_to_encrypt, NULL, NULL
151 FROM innodb_table_name_to_encrypt
152 ORDER BY int_column_name_to_encrypt;
153 SET counter_name_to_encrypt = counter_name_to_encrypt+1;
154 END WHILE;
155 COMMIT;
156 END
157 $$
158 --delimiter ;
159
160 INSERT INTO myisam_table_name_to_encrypt
161 SELECT NULL, 'char_literal_to_encrypt', NULL, 'text_to_encrypt';
162 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
163 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
164 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
165 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
166 INSERT INTO myisam_table_name_to_encrypt (char_column_name_to_encrypt)
167 SELECT char_column_name_to_encrypt FROM myisam_table_name_to_encrypt;
168
169 CALL proc_name_to_encrypt('file_name_to_encrypt',@useless_var_to_encrypt);
170
171 TRUNCATE TABLE aria_table_name_to_encrypt;
172
173 LOAD DATA INFILE 'file_name_to_encrypt' INTO TABLE aria_table_name_to_encrypt
174 (enum_column_name_to_encrypt);
175
176 --let datadir= `SELECT @@datadir`
177 --replace_result $datadir <DATADIR>
178 eval LOAD DATA LOCAL INFILE '$datadir/database_name_to_encrypt/file_name_to_encrypt'
179 INTO TABLE aria_table_name_to_encrypt (enum_column_name_to_encrypt);
180 --remove_file $datadir/database_name_to_encrypt/file_name_to_encrypt
181
182 UPDATE view_name_to_encrypt SET blob_column_name_to_encrypt =
183 COLUMN_CREATE('dyncol1_name_to_encrypt',func_name_to_encrypt(0))
184 ;
185
186 DELETE FROM aria_table_name_to_encrypt ORDER BY int_column_name_to_encrypt LIMIT 10;
187
188 --enable_warnings
189
190 #
191 # Other statements
192 #
193
194 --disable_result_log
195 ANALYZE TABLE myisam_table_name_to_encrypt;
196 CHECK TABLE aria_table_name_to_encrypt;
197 CHECKSUM TABLE innodb_table_name_to_encrypt, myisam_table_name_to_encrypt;
198 --enable_result_log
199 RENAME USER user_name_to_encrypt to new_user_name_to_encrypt;
200 REVOKE ALL PRIVILEGES, GRANT OPTION FROM new_user_name_to_encrypt;
201
202 #
203 # Cleanup
204 #
205
206 DROP DATABASE database_name_to_encrypt;
207 DROP USER new_user_name_to_encrypt;
208 DROP SERVER server_name_to_encrypt;