Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.32
-
None
-
Ubuntu 18.04 LTS running on an t3a.medium AWS EC2 instance
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
Fixed the second "more wrong" by doing correct order in mysql_upgrade - bb-10.2-MDEV-23201-mysql_upgrade-order-user-columns
Closed with ff8ffef3e1915d7a9caa0 in 10.2, but I cannot see that commit in list of commits on the right in jira ?!
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.