Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8090

CONNECT fails to fetch result sets for some tables of MySQL table type

    Details

      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

            People

            • Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              GeoffMontee Geoff Montee
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: