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

[Роман]

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)]

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

[George Ormond Lorch III (gl-az)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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)]

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 |
+----------+

[George Ormond Lorch III (gl-az)]

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/

[George Ormond Lorch III (gl-az)]

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

[Nick Lavlinsky (nick-methodlab-deactivatedaccount)]

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

[Jan Gantzert (j3n)]

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.

[Shahriyar Rzayev (rzayev-sehriyar)]

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