pt-show-grants fails against MySQL-5.7.6

[Simon J Mudd]

MySQL 5.7.6 changes the MySQL grant tables and as such this breaks pt-show-grants.

The Password column has gone and is replaced by a new column called authentication_string which currently stores the same hash as before.

[myuser@myserver ~]$ pt-show-grants | grep root.@.localhost
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
[myuser@myserver ~]$ rpm -q percona-toolkit
percona-toolkit-2.2.13-1.noarch
[myuser@myserver ~]$

This is because now the output of SHOW GRANTS FOR 'root'@'localhost shows only:

root@myserver [(none)]> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

So no password is shown. I'm not 100% sure if that's a bug or not (and may report it to Oracle).

You can see the password info if needed this way:

root@myserver [mysql]> select User,Host,authentication_string from user where User='root';
+------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *B9xxxxxxxxxxxxxxxxxxxxxxxxxxx8B |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

So some sort of patch would be good to make pt-show-grants continue to show the information while people are testing 5.7.

root@myserver [mysql]> select @@version;
+-------------------------+
| @@version |
+-------------------------+
| 5.7.6-log |
+-------------------------+
1 row in set (0.00 sec)

[Simon J Mudd (sjmudd)]

For reference see: http://bugs.mysql.com/bug.php?id=76465

[Nilnandan Joshi (nilnandan-joshi)]

Confirmed with MySQL 5.7.6,

nilnandan.joshi@bm-support01:~$ ps -ef | grep mysql | grep njoshi
nilnand+ 17506 1 0 02:29 pts/23 00:00:00 /bin/sh /home/njoshi/5.7.3/bin/mysqld_safe --defaults-file=/home/njoshi/sandboxes/msb_5_7_3/my.sandbox.cnf
nilnand+ 17735 17506 0 02:29 pts/23 00:00:00 /home/njoshi/5.7.3/bin/mysqld --defaults-file=/home/njoshi/sandboxes/msb_5_7_3/my.sandbox.cnf --basedir=/home/njoshi/5.7.3 --datadir=/home/njoshi/sandboxes/msb_5_7_3/data --plugin-dir=/home/njoshi/5.7.3/lib/plugin --log-error=/home/njoshi/sandboxes/msb_5_7_3/data/msandbox.err --pid-file=/home/njoshi/sandboxes/msb_5_7_3/data/mysql_sandbox5703.pid --socket=/tmp/mysql_sandbox5703.sock --port=5703

nilnandan.joshi@bm-support01:~$ pt-show-grants --user=root --password=msandbox --socket=/tmp/mysql_sandbox5706.sock | grep root.@.localhost
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
nilnandan.joshi@bm-support01:~$

we can see password in earlier version like MySQL 5.7.3

nilnandan.joshi@bm-support01:~/sandboxes/msb_5_7_3$ pt-show-grants --user=root --password=msandbox --socket=/tmp/mysql_sandbox5703.sock | grep root.@.localhost
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6C387FC3893DBA1E3BA155E74754DA6682D04747' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
nilnandan.joshi@bm-support01:~/sandboxes/msb_5_7_3$

[Simon J Mudd (sjmudd)]

See also the comments in the bug report sent to Oracle. This is by design so whatever pt-show-grants does it looks like it will have to come up with a new way of presenting grants.

Note: I'd hope that you provide a backward-compatibility hook, that is something which will take the 5.7.6+ specific syntax and offer it in the "as close as practically possible" to the pre-5.7.6 syntax. This would at least allow copying grants from a newer box to an older server. I think that the current older syntax is still understood by 5.7.6+ but haven't had a chance to follow up further.

However, it's clear. This change in 5.7 is quite intrusive and I'm sure I'm not the only one to bump into this and use pt-show-grants to collect or manage grants.

Let's see what ideas you come up with on solving this.

[Simon J Mudd (sjmudd)]

A fix in perl is available :-)

See: http://datacharmer.blogspot.com.es/2015/07/mysql-57-no-more-password-column.html
Not sure if it's useful for you but I'm not the only one to have seen this.

[Simon J Mudd (sjmudd)]

