failed to execute: SHOW SLAVE HOSTS

[Steve8291]

With newer versions of mariadb I think that instead of the SHOW SLAVE HOSTS statement mysqltuner should be using the SHOW REPLICA STATUS; statement.
That way you only need to give mysqltuner permission for GRANT SELECT, PROCESS, EXECUTE, REPLICA MONITOR, SHOW DATABASES, SHOW VIEW ON *.* TO 'mysqltuner'@'localhost'
The REPLICA MONITOR permission would be better than giving the REPLICATION MASTER ADMIN permission that is required to run SHOW SLAVE HOSTS

[surief89]

[!!] failed to execute: SHOW REPLICA STATUS\G
[!!] FAIL Execute SQL / return code: 256
I still often get the error, even though my server has a slave

[Steve8291]

Still the same error for me as well.
Server version: 10.5.11-MariaDB

[Steve8291]

Running the following bash script I get a return code of zero. Not sure why mysqltuner.pl still reports error 256.

#!/bin/bash
mysql -u mysqltuner --password='hEuEi3(TM9dZpz$lEaFPG?le2Z@qP2R0' -e "SHOW REPLICA STATUS\\G"
echo "$?"

mysql -u mysqltuner --password='hEuEi3(TM9dZpz$lEaFPG?le2Z@qP2R0' -e "SHOW SLAVE STATUS\\G"
echo "$?"

mysql -u mysqltuner --password='hEuEi3(TM9dZpz$lEaFPG?le2Z@qP2R0' -e "SHOW SLAVE HOSTS\\G"
echo "$?"

Prints

0
0
ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the REPLICATION MASTER ADMIN privilege(s) for this operation
1

This is testing for the wrong version with mysql_version_ge. I just reversed the test.
Starting at line 1180 of mysqltuner.pl I think the code should read:

    my @mysqlslave;
    if ( mysql_version_ge(8) or mysql_version_ge( 10, 5 ) ) {
        @mysqlslave = select_array("SHOW REPLICA STATUS\\G");
    }
    else {
        @mysqlslave = select_array("SHOW SLAVE STATUS\\G");
    }
    arr2hash( \%myrepl, \@mysqlslave, ':' );
    $result{'Replication'}{'Status'} = \%myrepl;

    my @mysqlslaves;
    if ( mysql_version_eq(8) or mysql_version_ge( 10, 5 ) ) {
        @mysqlslaves = select_array("SHOW SLAVE STATUS\\G");
    }
    else {
        @mysqlslaves = select_array "SHOW SLAVE HOSTS";
    }

I've tested the above code and it works on my version of mariaDB. If you would like me to submit a pull request I can do that. Since I don't actually have any slaves attached, probably someone else should test first.

[neilticktin]

Just wondering if we'll see this in the version hosted on github used by everyone... any update on that? (He asks as he sees the error just now ... although I'm running MySQL version 5.7.33-0ubuntu0.16.04.1.

[markruys]

For MySQL 8.0 the patch doesn't work. The problem is that unlike MariaDB 10.5 REPLICA STATUS also changes the output:

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.25-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 10.243.0.47
                  Source_User: replication
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.002049
          Read_Source_Log_Pos: 96712410
               Relay_Log_File: mysqld-relay-bin.000365
                Relay_Log_Pos: 67138716
        Relay_Source_Log_File: mysql-bin.002049
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
...

This breaks code like:

my ($io_running) = $myrepl{'Slave_IO_Running'};

Other issue is this:

@mysqlslaves = select_array "SHOW SLAVE STATUS";

For MySQL 8.0 this should be:

@mysqlslaves = select_array "SHOW REPLICAS";

Also the output slightly changes:

mysql> SHOW REPLICAS;
+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID                         |
+-----------+------+------+-----------+--------------------------------------+
|       163 |      | 3306 |       164 | 1653d138-fc33-11eb-9c69-023c4b151ba4 |
+-----------+------+------+-----------+--------------------------------------+