MySQL Bugs: #101417: "Turning ON the system variable '@@partial

Bug #101417	"Turning ON the system variable '@@partial_revokes'." but displays wrong
Submitted:	2 Nov 2020 11:04	Modified:	3 Nov 2020 16:17
Reporter:	Tsubasa Tanaka (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	CentOS (7.8.2003)
Assigned to:		CPU Architecture:	x86

Description:
1. Create "partial-revoked" user.
2. RESTART mysqld with --partial-revokes=OFF
3. Error-log says "Turning ON the system variable '@@partial_revokes'."
4. But SELECT @@global.partial_revokes is still 0.
   (Actual behavior is correctly partial_revokes = ON, This is only display issue)

How to repeat:

mysql80 8> SELECT @@global.partial_revokes;
+--------------------------+
| @@global.partial_revokes |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.01 sec)

mysql80 8> SET GLOBAL partial_revokes= 1;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> CREATE USER u1;
Query OK, 0 rows affected (0.01 sec)

mysql80 8> GRANT SELECT ON *.* TO u1;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> REVOKE SELECT ON mysql.* FROM u1;
Query OK, 0 rows affected (0.01 sec)

mysql80 8> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT ON *.* TO `u1`@`%`          |
| REVOKE SELECT ON `mysql`.* FROM `u1`@`%` |
+------------------------------------------+
2 rows in set (0.00 sec)

mysql80 8> RESTART;
Query OK, 0 rows affected (0.00 sec)

mysql80 8> SELECT * FROM performance_schema.error_log WHERE error_code = 'MY-013368';
+----------------------------+-----------+---------+------------+-----------+-------------------------------------------------------------------------------------------------------+
| LOGGED                     | THREAD_ID | PRIO    | ERROR_CODE | SUBSYSTEM | DATA                                                                                                  |
+----------------------------+-----------+---------+------------+-----------+-------------------------------------------------------------------------------------------------------+
| 2020-11-02 19:56:01.332574 |         0 | Warning | MY-013368  | Server    | At least one partial revoke exists on a database. Turning ON the system variable '@@partial_revokes'. |
+----------------------------+-----------+---------+------------+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 8> SELECT @@global.partial_revokes;
+--------------------------+
| @@global.partial_revokes |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.01 sec)

Suggested fix:
I'll attach patch for 8.0.22

Patch for MySQL 8.0.22 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug101417.patch (application/octet-stream, text), 289 bytes.

mtr patch for bug101417 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug101417_mtr.patch (application/octet-stream, text), 1.14 KiB.

Hello tanaka-San,

Thank you for the report and contribution.
Verified as described.

regards,
Umesh

# patched-mtr result

## Before fix

$ ./mtr partial_revokes_add_remove
Logging: ./mtr  partial_revokes_add_remove
MySQL Version 8.0.22
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/centos/mysql-8.0.22/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] auth_sec.partial_revokes_add_remove       [ fail ]
        Test ended at 2020-11-02 11:13:50

CURRENT_TEST: auth_sec.partial_revokes_add_remove
--- /home/centos/mysql-8.0.22/mysql-test/suite/auth_sec/r/partial_revokes_add_remove.result     2020-11-02 14:05:29.861417440 +0300
+++ /home/centos/mysql-8.0.22/mysql-test/var/log/partial_revokes_add_remove.reject      2020-11-02 14:13:50.173844418 +0300
@@ -1346,7 +1346,7 @@
 include/assert_grep.inc [Found the expected warning line in the server log.]
 SELECT @@global.partial_revokes;
 @@global.partial_revokes
-1
+0
 DROP USER foo;
 # 5. @@partial_revokes can be turned OFF after there exists no
 #    partial revokes in the user_attributes table

mysqltest: Result content mismatch

## After fix

$ ./mtr partial_revokes_add_remove
Logging: ./mtr  partial_revokes_add_remove
MySQL Version 8.0.22
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/centos/mysql-8.0.22/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] auth_sec.partial_revokes_add_remove       [ pass ]  24308
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 24.308 of 33 seconds executing testcases

Completed: All 2 tests were successful.

Duplicate of

Bug#100677 show variables like 'partial_revokes' reports incorrect output after the restart