[#MDEV-24122] Minor updates to MariaDB 10.2/10.3 seems to be adding a DEFAULT ROLE to the show grants command that is invalid.

[MDEV-24122] Minor updates to MariaDB 10.2/10.3 seems to be adding a DEFAULT ROLE to the show grants command that is invalid. Created: 2020-11-04 Updated: 2021-03-30 Resolved: 2021-01-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Upgrades
Affects Version/s:	10.2.35, 10.3.26
Fix Version/s:	10.2.37, 10.3.28, 10.4.18, 10.5.9

Type:

Bug

Priority:

Critical

Reporter:

cPanel Senior Tech's

Assignee:

Daniel Black

Resolution:

Fixed

Votes:

Labels:

None

Attachments:

Screen Shot 2020-11-04 at 1.37.00 AM.png

affected_datadir.tar.xz.partaa

affected_datadir.tar.xz.partab

Issue Links:

Relates
relates to	~~MDEV-24586~~	remove scripts/mysql_to_mariadb.sql	Closed

Description

Possibly related to the recent change in: ~~MDEV-22313~~

The default role is set to 0 (which is incorrect).

# mysql mysql -se "show grants for 'user1'@'localhost';"

Grants for user1@localhost

GRANT USAGE ON *.* TO `user1`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED'

SET DEFAULT ROLE 0 FOR 'user1'@'localhost'

This causes database users to have invalid grants.

Reading access rights for the cPanel user "user1" from live data:

 MariaDB/MySQL ...Invalid grant string: SET DEFAULT ROLE 0 FOR 'user1'@'localhost'

This breaks the cPanel "Databases" interface. (Screenshot attached) This does not occur for all servers that update to these versions and the cause of some of them breaking has yet to be determined.

Downgrading solves the issue. Additionally, another workaround that we have used is to set the default role to NONE.

MariaDB [(none)]> SET DEFAULT ROLE none FOR 'user1'@'localhost';

 Query OK, 0 rows affected (0.000 sec)

Comments

Comment by Anel Husakovic [ 2020-11-04 ]

So you have in 10.2.34 user1@localhost without any grants and default roles and after you upgraded with mysql_upgrade on 10.2.35 you obtained this type of error, right?

Comment by cPanel Senior Tech's [ 2020-11-04 ]

Hello,

Sorry, I should have provided the before command as well. Here it is:

# mysql mysql -se "show grants for 'user1'@'localhost';"

Grants for user1@localhost

GRANT USAGE ON *.* TO `user1`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED'

That's the default/standard. After the upgrade (mysql_upgrade) to 10.3.26, it shows the above but also the SET DEFAULT ROLE

# mysql mysql -se "show grants for 'user1'@'localhost';"

Grants for user1@localhost

GRANT USAGE ON *.* TO `user1`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED'

SET DEFAULT ROLE 0 FOR 'user1'@'localhost'

I should also mention that there are no defined roles found within the information_schema db. (Before and after the mysql_upgrade)...

# mysql information_schema -e "SELECT * FROM ENABLED_ROLES"

+-----------+

| ROLE_NAME |

+-----------+

| NULL      |

+-----------+

Does that help?

Comment by Anel Husakovic [ 2020-11-04 ]

Does this happen between major versions or minor?
I tried following on 10.2:

# Go to the commit before "show grants with default" for 10.2

$ git checkout 4e987b1c6ba7a && git checkout HEAD^ && make -j4

$ ./scripts/mysql_install_db --srcdir=.

# Server started: Version: '10.2.35-MariaDB-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

$ ./client/mysql -se "show grants for user@localhost;" -uroot

Grants for user@localhost

GRANT USAGE ON *.* TO 'user'@'localhost'

#Now update to 10.2.35 which is including the patch show grants with default roles

$ git checkout mariadb-10.2.35 && make -j4

$ mysql_upgrade

# with and without stopping the server got the same as below (no SET DEFAULT ROLE since they don't exist)

$ ./client/mysql -se "show grants for user@localhost;" -uroot

Grants for user@localhost

GRANT USAGE ON *.* TO 'user'@'localhost'

$ ./client/mysql -se "select * from information_schema.enabled_roles;"

ROLE_NAME

NULL

#However for the new user created:

$MariaDB [(none)]> show grants for new_user@localhost;

+----------------------------------------------+

| Grants for new_user@localhost                |

+----------------------------------------------+

| GRANT test_role TO 'new_user'@'localhost'    |

| GRANT USAGE ON *.* TO 'new_user'@'localhost' |

+----------------------------------------------+

2 rows in set (0.00 sec)

MariaDB [(none)]> SET DEFAULT ROLE test_role FOR new_user@localhost;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for new_user@localhost;

+-------------------------------------------------------+

| Grants for new_user@localhost                         |

+-------------------------------------------------------+

| GRANT test_role TO 'new_user'@'localhost'             |

| GRANT USAGE ON *.* TO 'new_user'@'localhost'          |

| SET DEFAULT ROLE test_role FOR 'new_user'@'localhost' |

+-------------------------------------------------------+

3 rows in set (0.00 sec)

Can you help me how to repeat please for tomorrow? Thanks.

Comment by cPanel Senior Tech's [ 2020-11-04 ]

Anel, this is happening on minor version updates with both 10.2 and 10.3 (we don't support 10.4 or 10.5 yet but it's likely happening there too). Users have stepped from 10.2.34 -> 10.2.35 with a yum update and started observing this behavior.

We have found that new installations are not affected and we've found a small subset of systems that weren't impacted (i.e. they had no default role set in SHOW GRANTS even after update). We've not been able to find differences between the setups, but the majority of customers are observing this behavior. We've received over a hundred inquiries on this in the last 12 hours.

Comment by cPanel Senior Tech's [ 2020-11-04 ]

It's further important to note that even if we set a broken user to NONE with:

mysql -e 'SET DEFAULT ROLE none FOR "user1"@"localhost";'

as soon as you restart MariaDB it goes back to:

# mysql mysql -se "show grants for 'user1'@'localhost';"

Grants for user1@localhost

GRANT USAGE ON *.* TO `user1`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED'

SET DEFAULT ROLE 0 FOR 'user1'@'localhost'

Comment by Hans Borresen [ 2020-11-04 ]

It happens between minor updates.
On a server which has used MariaDB for more than a year (it started with MySQL 5.7.28 -> MariaDB 10.2.27 -> MariaDB 10.3.18 -> 10.3.25)

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# rpm -q MariaDB-server

MariaDB-server-10.3.25-1.el7.centos.x86_64

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -e "SHOW GRANTS FOR 'hanstest'@'localhost';"

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `hanstest`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED' |

+-----------------------------------------------------------------------------------------------------------------+

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -u hanstest -p -e 'SELECT CURRENT_ROLE;'

Enter password:

+--------------+

| CURRENT_ROLE |

+--------------+

| NULL         |

+--------------+

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# yum -y update MariaDB-server

...

Updated:

  MariaDB-server.x86_64 0:10.3.26-1.el7.centos

Complete!

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -e "SHOW GRANTS FOR 'hanstest'@'localhost';"

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `hanstest`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED' |

| SET DEFAULT ROLE 0 FOR 'hanstest'@'localhost'                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

Setting the role to "none" temporarily fixes it:

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -e 'SET DEFAULT ROLE NONE FOR "hanstest"@"localhost";'

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -e "SHOW GRANTS FOR 'hanstest'@'localhost';"

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `hanstest`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED' |

+-----------------------------------------------------------------------------------------------------------------+

However, it comes back after a restart of MariaDB:

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# systemctl restart mariadb.service

[root@1ccdacd2-08e8-4224-ae1b-bb168ef92fa2 ~]# mysql -e "SHOW GRANTS FOR 'hanstest'@'localhost';"

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `hanstest`@`localhost` IDENTIFIED BY PASSWORD '*REDACTED' |

| SET DEFAULT ROLE 0 FOR 'hanstest'@'localhost'                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

Comment by Anel Husakovic [ 2020-11-04 ]

Just as a workaround can you please try this patch 17b182810b397248, although culprit should be somewhere else set_var_default_role::update/real_role variable.
However some test data would be great.

Comment by Daniel Black [ 2020-11-04 ]

I suspect this was a problem in the mysql upgrade from mysql-5.7 that was only just fixed in the last release by ~~MDEV-23201~~. The `0` for role make it look like this.

Can you include the `show create table mysql.user`?

MariaDB (badly by most accounts), assumes the columns in this table are a specific order. There shouldn't be extra columns before the last column `is_role`.

The table definition should be:
for 10.2 https://github.com/MariaDB/server/blob/10.2/scripts/mysql_system_tables.sql#L36
for 10.3 https://github.com/MariaDB/server/blob/10.3/scripts/mysql_system_tables.sql#L38

If its not this order, use a`ALTER TABLE USER` to get the right order, and then`FLUSH PRIVILEGES`

Comment by Hans Borresen [ 2020-11-05 ]

Anel, I have attached a copy of an affected datadir to the case. This was from a server which had upgraded to 10.3.26

The root pass in the datadir is set to testpassword123!@#

You can set it up as follows (make sure mariadb is not running when doing this):

[root@cent7 mariadb]# mv -vi /var/lib/mysql{,.bak}

‘/var/lib/mysql’ -> ‘/var/lib/mysql.bak’

[root@cent7 mariadb]# mkdir -pv /var/lib/mysql

mkdir: created directory ‘/var/lib/mysql’

[root@cent7 mariadb]# chown -v mysql. /var/lib/mysql

changed ownership of ‘/var/lib/mysql’ from root:root to mysql:mysql

[root@cent7 mariadb]# tar --strip-components=1 -xf /root/affected_datadir.tar.xz -C /var/lib/mysql

[root@cent7 mariadb]# sudo -u mysql ./sql/mysqld &

On commit 64fe9d6d9a (which is prior to 4e987b1c6ba7a)

[root@cent7 mariadb]# ./client/mysql -S /var/lib/mysql/mysql.sock -u root -p'testpassword123!@#' -e 'SHOW GRANTS FOR "hanstest"@"localhost";'

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' |

+-----------------------------------------------------------------------------------------------------------------+

On commit 4e987b1c6ba7a:

[root@cent7 mariadb]# ./client/mysql -S /var/lib/mysql/mysql.sock -u root -p'testpassword123!@#' -e 'SHOW GRANTS FOR "hanstest"@"localhost";'

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' |

| SET DEFAULT ROLE 0 FOR 'hanstest'@'localhost'                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

On commit 17b1828 (your patch):

[root@cent7 mariadb]# ./client/mysql -S /var/lib/mysql/mysql.sock -u root -p'testpassword123!@#' -e 'SHOW GRANTS FOR "hanstest"@"localhost";'

+-----------------------------------------------------------------------------------------------------------------+

| Grants for hanstest@localhost                                                                                   |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' |

| SET DEFAULT ROLE NONE FOR 'hanstest'@'localhost'                                                                |

+-----------------------------------------------------------------------------------------------------------------+

Now the "SET ROLE" statement is no longer invalid.

However, I would expect that for users without a custom role, nothing should be shown – right? That seems to be the case if you install 10.2.35 or 10.3.26 directly and then create users.

It is also the case when creating a new user on your patched version:

[root@cent7 mariadb]# ./client/mysql -S /var/lib/mysql/mysql.sock -u root -p'testpassword123!@#' -e 'CREATE USER "patched"@"localhost" IDENTIFIED BY "asdafasda#@!";'

[root@cent7 mariadb]# ./client/mysql -S /var/lib/mysql/mysql.sock -u root -p'testpassword123!@#' -e 'SHOW GRANTS FOR "patched"@"localhost";'

+----------------------------------------------------------------------------------------------------------------+

| Grants for patched@localhost                                                                                   |

+----------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'patched'@'localhost' IDENTIFIED BY PASSWORD '*ED8A18FE5E5D76DD13EE0A4933B84CE724E647BC' |

+----------------------------------------------------------------------------------------------------------------+

Given this, I would expect to also see nothing for the "hanstest" user, since it doesn't have any custom role.

I think something in the datadir may be in a weird state for users who upgraded from older versions of MariaDB.

Daniel:

[root@cpanel ~]# mysql -se 'SHOW CREATE TABLE mysql.user;'

Table	Create Table

user	CREATE TABLE `user` (\n  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',\n  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',\n  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',\n  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `Delete_history_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',\n  `ssl_cipher` blob NOT NULL,\n  `x509_issuer` blob NOT NULL,\n  `x509_subject` blob NOT NULL,\n  `max_questions` int(11) unsigned NOT NULL DEFAULT 0,\n  `max_updates` int(11) unsigned NOT NULL DEFAULT 0,\n  `max_connections` int(11) unsigned NOT NULL DEFAULT 0,\n  `max_user_connections` int(11) NOT NULL DEFAULT 0,\n  `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '',\n  `authentication_string` text COLLATE utf8_bin NOT NULL,\n  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `password_last_changed` timestamp NULL DEFAULT NULL,\n  `password_lifetime` smallint(5) unsigned DEFAULT NULL,\n  `account_locked` enum('N','Y') COLLATE utf8_bin NOT NULL DEFAULT 'N',\n  `is_role` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',\n  `default_role` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',\n  `max_statement_time` decimal(12,6) NOT NULL DEFAULT 0.000000,\n  PRIMARY KEY (`Host`,`User`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

Comment by Daniel Black [ 2020-11-05 ]

hborresen thanks for that.

The `password_last_changed`, `password_lifetime`, and `account_locked` are all mysql5.7 columns that aren't used in mariadb. They are causing a problem because of the ordinal usage of this table internally by MariaDB. A such the `password_lifetime` is being used as the default role, hence `0`.

I recommend for existing installs:

ALTER TABLE mysql.users DROP COLUMN IF EXISTS password_lifetime, DROP COLUMN IF EXISTS password_last_changed, DROP COLUMN IF EXISTS account_locked;

FLUSH PRIVILEGES;

This can be safely done on all MariaDB versions. Even MariaDB-10.4+ that implements password lifetimes and account locks stores them in the mysql.global_priv table.

If you have existing roles or max_statement time usage this may be revered back to a default state. No code changes are required related to this correction.

Comment by Daniel Black [ 2020-11-05 ]

anel up for review? bb-10.2-danielblack-~~MDEV-24122~~-m57-incorrect-user-table-alignment

First commit uses mysql_upgrade to ensure that the mysql5.7 columns are out of the way.

Second commit does a more thorough mysql.user check to see if the column name and the base type of the column are what's expected on start up.

Comment by Anel Husakovic [ 2020-11-05 ]

Thanks hborresen for your inputs, we are currently in review.

Comment by Hans Borresen [ 2020-11-05 ]

I am a bit curious – in the new commit (f68885b17aa3de30b1a8d70c12fe2eb6c09c2750), why move the columns to the end? Since you don't expect them to exist at all, I would have expected you to drop them, as you suggested in the comment.

Comment by Daniel Black [ 2020-11-06 ]

hborresen, thanks for the test data.

test on bb-10.2-danielblack-MDEV-24122-m57-incorrect-user-table-alignment after mysql_upgrade
MariaDB [(none)]> SHOW GRANTS FOR "hanstest"@"localhost";
+-----------------------------------------------------------------------------------------------------------------+
\| Grants for hanstest@localhost \|
+-----------------------------------------------------------------------------------------------------------------+
\| GRANT USAGE ON . TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' \|
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> create role admin;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SET DEFAULT ROLE admin FOR 'hanstest'@'localhost' ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR "hanstest"@"localhost";
+-----------------------------------------------------------------------------------------------------------------+
\| Grants for hanstest@localhost \|
+-----------------------------------------------------------------------------------------------------------------+
\| GRANT admin TO 'hanstest'@'localhost' \|
\| GRANT USAGE ON . TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' \|
\| SET DEFAULT ROLE admin FOR 'hanstest'@'localhost' \|
+-----------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR "hanstest"@"localhost";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: * NONE *

+-----------------------------------------------------------------------------------------------------------------+
\| Grants for hanstest@localhost \|
+-----------------------------------------------------------------------------------------------------------------+
\| GRANT admin TO 'hanstest'@'localhost' \|
\| GRANT USAGE ON . TO 'hanstest'@'localhost' IDENTIFIED BY PASSWORD '*94BE0C2A5749568CEA956A95930C71E50D01AB6C' \|
\| SET DEFAULT ROLE admin FOR 'hanstest'@'localhost' \|
+-----------------------------------------------------------------------------------------------------------------+

On moving to the end, I tried to explain it in https://github.com/MariaDB/server/commit/8bc4268ce476c4e595896e568fc17980b806781d, that they do get used in mysql_upgrade in 10.4, where MariaDB finally does support password expiry and account locking. I could do a different commit for 10.4, but I figured they could be used in rapid succession.

I'm going to ask around for other opinions on this, however if you have one I'd like to hear it too.

If the server does keep the mysql5.7 columns lying around, I'll change https://github.com/MariaDB/server/commit/f68885b17aa3de30b1a8d70c12fe2eb6c09c2750, so it doesn't leave `2020-11-05 16:31:35 140598436198336 [ERROR] Column count of mysql.user is wrong. Expected 46, found 49. The table is probably corrupted` in the error log.

(commit deleted - there was no mysql_upgrade error - I was running a 10.2 mysql_upgrade on a 10.3 datadir)

Comment by Hans Borresen [ 2020-11-06 ]

I'm going to ask around for other opinions on this, however if you have one I'd like to hear it too.

I don't have an opinion – I was just curious. I wasn't aware the migration to 10.4 would account for those tables.

Comment by Sergei Golubchik [ 2020-11-09 ]

danblack, I think your last commit is a bit too much. We specifically don't test for mysql.user structure because the privilege code adjusts and any older structure is also correct, should not cause any warnings.

Comment by Daniel Black [ 2020-11-10 ]

Thanks for looking serg I was most of the way though https://github.com/MariaDB/server/commit/4fe72f32e4fb1b0849429bb781903e2d23a5c3a3 before I noticed this. Changes are:

More columns than expected is a Note, rather than error /warning.
Checks are disabled if running in bootstrap or when mysql_user_table_is_in_short_password_format is true.

The message still is

[Note] Column count of mysql.user is wrong. Expected 46, found 49. The table is probably corrupted

which I don't particularly like the corrupted aspect of by without introducing messages or removing the last sentence was hard to change.

Do you have a preference towards leaving the mysql-5.7 columns vs dropping them (which avoids the too many in most cases)?

Comment by Sergei Golubchik [ 2020-11-10 ]

I thought you need to preserve mysql-5.7 columns as later mariadb versions support password expiration and account locking

Comment by Daniel Black [ 2020-11-11 ]

> I thought you need to preserve mysql-5.7 columns as later mariadb versions support password expiration and account locking

Yes, there's just a user experience failure if they go from 5.7 to <= 10.3 as password expiry/ account locking go dormant (though I have engineered the last_password_change to stay current even though nothing enforces the expiry). If its then considerable months/years before the 10.4 upgrade occurs they will suddenly have accounts locked and passwords expired (because expiry was never enforced) that were fine in <=10.3 (even though they where locked in the 5.7 days). `SHOW CREATE USER` will reveal the account lock and expiry if present however.

Comment by Daniel Black [ 2020-11-16 ]

I've resolved my doubts about keeping the columns in existence:

So sufficient as minimal fix is:
bb-10.2-danielblack-~~MDEV-24122~~-m57-incorrect-user-table-alignment

Is limiting the cases where the mysql.user structure is tests to !bootstrap, !mysql_user_table_is_in_short_password_format sufficient to not be too much now?
https://github.com/MariaDB/server/commit/045a9d98b9520cede0e22d5b8615c411ed5aaa98#diff-df544694418bef1c4bc6cdc5211ca133e7ad4d31901f16d0fdee8df6e4debe89R2222

Comment by Daniel Black [ 2020-12-04 ]

cvicentiu can you review and merge if appropriate:

bb-10.2-danielblack-~~MDEV-24122~~-m57-incorrect-user-table-alignment

before reassigning back to serg to look at:

bb-10.2-danielblack-~~MDEV-24122~~-m57-incorrect-user-table-alignment_part2

(which you're welcome to review too).

Comment by Vicențiu Ciorbaru [ 2021-01-22 ]

Hi danblack!

I think the patch is ok. There are a few changes I'd make to help explain the problem more clearly.

The JIRA entries are a bit of a mess so grasping the whole history is hard. Let's make a short summary in the commit message. My proposed wording:
1. For MariaDB versions before 10.2.35, when upgrading from MySQL 5.7 the columns belonging only to MySQL password_last_changed, password_lifetime, account_locked were placed in a wrong order for MariaDB to function properly with roles.
2. After MariaDB 10.2.35 thanks to ~~MDEV-23201~~, the columns order is resolved, when upgrading from 5.7, but not if the upgrade already happened with a previous 10.2 (or 10.1) version.
3. This patch corrects versions that upgraded from 5.7, before 10.2.35, by ordering the mysql.user columns is_role, default_role, max_statement_time explicitly.
  Corollary to this fix in mysql_upgrade, we explictly move the mysql-5.7 columns after the columns used by MariaDB. These columns are:
  * password_last_changed
  * password_lifetime
  * account_locked.
  This is done to facilitate the following process:
  A user could upgrade to MariaDB-10.2, and on a previously common wisdom of MySQL not upgrading more than one major version, and then upgrade to MariaDB-10.3, and then to MariaDB-10.4 in rapid succession, at which stage these columns would actually have a meaning the in migration to the new mysql.global_priv table (where account locking and password expiry have meaning).
  Those that take a slower upgrade path may find that a previously working MariaDB-10.3 user is now locked, or their password is expired. Because of this, the `password_last_changed` has its definition changed to update to the current timestamp, as the MariaDB remains ignorant of its existence. A user changing their password will now keep this field accurate.
  As a fallback, SHOW CREATE USER will however quickly expose the underlying cause.
I would not have the long test case in the commit message. You effectively described it in the test case, except that you should show the "problematic" table structure with a SHOW CREATE TABLE. I suggest the following: A SHOW CREATE TABLE right after importing the mysql 5.7 tables and doing the alter to
"pretend" upgrading with a bugged 10.2.34 or earlier. Keep the SHOW CREATE TABLE after mysql_upgrade.
The test case should not have the "for 10.4 do this".
Instead, make sure you work with the person merging 10.2 to 10.4 to properly merge the patch. Or you can do it yourself.

OK to push from me.
As for part 2, I would just do a simple check when opening the table, looking for this problematic situation, but only for the default_role column. Do a string compare on the column name after opening the table, see if they match. If they do not match, issue the warning and suggest running mysql_upgrade. You can annotate the strcmp with a comment referencing the MDEV that describes the initial problem, caused by cross-grading from 5.7 to 10.2.34 or to an earlier 10.1 / 10.2 version.

Comment by Sergei Golubchik [ 2021-01-22 ]

danblack, please, for this bug you don't need more than a couple of lines of the commit comment. Something like

~~MDEV-23201~~ correctly reordered columns in mysql.user table when upgrading from MySQL-5.7
Here we also correctly reorder columns in mysql.user table from an invalid order caused by an upgrade from MySQL-5.7 to MariaDB-before-~~MDEV-23201~~.

Comment by Daniel Black [ 2021-01-23 ]

Thanks for the reviews

Generated at Thu Feb 08 09:27:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-24122] Minor updates to MariaDB 10.2/10.3 seems to be adding a DEFAULT ROLE to the show grants command that is invalid. Created: 2020-11-04 Updated: 2021-03-30 Resolved: 2021-01-23