[MDEV-27611] CLONE - Slave user can't run "SHOW SLAVE STATUS" anymore after upgrade to 10.5, mysql_upgrade should take of that - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5.5, 10.5.13, 10.6
Fix Version/s: N/A
Component/s: Authentication and Privilege System, Documentation, Replication
Labels:
- privileges
- slave

Description

This is a clone of the original ~~MDEV-23610~~

This BUG is still not fixed. Although marked as closed. Found no way to reopen.... so created this clone.

Easy steps to reproduce:

SELECT VERSION();
SELECT USER();
GRANT REPLICATION CLIENT ON . TO 'CMDSYSTEM'@'%';
SHOW GRANTS;
SHOW SLAVE STATUS;

10.5.13-MariaDB-log
CMDSYSTEM@CAS-WS200115.caseris.intern
GRANT BINLOG MONITOR ON . TO `CMDSYSTEM`@`%` IDENTIFIED BY PASSWORD '*something'

Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation SQL.sql 10 1

Attachments

Issue Links

duplicates

MDEV-23899 admin privilege required to monitoring replication status - 10.5.5

Closed

is caused by

MDEV-21743 Split up SUPER privilege to smaller privileges

Closed

relates to

MDEV-22030 Don't grant REPLICATION MASTER ADMIN automatically on upgrade from an older JSON user table

Closed

MDEV-22152 REPLICATION MASTER ADMIN privilege not given to former SUPER users upon upgrade

Closed

MDEV-23918 admin privlege required to view contents of relay logs in 10.5

Closed

Activity

Ascending order - Click to sort in descending order

Sujatha Sivakumar (Inactive) added a comment - 2022-02-14 14:14

Hello Thomas,

Thanks for the bug report. I verified test from through 10.4 to 10.5 upgrade. The upgrade works fine and I am able to execute 'show slave states'. Can you please check it once.

Master:
======
Create a user with 'REPLICATION CLINET' privilege and ensure it is replicated to slave.

sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ./mtr --suite=rpl --start-and-exit

Logging: /home/sujatha/bug_repo/test-10.4/mysql-test/mysql-test-run.pl --suite=rpl --start-and-exit

VS config:

vardir: /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var

Checking leftover processes...

 - found old pid 24812 in 'mysqld.1.pid', killing it...

   process did not exist!

 - found old pid 24813 in 'mysqld.2.pid', killing it...

   process did not exist!

Removing old var directory...

Creating var directory '/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var'...

Checking supported features...

MariaDB Version 10.4.23-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

Using suites: rpl

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

worker[1]

Started [mysqld.1 - pid: 24964, winpid: 24964] [mysqld.2 - pid: 24965, winpid: 24965]

worker[1] Using config for test rpl.rpl_auto_increment

worker[1] Port and socket path for server(s):

worker[1] mysqld.1  16000 /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.1.sock

worker[1] mysqld.2  16001 /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.2.sock

worker[1] Server(s) started, not waiting for them to finish

sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ../client/mysql -uroot -S/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.1.sock

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.4.23-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '';

Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO user1@localhost;

Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.005 sec)

MariaDB [test]> create table t( f int) engine=innodb;

Query OK, 0 rows affected (0.037 sec)

MariaDB [test]> insert into t values (10);

Query OK, 1 row affected (0.009 sec)

Slave:
======

sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ../client/mysql -uuser1 -S/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.2.sock

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 13

Server version: 10.4.23-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;

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

| Grants for user1@localhost                             |

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

| GRANT REPLICATION CLIENT ON *.* TO `user1`@`localhost` |

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

