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

'Roles' are created incorrectly on an install that was previously MySQL 5.7

Details

    Description

      When moving from MySQL 5.7 to MariaDB 10.2, roles are not usable. This seems to persist, even if the MariaDB instance is further upgraded (first noticed on a production 10.3 server).

      Steps I have taken to replicate this issue:

      • Create fresh EC2 instance, running Ubuntu 18.04 LTS
      • Run apt update / apt upgrade
      • Install mysql-server (installs 5.7.30-0ubuntu0.18.04.1)
      • Proceed through installation process accepting defaults, ensure once finished server has started
      • Stop the server, uninstall the MySQL package, install the MariaDB package as per here
      • Stop the server, start without grant tables, update root user to use 'unix_socket' instead of 'auth_socket' (via FLUSH PRIVILEGES; ALTER USER `root`@`localhost` IDENTIFIED VIA unix_socket;)

      The below test script now shows roles don't work (on a straight MariaDB install, `is_role` would be 'Y' and the `SET ROLE` would work as expected:

      MariaDB [(none)]> CREATE ROLE `aRole`;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SET ROLE `aRole`;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> FLUSH PRIVILEGES;
      		 
      Query OK, 0 rows affected (0.00 sec)
      		 
       
      		 
      MariaDB [(none)]> SET ROLE `aRole`;
      		 
      ERROR 1959 (OP000): Invalid role specification `aRole`
      		 
      MariaDB [(none)]> SELECT `User`, `is_role` FROM `mysql`.`user`;
      		 
      +------------------+---------+
      		 
      | User             | is_role |
      		 
      +------------------+---------+
      		 
      | root             | N       |
      		 
      | mysql.session    | N       |
      		 
      | mysql.sys        | N       |
      		 
      | debian-sys-maint | N       |
      		 
      | aRole            | N       |
      		 
      +------------------+---------+
      		 
      5 rows in set (0.00 sec)

      (Running the select prior to the FLUSH PRIVILEGES; has the same result)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          View 7 older comments
          elenst Elena Stepanova added a comment -

          serg, yes, that's what I meant (sorry for the confusion, now re-reading my comment I see it was ambiguous) .

          If we compare a clean 10.2 installation to a clean MySQL 5.7 installation, up to and including password_expired the column lists are identical.
          Right after it 10.2 has

            `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
          		 
            `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
          		 
            `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000,

          while MySQL 5.7 has

            `password_last_changed` timestamp NULL DEFAULT NULL,
          		 
            `password_lifetime` smallint(5) unsigned DEFAULT NULL,
          		 
            `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

          So, when a crossgrade from MySQL 5.7 to MariaDB 10.2 happens, since extra columns aren't dropped, and missing columns are added to the end, we end up with

            `password_last_changed` timestamp NULL DEFAULT NULL,
          		 
            `password_lifetime` smallint(5) unsigned DEFAULT NULL,
          		 
            `account_locked` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',
          		 
            `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
          		 
            `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
          		 
            `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000,

          which apparently role logic cannot handle.

          I can't tell what is "more wrong" – that the logic requires a certain position for certain columns, or that the columns aren't put into this exact position upon upgrade – but there is some discrepancy.

          elenst Elena Stepanova added a comment - serg , yes, that's what I meant (sorry for the confusion, now re-reading my comment I see it was ambiguous) . If we compare a clean 10.2 installation to a clean MySQL 5.7 installation, up to and including password_expired the column lists are identical. Right after it 10.2 has `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000, while MySQL 5.7 has `password_last_changed` timestamp NULL DEFAULT NULL, `password_lifetime` smallint(5) unsigned DEFAULT NULL, `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', So, when a crossgrade from MySQL 5.7 to MariaDB 10.2 happens, since extra columns aren't dropped, and missing columns are added to the end, we end up with `password_last_changed` timestamp NULL DEFAULT NULL, `password_lifetime` smallint(5) unsigned DEFAULT NULL, `account_locked` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N', `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000, which apparently role logic cannot handle. I can't tell what is "more wrong" – that the logic requires a certain position for certain columns, or that the columns aren't put into this exact position upon upgrade – but there is some discrepancy.
          danblack Daniel Black added a comment -

          Fixed the second "more wrong" by doing correct order in mysql_upgrade - bb-10.2-MDEV-23201-mysql_upgrade-order-user-columns

          danblack Daniel Black added a comment - Fixed the second "more wrong" by doing correct order in mysql_upgrade - bb-10.2- MDEV-23201 -mysql_upgrade-order-user-columns
          danblack Daniel Black added a comment -

          So fix version 10.2 rather than 10.1?

          danblack Daniel Black added a comment - So fix version 10.2 rather than 10.1?
          danblack Daniel Black added a comment -

          Confirmed as 10.2 from serg

          danblack Daniel Black added a comment - Confirmed as 10.2 from serg
          anel Anel Husakovic added a comment -

          Closed with ff8ffef3e1915d7a9caa0 in 10.2, but I cannot see that commit in list of commits on the right in jira ?!

          anel Anel Husakovic added a comment - Closed with ff8ffef3e1915d7a9caa0 in 10.2 , but I cannot see that commit in list of commits on the right in jira ?!

          People

            danblack Daniel Black
            rmhumphries Robert Humphries
            Votes:
            0 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.