Open Steve8291 opened 3 years ago
[!!] 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
Still the same error for me as well. Server version: 10.5.11-MariaDB
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.
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.
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 |
+-----------+------+------+-----------+--------------------------------------+
With newer versions of mariadb I think that instead of the
SHOW SLAVE HOSTS
statement mysqltuner should be using theSHOW REPLICA STATUS;
statement. That way you only need to give mysqltuner permission forGRANT SELECT, PROCESS, EXECUTE, REPLICA MONITOR, SHOW DATABASES, SHOW VIEW ON *.* TO 'mysqltuner'@'localhost'
TheREPLICA MONITOR
permission would be better than giving theREPLICATION MASTER ADMIN
permission that is required to runSHOW SLAVE HOSTS