Springle has submitted this change and it was merged. Change subject: Ensure MariaDB event logging statements are deterministic. ......................................................................
Ensure MariaDB event logging statements are deterministic. Change-Id: I0cb6d51fc817e680f8a33583391e88b8518c24c3 --- M dbtools/events_coredb_slave.sql M dbtools/events_labsdb.sql 2 files changed, 31 insertions(+), 17 deletions(-) Approvals: Springle: Looks good to me, approved jenkins-bot: Verified diff --git a/dbtools/events_coredb_slave.sql b/dbtools/events_coredb_slave.sql index 037a950..e9290a5 100644 --- a/dbtools/events_coredb_slave.sql +++ b/dbtools/events_coredb_slave.sql @@ -13,8 +13,7 @@ -- Remember, table is replicated! -- https://wikitech.wikimedia.org/wiki/MariaDB#Schema_Changes -drop table if exists event_log; -create table event_log ( +create table if not exists event_log ( server_id int unsigned not null, stamp datetime not null, event varchar(100) not null, @@ -39,12 +38,11 @@ do begin - -- If using statement based replication the @@server_id is unsafe. Convert it to a constant. - set @sql := concat( - 'delete from event_log where stamp < now() - interval 1 day and server_id = ', @@server_id - ); + declare sid int; - prepare stmt from @sql; execute stmt; deallocate prepare stmt; + set sid := @@server_id; + + delete from event_log where stamp < now() - interval 1 day and server_id = sid; end ;; @@ -64,6 +62,7 @@ do begin + declare sid int; declare all_done int default 0; declare thread_id bigint default null; declare thread_query varchar(100); @@ -82,6 +81,8 @@ declare continue handler for not found set all_done = 1; + set sid := @@server_id; + if (get_lock('wmf_slave_wikiuser_slow', 1) = 0) then signal sqlstate value '45000' set message_text = 'get_lock'; end if; @@ -95,7 +96,7 @@ kill thread_id; - insert into event_log values (@@server_id, now(), 'wmf_slave_wikiuser_slow', + insert into event_log values (sid, now(), 'wmf_slave_wikiuser_slow', concat('kill ',thread_id, '; ',thread_query) ); @@ -124,6 +125,7 @@ do begin + declare sid int; declare all_done int default 0; declare thread_id bigint default null; @@ -142,6 +144,8 @@ signal sqlstate value '45000' set message_text = 'get_lock'; end if; + set sid := @@server_id; + set all_done = 0; open threads_sleeping; @@ -151,7 +155,7 @@ kill thread_id; - insert into event_log values (@@server_id, now(), 'wmf_slave_wikiuser_sleep', + insert into event_log values (sid, now(), 'wmf_slave_wikiuser_sleep', concat('kill ',thread_id) ); @@ -179,6 +183,7 @@ do begin + declare sid int; declare all_done int default 0; declare thread_id bigint default null; declare thread_query varchar(100); @@ -203,6 +208,8 @@ signal sqlstate value '45000' set message_text = 'get_lock'; end if; + set sid := @@server_id; + set active_count := ( select count(ps.id) from information_schema.processlist ps @@ -225,7 +232,7 @@ kill thread_id; - insert into event_log values (@@server_id, now(), 'wmf_slave_overload (300)', + insert into event_log values (sid, now(), 'wmf_slave_overload (300)', concat('kill ',thread_id,'; ',thread_query) ); diff --git a/dbtools/events_labsdb.sql b/dbtools/events_labsdb.sql index 71adfb2..96c9784 100644 --- a/dbtools/events_labsdb.sql +++ b/dbtools/events_labsdb.sql @@ -3,6 +3,9 @@ set @cache_sql_log_bin := @@session.sql_log_bin; set @@session.sql_log_bin = 1; +set @cache_event_scheduler := @@global.event_scheduler; +set @@global.event_scheduler = 0; + create database if not exists ops; use ops; @@ -10,8 +13,7 @@ -- Remember, table is replicated! -- https://wikitech.wikimedia.org/wiki/MariaDB#Schema_Changes -drop table if exists event_log; -create table event_log ( +create table if not exists event_log ( server_id int unsigned not null, stamp datetime not null, event varchar(100) not null, @@ -36,10 +38,11 @@ do begin - delete from event_log - where stamp < now() - interval 1 day - and server_id = @@server_id - limit 1000; + declare sid int; + + set sid := @@server_id; + + delete from event_log where stamp < now() - interval 1 day and server_id = sid; end ;; @@ -56,6 +59,7 @@ do begin + declare sid int; declare thread_id bigint; set thread_id := ( select ps.id @@ -70,11 +74,13 @@ limit 1 ); + set sid := @@server_id; + if (thread_id is not null) then kill thread_id; - insert into event_log values (@@server_id, now(), 'wmf_labs_sleepers_txn', + insert into event_log values (sid, now(), 'wmf_labs_sleepers_txn', concat('kill ',thread_id) ); @@ -85,3 +91,4 @@ delimiter ; set @@session.sql_log_bin = @cache_sql_log_bin; +set @@global.event_scheduler = @cache_event_scheduler; -- To view, visit https://gerrit.wikimedia.org/r/145509 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I0cb6d51fc817e680f8a33583391e88b8518c24c3 Gerrit-PatchSet: 1 Gerrit-Project: operations/software Gerrit-Branch: master Gerrit-Owner: Springle <sprin...@wikimedia.org> Gerrit-Reviewer: Springle <sprin...@wikimedia.org> Gerrit-Reviewer: jenkins-bot <> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
