Missing grants for mysql.pxc.sst.role after in-place upgrade to PXC 8.4.7

[Ricky]

Hi everyone,

I recently performed an in-place upgrade of Percona XtraDB Cluster from 8.0.37 to 8.4.7. After the upgrade, I noticed that the mysql.pxc.sst.role is missing several grants compared to a fresh installation of version 8.4.7.

Here is what I see on the upgraded instance vs. what should be there:

Grants in 8.0.37 (before/after upgrade)

±-----------------------------------------------------------------------------------------------------------------+
| Grants for mysql.pxc.sst.role@localhost |
±-----------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON . TO mysql.pxc.sst.role@localhost |
| GRANT BACKUP_ADMIN ON . TO mysql.pxc.sst.role@localhost |
| GRANT CREATE ON PERCONA_SCHEMA.* TO mysql.pxc.sst.role@localhost |
| GRANT SELECT ON performance_schema.* TO mysql.pxc.sst.role@localhost |
| GRANT SELECT, INSERT, CREATE, ALTER ON PERCONA_SCHEMA.xtrabackup_history TO mysql.pxc.sst.role@localhost |
±-----------------------------------------------------------------------------------------------------------------+

Expected Grants in 8.4.7 (from a fresh install):

±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql.pxc.sst.role@localhost |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SHUTDOWN, PROCESS, SUPER, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE USER, CREATE ROLE, DROP ROLE ON . TO mysql.pxc.sst.role@localhost WITH GRANT OPTION |
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,INNODB_REDO_LOG_ARCHIVE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON . TO mysql.pxc.sst.role@localhost |
| GRANT BACKUP_ADMIN ON . TO mysql.pxc.sst.role@localhost WITH GRANT OPTION |
| GRANT CREATE ON PERCONA_SCHEMA.* TO mysql.pxc.sst.role@localhost |
| GRANT SELECT, INSERT, UPDATE ON performance_schema.* TO mysql.pxc.sst.role@localhost WITH GRANT OPTION |
| GRANT SELECT, INSERT, CREATE, ALTER ON PERCONA_SCHEMA.xtrabackup_history TO mysql.pxc.sst.role@localhost |
| GRANT INSERT, DELETE ON mysql.plugin TO mysql.pxc.sst.role@localhost |
±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Because of these missing grants, I wasn’t able to use CLONE as the SST method.

I also tested a minor release upgrade from 8.0.35 to 8.0.44 and noticed the same behavior: the upgrade process doesn’t seem to update the mysql.pxc.sst.role grants automatically.

Is this a known issue or expected behavior? Should these be updated manually?

Regards,
Enrico

[jrivera]

Can you share a sanitized version of your mysqld log file from the upgraded node?

[Ricky]

Here you are: server_log_upgrade_80035_80407 - Pastebin.com

Thanks

[jrivera]

I tested it and did not get the same results after upgrading in-place from 8.0.37-29.1 to 8.4.7-7.1.

BEFORE

mysql> show grants for 'mysql.pxc.sst.role'@localhost;
+------------------------------------------------------------------------------------------------------------------+
| Grants for mysql.pxc.sst.role@localhost                                                                          |
+------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO `mysql.pxc.sst.role`@`localhost`         |
| GRANT BACKUP_ADMIN ON *.* TO `mysql.pxc.sst.role`@`localhost`                                                    |
| GRANT CREATE ON `PERCONA_SCHEMA`.* TO `mysql.pxc.sst.role`@`localhost`                                           |
| GRANT SELECT ON `performance_schema`.* TO `mysql.pxc.sst.role`@`localhost`                                       |
| GRANT SELECT, INSERT, CREATE, ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `mysql.pxc.sst.role`@`localhost` |
+------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

AFTER:

