[MDEV-8090] CONNECT fails to fetch result sets for some tables of MySQL table type Created: 2015-05-02  Updated: 2015-05-05  Resolved: 2015-05-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0.17
Fix Version/s: 10.0.18

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: connect-engine, verified


 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



 Comments   
Comment by Geoff Montee (Inactive) [ 2015-05-02 ]

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 Quit
 

150501 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 Quit
 

150501 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 Quit

Compare 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

Comment by Olivier Bertrand [ 2015-05-02 ]

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.

Comment by Elena Stepanova [ 2015-05-02 ]

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.

Comment by Olivier Bertrand [ 2015-05-02 ]

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.

Comment by Geoff Montee (Inactive) [ 2015-05-02 ]

Thanks for fixing it so quickly, Olivier!

Comment by Stoykov (Inactive) [ 2015-05-05 ]

I was able to reproduce the case without existing anonymous user.

However, removing the primary key at the connect table solves the issue.

Comment by Elena Stepanova [ 2015-05-05 ]

ivan.stoykov@skysql.com,

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 '%'.

Generated at Thu Feb 08 07:24:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.