1 row in set (0.001 sec)

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

                Slave_IO_State: Waiting for master to send event

                   Master_Host: localhost

                   Master_User: root

                   Master_Port: 16000

                 Connect_Retry: 60

               Master_Log_File: master-bin.000001

           Read_Master_Log_Pos: 1105

                Relay_Log_File: slave-relay-bin.000002

                 Relay_Log_Pos: 1405

         Relay_Master_Log_File: master-bin.000001

              Slave_IO_Running: Yes

             Slave_SQL_Running: Yes

               Replicate_Do_DB:

           Replicate_Ignore_DB:

            Replicate_Do_Table:

        Replicate_Ignore_Table: test.t_ignored1

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

                    Last_Errno: 0

                    Last_Error:

                  Skip_Counter: 0

           Exec_Master_Log_Pos: 1105

               Relay_Log_Space: 1714

               Until_Condition: None

                Until_Log_File:

                 Until_Log_Pos: 0

            Master_SSL_Allowed: No

            Master_SSL_CA_File:

            Master_SSL_CA_Path:

               Master_SSL_Cert:

             Master_SSL_Cipher:

                Master_SSL_Key:

         Seconds_Behind_Master: 0

 Master_SSL_Verify_Server_Cert: No

                 Last_IO_Errno: 0

                 Last_IO_Error:

                Last_SQL_Errno: 0

                Last_SQL_Error:

   Replicate_Ignore_Server_Ids:

              Master_Server_Id: 1

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: No

                   Gtid_IO_Pos:

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: conservative

                     SQL_Delay: 0

           SQL_Remaining_Delay: NULL

       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

              Slave_DDL_Groups: 4

Slave_Non_Transactional_Groups: 0

    Slave_Transactional_Groups: 1

1 row in set (0.002 sec)

ERROR: No query specified

MariaDB [(none)]> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [test]> select * from t;

+------+

| f    |

+------+

|   10 |

+------+

1 row in set (0.003 sec)

10.4 master and slave are stopped.

10.5 start master and slave.

Logging: /home/sujatha/bug_repo/test-10.5/mysql-test/mysql-test-run.pl --suite=rpl --start-and-exit

VS config:

vardir: /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var

Checking leftover processes...

Removing old var directory...

Creating var directory '/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var'...

Checking supported features...

MariaDB Version 10.5.14-MariaDB-debug

 - SSL connections supported

 - binaries are debug compiled

Using suites: rpl

Collecting tests...

Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT

--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019

worker[1]

Started [mysqld.1 - pid: 25137, winpid: 25137] [mysqld.2 - pid: 25138, winpid: 25138]

worker[1] Using config for test rpl.rpl_auto_increment

worker[1] Port and socket path for server(s):

worker[1] mysqld.1  16000 /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.1.sock

worker[1] mysqld.2  16001 /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock

worker[1] Server(s) started, not waiting for them to finish

Now I will shutdown both master and slave and replace the slave 'datadir' with that of 10.4 slave 'datadir'.
And then restart slave on 10.5 and run mysql_upgrade.

datadir=/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/mysqld.2/data

Replaced it with 10.5. Restarted slave on 10.5.

sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ vim ./var/my.cnf

sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ /home/sujatha/bug_repo/test-10.5/bld/sql/mariadbd --defaults-group-suffix=.2 --defaults-file=/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/my.cnf --log-output=file --binlog-format=mixed --innodb --innodb-cmpmem --innodb-cmp-per-index --innodb-trx --innodb-locks --innodb-lock-waits --innodb-metrics --innodb-buffer-pool-stats --innodb-buffer-page --innodb-buffer-page-lru --innodb-sys-columns --innodb-sys-fields --innodb-sys-foreign --innodb-sys-foreign-cols --innodb-sys-indexes --innodb-sys-tables --innodb-sys-virtual --log-bin=slave-bin --replicate-ignore-table=test.t_ignored1 --core-file --loose-debug-sync-timeout=300

2022-02-01 14:02:38 0 [Note] /home/sujatha/bug_repo/test-10.5/bld/sql/mariadbd (mysqld 10.5.14-MariaDB-debug-log) starting as process 25252 ...

2022-02-01 14:02:38 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 32190)

2022-02-01 14:02:38 0 [Warning] Changed limits: max_open_files: 1024  max_connections: 151 (was 151)  table_cache: 421 (was 2000)

Run mysql_upgrade on slave.

sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ ../client/mysql_upgrade -S/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock

Phase 1/7: Checking and upgrading mysql database

Processing databases

mysql

mysql.column_stats                                 OK

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.global_priv                                  OK

mysql.gtid_slave_pos                               OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.index_stats                                  OK

mysql.innodb_index_stats                           OK

mysql.innodb_table_stats                           OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.proxies_priv                                 OK

mysql.roles_mapping                                OK

mysql.servers                                      OK

mysql.table_stats                                  OK

