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

CONNECT ENGINE TBL gets 'database' confused

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

          Ascending order - Click to sort in descending order
          jamesrleu James R. Leu created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          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)           		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

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

          Here is how to reproduce

          {noformat}
          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) */;
          {noformat}
          {noformat}
          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) */;
          {noformat}
          {noformat}
          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';
          {noformat}

          {noformat}
          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)
          {noformat}
          elenst Elena Stepanova made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          Thanks for the report and test case.
          I don't see anything in documentation that would justify the error, so it is most likely a bug.
          Partitioning play no role in the scenario.

          elenst Elena Stepanova added a comment - Thanks for the report and test case. I don't see anything in documentation that would justify the error, so it is most likely a bug. Partitioning play no role in the scenario.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.1.8 [ 19605 ]
          Assignee Olivier Bertrand [ bertrandop ]
          bertrandop Olivier Bertrand added a comment -

          This bug was not specific to the TBL table type but could occur with all types using PROXY tables. A simple test case can be:

          use test;
          		 
          create table t1 (a int not null, b char(10)) engine=myisam;
          		 
          use connect;
          		 
          create table t1 engine=connect table_type=PROXY tabname='test.t1';
          		 
          select * from t1;

          Does not fail anymore.

          bertrandop Olivier Bertrand added a comment - This bug was not specific to the TBL table type but could occur with all types using PROXY tables. A simple test case can be: use test; create table t1 (a int not null, b char(10)) engine=myisam; use connect; create table t1 engine=connect table_type=PROXY tabname='test.t1'; select * from t1; Does not fail anymore.
          bertrandop Olivier Bertrand made changes -
          Fix Version/s 10.0.24 [ 21101 ]
          Fix Version/s 10.1.11 [ 21202 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Confirmed [ 10101 ] Closed [ 6 ]
          jamesrleu James R. Leu added a comment -

          Awesome! I look forward to testing 10.1.11

          jamesrleu James R. Leu added a comment - Awesome! I look forward to testing 10.1.11
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 72846 ] MariaDB v4 [ 149888 ]

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.