I can generate federated tables from an opencart database to my local database without issue. However, one particular table (option_descriptions) returns no data to its federated (local) table despite there being data in the source table.
I thought this might be a problem with constraints or indexes but have been able to exclude those issues. I expect the problem must be table definition related, but I have no idea what the specifics of that might be.
The problem can be recreated locally using the following script. Just change the @connection_string variable to suit your database
Thanks in advance.
`
--
-- Federated Tables Problem
-- Results expected to show 11 for both tables / databases
-- I don't understand why table_2 returns no data on the federated table
--
-- ------------------ ------ ----------
-- TABLE NAME SOURCE FEDERATED
-- ------------------ ------ ----------
-- TABLE_1 Row Count 11 11
-- TABLE_2 Row Count 11 << 0 >>
-- ------------------ ------ ----------
-- The following script generates the result above.
-- Modify this to suit ...
SET @connection_string = "mysql://root:trapdoor@localhost:3306";
-- Create a local database to contain a pair of tables as a source
--
DROP DATABASE IF EXISTS `fed_test_source`;
CREATE DATABASE `fed_test_source`;
USE fed_test_source;
CREATE TABLE `TABLE_1` (
`option_id` INT(11) NOT NULL AUTO_INCREMENT,
`type` VARCHAR(32) NOT NULL,
`sort_order` INT(3) NOT NULL,
PRIMARY KEY (`option_id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `TABLE_1` (`option_id`, `type`, `sort_order`)
VALUES('1','select','15')
,('2','select','30')
,('3','radio','10')
,('4','select','20')
,('5','select','-100')
,('6','select','100')
,('7','select','0')
,('8','select','45')
,('9','select','0')
,('10','select','0')
,('11','select','0');
CREATE TABLE `TABLE_2` (
`option_id` INT(11) NOT NULL,
`language_id` INT(11) NOT NULL,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`option_id`,`language_id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO `TABLE_2` (`option_id`, `language_id`, `name`)
VALUES('1','1','Barrel Length')
,('2','1','Cast')
,('3','1','Colour')
,('4','1','Size')
,('5','1','Calibre')
,('6','1','Pitch')
,('7','1','Power Level')
,('8','1','Weight')
,('9','1','Capacity')
,('10','1','Length')
,('11','1','Type');
-- Now create a pair of federated tables linked to the tables created above
DROP DATABASE IF EXISTS `fed_test`;
CREATE DATABASE `fed_test`;
USE fed_test;
DROP TABLE IF EXISTS `fed_TABLE_1`;
DROP TABLE IF EXISTS `fed_TABLE_2`;
-- Create federated table fed_TABLE_1;
SET @sqlstr = CONCAT("
CREATE TABLE `fed_TABLE_1` (
`option_id` BIGINT(20) NOT NULL AUTO_INCREMENT, -- change
`type` VARCHAR(32) NOT NULL,
`sort_order` INT(3) NOT NULL,
PRIMARY KEY (`option_id`)
) ENGINE=FEDERATED
AUTO_INCREMENT=1
DEFAULT CHARSET=utf8
CONNECTION='",
@connection_string,
"/fed_test_source/TABLE_1';");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Create federated table fed_TABLE_2;
SET @sqlstr = CONCAT("
CREATE TABLE `fed_TABLE_2` (
`option_id` BIGINT(20) NOT NULL,
`language_id` INT(11) NOT NULL,
`name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`option_id`,`language_id`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
CONNECTION='",
@connection_string,
"/fed_test_source/TABLE_2';");
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT 'TABLE_1 Row Count' `name`, (SELECT COUNT(*) `row count` FROM `fed_test_source`.`TABLE_1`) `SOURCE`,(SELECT COUNT(*) `row count` FROM fed_test.`fed_TABLE_1`) `FEDERATED`
UNION
SELECT 'TABLE_2 Row Count' `name`, (SELECT COUNT(*) `row count` FROM `fed_test_source`.`TABLE_2`) `SOURCE`,(SELECT COUNT(*) `row count` FROM fed_test.`fed_TABLE_2`) `FEDERATED`;
`
Based on the script provided, I would expect the row count for both tables, across remote and source databases, to be equivalent.
I am getting no data on the federated table for one of the sample tables generated in the script.
I have tried using different databases as SOURCE.