mysql.tables_priv                                  OK

mysql.time_zone                                    OK

mysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OK

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysql.transaction_registry                         OK

Phase 2/7: Installing used storage engines... Skipped

Phase 3/7: Fixing views

mysql.user                                         OK

Phase 4/7: Running 'mysql_fix_privilege_tables'

Phase 5/7: Fixing table and database names

Phase 6/7: Checking and upgrading tables

Processing databases

information_schema

mtr

mtr.global_suppressions                            OK

mtr.test_suppressions                              OK

performance_schema

test

test.t                                             OK

Phase 7/7: Running 'FLUSH PRIVILEGES'

OK

sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ ../client/mysql -uuser1 -S/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 19

Server version: 10.5.14-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show privileges;

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

| Privilege                | Context                               | Comment |

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

| Alter                    | Tables                                | To alter the table                                                 |

| Alter routine            | Functions,Procedures                  | To alter or drop stored functions/procedures                       |

| Create                   | Databases,Tables,Indexes              | To create new databases and tables                                 |

| Create routine           | Databases                             | To use CREATE FUNCTION/PROCEDURE                                   |

| Create temporary tables  | Databases                             | To use CREATE TEMPORARY TABLE                                      |

| Create view              | Tables                                | To create new views                                                |

| Create user              | Server Admin                          | To create new users                                                |

| Delete                   | Tables                                | To delete existing rows                                            |

| Delete history           | Tables                                | To delete versioning table historical rows                         |

| Drop                     | Databases,Tables                      | To drop databases, tables, and views                               |

| Event                    | Server Admin                          | To create, alter, drop and execute events                          |

| Execute                  | Functions,Procedures                  | To execute stored routines                                         |

| File                     | File access on server                 | To read and write files on the server                              |

| Grant option             | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess                |

| Index                    | Tables                                | To create or drop indexes                                          |

| Insert                   | Tables                                | To insert data into tables                                         |

| Lock tables              | Databases                             | To use LOCK TABLES (together with SELECT privilege)                |

| Process                  | Server Admin                          | To view the plain text of currently executing queries              |

| Proxy                    | Server Admin                          | To make proxy user possible                                        |

| References               | Databases,Tables                      | To have references on tables                                       |

| Reload                   | Server Admin                          | To reload or refresh tables, logs and privileges                   |

| Binlog admin             | Server                                | To purge binary logs                                               |

| Binlog monitor           | Server                                | To use SHOW BINLOG STATUS and SHOW BINARY LOG                      |

| Binlog replay            | Server                                | To use BINLOG (generated by mariadb-binlog)                        |

| Replication master admin | Server                                | To monitor connected slaves                                        |

| Replication slave admin  | Server                                | To start/stop slave and apply binlog events                        |

| Slave monitor            | Server                                | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS                  |

| Replication slave        | Server Admin                          | To read binary log events from the master                          |

| Select                   | Tables                                | To retrieve rows from table                                        |

| Show databases           | Server Admin                          | To see all databases with SHOW DATABASES                           |

| Show view                | Tables                                | To see views with SHOW CREATE VIEW                                 |

| Shutdown                 | Server Admin                          | To shut down the server                                            |

| Super                    | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.                |

| Trigger                  | Tables                                | To use triggers                                                    |

| Create tablespace        | Server Admin                          | To create/alter/drop tablespaces                                   |

| Update                   | Tables                                | To update existing rows                                            |

| Set user                 | Server                                | To create views and stored routines with a different definer       |

| Federated admin          | Server                                | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements |

| Connection admin         | Server                                | To bypass connection limits and kill other users' connections      |

| Read_only admin          | Server                                | To perform write operations even if @@read_only=ON                 |

| Usage                    | Server Admin                          | No privileges - allow connect only                                 |

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