Any progress on this. I'm testing 5.7 and this sudden loss of "password information" means copying or seeing grants now just doesn't work. We can argue about the merits of replacing the password column with an authentication_string but this seems to be here to stay and therefore taking it into account seems most worthwhile.

[Simon J Mudd (sjmudd)]

I've attached a suggested work around.

This does an additional SHOW CREATE USER and then modifies the output to:
CREATE USER IF NOT EXISTS .....; -- to create the user (this is replication safe)
ALTER USER <rest of output of SHOW CREATE USER>; -- to modify the credentials as needed.

This should work on 5.6 and later.
It does not try to generate "backwards compatible grants" from 5.7 servers to 5.6. That may be useful later.

[Simon J Mudd (sjmudd)]

Hello. With Percona Server 5.7 RC announced people are likely to start to bump into this issue, yet I see no response to the suggested patch or an alternative work around. Any thoughts on this?

[Frank Cizmich (frank-cizmich)]

Hello Simon,

You're right. Your patch looks great BTW. I'm implementing it now almost verbatim.

If all goes well it'll be in next release.

Many thanks!

[Michael Froehlich (michael-froehlich)]

Hi,

your bugfix created some new problems:

if the version is bigger 5.7.6, you run "show create user", which is ok for mysql 5.7. But the server-version check is not working as it should.
According to this, my mariadb 10 Server can not handle "show create user" commands.

The Problem is in Line 1910:
1910 if ( VersionCompare::cmp($version, '5.7.6') >= 0) {
1911 eval {
1912 @create_user = @{ $dbh->selectcol_arrayref("SHOW CREATE USER $user_host") };
1913 };

PTDEBUG=1:
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.0.23-MariaDB-log at 2016-03-08 07:18:06
# pt_show_grants:1905 17737 Checking user 'andy'@'localhost'
# pt_show_grants:1915 17737 DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'andy'@'localhost'' at line 1 [for Statement "SHOW CREATE USER 'andy'@'localhost'"] at /usr/bin/pt-show-grants line 1912.

Regards, Michael

[Markus Kienel (markus-3)]

Problem exists on Jessie with Maria-DB 10.1.13 also.

-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.1.13-MariaDB-1~jessie at 2016-04-02 00:29:40
# pt_show_grants:1905 20196 Checking user ''@'localhost'
# pt_show_grants:1915 20196 DBD::mysql::db selectcol_arrayref failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER ''@'localhost'' at line 1 [for Statement "SHOW CREATE USER ''@'localhost'"] at /usr/bin/pt-show-grants line 1912.
#
# pt_show_grants:1918 20196 CreateUser: $VAR1 = [];
#
Use of uninitialized value $create in substitution (s///) at /usr/bin/pt-show-grants line 1923.

[NJ Johansson (nj.johansson)]

I just bumped into the same issue with MariaDB 10.1.14 and Percona tools 2.2.17:

# pt-show-grants
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.1.14-MariaDB at 2016-06-20 11:15:03
Use of uninitialized value $create in substitution (s///) at /bin/pt-show-grants line 1923.

# pt-show-grants --version
pt-show-grants 2.2.17

[Mahesh Patil (maheshpatil-blr)]

Hello,

I am using Percona toolkit : 2.2.19, I want to store all GRANTs in a file, I am stuck with this error. It's not fixed ? What is the workaround?

[root@warehouse dba]# pt-show-grants -umpDBA --ask-pass > grants.sql
Enter password:
Error ## Use of uninitialized value $create in substitution (s///) at /usr/bin/pt-show-grants line 1937, <STDIN> line 1.
[root@warehouse dba]# mysql -umpDBA -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 292265
Server version: 5.7.9-log MySQL Community Server (GPL)

[Jonathan Nicol (jnicol)]

This is not fixed in 2.2.19, for MariaDB 10.0

-- Grants dumped by pt-show-grants
-- Dumped from server foo via TCP/IP, MySQL 10.0.26-MariaDB at 2016-10-11 19:19:45
Use of uninitialized value $create in substitution (s///) at /usr/bin/pt-show-grants line 1937.

[Max Bowsher (maxb)]

It seems a proper fix is in Git for 2.2.20.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-387