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

Non-atomic CREATE OR REPLACE USER breaks replication

    XMLWordPrintable

Details

    • Can result in unexpected behaviour
    • Hide
      AI generated: If CREATE OR REPLACE USER or CREATE OR REPLACE ROLE failed (for example, due to an invalid authentication plugin), the existing account was permanently dropped without writing to the binary log, causing replication to fail on replicas. The statement now validates the definition first, leaving the existing account untouched on failure, and builds successful replacements from default values so unspecified attributes (such as passwords, resource limits, SSL options, and lock status) do not survive. This also removes orphan privileges and prevents reading invalid memory during SHOW GRANTS on accounts that were granted a replaced role.
      Show
      AI generated: If CREATE OR REPLACE USER or CREATE OR REPLACE ROLE failed (for example, due to an invalid authentication plugin), the existing account was permanently dropped without writing to the binary log, causing replication to fail on replicas. The statement now validates the definition first, leaving the existing account untouched on failure, and builds successful replacements from default values so unspecified attributes (such as passwords, resource limits, SSL options, and lock status) do not survive. This also removes orphan privileges and prevents reading invalid memory during SHOW GRANTS on accounts that were granted a replaced role.

    Description

      I would expect it to be already filed, but can't find it.

      CREATE [OR REPLACE] USER was not a part of the big "Atomic DDL" effort MDEV-17567, so I suppose we can say that it is not atomic by design. The problem is that it breaks replication too easily.

      --source include/master-slave.inc
      CREATE USER u;
      --error ER_PLUGIN_IS_NOT_LOADED
      CREATE OR REPLACE USER u IDENTIFIED VIA x;
      CREATE USER u;
      SHOW BINLOG EVENTS;
       
      --sync_slave_with_master
       
      --connection master
      DROP USER u;
      --source include/rpl_end.inc
      

      Failing CREATE OR REPLACE drops the user without creating a new one (== not atomic), but it also doesn't write DROP in the binary log, so the next successful CREATE fails on the slave.

      main 6ef1303d6931bcbff6cc4a1d66aad8d9b1aa52e6

      CREATE OR REPLACE USER u IDENTIFIED VIA x;
      ERROR HY000: Plugin 'x' is not loaded
      CREATE USER u;
      SHOW BINLOG EVENTS;
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	4	Format_desc	1	256	Server ver: 12.1.0-MariaDB-asan-debug-log, Binlog ver: 4
      master-bin.000001	256	Gtid_list	1	285	[]
      master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
      master-bin.000001	329	Gtid	1	371	GTID 0-1-1
      master-bin.000001	371	Query	1	451	use `test`; CREATE USER u
      master-bin.000001	451	Gtid	1	493	GTID 0-1-2
      master-bin.000001	493	Query	1	573	use `test`; CREATE USER u
      ...
      Last_Errno	1396
      Last_Error	Error 'Operation CREATE USER failed for 'u'@'%'' on query. Default database: 'test'. Query: 'CREATE USER u'
      

      Attachments

        Activity

          People

            wlad Vladislav Vaintroub
            elenst Elena Stepanova
            Vladislav Vaintroub Vladislav Vaintroub
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 0d
                0d
                Remaining:
                Remaining Estimate - 0d
                0d
                Logged:
                Time Spent - 1d 25m
                1d 25m

                Git Integration

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