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
102
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
|
TRUNCATE TABLE mysql.general_log;
CALL mtr.add_suppression('Following users were specified in CREATE USER IF NOT EXISTS but they already exist');
CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS but they do not exist');
--------------- general log ---------------------------------------
SET GLOBAL general_log_file = '.../log/rewrite_general.log';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log= 'ON';
set character set 'hebrew';
set charset default,@dummy='A';
set names 'latin1',@dummy='B';
set names 'latin1' collate 'latin1_german2_ci';
set names default,@dummy='c';
CREATE TABLE t1(f1 INT, f2 INT, f3 INT, f4 INT);
CREATE PROCEDURE proc_rewrite_1() INSERT INTO test.t1 VALUES ("hocus pocus");
CREATE FUNCTION func_rewrite_1(i INT) RETURNS INT DETERMINISTIC RETURN i+1;
CREATE USER test_user1 IDENTIFIED WITH mysql_native_password BY 'azundris1';
GRANT SELECT(f2), INSERT(f3), INDEX, UPDATE(f1,f3, f2, f4), ALTER on
test.t1 TO test_user1;
GRANT ALL ON PROCEDURE test.proc_rewrite_1 TO test_user1;
GRANT EXECUTE ON FUNCTION test.func_rewrite_1 TO test_user1;
CREATE USER test_user3@localhost IDENTIFIED WITH mysql_native_password BY 'meow' REQUIRE SSL;
GRANT SELECT,USAGE ON test.* TO test_user3@localhost;
ALTER USER test_user3@localhost IDENTIFIED BY 'meow'
REQUIRE X509 WITH
MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2
MAX_CONNECTIONS_PER_HOUR 3 MAX_USER_CONNECTIONS 4;
GRANT USAGE ON test.* TO test_user3@localhost WITH GRANT OPTION;
ALTER USER test_user3@localhost REQUIRE NONE;
DROP PROCEDURE proc_rewrite_1;
DROP FUNCTION func_rewrite_1;
DROP TABLE t1;
CREATE USER test_user2 IDENTIFIED WITH mysql_native_password BY 'azundris2';
CHANGE REPLICATION SOURCE TO SOURCE_PASSWORD='azundris3';
CREATE USER 'test_user4'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'test_user4'@'localhost' IDENTIFIED BY 'azundris4';
CREATE USER test_user5 IDENTIFIED WITH mysql_native_password AS
'*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF', test_user6 IDENTIFIED BY 'test';
ALTER USER IF EXISTS test_user5 IDENTIFIED BY 'test',
test_user6 IDENTIFIED WITH mysql_native_password AS
'*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF', test_user7 IDENTIFIED BY 'test';
Warnings:
Note 3162 Authorization ID 'test_user7'@'%' does not exist.
CREATE USER IF NOT EXISTS test_user6 IDENTIFIED BY 'test',
test_user7 IDENTIFIED BY 'test';
Warnings:
Note 3163 Authorization ID 'test_user6'@'%' already exists.
ALTER USER test_user7 IDENTIFIED WITH mysql_native_password AS
'*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
CREATE USER test_user8 IDENTIFIED BY '';
ALTER USER test_user8 IDENTIFIED BY '';
CREATE USER test_user9 IDENTIFIED WITH 'caching_sha2_password' BY '';
ALTER USER test_user9 IDENTIFIED WITH 'caching_sha2_password' BY '';
SET PASSWORD FOR test_user9 = "";
CREATE USER u1, u2;
GRANT CREATE USER ON *.* to 'u1' WITH GRANT OPTION;
SET PASSWORD = '' REPLACE '';
ALTER USER u1 IDENTIFIED BY '123' REPLACE '', u2 IDENTIFIED BY '456'
PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER u2 IDENTIFIED BY 'xyz', u1 IDENTIFIED BY 'abc' REPLACE '123';
SET GLOBAL general_log= 'OFF';
DROP USER u1, u2;
DROP USER 'test_user4'@'localhost';
DROP USER 'test_user3'@'localhost';
DROP USER test_user9, test_user8;
DROP USER test_user7, test_user6, test_user5;
DROP USER test_user2;
DROP USER test_user1;
CREATE TABLE test_log (argument TEXT);
LOAD DATA LOCAL INFILE '.../log/rewrite_general.log'
INTO TABLE test_log FIELDS TERMINATED BY '\n' LINES TERMINATED BY '\n';
This line should be followed by two SELECTs with empty result sets
SELECT argument FROM test_log WHERE argument LIKE CONCAT('%azun','dris%');
argument
SELECT argument FROM mysql.general_log WHERE argument LIKE CONCAT('%azun','dris%');
argument
Show that we logged stuff at all:
------ from file ------
SELECT TRIM(LEADING '\t' FROM MID(argument,LOCATE('Query',argument)+5)) FROM
test_log WHERE (argument LIKE '%BY %' OR argument LIKE '%AS %'
OR argument LIKE '%PASSWORD %')
AND argument NOT LIKE '%Prepare%';
TRIM(LEADING '\t' FROM MID(argument,LOCATE('Query',argument)+5))
CREATE USER 'test_user1'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>
CREATE USER 'test_user3'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY <secret> REQUIRE SSL
ALTER USER 'test_user3'@'localhost' IDENTIFIED BY <secret> REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3 MAX_USER_CONNECTIONS 4
CREATE USER 'test_user2'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>
CHANGE REPLICATION SOURCE TO SOURCE_PASSWORD = <secret>
ALTER USER 'test_user4'@'localhost' IDENTIFIED BY <secret>
CREATE USER 'test_user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF','test_user6'@'%' IDENTIFIED BY <secret>
ALTER USER IF EXISTS 'test_user5'@'%' IDENTIFIED BY <secret>,'test_user6'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF','test_user7'@'%' IDENTIFIED BY <secret>
CREATE USER IF NOT EXISTS 'test_user6'@'%' IDENTIFIED BY <secret>,'test_user7'@'%' IDENTIFIED BY <secret>
ALTER USER test_user7 IDENTIFIED WITH mysql_native_password AS
CREATE USER 'test_user8'@'%' IDENTIFIED BY <secret>
ALTER USER 'test_user8'@'%' IDENTIFIED BY <secret>
CREATE USER 'test_user9'@'%' IDENTIFIED WITH 'caching_sha2_password' BY <secret>
ALTER USER 'test_user9'@'%' IDENTIFIED WITH 'caching_sha2_password' BY <secret>
SET PASSWORD FOR `test_user9`@`%`=<secret>
SET PASSWORD FOR `u1`@`%`=<secret> REPLACE <secret>
ALTER USER 'u1'@'%' IDENTIFIED BY <secret> REPLACE <secret>,'u2'@'%' IDENTIFIED BY <secret> PASSWORD REQUIRE CURRENT OPTIONAL
ALTER USER 'u2'@'%' IDENTIFIED BY <secret>,'u1'@'%' IDENTIFIED BY <secret> REPLACE <secret>
------ from table ------
SELECT argument FROM mysql.general_log WHERE (argument LIKE '%BY %' OR
argument LIKE '%AS %' OR argument LIKE '%PASSWORD %')
AND command_type NOT LIKE 'Prepare';
argument
CREATE USER 'test_user1'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>
CREATE USER 'test_user3'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY <secret> REQUIRE SSL
ALTER USER 'test_user3'@'localhost' IDENTIFIED BY <secret> REQUIRE X509 WITH MAX_QUERIES_PER_HOUR 1 MAX_UPDATES_PER_HOUR 2 MAX_CONNECTIONS_PER_HOUR 3 MAX_USER_CONNECTIONS 4
CREATE USER 'test_user2'@'%' IDENTIFIED WITH 'mysql_native_password' BY <secret>
CHANGE REPLICATION SOURCE TO SOURCE_PASSWORD = <secret>
ALTER USER 'test_user4'@'localhost' IDENTIFIED BY <secret>
CREATE USER 'test_user5'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF','test_user6'@'%' IDENTIFIED BY <secret>
ALTER USER IF EXISTS 'test_user5'@'%' IDENTIFIED BY <secret>,'test_user6'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF','test_user7'@'%' IDENTIFIED BY <secret>
CREATE USER IF NOT EXISTS 'test_user6'@'%' IDENTIFIED BY <secret>,'test_user7'@'%' IDENTIFIED BY <secret>
CREATE USER 'test_user8'@'%' IDENTIFIED BY <secret>
ALTER USER 'test_user8'@'%' IDENTIFIED BY <secret>
CREATE USER 'test_user9'@'%' IDENTIFIED WITH 'caching_sha2_password' BY <secret>
ALTER USER 'test_user9'@'%' IDENTIFIED WITH 'caching_sha2_password' BY <secret>
SET PASSWORD FOR `test_user9`@`%`=<secret>
SET PASSWORD FOR `u1`@`%`=<secret> REPLACE <secret>
ALTER USER 'u1'@'%' IDENTIFIED BY <secret> REPLACE <secret>,'u2'@'%' IDENTIFIED BY <secret> PASSWORD REQUIRE CURRENT OPTIONAL
ALTER USER 'u2'@'%' IDENTIFIED BY <secret>,'u1'@'%' IDENTIFIED BY <secret> REPLACE <secret>
------ done ------
------ rewrite ------
SELECT argument FROM mysql.general_log WHERE argument LIKE CONCAT('set ','character set %');
argument
set character set 'hebrew'
SELECT argument FROM mysql.general_log WHERE argument LIKE CONCAT('set ','names %');
argument
set names 'latin1',@dummy='B'
set names 'latin1' collate 'latin1_german2_ci'
set names default,@dummy='c'
SELECT argument FROM mysql.general_log WHERE argument LIKE 'GRANT %' AND command_type NOT LIKE 'Prepare';
argument
GRANT SELECT(f2), INSERT(f3), INDEX, UPDATE(f1,f3, f2, f4), ALTER on
test.t1 TO test_user1
GRANT ALL ON PROCEDURE test.proc_rewrite_1 TO test_user1
GRANT EXECUTE ON FUNCTION test.func_rewrite_1 TO test_user1
GRANT SELECT,USAGE ON test.* TO test_user3@localhost
GRANT USAGE ON test.* TO test_user3@localhost WITH GRANT OPTION
GRANT CREATE USER ON *.* to 'u1' WITH GRANT OPTION
------ done ------ see log_tables.test for more proof! :)
SELECT COUNT(*)=1 OR COUNT(*)=2 FROM mysql.general_log WHERE argument LIKE 'CREATE USER%' AND argument LIKE CONCAT('%AS %');
COUNT(*)=1 OR COUNT(*)=2
1
Bug#13958454 -- show we print SET @a:=5, but SELECT (@a:=5)
EXPLAIN SELECT @a=5,@b:=10,@c:=20,@d:=40+5,(@e:=80)+5;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
Note 1003 /* select#1 */ select ((@`a`) = 5) AS `@a=5`,(@b:=10) AS `@b:=10`,(@c:=20) AS `@c:=20`,(@d:=(40 + 5)) AS `@d:=40+5`,((@e:=80) + 5) AS `(@e:=80)+5`
End of 5.6 tests!
#
# Bug#16953758: PREPARED STATEMENT IS WRITTEN TO GENERAL QUERY LOG AFTER ITS EXECUTION IS FINISH
#
TRUNCATE TABLE mysql.general_log;
SET GLOBAL general_log='ON';
SET @sql='SELECT command_type, argument FROM mysql.general_log WHERE argument LIKE "%Bug#16953758%"';
PREPARE stmt FROM @sql;
EXECUTE stmt;
command_type argument
Query SET @sql='SELECT command_type, argument FROM mysql.general_log WHERE argument LIKE "%Bug#16953758%"'
Prepare SELECT command_type, argument FROM mysql.general_log WHERE argument LIKE "%Bug#16953758%"
Execute SELECT command_type, argument FROM mysql.general_log WHERE argument LIKE "%Bug#16953758%"
DEALLOCATE PREPARE stmt;
#
# Bug#18616826: PREPARED STATEMENTS WHOSE EXECUTION FAIL ARE NOT LOGGED TO THE GENERAL LOG
#
TRUNCATE TABLE mysql.general_log;
SET @sql='DROP TABLE 18616826_does_not_exist';
PREPARE stmt FROM @sql;
EXECUTE stmt;
ERROR 42S02: Unknown table 'test.18616826_does_not_exist'
DEALLOCATE PREPARE stmt;
SELECT command_type, argument FROM mysql.general_log WHERE argument LIKE "DROP TABLE 18616826_does_not_exist";
command_type argument
Prepare DROP TABLE 18616826_does_not_exist
Execute DROP TABLE 18616826_does_not_exist
End of 5.7 tests!
# shutdown the server from mtr.
# restart the server.
SET GLOBAL general_log_file = '.../log/rewrite_general.log';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log= 'ON';
CREATE USER 'plaintext_test' IDENTIFIED BY 'pwd';
ALTER USER plaintext_test IDENTIFIED BY '' REPLACE 'pwd';
SET PASSWORD FOR plaintext_test='456' REPLACE '';
SET PASSWORD='789' REPLACE '456';
DROP USER plaintext_test;
# shutdown the server from mtr.
# restart the server.
SELECT count(*)=6 or count(*)=9 FROM mysql.general_log WHERE argument LIKE '%plaintext_test%' or argument like '%789%';
count(*)=6 or count(*)=9
1
DROP TABLE test_log;
SET GLOBAL general_log_file = '.../mysqld.1/mysqld.log';
SET GLOBAL log_output= 'FILE';
SET GLOBAL general_log= 1;
TRUNCATE TABLE mysql.general_log;
End of 5.7 tests!
|
