Skip to content

Commit d5e7607

Browse files
author
Sean Pringle
committed
Ensure MariaDB event logging statements are deterministic.
Change-Id: I0cb6d51fc817e680f8a33583391e88b8518c24c3
1 parent 1c17054 commit d5e7607

File tree

2 files changed

+31
-17
lines changed

2 files changed

+31
-17
lines changed

‎dbtools/events_coredb_slave.sql‎

Lines changed: 17 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -13,8 +13,7 @@ use ops;
1313
-- Remember, table is replicated!
1414
-- https://wikitech.wikimedia.org/wiki/MariaDB#Schema_Changes
1515

16-
drop table if exists event_log;
17-
create table event_log (
16+
create table if not exists event_log (
1817
server_id int unsigned not null,
1918
stamp datetime not null,
2019
event varchar(100) not null,
@@ -39,12 +38,11 @@ create event wmf_slave_purge
3938

4039
do begin
4140

42-
-- If using statement based replication the @@server_id is unsafe. Convert it to a constant.
43-
set @sql := concat(
44-
'delete from event_log where stamp < now() - interval 1 day and server_id = ', @@server_id
45-
);
41+
declare sid int;
42+
43+
set sid := @@server_id;
4644

47-
prepare stmt from @sql; execute stmt; deallocate prepare stmt;
45+
delete from event_log where stamp < now() - interval 1 day and server_id = sid;
4846

4947
end ;;
5048

@@ -64,6 +62,7 @@ create event wmf_slave_wikiuser_slow
6462

6563
do begin
6664

65+
declare sid int;
6766
declare all_done int default 0;
6867
declare thread_id bigint default null;
6968
declare thread_query varchar(100);
@@ -82,6 +81,8 @@ create event wmf_slave_wikiuser_slow
8281

8382
declare continue handler for not found set all_done = 1;
8483

84+
set sid := @@server_id;
85+
8586
if (get_lock('wmf_slave_wikiuser_slow', 1) = 0) then
8687
signal sqlstate value '45000' set message_text = 'get_lock';
8788
end if;
@@ -95,7 +96,7 @@ create event wmf_slave_wikiuser_slow
9596

9697
kill thread_id;
9798

98-
insert into event_log values (@@server_id, now(), 'wmf_slave_wikiuser_slow',
99+
insert into event_log values (sid, now(), 'wmf_slave_wikiuser_slow',
99100
concat('kill ',thread_id, '; ',thread_query)
100101
);
101102

@@ -124,6 +125,7 @@ create event wmf_slave_wikiuser_sleep
124125

125126
do begin
126127

128+
declare sid int;
127129
declare all_done int default 0;
128130
declare thread_id bigint default null;
129131

@@ -142,6 +144,8 @@ create event wmf_slave_wikiuser_sleep
142144
signal sqlstate value '45000' set message_text = 'get_lock';
143145
end if;
144146

147+
set sid := @@server_id;
148+
145149
set all_done = 0;
146150
open threads_sleeping;
147151

@@ -151,7 +155,7 @@ create event wmf_slave_wikiuser_sleep
151155

152156
kill thread_id;
153157

154-
insert into event_log values (@@server_id, now(), 'wmf_slave_wikiuser_sleep',
158+
insert into event_log values (sid, now(), 'wmf_slave_wikiuser_sleep',
155159
concat('kill ',thread_id)
156160
);
157161

@@ -179,6 +183,7 @@ create event wmf_slave_overload
179183

180184
do begin
181185

186+
declare sid int;
182187
declare all_done int default 0;
183188
declare thread_id bigint default null;
184189
declare thread_query varchar(100);
@@ -203,6 +208,8 @@ create event wmf_slave_overload
203208
signal sqlstate value '45000' set message_text = 'get_lock';
204209
end if;
205210

211+
set sid := @@server_id;
212+
206213
set active_count := (
207214
select count(ps.id)
208215
from information_schema.processlist ps
@@ -225,7 +232,7 @@ create event wmf_slave_overload
225232

226233
kill thread_id;
227234

228-
insert into event_log values (@@server_id, now(), 'wmf_slave_overload (300)',
235+
insert into event_log values (sid, now(), 'wmf_slave_overload (300)',
229236
concat('kill ',thread_id,'; ',thread_query)
230237
);
231238

‎dbtools/events_labsdb.sql‎

Lines changed: 14 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,15 +3,17 @@
33
set @cache_sql_log_bin := @@session.sql_log_bin;
44
set @@session.sql_log_bin = 1;
55

6+
set @cache_event_scheduler := @@global.event_scheduler;
7+
set @@global.event_scheduler = 0;
8+
69
create database if not exists ops;
710

811
use ops;
912

1013
-- Remember, table is replicated!
1114
-- https://wikitech.wikimedia.org/wiki/MariaDB#Schema_Changes
1215

13-
drop table if exists event_log;
14-
create table event_log (
16+
create table if not exists event_log (
1517
server_id int unsigned not null,
1618
stamp datetime not null,
1719
event varchar(100) not null,
@@ -36,10 +38,11 @@ create event wmf_labs_purge
3638

3739
do begin
3840

39-
delete from event_log
40-
where stamp < now() - interval 1 day
41-
and server_id = @@server_id
42-
limit 1000;
41+
declare sid int;
42+
43+
set sid := @@server_id;
44+
45+
delete from event_log where stamp < now() - interval 1 day and server_id = sid;
4346

4447
end ;;
4548

@@ -56,6 +59,7 @@ create event wmf_labs_sleepers_txn
5659

5760
do begin
5861

62+
declare sid int;
5963
declare thread_id bigint;
6064

6165
set thread_id := ( select ps.id
@@ -70,11 +74,13 @@ create event wmf_labs_sleepers_txn
7074
limit 1
7175
);
7276

77+
set sid := @@server_id;
78+
7379
if (thread_id is not null) then
7480

7581
kill thread_id;
7682

77-
insert into event_log values (@@server_id, now(), 'wmf_labs_sleepers_txn',
83+
insert into event_log values (sid, now(), 'wmf_labs_sleepers_txn',
7884
concat('kill ',thread_id)
7985
);
8086

@@ -85,3 +91,4 @@ create event wmf_labs_sleepers_txn
8591
delimiter ;
8692

8793
set @@session.sql_log_bin = @cache_sql_log_bin;
94+
set @@global.event_scheduler = @cache_event_scheduler;

0 commit comments

Comments
 (0)