[MDEV-9239] CONNECT ENGINE TBL gets 'database' confused Created: 2015-12-04  Updated: 2016-01-10  Resolved: 2016-01-10

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.0.24, 10.1.11

Type: Bug Priority: Minor
Reporter: James R. Leu Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 7, linux x86_64


 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)



 Comments   
Comment by Elena Stepanova [ 2016-01-07 ]

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.

Comment by Olivier Bertrand [ 2016-01-10 ]

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.

Comment by James R. Leu [ 2016-01-10 ]

Awesome! I look forward to testing 10.1.11

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