[MDEV-11311] Create federated table does not work as expected. Created: 2016-11-18  Updated: 2017-05-24  Resolved: 2017-05-24

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Federated
Affects Version/s: 10.1.14, 10.1, 10.2
Fix Version/s: 10.1.24, 10.2.7

Type: Bug Priority: Major
Reporter: Matt Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian 8.5 - linux 3.16.0-4 on vmware


 Description   

When attempting to create a connection to a specific table using the federated storage engine, an error is thrown.

Engine FEDERATED failed to discover table `mysql56test-cdb001`.`t_topics_indexes` with 'CREATE TABLE `t_topics_indexes` (
 
    `topic_indexID` bigint(20) NOT NULL AUTO_INCREMENT,
 
    `topicID` bigint(20) NOT NULL,
 
    `index_typeID` tinyint(4) NOT NULL,
 
    `index_value` varchar(1024) NOT NULL,
 
    PRIMARY KEY (`topic_indexID`),
 
    KEY `FK_TI_TOPICID_idx` (`topicID`),
 
    KEY `IDX_TYPE` (`index_typeID`),
 
    KEY `UIDX_TOPICTYPEVALUE` (`topicID`,`index_typeID`,`index_value`(255)),
 
    CONSTRAINT `FK_TI_TOPICID` FOREIGN KEY (

If I remove this key 

KEY `UIDX_TOPICTYPEVALUE` (`topicID`,`index_typeID`,`index_value`(255))

it works as expected.

Here is the full create statement for the table.

CREATE TABLE `t_topics_indexes` (   `topic_indexID` bigint(20) NOT NULL AUTO_INCREMENT,   `topicID` bigint(20) NOT NULL,   `index_typeID` tinyint(4) NOT NULL,   `index_value` varchar(1024) NOT NULL,   PRIMARY KEY (`topic_indexID`),   KEY `FK_TI_TOPICID_idx` (`topicID`),   KEY `IDX_TYPE` (`index_typeID`),   KEY `UIDX_TOPICTYPEVALUE` (`topicID`,`index_typeID`,`index_value`(255)),   CONSTRAINT `FK_TI_TOPICID` FOREIGN KEY (`topicID`) REFERENCES `t_topics` (`topicID`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=14606 DEFAULT CHARSET=utf8;

All other tables on this database are able to be added normally.

 Comments   
Comment by Elena Stepanova [ 2016-11-18 ]

You quoted the full create statement for the remote table, what was one for the FEDERATED table?

Comment by Matt [ 2016-11-18 ]

CREATE TABLE `t_topics_indexes` ENGINE=FEDERATED CONNECTION='mysql56test-cdb001'

Comment by Matt [ 2016-11-18 ]

And the create server command:
create server `mysql56test-cdb001` foreign data wrapper 'mysql' OPTIONS (HOST 'mysql56test', DATABASE 'cdb001', USER 'federator', PASSWORD 'redacted', OWNER 'root')

Comment by Elena Stepanova [ 2016-12-17 ]

Thanks, I was able to reproduce it.

The problem is that the length of the index. In the underlying table, the length is limited, and everything is going fine. But apparently upon table discovery the explicit limit is ignored.

MTR test case

 
--source include/have_innodb.inc
 
--source suite/federated/have_federatedx.inc
 
 
 
CREATE TABLE t1 (
 
  `topic_indexID` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `topicID` bigint(20) NOT NULL,
 
  `index_typeID` tinyint(4) NOT NULL,
 
  `index_value` varchar(1024) NOT NULL,
 
  PRIMARY KEY (`topic_indexID`),
 
  KEY `UIDX_TOPICTYPEVALUE` (`topicID`,`index_typeID`,`index_value`(255))
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 
;
 
 
 
CREATE DATABASE fed CHARSET utf8;
 
USE fed;
 
--eval CREATE SERVER s foreign data wrapper 'mysql' OPTIONS (HOST '127.0.0.1', PORT $MASTER_MYPORT, DATABASE 'test', USER 'root', PASSWORD '', OWNER 'root')
 
CREATE TABLE t1 ENGINE=FEDERATED CONNECTION='s';

Comment by Bruno Perel [ 2017-01-15 ]

In my case, the error appears if the remote table has at least one "FULLTEXT" index. So like Elena says, it's probably a matter of index size.

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