Script wmf-auto-reimage was launched by jynus on neodymium.eqiad.wmnet for hosts:

['db1115.eqiad.wmnet']

The log can be found in /var/log/wmf-auto-reimage/201802221205_jynus_17416.log.

Completed auto-reimage of hosts:

['db1115.eqiad.wmnet']

and were ALL successful.

Change 413317 merged by Jcrespo:
[operations/dns@master] wmnet: Change tendril backend to db1115

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

Change 413363 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril:Migrate target host of mariadb maintenance to db1115

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

Change 413363 merged by Jcrespo:
[operations/puppet@production] tendril:Migrate target host of mariadb maintenance to db1115

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

So things pending to fix, aside from the above commits:

• Fix package required for CONNECT- sudo apt-get install libodbc1
• Fix automonting of /srv
• Fix my.cnf basedir on puppet
• Probably firewall or grants issue with labsdbs

• Everything on codfw

But the basic stuff seems to be working for now, so leaving it up for a while

Change 413368 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril: MariaDB fixes and tunings

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

Change 413375 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Fix and standarize firewall holes to all cloud-related mariadbs

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

Change 413368 merged by Jcrespo:
[operations/puppet@production] tendril: MariaDB fixes and tunings

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

Change 413393 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril: Enable mysql binlog with format ROW

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

Change 413393 merged by Jcrespo:
[operations/puppet@production] tendril: Enable mysql binlog with format ROW

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

Change 413401 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril: Fix for server to start correcty

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

Change 413401 merged by Jcrespo:
[operations/puppet@production] tendril: Fix for server to start correcty

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

Change 413415 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] install_serer: Do not format any db in eqiad

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

Change 413417 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril: Revert configuration to tendril's original one

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

Change 413417 merged by Jcrespo:
[operations/puppet@production] tendril: Revert configuration to tendril's original one

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

Change 413423 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] install_server: Cleanup so no db server can be reimaged

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

Change 413415 abandoned by Marostegui:
install_server: Do not format any db in eqiad

Reason:
Jaime already push a similar change

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

Change 413423 merged by Jcrespo:
[operations/puppet@production] install_server: Cleanup so no db server can be reimaged

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

Does the /srv/ still needs fixing? I see it on fstab but I am not sure whether it is working or not. I have rebooted db2093 just to test and working fine.
So I am asking to see if we can mark it as completed.

I was thinking that it wouldn't hurt to copy all the content of db1011 to somewhere else just in case. Even db2093 or esXXXX or dbstore1002 or something.
Just to keep it on a safe place.

Dropping a host works fine.
Adding a host fails with:

ERROR 1105 (HY000) at line 338: (1045) Access denied for user 'watchdog'@'10.64.0.122' (using password: YES)

I will get that fixed

Let me test some config changes first.

I have changed: https://wikitech.wikimedia.org/wiki/Tendril

Change 413709 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] prometheus: Add db1115 and db2093

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

Change 413709 merged by Marostegui:
[operations/puppet@production] prometheus: Add db1115 and db2093

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

Change 413712 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] tendril: Revert all db optimizations except the default table engine

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

Change 413712 merged by Jcrespo:
[operations/puppet@production] tendril: Revert all db optimizations except the default table engine

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

Change 413375 merged by Jcrespo:
[operations/puppet@production] mariadb: Fix and standarize firewall holes to all cloud-related mariadbs

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

We could copy db1011's content to db1113 or db1114 which right now are not used and are just spares.

Mentioned in SAL (#wikimedia-operations) [2018-02-26T06:15:11Z] <marostegui> Stop MySQL on db1115 tendril database to copy it to db2093. Tendril (dbtree) service will be down for maintenance - T184704

Change 414641 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] site.pp: Add a comment about db1113

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

Change 414641 merged by Marostegui:
[operations/puppet@production] site.pp: Add a comment about db1113

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

db1011's content is now placed at db1113:/srv/db1011_backup

