So far I have fixed all the drifts and replication is catching up. We'll see if we get more of those.

BTW, for the record, some grafana metrics for context:

master:

backup:

from: https://grafana-labs.wikimedia.org/d/000000018/labs-project-board?orgId=1&var-project=clouddb-services&var-server=All&from=now-12h&to=now

Is there a method you recommend for recloning the tables, @Marostegui . I cannot say I have done a single table clone on mysql to a replica this decade 🙂.

We can always check on the users/data, of course. For all I know, we have new databases that need to be excluded from replication. I'll poke it today. Thanks!

Not sure if it is of value, but I caught the MariaDB log output from the first startup of the database after we forced the instance to reboot.

We have a script that we actually used to use on labsdb1004/labsdb1005 (the old toolsdb) to sync tables: https://github.com/wikimedia/operations-software/blob/master/dbtools/reimport_from_master.sh

Circling back around to this. It looks like I'm seeing: s51245__totoazero.maj_articles_recents is the major source of trouble. Timing-wise, I don't think I see any other issues.

That's the totoazero tool. Admins are @Framawiki and @totoazero. If that table is something you folks intend to keep, I can use/adapt that script to reclone it for you.

@Bstorm you might want to have the host up-to-date with replication before attempting the restore, probably the best approach is to skip those tables with replication filters (I can help with that once you've identified the ones you want to skip)

Ahh, yeah, oops. I see that Seconds_Behind_Master: NULL and the threads are dead all over the logs. So @Marostegui were the earlier ones that I think are timestamped before T253738#6169013 fixed already? If so, then I'm only aware of s51245__totoazero.maj_articles_recents.

Yes, the ones that I fixed might still have issues, as they could come once replication resumes again.
If it says NULL, above that you'll be able to see the table involved, you'd need to exclude it as a replication filter, and then hit start slave; again, so it will ignore anything that comes to that table.
You might need to do that for all the tables that will keep failing (if any).

Change 599926 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: Add misbehaving table to replication filters

https://gerrit.wikimedia.org/r/599926

So stupid question, @Marostegui, if I do that patch and restart on mysqld on the slave without restarting the master, will that work or do I need to CHANGE REPLICATION FILTER with the whole mess?

If you add the replication filter to my.cnf and restart the process on the slave, that would work, no need to touch the master there.
You can also do it without restarting mysql by doing: `change replication filter REPLICATE_WILD_IGNORE_TABLE = ('$DB.$TABLE'); (make sure to include the existing ones that you can get with a show slave status\G)

I am writing from my phone, so I could've made some typos!

Change 599926 merged by Bstorm:
[operations/puppet@production] toolsdb: Add misbehaving table to replication filters

https://gerrit.wikimedia.org/r/599926

So after merging that, I realized that the escapes don't seem to be correct for a table vs a wild_table, so I stopped puppet and made it a wildcard entry. That seems to have got replication moving again.

Mentioned in SAL (#wikimedia-cloud) [2020-05-29T23:42:04Z] <bstorm_> stopped puppet and restarting mariadb on clouddb1002 after filtering out a table T253738

Seconds_Behind_Master: 213890 sounds a lot better. Let's see if it actually catches up.

In T253738#6178414, @Bstorm wrote:

Seconds_Behind_Master: 213890 sounds a lot better. Let's see if it actually catches up.

Keep in mind that it can break anytime soon and you might need to add more replication filters until it has caught up with the master (Seconds_Behind_Master: 0)

Change 601385 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: add another temprorary filter

https://gerrit.wikimedia.org/r/601385

Change 601385 merged by Bstorm:
[operations/puppet@production] toolsdb: add another temprorary filter

https://gerrit.wikimedia.org/r/601385

Mentioned in SAL (#wikimedia-cloud) [2020-06-01T18:51:28Z] <bstorm_> restarting mariadb on clouddb1002 T253738

So far so good. It's still replicating. It hasn't fully caught up yet, though.

Replication still progressing ('Seconds_Behind_Master: 108583').

New things seem to be coming in faster than they are going out right now (Seconds_Behind_Master: 123536), but at least it has not hit another hard failure yet.

Seconds_Behind_Master: 173230

This server is getting behind because there is an optimize happening from one of the users which is causing high load:

|    5 | system user     |           | s51434__mixnmatch_p | Connect |  71110 | copy to tmp table                | optimize table entry |   44.832 |

That table is quite big:

-rw-rw---- 1 mysql mysql  70G Jun  3 17:58 entry.ibd

But it looks like it is almost done:

-rw-rw---- 1 mysql mysql   58G Jun  4 13:48 #sql-5595_5.ibd

New breakage:

Seconds_Behind_Master: NULL
Last_SQL_Error: Could not execute Write_rows_v1 event on table s51080__checkwiki_p.cw_new; Duplicate entry 'enwiki-Ituri Province' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log log.209740, end_log_pos 52679930

Change 602433 had a related patch set uploaded (by BryanDavis; owner: Bryan Davis):
[operations/puppet@production] toolsdb: add 3rd temporary filter for replication

https://gerrit.wikimedia.org/r/602433

Mentioned in SAL (#wikimedia-cloud) [2020-06-04T17:41:25Z] <bd808> Restarting mariadb after hand applying {{gerrit|602433}} (T253738)

Replicating again (Seconds_Behind_Master: 31577)

Another busted table:

Last_SQL_Error: Could not execute Delete_rows_v1 event on table s51080__checkwiki_p.cw_dumpscan; Can't find record in 'cw_dumpscan', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log log.209768, end_log_pos 75595466

s51080__checkwiki_p.cw_dumpscan excluded from replication. (Seconds_Behind_Master: 26287)

Seconds_Behind_Master: 12774

Seconds_Behind_Master: 0!!!

Next steps will be to figure out what can be done about the 4 tables that were filtered out to get this far.

Ok, so it seems like to use https://github.com/wikimedia/operations-software/blob/master/dbtools/reimport_from_master.sh, we need to adjust some of the user information for root in mariadb if I'm reading that correctly. I'm poking around that.

Change 602433 merged by Bstorm:
[operations/puppet@production] toolsdb: add more temporary filters for replication

https://gerrit.wikimedia.org/r/602433

So the tools with tables that are out of sorts are:

• totoazero
• request
• checkwiki

Since cloning the data requires setting sessions or the whole db to readonly, it may be better to work with users on this. I imagine that dumping the table, dropping it, removing the filter and then reloading it would work right @Marostegui ? I can make subtasks for working with the tool maintainers to decide to either abandon the data as something that is actually replicated or do something akin to that to restore replication.

Unfortunately setting specific sessions (or databases) to read-only isn't possible with mysql, it is either everything or nothing.
What you could do is:

• Talk to the tool owners to disable/stop those tools to make sure no writes arrive to their tables.
• Remove replication filters
• drop + import the tables from the master.

Thanks @Marostegui. I'll get that ball rolling.

Change 617533 had a related patch set uploaded (by Bstorm; owner: Bstorm):
[operations/puppet@production] toolsdb: remove one of the temporary replication filters

https://gerrit.wikimedia.org/r/617533

Change 617533 merged by Bstorm:
[operations/puppet@production] toolsdb: remove one of the temporary replication filters

https://gerrit.wikimedia.org/r/617533

That's one filter down. I documented the process in some detail at https://wikitech.wikimedia.org/wiki/Portal:Data_Services/Admin/Toolsdb#Re-importing_data_after_replication_failures