41 rows in set (0.001 sec)

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

                Slave_IO_State:

                   Master_Host: localhost

                   Master_User: root

                   Master_Port: 16000

                 Connect_Retry: 60

               Master_Log_File: master-bin.000001

           Read_Master_Log_Pos: 1105

                Relay_Log_File: slave-relay-bin.000002

                 Relay_Log_Pos: 1405

         Relay_Master_Log_File: master-bin.000001

              Slave_IO_Running: No

             Slave_SQL_Running: No

               Replicate_Do_DB:

           Replicate_Ignore_DB:

            Replicate_Do_Table:

        Replicate_Ignore_Table: test.t_ignored1

       Replicate_Wild_Do_Table:

   Replicate_Wild_Ignore_Table:

                    Last_Errno: 0

                    Last_Error:

                  Skip_Counter: 0

           Exec_Master_Log_Pos: 1105

               Relay_Log_Space: 1993

               Until_Condition: None

                Until_Log_File:

                 Until_Log_Pos: 0

            Master_SSL_Allowed: No

            Master_SSL_CA_File:

            Master_SSL_CA_Path:

               Master_SSL_Cert:

             Master_SSL_Cipher:

                Master_SSL_Key:

         Seconds_Behind_Master: NULL

 Master_SSL_Verify_Server_Cert: No

                 Last_IO_Errno: 0

                 Last_IO_Error:

                Last_SQL_Errno: 0

                Last_SQL_Error:

   Replicate_Ignore_Server_Ids:

              Master_Server_Id: 0

                Master_SSL_Crl:

            Master_SSL_Crlpath:

                    Using_Gtid: No

                   Gtid_IO_Pos:

       Replicate_Do_Domain_Ids:

   Replicate_Ignore_Domain_Ids:

                 Parallel_Mode: optimistic

                     SQL_Delay: 0

           SQL_Remaining_Delay: NULL

       Slave_SQL_Running_State:

              Slave_DDL_Groups: 0

Slave_Non_Transactional_Groups: 0

    Slave_Transactional_Groups: 0

1 row in set (0.001 sec)

ERROR: No query specified

So I was able to execute SHOW SLAVE STATUS.

Upon upgrade 'REPLICATION CLIENT' becomes 'SLAVE MONITOR' so I could not repeat bug.

Can you please confirm the verification process.

Thank you
S.Sujatha

