[MDEV-31426] Unable to grant "SLAVE MONITOR" on a database that was upgraded from 10.3 Created: 2023-06-07  Updated: 2023-06-08  Resolved: 2023-06-08

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.8.2, 10.6.12, 10.6.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Felix Bartels Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 22.04 with Mariadb packages from the Ubuntu repository (10.6.12 at the moment of writing)


 Description   

I recently upgraded a system from Ubuntu 20.04 to 22.04 and through this upgraded the local mariadb server from 10.3.x to 10.6.x (through the packages from the ubuntu repository). In my setup I am using https://github.com/prometheus/mysqld_exporter to export metrics to prometheus and with the upgrade this is now failing with the following error message:

caller=exporter.go:174 level=error msg="Error from scraper" scraper=slave_status err="Error 1227: Access denied; you need (at least one of) the SUPER, SLAVE MONITOR privilege(s) for this operation"

And while I am able to grant the "super" permission, whenever I try to grant "slave monitor" the permissions are actually not applied.

This can easily reproduced with the below docker-compose.yml. As long as the database was initialised with 10.3 the slave monitor permissions cannot be granted, but when the database is initialised with 10.6 it is no problem to grant the permission.

Steps to reproduce:

  • take the below compose file and make sure it uses the 10.3 image
  • run "docker-compose up", wait until the database is started then stop the container
  • modify the compose file to start with 10.6 instead and then run the following sql file:

CREATE USER IF NOT EXISTS 'prometheus'@'localhost' IDENTIFIED VIA unix_socket WITH MAX_USER_CONNECTIONS 3;
 
GRANT PROCESS, REPLICATION CLIENT, SLAVE MONITOR, SELECT ON *.* TO 'prometheus'@'localhost';
 
FLUSH PRIVILEGES;

  • after importing the sql file the resulting user is missing the slave monitor permission:

# mysql -e "show grants for prometheus@localhost";
 
+---------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for prometheus@localhost                                                                                                 |
 
+---------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SELECT, PROCESS, BINLOG MONITOR ON *.* TO `prometheus`@`localhost` IDENTIFIED VIA unix_socket WITH MAX_USER_CONNECTIONS 3 |
 
+---------------------------------------------------------------------------------------------------------------------------------+

docker-compose.yml for testing:

version: '3.3'
 
services:
 
  mariadb:
 
    volumes:
 
      - db:/var/lib/mysql
 
    environment:
 
      - MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=true
 
    container_name: mariadbtest
 
    #image: 'mariadb:10.8-focal'
 
    #image: 'mariadb:10.6-focal'
 
    image: 'mariadb:10.3-focal'
 
 
 
volumes:
 
  db:



 Comments   
Comment by Felix Bartels [ 2023-06-07 ]

The issue was also documented by another user at https://stackoverflow.com/questions/75112879/cant-grant-slave-monitor-to-user-on-mariadb-10-6-primary

Comment by Daniel Black [ 2023-06-07 ]

The trick is to run the mariadb-upgrade. The 10.3 table structure for privileges is different from 10.4+. After upgrading the grants are migrated correctly. Docker Official Image containers can use MARIADB_AUTO_UPGRADE=1 to effect this.

$ cat /tmp/init.d/r.sql 
INSTALL SONAME 'auth_socket';
 
CREATE USER IF NOT EXISTS 'prometheus'@'localhost' IDENTIFIED VIA unix_socket WITH MAX_USER_CONNECTIONS 3;
 
GRANT PROCESS, REPLICATION CLIENT /*M!100509 , SLAVE MONITOR */, SELECT ON *.* TO 'prometheus'@'localhost';
 
 
 
$ podman run --env MARIADB_ROOT_PASSWORD=bob -v m:/var/lib/mysql -v /tmp/init.d:/docker-entrypoint-initdb.d/:z mariadb:10.3
 
2023-06-07 23:29:40+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.39+maria~ubu2004 started.
 
2023-06-07 23:29:40+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
 
2023-06-07 23:29:40+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.3.39+maria~ubu2004 started.
 
2023-06-07 23:29:40+00:00 [Note] [Entrypoint]: Initializing database files
 
...
 
Version: '10.3.39-MariaDB-1:10.3.39+maria~ubu2004'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
 
2023-06-07 23:31:26 0 [Note] mysqld (initiated by: root[root] @ localhost []): Normal shutdown
 
2023-06-07 23:31:26 0 [Note] Event Scheduler: Purging the queue. 0 events
 
2023-06-07 23:31:26 0 [Note] InnoDB: FTS optimize thread exiting.
 
2023-06-07 23:31:26 0 [Note] InnoDB: Starting shutdown...
 
2023-06-07 23:31:26 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
 
2023-06-07 23:31:26 0 [Note] InnoDB: Buffer pool(s) dump completed at 230607 23:31:26
 
2023-06-07 23:31:28 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
 
2023-06-07 23:31:28 0 [Note] InnoDB: Shutdown completed; log sequence number 1625455; transaction id 21
 
2023-06-07 23:31:28 0 [Note] mysqld: Shutdown complete
 
 
 
 
 
$ podman run --env MARIADB_AUTO_UPGRADE=1 -v m:/var/lib/mysql  mariadb:10.6
 
...
 
 
 
$ podman exec -ti upbeat_lichterman mariadb -pbob
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 3
 
Server version: 10.6.13-MariaDB-1:10.6.13+maria~ubu2004 mariadb.org binary 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 for 'prometheus'@'localhost' ;
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for prometheus@localhost                                                                                                                |
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SELECT, PROCESS, BINLOG MONITOR, SLAVE MONITOR ON *.* TO `prometheus`@`localhost` IDENTIFIED VIA unix_socket WITH MAX_USER_CONNECTIONS 3 |
 
+------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> Bye

note: its also a common misconception that FLUSH PRIVILEGES is required after grants/create user. It isn't.

Comment by Felix Bartels [ 2023-06-08 ]

Hi Daniel,

thanks for your detailed reply. This was indeed the missing piece in my system. Ticket can be closed.

Generated at Thu Feb 08 10:23:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.