MySQL 5.7 doesn't have roles, only MySQL 8.0 does (according to MySQL manual).
And MariaDB 10.2 won't start on MySQL 8.0 datadir.
So, I recommend you to check which version you had and which version you ended up with.

In any case, MariaDB doesn't support MySQL 8.0 privilege tables, so the migration won't work.

Good Afternoon Elena,

Sorry, I might have not been clear in my description. The install route I went down was:

• Install of MySQL 5.7
• Install of MariaDB 10.2 (onto MySQL 5.7 datadir)
• CREATE ROLE statement

It is at the third bullet point that roles don't work (in MariaDB 10.2), I wasn't attempting to use roles on MySQL 5.7, but something about the datadir that was created by MySQL 5.7 means that roles don't work in MariaDB.

Thanks,
Robert

Okay, I see.
In your description of steps (both initial and in the comment above) I don't see a moment when you ran mysql_upgrade after installing MariaDB. Did you?

Hi Elena,

On Ubuntu, the deb packages provided by apt run mysql_upgrade as put of the install process. Despite that, I thought that might be the issue myself, but when I tried to run mysql_upgrade it gave the message about not needing to, and to run with the force flag to run anyway (I did also try running with the force flag, but it had no effect on the issue).

Thanks,
Robert

Thanks. I can reproduce it.

For my earlier mysql_upgrade question – yes, mysql_upgrade is supposed to run on Ubuntu as a part of the upgrade process. Unfortunately, it is done in the background and has all kinds of problems – depending on luck, it may not run at all, or run partially and get aborted, or run concurrently with something else and end up with wrong structures. See MDEV-14622 for more details. That's why it is always better to run it after a major upgrade, with --force if necessary.
But this problem turns out to be unrelated to it.

Comparing to MariaDB 10.2, mysql.user table in MySQL 5.7 has extra columns before is_role:

+  `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',

Upon "upgrade" MariaDB doesn't normally drop columns or tables, as it doesn't have the concept of downgrade or cross-grade. So, the table has extra columns which apparently prevent roles from being stored properly.
You can work around this particular issue by dropping the extra columns; but I'm sure there will be more, as there are other discrepancies.

Assigning to serg to decide what, if anything, needs to be done about it. I don't know if trying to maintain compatibility is worth the effort at this stage, since privilege systems keep diverging. But it could be reasonable to refuse to check the structure of essential tables and refuse to start up in case they've diverged, or at least to write errors in the log and further refuse to create objects which can't be properly stored.

elenst, I don't quite understand. MySQL 5.7 does not have is_role column as far as I can see.

Do you mean that when running mysql_upgrade from 10.2 on 5.7 datadir, this is_role column is added in the wrong (or, say, unexpected) position?

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.

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

So fix version 10.2 rather than 10.1?

Confirmed as 10.2 from serg

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