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

ALTER SEQUENCE may end up in optimistic parallel slave binlog out-of-order

Details

    Description

      On optimistic parallel slave ALTER (Innodb) SEQUENCE passes the commit right too early to the next-to-commit transaction. It does so before its bilogging is done so that the following transaction may win race to binlog first and ALTER fail with an error like

      Last_Error: Error 'An attempt was made to binlog GTID 0-1-100 which would create an out-of-order sequence number with existing GTID 0-1-101 and gtid strict mode is enabled' on query. Default database: 'test'. Query: 'alter sequence s1 restart with 1'
      

      As a possible w/a, note ALTER has actually completed in the engine part (Innodb) not being able to binlog. The slave state is already past the ALTER's gtid so it's correct, nothing to do with gtid_slave_pos. If its absence in binlog is tolerable then the slave can be simply restarted. If the ALTER binlogging matters, then consider to re-execute ALTER, preserving its gitd course, from a slave local connection. After replaying from local slave connection ALTER (and its GTID) will end up logged out-of-order though (so gtid strict mode replication from this slave to elsewhere won't work out at its point).

      Attachments

        Activity

          Elkin Andrei Elkin created issue -
          Elkin Andrei Elkin made changes -
          Field Original Value New Value
          Description On optimistic parallel slave ALTER SEQUENCE passes commit right to early to the next-to-commit transaction. It does so before its bilogging is done so that the following transaction may win race to binlog first and ALTER fail with an error like

          {noformat}
          Last_Error: Error 'An attempt was made to binlog GTID 0-1-100 which would create an out-of-order sequence number with existing GTID 0-1-101
          {noformat}
          On optimistic parallel slave ALTER SEQUENCE passes the commit right too early to the next-to-commit transaction. It does so before its bilogging is done so that the following transaction may win race to binlog first and ALTER fail with an error like

          {noformat}
          Last_Error: Error 'An attempt was made to binlog GTID 0-1-100 which would create an out-of-order sequence number with existing GTID 0-1-101
          {noformat}
          Elkin Andrei Elkin made changes -
          Description On optimistic parallel slave ALTER SEQUENCE passes the commit right too early to the next-to-commit transaction. It does so before its bilogging is done so that the following transaction may win race to binlog first and ALTER fail with an error like

          {noformat}
          Last_Error: Error 'An attempt was made to binlog GTID 0-1-100 which would create an out-of-order sequence number with existing GTID 0-1-101
          {noformat}
          On optimistic parallel slave ALTER (Innodb) SEQUENCE passes the commit right too early to the next-to-commit transaction. It does so before its bilogging is done so that the following transaction may win race to binlog first and ALTER fail with an error like

          {noformat}
          Last_Error: Error 'An attempt was made to binlog GTID 0-1-100 which would create an out-of-order sequence number with existing GTID 0-1-101 and gtid strict mode is enabled' on query. Default database: 'test'. Query: 'alter sequence s1 restart with 1'
          {noformat}

          As a possible w/a, note ALTER has actually completed in the engine part (Innodb) not being able to binlog. The slave state is already past the ALTER's gtid so it's correct, nothing to do with gtid_slave_pos. If its absence in binlog is tolerable then the slave can be simply restarted. If the ALTER binlogging matters, then consider to re-execute ALTER, preserving its gitd course, from a slave local connection. After replaying from local slave connection ALTER (and its GTID) will end up logged out-of-order though (so gtid strict mode replication from this slave to elsewhere won't work out at its point).
          Elkin Andrei Elkin made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          pandi.gurusamy Pandikrishnan Gurusamy made changes -
          Affects Version/s 10.6.12 [ 28513 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.8 [ 26121 ]
          Fix Version/s 10.9 [ 26905 ]
          Fix Version/s 10.10 [ 27530 ]
          Fix Version/s 10.11 [ 27614 ]
          Elkin Andrei Elkin made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          Elkin Andrei Elkin made changes -
          Assignee Andrei Elkin [ elkin ] Brandon Nesterenko [ JIRAUSER48702 ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          bnestere Brandon Nesterenko made changes -
          Status In Review [ 10002 ] Stalled [ 10000 ]
          Elkin Andrei Elkin made changes -
          Assignee Brandon Nesterenko [ JIRAUSER48702 ] Andrei Elkin [ elkin ]
          Elkin Andrei Elkin made changes -
          Component/s Replication [ 10100 ]
          Fix Version/s 10.4.29 [ 28510 ]
          Fix Version/s 10.5.20 [ 28512 ]
          Fix Version/s 10.6.13 [ 28514 ]
          Fix Version/s 10.9.6 [ 28520 ]
          Fix Version/s 10.10.4 [ 28522 ]
          Fix Version/s 10.11.3 [ 28524 ]
          Fix Version/s 10.8.8 [ 28518 ]
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.6 [ 24028 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          ralf.gebhardt Ralf Gebhardt made changes -
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 181245

          People

            Elkin Andrei Elkin
            Elkin Andrei Elkin
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.