We run a proxy in front of the eventlogging database, called m4-master

If we can't write to the failover, then we probably shouldn't use the failover proxy name for the EventLogging mysql writer, eh? Let's change that to db1107.

In T188991#4027078, @Ottomata wrote:

We run a proxy in front of the eventlogging database, called m4-master

If we can't write to the failover, then we probably shouldn't use the failover proxy name for the EventLogging mysql writer, eh? Let's change that to db1107.

I don't really get that comment :-)
Can you explain a bit what you mean?

In T188991#4027191, @Marostegui wrote:

In T188991#4027078, @Ottomata wrote:

We run a proxy in front of the eventlogging database, called m4-master

If we can't write to the failover, then we probably shouldn't use the failover proxy name for the EventLogging mysql writer, eh? Let's change that to db1107.

I don't really get that comment :-)
Can you explain a bit what you mean?

Basically that as follow up step the log db on db1108 should be able to accept writes, or that we hardcode db1107 instead of m4-master on Eventlogging's config to avoid this in the future. But we'll have a lot of time to follow up on this, let's focus on recovering the data first :)

In T188991#4027223, @elukey wrote:

In T188991#4027191, @Marostegui wrote:

In T188991#4027078, @Ottomata wrote:

We run a proxy in front of the eventlogging database, called m4-master

If we can't write to the failover, then we probably shouldn't use the failover proxy name for the EventLogging mysql writer, eh? Let's change that to db1107.

I don't really get that comment :-)
Can you explain a bit what you mean?

Basically that as follow up step the log db on db1108 should be able to accept writes, or that we hardcode db1107 instead of m4-master on Eventlogging's config to avoid this in the future. But we'll have a lot of time to follow up on this, let's focus on recovering the data first :)

gotcha - thanks.
Let's follow up after the whole thing is recovered.

Mentioned in SAL (#wikimedia-analytics) [2018-03-06T19:06:21Z] <elukey> cleaned up id=0 rows on db1108 (log database) for T188991

It seems that we were (kind of) lucky. For some reason that we don't know (it predates most of us), the tables on the slave db do not have the id field with AUTO-INCREMENT, meanwhile by default on the master this is the case. When the Eventlogging Mysql consumers were pushing new rows to db1108 (the slave) they ended up not specifying any id, and the one that was picked up was 0 (not present in any table on the master since the ids all starts from 1). For each table, once the first row was inserted (with id 0) then the other ones were failing because they were trying to override the same id 0 one, returning (only sometimes) a "Duplicate row etc.." on the eventlogging logs.

So we ended up deleting on the slave all the rows with id 0 (one for each table more or less), and we moved the Eventlogging Mysql Kafka consumers' committed offset in the past via a script that @Ottomata wrote to replay all the data. No duplicates were inserted because of the uuid field of the eventlogging tables.

Data should now be recovered.

For posterity, here's the script I used: https://gist.github.com/ottomata/d69ba72313c44e8e45e6453f4ea97074

Before closing this task:

1. review the m4-master failover policy.
2. document this procedure on wikitech

A bit of historic context about the why db1108 is not read-only:

# History context: there used to be a distinction between
# EL master and slaves, namely that only the master was not
# in read only mode. The Analytics team removed this constraint
# before deploying the eventlogging_cleaner script (T156933),
# that needed to DELETE/UPDATE rows on the job database without
# running as root for obvious reasons.

So what we could do, in theory, is to change the m4-master endpoint with something that is not dbproxied (namely that ends up to db1107 only, so if it goes down no automatic failover happens).

In case of db1107 failure, the mysql consumers would keep failing to insert data and eventually the analytics alarms would trigger. Resetting the consumer groups to a previous/earlier state would ensure that no data is lost.

Change 425231 had a related patch set uploaded (by Elukey; owner: Elukey):
[operations/dns@master] Set m4-master to db1107 rather than dbproxy

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

Change 425231 merged by Elukey:
[operations/dns@master] Set m4-master to db1107 rather than dbproxy

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

Created https://wikitech.wikimedia.org/wiki/Analytics/Systems/EventLogging/Administration#Mysql_insertion_rate_dropping_to_zero_due_to_db_failures

Mentioned in SAL (#wikimedia-analytics) [2018-04-10T09:00:44Z] <elukey> restart eventlogging mysql consumers on eventlog1002 to pick up new DNS changes for m4-master - T188991