Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.17
-
Fix Version/s: 10.0.18
-
Component/s: Storage Engine - Connect
-
Labels:
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
Thanks for fixing it so quickly, Olivier!
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_test 31 Query select @@version_comment limit 1 31 Query SELECT * FROM connect_table1 32 Connect connect_tester@localhost as anonymous on connect_test 31 Quit 32 Quit150501 17:16:18 34 Connect root@localhost as anonymous on connect_test 34 Query select @@version_comment limit 1 34 Query SELECT COUNT(*) FROM connect_table1 35 Connect connect_tester@localhost as anonymous on connect_test 34 Quit 35 Quit150501 17:16:55 40 Connect root@localhost as anonymous on connect_test 40 Query select @@version_comment limit 1 40 Query SELECT COUNT(*) FROM connect_table2 41 Connect connect_tester@localhost as anonymous on connect_test 40 Quit 41 QuitCompare these to the one query that succeeded:
150501 17:16:34 37 Connect root@localhost as anonymous on connect_test 37 Query select @@version_comment limit 1 37 Query SELECT * FROM connect_table2 38 Connect connect_tester@localhost as anonymous on connect_test 38 Query SELECT `n`, `str` FROM `real_table2` 37 Quit 38 Quit