mysql> show grants for 'mysql.pxc.sst.role'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql.pxc.sst.role@localhost                                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO `mysql.pxc.sst.role`@`localhost`                                                                   |
| GRANT BACKUP_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,INNODB_REDO_LOG_ARCHIVE,OPTIMIZE_LOCAL_TABLE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.pxc.sst.role`@`localhost` |
| GRANT CREATE ON `PERCONA_SCHEMA`.* TO `mysql.pxc.sst.role`@`localhost`                                                                                                     |
| GRANT SELECT ON `performance_schema`.* TO `mysql.pxc.sst.role`@`localhost`                                                                                                 |
| GRANT SELECT, INSERT, CREATE, ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `mysql.pxc.sst.role`@`localhost`                                                           |
| GRANT INSERT, DELETE ON `mysql`.`plugin` TO `mysql.pxc.sst.role`@`localhost`                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

Can you share exact steps in detail on how you upgraded? What operating system are you using?

[Ricky]

Hi, thanks for the reply.

Actually, your output confirms what I noticed: you shouldn’t compare the grants before and after the upgrade of the same instance, but rather compare the state after the upgrade (8.0.37 → 8.4.7) against a clean 8.4.7 installation.

Here is your output compared to a fresh 8.4.7 install:

From your upgrading in-place
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Grants for mysql.pxc.sst.role@localhost                                                                                                                                    |
 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO `mysql.pxc.sst.role`@`localhost`                                                                   |
 | GRANT BACKUP_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,INNODB_REDO_LOG_ARCHIVE,OPTIMIZE_LOCAL_TABLE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.pxc.sst.role`@`localhost` |
 | GRANT CREATE ON `PERCONA_SCHEMA`.* TO `mysql.pxc.sst.role`@`localhost`                                                                                                     |
 | GRANT SELECT ON `performance_schema`.* TO `mysql.pxc.sst.role`@`localhost`                                                                                                 |
 | GRANT SELECT, INSERT, CREATE, ALTER ON `PERCONA_SCHEMA`.`xtrabackup_history` TO `mysql.pxc.sst.role`@`localhost`                                                           |
 | GRANT INSERT, DELETE ON `mysql`.`plugin` TO `mysql.pxc.sst.role`@`localhost`                                                                                               |
 +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set

8.4.7 (from a fresh install):
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql.pxc.sst.role@localhost 																																			|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SHUTDOWN, PROCESS, SUPER, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE USER, CREATE ROLE, DROP ROLE ON . TO mysql.pxc.sst.role@localhost WITH GRANT OPTION 		|
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,INNODB_REDO_LOG_ARCHIVE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON . TO mysql.pxc.sst.role@localhost 												|
| GRANT BACKUP_ADMIN ON . TO mysql.pxc.sst.role@localhost WITH GRANT OPTION																											|
| GRANT CREATE ON PERCONA_SCHEMA.* TO mysql.pxc.sst.role@localhost 																													|
| GRANT SELECT, INSERT, UPDATE ON performance_schema.* TO mysql.pxc.sst.role@localhost WITH GRANT OPTION																			|
| GRANT SELECT, INSERT, CREATE, ALTER ON PERCONA_SCHEMA.xtrabackup_history TO mysql.pxc.sst.role@localhost 																			|
| GRANT INSERT, DELETE ON mysql.plugin TO mysql.pxc.sst.role@localhost																												|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set

[jrivera]

Indeed, there is a difference. I’ll continue comparing tomorrow. This is likely a bug that’s introduced during the in-place upgrade step. I’ll update this thread soon!

[jrivera]

Ricky, I can confirm there’s a difference in grants for the fresh install vs the upgraded instance. Please submit a bug report here Jira.

[Ricky]

I tried to create a new topic (while logged in, after completing registration on Atlassian) but I get this error:

In my opinion, since this is a fairly serious bug, could you kindly open the bug report yourself?

Thank you

[jrivera]

Thanks for the response. I can surely open a bug report for the difference in the GRANTs, but I don’t think that will fix your underlying issue with the CLONE method. Please hear me out and let me know if my understanding is wrong.

I also tested a minor release upgrade from 8.0.35 to 8.0.44 and noticed the same behavior

There is a known limitation regarding cloning between versions in 8.0.x, as the documentation states.

The clone plugin clones instances within the same series only. It clones from version 8.0.37 to 8.0.42, but not from 8.0 to 8.4. Before the 8.0.37 version, it requires matching point release numbers.

Regarding this:

Because of these missing grants, I wasn’t able to use CLONE as the SST method.

Were you performing a CLONE SST during the upgrade from 8.0.37 to 8.4.7? For in-place upgrades, the only supported method is to use IST and not SST. This means that for a 3-node PXC cluster running 8.0.37, after an in-place upgrade of one node, only IST should occur, not SST.

Add to that the limitation of the CLONE plugin itself, as stated in the documentation above.

Now, if the issue is that the CLONE SST fails to function after an in-place upgrade between 8.4.7 nodes, then that’s a different issue altogether. Can you confirm if that is the case?

[Ricky]

Thanks for reply,
I mentioned the clone because its failure led me to investigate and realize that the grants were different post-upgrade; however, this bug is independent of whether the clone plugin or xtrabackup is used.
As reported in previous comments, I noticed the issue (both on 8.0.35 → 8.0.44 and 8.0.37 → 8.4.7) simply by upgrading a single cluster node or a single instance.

p.s. After manually fixing the grants, IST was no longer available, so SST with the clone plugin worked fine (on 8.4.7). Comparing it on the same cluster, it took about 1/3 of the time compared to xtrabackup.

Regards

[jrivera]

Created this bug report: Jira