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

Avoid temporary table updates being written to binlog if no data was altered

Details

    • Task
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • None
    • Replication
    • None

    Description

      Given a slave server with read_only = on and binlog_format = statement which is used to perform complex queries involving some temporary table (which work as expected despite read_only mode)

      However, even if no other data has (and could have) been altered on the slave, all statements dealing with the temporary table appear in the slave's binlog. It doesn't help to cover them in a transaction which is rolled back afterwards.

      In contrast, with binlog_format = row nothing is written to the binlog as expected. But on a read-only slave the binlog format shouldn't matter, especially since "statement" is the default.

      (Side note: the binlog record increases the GTID sequence number which might lead to issues when GTID strict mode is turned on).

      Attachments

        Issue Links

          Activity

            on a read-only slave the binlog format shouldn't matter

            It's the other way round – binlog format matters as much as always, because read_only option does not anyhow affect binary logging, whatever the format is. It prohibits execution of user's SQL updates (with some exceptions); but whatever is executed, is written into binary log as usual. Updates to temporary tables are normally written to the binary log in statement mode and not written in row mode, so they continue working in the same exact way, just like super-user updates are written normally into the binary log, regardless the read_only mode.

            If you are running your logic with temporary tables as a user with SUPER privilege, you can use SET sql_log_bin=0 to prevent binlog updates.

            elenst Elena Stepanova added a comment - on a read-only slave the binlog format shouldn't matter It's the other way round – binlog format matters as much as always, because read_only option does not anyhow affect binary logging, whatever the format is. It prohibits execution of user's SQL updates (with some exceptions); but whatever is executed, is written into binary log as usual. Updates to temporary tables are normally written to the binary log in statement mode and not written in row mode, so they continue working in the same exact way, just like super-user updates are written normally into the binary log, regardless the read_only mode. If you are running your logic with temporary tables as a user with SUPER privilege, you can use SET sql_log_bin=0 to prevent binlog updates.

            You got a point with the binlog_format, I didn't think about that. I also know that sql_log_bin = 0 would help, but granting SUPER privileges might not be a suitable solution.

            I still think that particular behavior of temporary tables in read-only mode comes somewhat unexpected: Read-only is used to prevent updates on slaves for unprivileged users - which is a great feature in an environment where various users need access to a replica for data mining, and it's great that the can use temporary table for this purpose even if read only.

            But creating a binlog entry consumes a sequence number for the current GTID domain id. So with log-slave-updates = 'ON' it will pollute the binlog with two identical sequence numbers for the current domain, and with gtid_strict_mode = 'ON' replication would break immediately.

            Maybe my understanding of read-only = 'ON' is wrong, but then this might need clarification in the documentation.

            DrMurx Jan Kunzmann (Inactive) added a comment - You got a point with the binlog_format, I didn't think about that. I also know that sql_log_bin = 0 would help, but granting SUPER privileges might not be a suitable solution. I still think that particular behavior of temporary tables in read-only mode comes somewhat unexpected: Read-only is used to prevent updates on slaves for unprivileged users - which is a great feature in an environment where various users need access to a replica for data mining, and it's great that the can use temporary table for this purpose even if read only. But creating a binlog entry consumes a sequence number for the current GTID domain id. So with log-slave-updates = 'ON' it will pollute the binlog with two identical sequence numbers for the current domain, and with gtid_strict_mode = 'ON' replication would break immediately. Maybe my understanding of read-only = 'ON' is wrong, but then this might need clarification in the documentation.

            I agree that the way it's happening now is unfortunate, especially since for some replication scenarios it might be really important that you don't have direct updates in the slave's binary log.

            At the moment, I don't have a good suggestion how to solve it. The first thought would be to introduce yet another variation of read_only option, but we already have too many, for my taste. Blindly disabling all writes to the binary log would probably be wrong, as a superuser might perform something that should actually be written. And if we keep writing superuser's updates, we can't disable binary logging of all updates to temporary tables, because it might make superuser's events inconsistent.

            I will convert it into a feature request, hopefully eventually we'll come up with a suitable solution.
            Meanwhile, greenman, could you please find a suitable place to document that read_only option doesn't stop events from being written to the binary log, if these events are allowed to happen under read_only, and if they are normally writable to binary log?

            elenst Elena Stepanova added a comment - I agree that the way it's happening now is unfortunate, especially since for some replication scenarios it might be really important that you don't have direct updates in the slave's binary log. At the moment, I don't have a good suggestion how to solve it. The first thought would be to introduce yet another variation of read_only option, but we already have too many, for my taste. Blindly disabling all writes to the binary log would probably be wrong, as a superuser might perform something that should actually be written. And if we keep writing superuser's updates, we can't disable binary logging of all updates to temporary tables, because it might make superuser's events inconsistent. I will convert it into a feature request, hopefully eventually we'll come up with a suitable solution. Meanwhile, greenman , could you please find a suitable place to document that read_only option doesn't stop events from being written to the binary log, if these events are allowed to happen under read_only , and if they are normally writable to binary log?

            Generally a superuser can perform writes even under read-only. So if one cannot perform writes — they're not a superuser and I don't see why these actions should be binlogged.

            serg Sergei Golubchik added a comment - Generally a superuser can perform writes even under read-only. So if one cannot perform writes — they're not a superuser and I don't see why these actions should be binlogged.

            People

              Unassigned Unassigned
              DrMurx Jan Kunzmann (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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