Sujatha Sivakumar (Inactive) added a comment - 2022-02-14 14:14 Hello Thomas, Thanks for the bug report. I verified test from through 10.4 to 10.5 upgrade. The upgrade works fine and I am able to execute 'show slave states'. Can you please check it once. Master: ====== Create a user with 'REPLICATION CLINET' privilege and ensure it is replicated to slave. sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ./mtr --suite=rpl --start-and-exit Logging: /home/sujatha/bug_repo/test-10.4/mysql-test/mysql-test-run.pl --suite=rpl --start-and-exit VS config: vardir: /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var Checking leftover processes... - found old pid 24812 in 'mysqld.1.pid', killing it... process did not exist! - found old pid 24813 in 'mysqld.2.pid', killing it... process did not exist! Removing old var directory... Creating var directory '/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var'... Checking supported features... MariaDB Version 10.4.23-MariaDB-debug - SSL connections supported - binaries are debug compiled Using suites: rpl Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 worker[1] Started [mysqld.1 - pid: 24964, winpid: 24964] [mysqld.2 - pid: 24965, winpid: 24965] worker[1] Using config for test rpl.rpl_auto_increment worker[1] Port and socket path for server(s): worker[1] mysqld.1 16000 /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.1.sock worker[1] mysqld.2 16001 /home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.2.sock worker[1] Server(s) started, not waiting for them to finish sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ../client/mysql -uroot -S/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.1.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.4.23-MariaDB-debug-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY ''; Query OK, 0 rows affected (0.005 sec) MariaDB [(none)]> GRANT REPLICATION CLIENT ON *.* TO user1@localhost; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.005 sec) MariaDB [test]> create table t( f int) engine=innodb; Query OK, 0 rows affected (0.037 sec) MariaDB [test]> insert into t values (10); Query OK, 1 row affected (0.009 sec) Slave: ====== sujatha@sujatha:~/bug_repo/test-10.4/bld/mysql-test$ ../client/mysql -uuser1 -S/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/tmp/mysqld.2.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 10.4.23-MariaDB-debug-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show grants; +--------------------------------------------------------+ | Grants for user1@localhost | +--------------------------------------------------------+ | GRANT REPLICATION CLIENT ON *.* TO `user1`@`localhost` | +--------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: root Master_Port: 16000 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1105 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 1405 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: test.t_ignored1 Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1105 Relay_Log_Space: 1714 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 4 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1 1 row in set (0.002 sec) ERROR: No query specified MariaDB [(none)]> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test]> select * from t; +------+ | f | +------+ | 10 | +------+ 1 row in set (0.003 sec) 10.4 master and slave are stopped. 10.5 start master and slave. Logging: /home/sujatha/bug_repo/test-10.5/mysql-test/mysql-test-run.pl --suite=rpl --start-and-exit VS config: vardir: /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var'... Checking supported features... MariaDB Version 10.5.14-MariaDB-debug - SSL connections supported - binaries are debug compiled Using suites: rpl Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 worker[1] Started [mysqld.1 - pid: 25137, winpid: 25137] [mysqld.2 - pid: 25138, winpid: 25138] worker[1] Using config for test rpl.rpl_auto_increment worker[1] Port and socket path for server(s): worker[1] mysqld.1 16000 /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.1.sock worker[1] mysqld.2 16001 /home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock worker[1] Server(s) started, not waiting for them to finish Now I will shutdown both master and slave and replace the slave 'datadir' with that of 10.4 slave 'datadir'. And then restart slave on 10.5 and run mysql_upgrade. datadir=/home/sujatha/bug_repo/test-10.4/bld/mysql-test/var/mysqld.2/data Replaced it with 10.5. Restarted slave on 10.5. sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ vim ./var/my.cnf sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ /home/sujatha/bug_repo/test-10.5/bld/sql/mariadbd --defaults-group-suffix=.2 --defaults-file=/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/my.cnf --log-output=file --binlog-format=mixed --innodb --innodb-cmpmem --innodb-cmp-per-index --innodb-trx --innodb-locks --innodb-lock-waits --innodb-metrics --innodb-buffer-pool-stats --innodb-buffer-page --innodb-buffer-page-lru --innodb-sys-columns --innodb-sys-fields --innodb-sys-foreign --innodb-sys-foreign-cols --innodb-sys-indexes --innodb-sys-tables --innodb-sys-virtual --log-bin=slave-bin --replicate-ignore-table=test.t_ignored1 --core-file --loose-debug-sync-timeout=300 2022-02-01 14:02:38 0 [Note] /home/sujatha/bug_repo/test-10.5/bld/sql/mariadbd (mysqld 10.5.14-MariaDB-debug-log) starting as process 25252 ... 2022-02-01 14:02:38 0 [Warning] Could not increase number of max_open_files to more than 1024 (request: 32190) 2022-02-01 14:02:38 0 [Warning] Changed limits: max_open_files: 1024 max_connections: 151 (was 151) table_cache: 421 (was 2000) Run mysql_upgrade on slave. sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ ../client/mysql_upgrade -S/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock Phase 1/7: Checking and upgrading mysql database Processing databases mysql mysql.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.roles_mapping OK mysql.servers OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.transaction_registry OK Phase 2/7: Installing used storage engines... Skipped Phase 3/7: Fixing views mysql.user OK Phase 4/7: Running 'mysql_fix_privilege_tables' Phase 5/7: Fixing table and database names Phase 6/7: Checking and upgrading tables Processing databases information_schema mtr mtr.global_suppressions OK mtr.test_suppressions OK performance_schema test test.t OK Phase 7/7: Running 'FLUSH PRIVILEGES' OK sujatha@sujatha:~/bug_repo/test-10.5/bld/mysql-test$ ../client/mysql -uuser1 -S/home/sujatha/bug_repo/test-10.5/bld/mysql-test/var/tmp/mysqld.2.sock Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 19 Server version: 10.5.14-MariaDB-debug-log Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show privileges; +--------------------------+---------------------------------------+--------------------------------------------------------------------+ | Privilege | Context | Comment | +--------------------------+---------------------------------------+--------------------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Delete history | Tables | To delete versioning table historical rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Binlog admin | Server | To purge binary logs | | Binlog monitor | Server | To use SHOW BINLOG STATUS and SHOW BINARY LOG | | Binlog replay | Server | To use BINLOG (generated by mariadb-binlog) | | Replication master admin | Server | To monitor connected slaves | | Replication slave admin | Server | To start/stop slave and apply binlog events | | Slave monitor | Server | To use SHOW SLAVE STATUS and SHOW RELAYLOG EVENTS | | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create tablespace | Server Admin | To create/alter/drop tablespaces | | Update | Tables | To update existing rows | | Set user | Server | To create views and stored routines with a different definer | | Federated admin | Server | To execute the CREATE SERVER, ALTER SERVER, DROP SERVER statements | | Connection admin | Server | To bypass connection limits and kill other users' connections | | Read_only admin | Server | To perform write operations even if @@read_only=ON | | Usage | Server Admin | No privileges - allow connect only | +--------------------------+---------------------------------------+--------------------------------------------------------------------+ 41 rows in set (0.001 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: localhost Master_User: root Master_Port: 16000 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1105 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 1405 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: test.t_ignored1 Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1105 Relay_Log_Space: 1993 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.001 sec) ERROR: No query specified So I was able to execute SHOW SLAVE STATUS. Upon upgrade 'REPLICATION CLIENT' becomes 'SLAVE MONITOR' so I could not repeat bug. Can you please confirm the verification process. Thank you S.Sujatha

