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

CONNECT ENGINE TBL gets 'database' confused

    XMLWordPrintable

    Details

      Description

      If the resulting table produced from a ENGINE=CONNECT table_type=TBL is the same name as one of the contained tables, attempt to query the new table fail with

      ERROR 1296 (HY000): Got error 174 'Error accessing FOO.Archive: This MySQL table is defined on itself' from CONNECT

      Here is how to reproduce

      CREATE DATABASE FOO;
      USE FOO;
      CREATE TABLE `Archive` (
        `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `alarmed` datetime DEFAULT NULL,
        PRIMARY KEY (`event_id`, `alarmed`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (TO_DAYS(alarmed))
      (PARTITION p_2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
      PARTITION p_other VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

      CREATE DATABASE BAR;
      USE BAR;
      CREATE TABLE `Archive` (
        `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `alarmed` datetime DEFAULT NULL,
        PRIMARY KEY (`event_id`, `alarmed`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      /*!50100 PARTITION BY RANGE (TO_DAYS(alarmed))
      (PARTITION p_2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
      PARTITION p_other VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

      CREATE DATABASE BAZ;
      USE BAZ;
       
      CREATE TABLE `Archive` (
        `event_id` bigint(20) unsigned NOT NULL,
        `alarmed` datetime DEFAULT NOW()
      ) ENGINE=CONNECT table_type=TBL table_list='FOO.Archive,BAR.Archive';

      select * from Archive;
       
      ERROR 1296 (HY000): Got error 174 'Error accessing FOO.Archive: This MySQL table is defined on itself' from CONNECT
       
      USE msql;
      select * from BAZ.Archive;
       
      ERROR 1296 (HY000): Got error 174 'Error accessing FOO.Archive: This MySQL table is defined on itself' from CONNECT
       
      use BAZ;
       alter table Archive RENAME to Archive2;
       
      select * from Archive2;
      Empty set (0.00 sec)
       
      use mysql;
      select * from BAZ.Archive2;
      Empty set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            jamesrleu James R. Leu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: