Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15426

Event scheduler disabled on a slave possibly breaking replication

    XMLWordPrintable

    Details

      Description

      The environment is:

      master:
      mariadb 10.1.31
      debian stretch
      event_scheduler=ON

      slave:
      mariadb 10.1.31
      debian stretch
      event_scheduler=OFF

      Replication: ROW based

      The master was originally a standalone server and we wanted to add a slave.
      To do so we stopped mariadb and copied all the content over to the future slave.

      We do run events on the master and we didn't want those to run on the slave, as otherwise row based replication will break.

      When setting up the slave we started it for the first time with event_scheduler=0
      We started replication and it was working finely until an event was created on the master.
      It broke replication with:

                     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'
      
      

      At first we thought the mysql.event  table could be corrupted because of the data transfer between the two servers. But it wasn't or doesn't look like it (as per check table event command)
      These are all the actions we have tried to try to get this fixed.

      • Repair event table + restarting mariadb
      • Analyze event table + restarting mariadb
      • Dropping + creating the event table + restarting mariadb
      • mysqldump from the master + load on the slave + restarting mariadb
      • altering event table to make it InnoDB + restarting mariadb
      • mysql_upgrade --force -s

      The table doesn't look like it is corrupted:

      mysql:root@localhost [mysql]> check table event;
      +-------------+-------+----------+----------+
      | Table       | Op    | Msg_type | Msg_text |
      +-------------+-------+----------+----------+
      | mysql.event | check | status   | OK       |
      +-------------+-------+----------+----------+
      1 row in set (0.05 sec)
       
      mysql:root@localhost [mysql]> select count(*) from event;
      +----------+
      | count(*) |
      +----------+
      |     1812 |
      +----------+
      1 row in set (0.00 sec)
       
      mysql:root@localhost [mysql]> select * from event limit 1\G
      *************************** 1. row ***************************
                        db: tendril
                      name: es1011_eqiad_wmnet_3306_usage
                      body: begin
       
          if (get_lock('es1011_eqiad_wmnet_3306_usage', 1) = 0) then
              signal sqlstate value '45000' set message_text = 'get_lock';
          end if;
      

      Yet mariadb thinks so:

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

      And upon server restart

      Feb 27 13:26:03 db2093 mysqld[6921]: 2018-02-27 13:26:03 139909807241984 [ERROR] Slave SQL: 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.
      Feb 27 13:26:03 db2093 mysqld[6921]:   on schedule every 1 day starts date(now()) + interval floor(rand() * 23) hour
      Feb 27 13:26:03 db2093 mysqld[6921]:   do begin
      Feb 27 13:26:03 db2093 mysqld[6921]:     select @server_id := id, @enabled := enabled from servers where host = 'db2093.codfw.wmnet' and port = 3306;
      Feb 27 13:26:03 db2093 mysqld[6921]:     delete from schemata_check where server_id = @server_id;
      Feb 27 13:26:03 db2093 mysqld[6921]:     insert into schemata_check select @server_id, t.schema_name from db2093_codfw_wmnet_3306_schemata t;
      Feb 27 13:26:03 db2093 mysqld[6921]:   end', Gtid 171966586-171966586-319085319, Internal MariaDB error code: 1577
      Feb 27 13:26:03 db2093 mysqld[6921]: 2018-02-27 13:26:03 139909807241984 [Warning] Slave: Cannot proceed because system tables used by Event Scheduler were found damaged at server start Error_code: 1577
      

      This looks pretty similar to: https://bugs.mysql.com/bug.php?id=70975 which affects 5.7 and is still open.

      We could probably try to see if this doesn't happen if the slave has event_scheduler=ON (even though replication will be broken because of data drifts)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              marostegui Manuel Arostegui
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.