mysqldump using deprecated variable INFORMATION_SCHEMA.SESSION_VARIABLES

[Francesco]

I migrated from MySQL 5.7 to Percona Server, and now I have this error when using mysqldump:
(I don't know if this bug should be reported to MySQL, but I didn't have this bug before migrating)

# mysqldump -u backups -pxxxx -h 127.0.0.1 --single-transaction --quick dbname
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13 Distrib 5.7.17-12, for debian-linux-gnu (x86_64)
--
-- Host: 127.0.0.1 Database: dbname
-- ------------------------------------------------------
-- Server version 5.7.17-12

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50112 SELECT COUNT(*) INTO @is_rocksdb_supported FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='rocksdb_bulk_load' */;
/*!50112 SET @save_old_rocksdb_bulk_load = IF (@is_rocksdb_supported, 'SET @old_rocksdb_bulk_load = @@rocksdb_bulk_load', 'SET @dummy_old_rocksdb_bulk_load = 0') */;
/*!50112 PREPARE s FROM @save_old_rocksdb_bulk_load */;
/*!50112 EXECUTE s */;
/*!50112 SET @enable_bulk_load = IF (@is_rocksdb_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @dummy_rocksdb_bulk_load = 0') */;
/*!50112 PREPARE s FROM @enable_bulk_load */;
/*!50112 EXECUTE s */;
/*!50112 DEALLOCATE PREPARE s */;
mysqldump: Couldn't execute 'SELECT COUNT(*) FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'rocksdb\_skip\_fill\_cache'': The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56' (3167)

[George Ormond Lorch III (gl-az)]

This was introduced as part of our work in porting Facebook MySQL MyRocks engine to Percona Server 5.7. The use of I_S over P_S was not caught during the merge of this feature from 5.6 to 5.7

An immediate workaround is to change the show_compatibility_56 back to the default value of ON which will expose the needed tables within I_S. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56 for details.

Alternately, a mysqldump binary from a previous version install can be used in place of the one packaged with the recent release.

I expect to have this fixed with the next release of Percona Server 5.7

[George Ormond Lorch III (gl-az)]

http://jenkins.percona.com/view/5.7/job/mysql-5.7-param/758/
https://github.com/percona/percona-server/pull/1574

[Hrvoje Matijakovic (hrvojem)]

You can test the packages from percona testing repos that resolve this issue.

[Francesco (francesco-montanari)]

I confirm that the issue is fixed 5.7.17-13

thank you very much for your work.

[Jan Gantzert (j3n)]

Hi,
are you really sure, that this is fixed? It does not work for me.

Greetings Jan

Ubuntu 16.04:
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.2 LTS"

mysqldump --version:
mysqldump Ver 10.13 Distrib 5.7.17-13, for debian-linux-gnu (x86_64)

mysql -e "show global variables like '%show_compatibility_56%'" (doesnt matter anyway)
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| show_compatibility_56 | ON |
+-----------------------+-------+

mysqldump -uroot --opt --skip-comments --skip-dump-date --complete-insert test_db | less

results in =>

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=?', 'SELECT 0') */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;
DROP TABLE IF EXISTS `first_table`;

[Francesco (francesco-montanari)]

@Jan What is the error in your case? I don't see any in the output you posted.

Anyway I don't think it's related to this bug, otherwise it would have worked by enabling show_compatibility_56

[Jan Gantzert (j3n)]

@Francesco
The thing is, that the sql dump has the rockdb comments included. I dont use rockdb, I use innodb.
The dump should not have this comment. Regarding @gl-az in this ticket, this is a myrocks regression problem.

The concrete error happens, when I try to import:
Always somthing like: "USING rockdb Incorrect arguments to EXECUTE"

With 5.7.11 the dump is correct, with 5.7.12/13 incorrect:

5.7.11:
-- MySQL dump 10.13 Distrib 5.7.17-11, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: show_gl_wp
-- ------------------------------------------------------
-- Server version 5.7.17-11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `blabla`
--

5.7.13:

-- MySQL dump 10.13 Distrib 5.7.17-13, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: show_gl_wp
-- ------------------------------------------------------
-- Server version 5.7.17-13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */;
/*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
/*!50717 SET @rocksdb_get_is_supported = IF (@rocksdb_has_p_s_session_variables, 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=?', 'SELECT 0') */;
/*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
/*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */;
/*!50717 DEALLOCATE PREPARE s */;
/*!50717 SET @rocksdb_enable_bulk_load = IF (@rocksdb_is_supported, 'SET SESSION rocksdb_bulk_load = 1', 'SET @rocksdb_dummy_bulk_load = 0') */;
/*!50717 PREPARE s FROM @rocksdb_enable_bulk_load */;
/*!50717 EXECUTE s */;
/*!50717 DEALLOCATE PREPARE s */;

--
-- Table structure for table `blabla`
--

Greetings Jan

[Jan Gantzert (j3n)]

I think I found the problem.
The import only works with some users.
The Import works with:
mysql -uroot -proot -P3306 --database=test < test.sql

But not with:
mysql -uuser -puser -P3306 --database=test < test.sql
=> ERROR 1210 (HY000) at line 16: Incorrect arguments to EXECUTE

Line 16: /*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */;

Both users have of course the right to import etc.
And the import works with <= 5.7.11 (without the rockdb comments)

[George Ormond Lorch III (gl-az)]

This is not the same issue as the original, but related to the original change.

The procedure 's' should be "SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=?" and the parameter value would be "rocksdb_bulk_load".

So a dump user needs to be able to query from p_s.session_variables, which doesn't sound like something that would be prohibited by default.

[Jan Gantzert (j3n)]

Thanks for the answer.
Should I make a new issue for this bug?

[George Ormond Lorch III (gl-az)]

Yes Jan, please, and also please include all grant information about the user that can not execute the dump, something is very odd to me if even a restricted user can not view their own session variables.

[CJ (rcj1)]

@j3n - What is the new bug number?

[George Ormond Lorch III (gl-az)]

It looks like Jans issue is the same as this https://bugs.launchpad.net/percona-server/+bug/1686603

Not strictly a permissions issue but a parameter to a stored proc issue (calling a stored proc with more parameters than the proc requires). It is curious though why one user succeeds and another fails. If it is the same, then it is only possible to encounter on a system with no performance schema at all, which I believe requires the server to be _built_ without PFS.

[Shahriyar Rzayev (rzayev-sehriyar)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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