mysqldump execute 'SELECT 0' with params - ERROR 1210 (HY000)

Bug #1686603 reported by Роман on 2017-04-27
18
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.7
Fix Released
Critical
George Ormond Lorch III

Bug Description

I have this error when using mysqldump:

ERROR 1210 (HY000) at line 21: Incorrect arguments to EXECUTE

Server version 5.7.17-13

I guess, that the error in these lines:

/*!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 */;

because, if @rocksdb_has_p_s_session_variables == 0, we have @rocksdb_get_is_supported = 'SELECT 0', and if you try to execute this query using params (@rocksdb_bulk_load_var_name), you get ERROR 1210 (HY000)

Роман (r-savinkov) wrote :

I think, it need to assigned to @gl-az, because he did it:
https://bugs.launchpad.net/percona-server/+bug/1676401

Changed in percona-server:
assignee: nobody → George Ormond Lorch III (gl-az)
George Ormond Lorch III (gl-az) wrote :

This case can only happen if PFS is completely missing the session_variables table, which I believe is only possible if PFS is actually compiled out of the server.

Роман (r-savinkov) wrote :

Or if I made dump on Server 5.7.17-13 and try to restore on 5.7.17-12

George Ormond Lorch III (gl-az) wrote :

How so? performance_schema.session_variables should still be present in 5.7.17-12, thus the prepared query 's' should not be the 'SELECT 0', which causes the error, but 'SELECT COUNT(*) INTO @rocksdb_is_supported FROM performance_schema.session_variables WHERE VARIABLE_NAME=?'.

Let's look at the full sequence:

1 > /*!50717 SET @rocksdb_bulk_load_var_name='rocksdb_bulk_load' */;
2 > /*!50717 SELECT COUNT(*) INTO @rocksdb_has_p_s_session_variables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'session_variables' */;
3 > /*!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') */;
4 > /*!50717 PREPARE s FROM @rocksdb_get_is_supported */;
5 > /*!50717 EXECUTE s USING @rocksdb_bulk_load_var_name */;

The key line here is line 2 that looks to see if the performance_schema.session_variables table exists.
Line 3 will only set @rocksdb_get_is_supported to 'SELECT 0' if @rocksdb_has_p_s_session_variables is 0 or unset, which AFAIK can only happen if 5.7.17 is compiled without PFS. So the real issue is that the check to see if peformance_schema.session_variables exists is broken by conditionally preparing one of two statements, one that takes a parameter and one that doesn't. When the statement that takes no parameter is executed with a parameter, we get the error.

Роман (r-savinkov) wrote :

Sorry, You right. I use docker for check my backup automaticly. There is not PFS in container. And I had mistake, I had Server 5.7.15-9 in container, when I got error.

George Ormond Lorch III (gl-az) wrote :

If you had 5.7.15, the MySQL comment version guards "/*!50717 ...*/" should have prevented any of these from executing at all on the reload.

The 'SELECT 0' without a parameter is most certainly a bug, but I am trying to understand the conditions where it is possibly hit to try to make sure we implement tests to cover.

Boris (borisemerge) wrote :

I am getting this EXACT error when trying to import a mysqldump image from 5.7.17-12 into 5.7.17-13 and even 5.7.18-14. One thing to note is that I do have the setting 'performance_schema=OFF' set in my my.cnf configuration file.

George Ormond Lorch III (gl-az) wrote :

Thanks Boris, that is interesting as disabling PFS in that way is still supposed to leave the performance_schema.session_variables and performance_schema.global_variables tables operational and I have validated as much. I wonder if maybe there is some path through upgrade where these tables don't get some initial creation/activation. If so, then there may be some upstream issue. This behavior is documented here https://dev.mysql.com/doc/refman/5.7/en/performance-schema-system-variables.html#sysvar_performance_schema

So to repeat what I said before, I can not find a way to disable these tables unless I actually compiled it out, which no standard distribution or package does that I know of. Since there are a few of you hitting it, there must be some path where these tables remain inaccessible or inactive.

