mysqldump using deprecated variable INFORMATION_SCHEMA.SESSION_VARIABLES

Bug #1676401 reported by Francesco on 2017-03-27
64
This bug affects 11 people
Affects Status Importance Assigned to Milestone
Percona Server
Status tracked in 5.7
5.5
Undecided
Unassigned
5.6
Undecided
Unassigned
5.7
High
George Ormond Lorch III

Bug Description

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)

tags: added: myrocks regression
George Ormond Lorch III (gl-az) wrote :

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

Hrvoje Matijakovic (hrvojem) wrote :

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

Francesco (francesco-montanari) wrote :

I confirm that the issue is fixed 5.7.17-13

thank you very much for your work.

Jan Gantzert (j3n) wrote :

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) wrote :

@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) wrote :

@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) wrote :

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) wrote :

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) wrote :

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

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.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers