Debian Codegraph @

mysql-test/suite/component_keyring_file/r/table_encrypt_2.result

raw
1 # ----------------------------------------------------------------------
2 # Setup
3 # Creating local configuration file for keyring component: component_keyring_file
4 # Creating manifest file for current MySQL server instance
5 # Re-starting mysql server with manifest file
6 # ----------------------------------------------------------------------
7 DROP TABLE IF EXISTS t1;
8 SET GLOBAL innodb_file_per_table = 1;
9 SELECT @@innodb_file_per_table;
10 @@innodb_file_per_table
11 1
12 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
13 SHOW CREATE TABLE t1;
14 Table Create Table
15 t1 CREATE TABLE `t1` (
16 `c1` int DEFAULT NULL,
17 `c2` char(20) DEFAULT NULL
18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
19 INSERT INTO t1 VALUES(0, "aaaaa");
20 INSERT INTO t1 VALUES(1, "bbbbb");
21 INSERT INTO t1 VALUES(2, "ccccc");
22 INSERT INTO t1 VALUES(3, "ddddd");
23 INSERT INTO t1 VALUES(4, "eeeee");
24 INSERT INTO t1 VALUES(5, "fffff");
25 INSERT INTO t1 VALUES(6, "ggggg");
26 INSERT INTO t1 VALUES(7, "hhhhh");
27 INSERT INTO t1 VALUES(8, "iiiii");
28 INSERT INTO t1 VALUES(9, "jjjjj");
29 INSERT INTO t1 select * from t1;
30 INSERT INTO t1 select * from t1;
31 INSERT INTO t1 select * from t1;
32 INSERT INTO t1 select * from t1;
33 INSERT INTO t1 select * from t1;
34 INSERT INTO t1 select * from t1;
35 SELECT * FROM t1 LIMIT 10;
36 c1 c2
37 0 aaaaa
38 1 bbbbb
39 2 ccccc
40 3 ddddd
41 4 eeeee
42 5 fffff
43 6 ggggg
44 7 hhhhh
45 8 iiiii
46 9 jjjjj
47 SELECT * FROM t1 LIMIT 10;
48 c1 c2
49 0 aaaaa
50 1 bbbbb
51 2 ccccc
52 3 ddddd
53 4 eeeee
54 5 fffff
55 6 ggggg
56 7 hhhhh
57 8 iiiii
58 9 jjjjj
59 ALTER INSTANCE ROTATE INNODB MASTER KEY;
60 DROP TABLE t1;
61 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
62 INSERT INTO t1 VALUES(0, "aaaaa");
63 INSERT INTO t1 VALUES(1, "bbbbb");
64 INSERT INTO t1 VALUES(2, "ccccc");
65 INSERT INTO t1 VALUES(3, "ddddd");
66 INSERT INTO t1 VALUES(4, "eeeee");
67 INSERT INTO t1 VALUES(5, "fffff");
68 INSERT INTO t1 VALUES(6, "ggggg");
69 INSERT INTO t1 VALUES(7, "hhhhh");
70 INSERT INTO t1 VALUES(8, "iiiii");
71 INSERT INTO t1 VALUES(9, "jjjjj");
72 SELECT * FROM t1 LIMIT 10;
73 c1 c2
74 0 aaaaa
75 1 bbbbb
76 2 ccccc
77 3 ddddd
78 4 eeeee
79 5 fffff
80 6 ggggg
81 7 hhhhh
82 8 iiiii
83 9 jjjjj
84 DROP TABLE t1;
85 SET block_encryption_mode = 'aes-256-cbc';
86 DROP DATABASE IF EXISTS tde_db;
87 CREATE DATABASE tde_db;
88 CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENCRYPTION = 'Y' ENGINE = InnoDB;
89 INSERT INTO tde_db.t1 VALUES(0, 'abc');
90 INSERT INTO tde_db.t1 VALUES(1, 'xyz');
91 INSERT INTO tde_db.t1 VALUES(2, null);
92 INSERT INTO tde_db.t1 VALUES(3, null);
93 SELECT * FROM tde_db.t1 LIMIT 10;
94 c1 c2
95 0 abc
96 1 xyz
97 2 NULL
98 3 NULL
99 ALTER INSTANCE ROTATE INNODB MASTER KEY;
100 SELECT * FROM tde_db.t1 LIMIT 10;
101 c1 c2
102 0 abc
103 1 xyz
104 2 NULL
105 3 NULL
106 # Mysqldump output
107
108 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tde_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
109
110 USE `tde_db`;
111 /*!40101 SET @saved_cs_client = @@character_set_client */;
112 /*!50503 SET character_set_client = utf8mb4 */;
113 CREATE TABLE `t1` (
114 `c1` int NOT NULL,
115 `c2` char(50) DEFAULT NULL,
116 PRIMARY KEY (`c1`)
117 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y';
118 /*!40101 SET character_set_client = @saved_cs_client */;
119 INSERT INTO `t1` VALUES (0,'abc'),(1,'xyz'),(2,NULL),(3,NULL);
120 # Redirecting mysqlpump output to MYSQL_TMP_DIR/mysqlpump_encrypt.sql
121 DROP DATABASE tde_db;
122 Pattern "ALTER INSTANCE ROTATE INNODB MASTER KEY" found
123 # Loading tables from mysqlpump_encrypt.sql
124 SELECT * FROM tde_db.t1 LIMIT 10;
125 c1 c2
126 0 abc
127 1 xyz
128 2 NULL
129 3 NULL
130 INSERT INTO tde_db.t1 VALUES(4, null);
131 SELECT * FROM tde_db.t1 LIMIT 10;
132 c1 c2
133 0 abc
134 1 xyz
135 2 NULL
136 3 NULL
137 4 NULL
138 DROP DATABASE tde_db;
139 #
140 # Bug #26634507 CREATE_OPTIONS FLD IN INFORMATION_SCHEMA.TABLES NOT
141 # FILLING PROPERLY.
142 # The CREATE_OPTIONS field from I_S.TABLES should show the option
143 # 'ENCRYPTION='.
144 #
145 CREATE TABLE not_encrypted1 (col1 INT) ENCRYPTION='n';
146 CREATE TABLE not_encrypted2 (col1 INT) ENCRYPTION='N';
147 CREATE TABLE encrypted1 (col1 INT) ENCRYPTION='y';
148 CREATE TABLE encrypted2 (col1 INT) ENCRYPTION='Y';
149 SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
150 FROM INFORMATION_SCHEMA.TABLES
151 WHERE TABLE_NAME like '%encrypted%'
152 ORDER BY TABLE_NAME;
153 TABLE_SCHEMA TABLE_NAME CREATE_OPTIONS
154 test encrypted1 ENCRYPTION='y'
155 test encrypted2 ENCRYPTION='Y'
156 test not_encrypted1
157 test not_encrypted2
158 DROP TABLE encrypted1;
159 DROP TABLE not_encrypted1;
160 DROP TABLE encrypted2;
161 DROP TABLE not_encrypted2;
162 SET GLOBAL innodb_file_per_table=1;
163 # ----------------------------------------------------------------------
164 # Teardown
165 # Removing manifest file for current MySQL server instance
166 # Removing local keyring file for keyring component: component_keyring_file
167 # Removing local configuration file for keyring component: component_keyring_file
168 # Restarting server without the manifest file
169 # ----------------------------------------------------------------------