Thomas Huppertz added a comment - 2022-02-15 11:39 - edited

Hi,
thx for your test. I think you are right it works after an update from 10.4. to 10.5, but according to your website:
https://mariadb.com/kb/en/grant/#replication-client

REPLICATION CLIENT

Execute SHOW MASTER STATUS, SHOW SLAVE STATUS and SHOW BINARY LOGS informative statements.

Renamed to BINLOG MONITOR in MariaDB 10.5.2 (but still supported as an alias for compatibility reasons).

This should also work without an upgrade, Install 10.5. issue the command and it will fail.

So either documentation is wrong or the right does not work as expected.

Thomas Huppertz added a comment - 2022-02-15 11:39 - edited Hi, thx for your test. I think you are right it works after an update from 10.4. to 10.5, but according to your website: https://mariadb.com/kb/en/grant/#replication-client REPLICATION CLIENT Execute SHOW MASTER STATUS, SHOW SLAVE STATUS and SHOW BINARY LOGS informative statements. Renamed to BINLOG MONITOR in MariaDB 10.5 . 2 (but still supported as an alias for compatibility reasons). This should also work without an upgrade, Install 10.5. issue the command and it will fail. So either documentation is wrong or the right does not work as expected.

Sujatha Sivakumar (Inactive) added a comment - 2022-02-15 12:12

Hello Thomas,

Thank you. Actually the documentation needs to be corrected.

https://mariadb.com/docs/reference/mdb/privileges/BINLOG_MONITOR/

BINLOG_MONITOR is for Binlog specific operations.
SLAVE_MONITOR is for Replica/Slave specific monitoring.

I will assign the issue to documentation team.

Sujatha Sivakumar (Inactive) added a comment - 2022-02-15 12:12 Hello Thomas, Thank you. Actually the documentation needs to be corrected. https://mariadb.com/docs/reference/mdb/privileges/BINLOG_MONITOR/ BINLOG_MONITOR is for Binlog specific operations. SLAVE_MONITOR is for Replica/Slave specific monitoring. I will assign the issue to documentation team.

Sujatha Sivakumar (Inactive) added a comment - 2022-02-15 12:15

Hello Ian,

Can you please correct https://mariadb.com/kb/en/grant/#replication-client documentation as per https://mariadb.com/docs/reference/mdb/privileges/REPLICATION_CLIENT/.

Thank you.

Sujatha Sivakumar (Inactive) added a comment - 2022-02-15 12:15 Hello Ian, Can you please correct https://mariadb.com/kb/en/grant/#replication-client documentation as per https://mariadb.com/docs/reference/mdb/privileges/REPLICATION_CLIENT/ . Thank you.

Daniel Black added a comment - 2023-09-13 06:33

I checked the KB documentation of REPLICATION CLIENT and it matches the corp docs and functionality.

Daniel Black added a comment - 2023-09-13 06:33 I checked the KB documentation of REPLICATION CLIENT and it matches the corp docs and functionality.

People

Assignee:: Ian Gilfillan

Reporter:: Thomas Huppertz

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-01-25 10:29

Updated:: 2023-09-13 06:33

Resolved:: 2023-09-13 06:33

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server