Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.17
Description
When attempting to query some CONNECT tables with table_type set to mysql, the query returns the error:
ERROR 1296 (HY000) at line 1: Got error 122 'Fetch: No Result Set' from CONNECT
|
For example, let's create some objects:
CREATE DATABASE `connect_test`;
|
|
|
CREATE USER 'connect_tester'@'%' IDENTIFIED BY 'password';
|
-- FILE is needed for CONNECT
|
GRANT FILE ON *.* TO 'connect_tester'@'%';
|
GRANT ALL PRIVILEGES ON `connect_test`.* TO 'connect_tester'@'%';
|
|
|
|
|
CREATE TABLE `connect_test`.`real_table1` (
|
`n` int NOT NULL,
|
PRIMARY KEY (`n`) );
|
|
INSERT INTO `connect_test`.`real_table1` (n) VALUES
|
(1),
|
(2),
|
(3),
|
(4),
|
(5),
|
(6),
|
(7),
|
(8),
|
(9);
|
|
|
CREATE TABLE `connect_test`.`connect_table1` (
|
`n` int NOT NULL,
|
PRIMARY KEY (`n`) )
|
ENGINE=CONNECT
|
`table_type`=mysql
|
`dbname`=connect_test
|
`tabname`=real_table1
|
option_list='database=connect_test,user=connect_tester,password=password';
|
|
CREATE TABLE `connect_test`.`real_table2` (
|
`n` int NOT NULL,
|
`str` VARCHAR(50),
|
PRIMARY KEY (`n`) );
|
|
INSERT INTO `connect_test`.`real_table2` (n, str) VALUES
|
(1, 'correct'),
|
(2, 'horse'),
|
(3, 'battery'),
|
(4, 'staple'),
|
(5, 'correct'),
|
(6, 'horse'),
|
(7, 'battery'),
|
(8, 'staple'),
|
(9, 'correct');
|
|
|
CREATE TABLE `connect_test`.`connect_table2` (
|
`n` int NOT NULL,
|
`str` VARCHAR(50),
|
PRIMARY KEY (`n`) )
|
ENGINE=CONNECT
|
`table_type`=mysql
|
`dbname`=connect_test
|
`tabname`=real_table2
|
option_list='database=connect_test,user=connect_tester,password=password';
|
Now, if we query real_table1, everything works:
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT * FROM real_table1;"
|
+---+
|
| n |
|
+---+
|
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
| 5 |
|
| 6 |
|
| 7 |
|
| 8 |
|
| 9 |
|
+---+
|
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT COUNT(*) FROM real_table1;"
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 9 |
|
+----------+
|
However, querying connect_table1 fails:
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT * FROM connect_table1;"
|
ERROR 1296 (HY000) at line 1: Got error 122 'Fetch: No Result Set' from CONNECT
|
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT COUNT(*) FROM connect_table1;"
|
ERROR 1296 (HY000) at line 1: Got error 122 'Fetch: No Result Set' from CONNECT
|
Querying real_table2 works:
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT * FROM real_table2;"
|
+---+---------+
|
| n | str |
|
+---+---------+
|
| 1 | correct |
|
| 2 | horse |
|
| 3 | battery |
|
| 4 | staple |
|
| 5 | correct |
|
| 6 | horse |
|
| 7 | battery |
|
| 8 | staple |
|
| 9 | correct |
|
+---+---------+
|
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT COUNT(*) FROM real_table2;"
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 9 |
|
+----------+
|
Whereas querying connect_table2 only fails if attempting to calculate COUNT
:
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT * FROM connect_table2;"
|
+---+---------+
|
| n | str |
|
+---+---------+
|
| 1 | correct |
|
| 2 | horse |
|
| 3 | battery |
|
| 4 | staple |
|
| 5 | correct |
|
| 6 | horse |
|
| 7 | battery |
|
| 8 | staple |
|
| 9 | correct |
|
+---+---------+
|
[gmontee@localhost ~]$ mysql -u root connect_test --execute="SELECT COUNT(*) FROM connect_table2;"
|
ERROR 1296 (HY000) at line 1: Got error 122 'Fetch: No Result Set' from CONNECT
|
Attachments
Activity
I ran this test and got the same errors (except that I had to create the user as @'localhost' instead of @'%'. I don't know why but I had "access denied" errors)
I shall investigate the case to find why it fails.
Meanwhile, a workaround is to suppress the primary key indexing of the MYSQL tables.
Access denied errors are mostly likely caused by the presence of anonymous users. select user, host from mysql.user and drop users with the empty name.
CONNECT was fooled by MariaDB calling the engine via index_read with a NULL key for these queries. I don't know why because these are not indexed queries having no where clause.
Notes:
Thanks Elena, this was an anonymous user problem indeed.
Note also that creating a MYSQL table with options and option_list is deprecated and may be not supported in future version. You can do it like this:
CREATE TABLE `test`.`connect_table1` (
|
`n` int NOT NULL,
|
PRIMARY KEY (`n`) )
|
ENGINE=CONNECT
|
`table_type`=mysql
|
CONNECTION='mysql://connect_tester:password@localhost/test/real_table1';
|
Note also that defining indexes is most of the time useless and should be avoided.
I was able to reproduce the case without existing anonymous user.
However, removing the primary key at the connect table solves the issue.
The reported problem has nothing to do with anonymous users.
The anonymous users cause a different error (access denied), which doesn't allow to reproduce the reported issue, which is why they need to be dropped (or the provided test case needs to be modified to create the user with a particular host rather than '%'.
According to the general log, CONNECT isn't even querying the underlying table when these are failing.
These are the general log entries for the three queries that fail:
150501 17:16:03 31 Connect root@localhost as anonymous on connect_test31 Query select @@version_comment limit 131 Query SELECT * FROM connect_table132 Connect connect_tester@localhost as anonymous on connect_test31 Quit32 Quit150501 17:16:18 34 Connect root@localhost as anonymous on connect_test34 Query select @@version_comment limit 134 Query SELECT COUNT(*) FROM connect_table135 Connect connect_tester@localhost as anonymous on connect_test34 Quit35 Quit150501 17:16:55 40 Connect root@localhost as anonymous on connect_test40 Query select @@version_comment limit 140 Query SELECT COUNT(*) FROM connect_table241 Connect connect_tester@localhost as anonymous on connect_test40 Quit41 QuitCompare these to the one query that succeeded:
150501 17:16:34 37 Connect root@localhost as anonymous on connect_test37 Query select @@version_comment limit 137 Query SELECT * FROM connect_table238 Connect connect_tester@localhost as anonymous on connect_test38 Query SELECT `n`, `str` FROM `real_table2`37 Quit38 Quit