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
|
create database if not exists events_test;
use events_test;
"We use procedure here because its statements won't be logged into the general log"
"If we had used normal select that are logged in different ways depending on whether"
"the test suite is run in normal mode or with --ps-protocol"
CREATE procedure select_general_log()
BEGIN
SELECT user_host, argument FROM mysql.general_log WHERE argument LIKE '%alabala%';
END|
"Check General Query Log"
SET GLOBAL event_scheduler=2;
create event log_general on schedule every 1 minute do SELect 'alabala', sleep(3) from dual;
TRUNCATE mysql.general_log;
"1 row, the current statement!"
call select_general_log();
user_host argument
SET GLOBAL event_scheduler=1;
"Wait the scheduler to start"
"Should see 3 rows - the 'SELect' is in the middle. The other two are selects from general_log"
call select_general_log();
user_host argument
root[root] @ localhost [localhost] SELect 'alabala', sleep(3) from dual
DROP PROCEDURE select_general_log;
DROP EVENT log_general;
SET GLOBAL event_scheduler=2;
"Check slow query log"
"Save the values"
SET @old_global_long_query_time:=(select get_value());
SET @old_session_long_query_time:=@@long_query_time;
SHOW VARIABLES LIKE 'log_slow_queries';
Variable_name Value
log_slow_queries ON
DROP FUNCTION get_value;
TRUNCATE mysql.slow_log;
SELECT user_host, query_time, db, sql_text FROM mysql.slow_log;
user_host query_time db sql_text
"Set new values"
SET GLOBAL long_query_time=4;
SET SESSION long_query_time=1;
"Check that logging is working"
SELECT SLEEP(2);
SLEEP(2)
0
SELECT user_host, query_time, db, sql_text FROM mysql.slow_log;
user_host query_time db sql_text
root[root] @ localhost [] SLEEPVAL events_test SELECT SLEEP(2)
TRUNCATE mysql.slow_log;
CREATE TABLE slow_event_test (slo_val tinyint, val tinyint);
"This won't go to the slow log"
CREATE EVENT long_event ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(3);
SELECT * FROM slow_event_test;
slo_val val
SET GLOBAL event_scheduler=1;
"Sleep some more time than the actual event run will take"
SHOW VARIABLES LIKE 'event_scheduler';
Variable_name Value
event_scheduler 1
"Check our table. Should see 1 row"
SELECT * FROM slow_event_test;
slo_val val
4 0
"Check slow log. Should not see anything because 3 is under the threshold of 4 for GLOBAL, though over SESSION which is 2"
SELECT user_host, query_time, db, sql_text FROM mysql.slow_log;
user_host query_time db sql_text
"This should go to the slow log"
SET SESSION long_query_time=10;
SET GLOBAL long_query_time=1;
DROP EVENT long_event;
CREATE EVENT long_event2 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(2);
"Sleep some more time than the actual event run will take"
"Check our table. Should see 2 rows"
SELECT * FROM slow_event_test;
slo_val val
4 0
1 0
"Check slow log. Should see 1 row because 4 is over the threshold of 3 for GLOBAL, though under SESSION which is 10"
SELECT user_host, query_time, db, sql_text FROM mysql.slow_log;
user_host query_time db sql_text
root[root] @ localhost [localhost] SLEEPVAL events_test INSERT INTO slow_event_test SELECT @@long_query_time, SLEEP(2)
DROP EVENT long_event2;
SET GLOBAL long_query_time =@old_global_long_query_time;
SET SESSION long_query_time =@old_session_long_query_time;
TRUNCATE mysql.slow_log;
DROP TABLE slow_event_test;
drop database events_test;
|
