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

Can't create "new password" users on old_passwords=1 replication setup

    XMLWordPrintable

Details

    Description

      When having both master and slave set up with old_passwords=1 it is possible to create users with new password hash format by setting old_passwords=0 on the session level.

      The slave will break on this though as the variable setting is not replicated, and so either the old format is used on the slave, or an error about wrong password hash size is raised on the slave side, stopping the slave SQL thread, when giving the password hash value directly.

      How to reproduce:

      First create a replication setup where old_passwords=1 is the default global setting for both master and slave.

      Then, on the master:

      SET SESSION old_passwords=0; 
      CRETE USER 'test_user'@'localhost' IDENTIFIED BY 'xxx';
       
      SELECT user,host, password FROM mysql.user WHERE user='test_user'\G
      *************************** 1. row ***************************
          user: test_user
          host: localhost
      password: *3D56A309CD04FA2EEF181462E59011F075C89548
      

      On the slave user was created with old password format:

      select user,host, password from mysql.user where user='test_user'\G
      *************************** 1. row ***************************
          user: test_user
          host: localhost
      password: 663c5dd53dae4ed0
      

      Now lets try to set the password hash directly on the master side:

      SET SESSION old_passwords=0; 
       
      CREATE USER 'test_user2'@'localhost' identified by password "*1234567891234567891234567891234567891234";
      

      This breaks the slave:

                     Last_SQL_Errno: 1372
                     Last_SQL_Error: Error 'Password hash should be a 16-digit hexadecimal number' on query. Default database: ''. Query: 'create user 'test_user2'@'localhost' identified by password "*1234567891234567891234567891234567891234"'
      

      The only workaround for now seems to be to use CREATE USER without password first, then to UPDATE the password column in the mysql.user column directly, followed by a FLUSH PRIVILEGES. This gets replicated to the slave correctly.

       

      Attachments

        Activity

          People

            serg Sergei Golubchik
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            5 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.