Boris (borisemerge) wrote :

Reading https://dev.mysql.com/doc/refman/5.7/en/performance-schema-system-variables.html#sysvar_performance_schema , I also have show_compatibiliy_56=on enabled AND when the database was first initialized using mysql_install_db, we had to add the "--skip-sys-schema" parameter since it would crash mysql when the server in FIPS mode. Hope this might help.

Jan Gantzert (j3n) wrote :

I am using the standard ubuntu 16.04 package with version 5.7.18-14.
I can set show_compatibiliy_56 to on or off and
performance_schema to on or off, but that does not change anything.

The only thing is to switch the import to the root user like I said in
https://bugs.launchpad.net/percona-server/+bug/1676401/comments/8

Maybe there are 2 bugs in one?
Hope that helps.

Teodor Milkov (tm-del) wrote :

I hit this bug too. If I create a user, who has access to his own db *only*, then he can't lookup the performance_schema from information_schema. Here's a demonstration:

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES;
+----------+
| COUNT(*) |
+----------+
| 74 |
+----------+
1 row in set (0.07 sec)

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'information_schema';
+----------+
| COUNT(*) |
+----------+
| 74 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA != 'information_schema';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.08 sec)

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)

Teodor Milkov (tm-del) wrote :

Forgot to mention how I've created the above user:

mysql> CREATE USER 'tester42'@'localhost' IDENTIFIED BY 'qwerty123';
mysql> GRANT ALL ON test.* TO 'tester42'@'localhost';

Teodor Milkov (tm-del) wrote :

And eventually, allowing access to performance_schema for my test user, makes it visible in information schema. E.g.:

mysql root> GRANT SELECT ON performance_schema.* TO 'tester42'@'localhost';

---

mysql tester42> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';
+----------+
| COUNT(*) |
+----------+
| 87 |
+----------+
1 row in set (0.00 sec)

Jason Short (shortj) wrote :

Here are the workarounds we've had to implement to prevent these rocksdb patches from breaking our automation:

1. mysqldump 5.6.36 and 5.7.18 want to be able to query performance_schema, which is not available to non-root users by default.

Solution: INSERT INTO mysql.db SET host='%', user='', db='performance_schema',Select_priv='Y'

2. importing dumps from 5.6.36 into 5.7.18 breaks due to show_compatibility_56 being off by default in >5.7.6.

Solution: SET GLOBAL show_compatibility_56 = ON and set in my.cnf

These rocksdb patches seem very poorly thought out. Was no consideration given to dumps being run against servers with PFS disabled, imported by non-root users, or portability across versions?

Boris (borisemerge) wrote :

I created the accounts the same way as Teodor did and had the issues so I tried what Teodor's solution by adding the GRANT SELECT permissions on the non-root account and now things are working again. This still does seems like a poor workaround.

mysql -umyuseraccount -p
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+

mysql -uroot -p
GRANT SELECT ON performance_schema.* TO "myuseraccount"@"localhost";

mysql -umyuseraccount -p
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'performance_schema';
+----------+
| COUNT(*) |
+----------+
| 87 |
+----------+

https://github.com/percona/percona-server/pull/1756

Here is a straight jenkins run without MyRocks : http://jenkins.percona.com/view/5.7/job/mysql-5.7-param/955/

Here is a straight jenkins run with MyRocks built and 'visible' to the servers in mtr : http://jenkins.percona.com/view/5.7/job/mysql-5.7-param/956/

Here is a jenkins run with just the MyRocks suites : http://jenkins.percona.com/view/5.7/job/mysql-5.7-param/957/

Is there any way to disable all this "rocksdb" stuff in mysqldump?
Same problems with restoring dump using non-privileged users...

Jan Gantzert (j3n) wrote :

Is there a release date for the next version?
This stuff is getting really annoying to us, because our mysqldumps for backup and development
cannot be imported without workarounds.

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

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

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.