Mentioned in SAL (#wikimedia-operations) [2018-02-27T06:21:55Z] <marostegui> Stop MySQL on db1115 to copy it to db2093 - tendril (dbtree) service will be down for this maintenance - T184704

The copy to codfw has been done.
db2093 is now replicating from db1115.
So far it is not able to keep up with the master, but let's give it sometime to warm up.

Replication was working smoothly till I added db2093 to tendril (it wasn't there). Then it broke on db2093

             Last_SQL_Error: Error 'Cannot proceed because system tables used by Event Scheduler were found damaged at server start' on query. Default database: 'tendril'. Query: 'CREATE DEFINER=`root`@`10.64.32.13` event db2093_codfw_wmnet_3306_schema_prep
on schedule every 1 day starts date(now()) + interval floor(rand() * 23) hour
do begin

  select @server_id := id, @enabled := enabled from servers where host = 'db2093.codfw.wmnet' and port = 3306;

  delete from schemata_check where server_id = @server_id;
  insert into schemata_check select @server_id, t.schema_name from db2093_codfw_wmnet_3306_schemata t;

end'

I am investigating

We might be hitting: https://bugs.mysql.com/bug.php?id=70975 and https://mariadb.com/kb/en/library/mariadb-community-couldnt-execute-show-events-cannot-proceed-because-system/

mysql:root@localhost [tendril]> show events;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start
mysql:root@localhost [tendril]> use mysql
Database changed
mysql:root@localhost [mysql]> check table event;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| mysql.event | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

Things that haven't fixed the issue:

• mysql_upgrade --force -s
• dumping mysql.event table from db1115 and placing it back on db2093
• checking and auto-repairing all tables on mysql database

"Note: restarting mysql fixed the problem, but that is rather intrusive and leaves the server cold for a while." ?

That was tested of course, after every single action I listed before, but unfortunately that didn't work for us :-)

Can you test on your lab if replication of events on that version fails from event_scheduler=1 to event_scheduler=0? I cannot even do SHOW EVENTS on the replica.

The immediate fix is to ignore the event creation and do the rest manually, but it needs a more long-term fix. I agree it is possibly a bug.

In T184704#4005549, @jcrespo wrote:

Can you test on your lab if replication of events on that version fails from event_scheduler=1 to event_scheduler=0? I cannot even do SHOW EVENTS on the replica.

Heh, I was just doing so :-)

https://dev.mysql.com/doc/refman/5.7/en/replication-features-invoked.html According to this, an alternative would be to enable global events but disable manually each one (?!) and it should "work", but I am not sure if even we could do that in the current state.

In T184704#4005554, @jcrespo wrote:

https://dev.mysql.com/doc/refman/5.7/en/replication-features-invoked.html According to this, an alternative would be to enable global events but disable manually each one (?!) and it should "work", but I am not sure if even we could do that in the current state.

Plus the fact that we drop/create events when we add a new host. So not sure how that would work either.
Let's see what I can get from my tests.

Worst case scenario, we can skip those events creations for now and let replication work, but let's wait for the tests.

Some more food for thought of my tests (10.1.31 on jessie).
The master has event_scheduler=ON and the slave has it OFF

• I have manually corrupted the event table and restarted mysql and attempted to create an event

Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [ERROR] mysqld: Table './mysql/event' is marked as crashed and last (automatic?) repair failed
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [ERROR] mysqld: Table 'event' is marked as crashed and last (automatic?) repair failed
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [ERROR] Cannot open mysql.event
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [ERROR] mysqld: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [Note] Reading of all Master_info entries succeded
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [Note] Added new Master_info '' to hash table
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511102704384 [Note] Slave SQL thread initialized, starting replication in log 'mariadb-bin.000007' at position 1128, relay log './mysqld-relay-bin.000005' position: 1066
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511104141248 [Note] /usr/sbin/mysqld: ready for connections.
Feb 27 07:25:20 clone mysqld[6663]: Version: '10.1.31-MariaDB-1~jessie'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511103007488 [Note] Slave I/O thread: connected to master 'replication@192.168.56.13:3306',replication started in log 'mariadb-bin.000007' at position 1388
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511084710656 [ERROR] Slave SQL: Error 'Cannot proceed because system tables used by Event Scheduler were found damaged at server start' on query. Default database: 'test'. Query: 'CREATE DEFINER=`root`@`localhost`
Feb 27 07:25:20 clone mysqld[6663]: 2018-02-27  7:25:20 140511084710656 [Warning] Slave: Cannot proceed because system tables used by Event Scheduler were found damaged at server start Error_code: 1577

The table is badly corrupted so I cannot even repair it (good):

MariaDB [(none)]> show global variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [mysql]> check table event;
+-------------+-------+----------+----------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                           |
+-------------+-------+----------+----------------------------------------------------+
| mysql.event | check | warning  | Table is marked as crashed and last repair failed  |
| mysql.event | check | warning  | Size of indexfile is: 4096      Should be: 2048    |
| mysql.event | check | warning  | Size of datafile is: 30670848       Should be: 164 |
| mysql.event | check | error    | Wrong bytesec: 148-129-18 at linkstart: 0          |
| mysql.event | check | error    | Corrupt                                            |
+-------------+-------+----------+----------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [mysql]> repair table event;
+-------------+--------+----------+---------------------------------------------------------------------------+
| Table       | Op     | Msg_type | Msg_text                                                                  |
+-------------+--------+----------+---------------------------------------------------------------------------+
| mysql.event | repair | info     | Wrong bytesec: 148-129- 18 at 0; Skipped                                  |
| mysql.event | repair | info     | Found link that points at 2960886366022950129 (outside data file) at 1580 |
| mysql.event | repair | info     | Found link that points at 3629724800785957258 (outside data file) at 1788 |
| mysql.event | repair | info     | Found block with too small length at 1888; Skipped                        |
| mysql.event | repair | error    | Not enough memory for blob at 1928 (need 1566630461)                      |
| mysql.event | repair | info     | Wrong bytesec: 148-129- 18 at 0; Skipped                                  |
| mysql.event | repair | info     | Found link that points at 2960886366022950129 (outside data file) at 1580 |
| mysql.event | repair | info     | Found link that points at 3629724800785957258 (outside data file) at 1788 |
| mysql.event | repair | info     | Found block with too small length at 1888; Skipped                        |
| mysql.event | repair | error    | Not enough memory for blob at 1928 (need 1566630461)                      |
| mysql.event | repair | status   | Operation failed                                                          |
+-------------+--------+----------+---------------------------------------------------------------------------+
11 rows in set (0.06 sec)

So I just copy it from the master:

MariaDB [mysql]> check table event;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| mysql.event | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

Stopping and starting the replication thread doesn't fix the issue - as expected a mysql restart is needed:

MariaDB [mysql]> stop slave; start slave;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.13
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000007
          Read_Master_Log_Pos: 1388
               Relay_Log_File: mysqld-relay-bin.000005
                Relay_Log_Pos: 1066
        Relay_Master_Log_File: mariadb-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1577
                   Last_Error: Error 'Cannot proceed because system tables used by Event Scheduler were found damaged at server start' on query. Default database: 'test'. Query: 'CREATE DEFINER=`root`@`localhost` EVENT test1     ON SCHEDULE       AT CURRENT_TIMESTAMP + INTERVAL 1 DAY     DO CALL myproc(5, 23)'

And after a mysql restart....replication works and the event is there

MariaDB [(none)]> show global variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

MariaDB [test]> show events\G
*************************** 1. row ***************************
                  Db: test
                Name: test1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: ONE TIME
          Execute at: 2017-02-14 03:21:19

I have opened a bug report with MariaDB: https://jira.mariadb.org/browse/MDEV-15426

Mentioned in SAL (#wikimedia-operations) [2018-02-27T14:05:53Z] <marostegui> Update tendril shard table for the "tendril" replication topology - T184704

Whilst we follow up this potential bug with MariaDB - I have skipped those events queries on db2093 to let it replicate because even if we are able to overcome that event scheduler issue, we need to evaluate if db2093 can replicate and keep up or if we need to add some filters there.

Even though replication is flowing, it should be easy to reproduce the events breakage, just dropping/adding a host in tendril will make it crash.
And the events table keeps being damaged anyways, so the "bug" is still there.

mysql:root@localhost [tendril]> show events;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start

We probably do need to filter (one, or maybe both):

• global_status
• global_status_log

I have set: Replicate_Wild_Ignore_Table: tendril.global_status_logon db2093 to see how replication goes.

An ignore on global_status_log is making no effect. Probably we should try to go for an ignore on global_status too...

I have set this now - we will see how it goes and if the slave can start catching up:

Replicate_Wild_Ignore_Table: tendril.global_status_log,tendril.global_status_log_5m

But as per the binlog scans, all the activity happens on global_status table mostly.

Yes, global_status is a snapshot of "current" state, so rows are deleted and inserted all the time- it can be ignored too.

In T184704#4009554, @jcrespo wrote:

Yes, global_status is a snapshot of "current" state, so rows are deleted and inserted all the time- it can be ignored too.

Thanks for throwing some light into this!
Added now: Replicate_Wild_Ignore_Table: tendril.global_status_log,tendril.global_status_log_5m,tendril.global_status

Replication is not a huge deal- if it is a controlled failover, we can copy data around. If it is an emergency failover, we can start from a blank slave- we only need to run the creation and removal scripts on both hosts (or do it on wrapper scripts).

If things don't improve, my proposal is to leave both hosts as standalones with binary log disabled. Even if replication works, we don't have the guarantee it will always will, and with the config and engines it have, it was probably going to be a headache.

In T184704#4009584, @jcrespo wrote:

Replication is not a huge deal- if it is a controlled failover, we can copy data around. If it is an emergency failover, we can start from a blank slave- we only need to run the creation and removal scripts on both hosts (or do it on wrapper scripts).

If things don't improve, my proposal is to leave both hosts as standalones with binary log disabled. Even if replication works, we don't have the guarantee it will always will, and with the config and engines it have, it was probably going to be a headache.

Yeah - agreed.
Things are not improving unfortunately, so it might be easier to leave them as standalone servers indeed without binlog.

Change 415257 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] tendril.my.cnf.erb: Disable binlog

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

Change 415257 merged by Marostegui:
[operations/puppet@production] tendril.my.cnf.erb: Disable binlog

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

Mentioned in SAL (#wikimedia-operations) [2018-02-28T11:37:09Z] <marostegui> Reset slave all on db2093 - T184704

This seems solved to me? We can talk backups on the other goal (e.g. regular logica backups of the structure and some tables, but not with mydumper).

In T184704#4009801, @jcrespo wrote:

This seems solved to me? We can talk backups on the other goal (e.g. regular logica backups of the structure and some tables, but not with mydumper).

Yes - only pending to restart db1115 to pick up the binlog disablement.
Let me know when it is a good moment to restart it.

If it is just a restart, just disable event_scheduler, wait for a few seconds and restart now.

ok! Doing it now - thanks

Mentioned in SAL (#wikimedia-operations) [2018-02-28T12:00:56Z] <marostegui> Reboot db1115 tendril master to pick up new my.cnf options - T184704

In T184704#4010361, @jcrespo wrote:

Actually, we need to puppetize the event_scheduler on or off. Maybe an eqiad/codfw.yaml enabled/disbled hiera key?

Or maybe for now as we are not planning on expanding this or working a lot more on this, doing a quick if on the tendril.my.cnf.erb to get it over with and spend proper time on it once we really start working towards tendril simplication/expansion/clean up etc.

Ok to me, as long as it is puppetized- for when we have to reboot the servers or they crash.

Change 415336 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] tendril.my.cnf.ebr: Enable/disable event_scheduler

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

Change 415336 merged by Marostegui:
[operations/puppet@production] tendril.my.cnf.ebr: Enable/disable event_scheduler

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