MySQL join with federated view not working

Question

I want to join a table with a view, where one table L is local, whereas the view F is a FEDERATED view residing on another server:

SELECT * FROM L LEFT JOIN F ON L.id = F.id;

Now the JOIN results in no hits despite the fact that there actually are many matches between the table and view. The ID field is bigint.

Frustrated, I created a TEMPORARY table T and dumped everything from F into it, thus making a local copy of F. Using T instead of F, the JOIN works as expected. But the process of creating T consumes memory and time.

What could be possible reasons for this odd MySQL behaviour?

Table definitions:

CREATE TABLE `L` (
  `id` bigint(20) NOT NULL,
  `id2` bigint(20) NOT NULL,
  PRIMARY KEY (`id`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and (this table is in fact a view on the remote server):

CREATE TABLE `F` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `field1` bigint(20) NOT NULL,
   ...
  `field5` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://userName:pword...';

@AlmaDo: InnoDB tables. Local server is 5.1.54-1ubuntu4, federated server is 5.5.35-0ubuntu0.12.04.2. — Gruber, Jun 26 '14 at 11:28
@AlmaDo: I now think I know what caused the problem. The federated table was in fact a view. Apparently JOINs on federated views don't work. I'm sorry for not discovering sooner. Thanks for your help, it helped me to investigate the matter better. — Gruber, Jun 26 '14 at 11:49
With `federated` engine, you should have table DDL in both servers - so - yes, VIEW is not applicable — Alma Do, Jun 26 '14 at 11:52
@AlmaDo: I have updated my question to reflect the discovery. If you'd like to you can add a regular more detailed answer. — Gruber, Jun 26 '14 at 11:55

score 2 · Accepted Answer · answered Jun 26 '14 at 11:59

As it states from definition of what FEDERATED storage-engine is, you must have table structure definition (so, for example .frm files for MyISAM) on both servers. That is because how FEDERATED engine works:

enter image description here

Therefore, you can not use VIEW since it has completely different meaning and structure. So instead you should mirror your table and then you'll be able to use it in your queries.

MySQL join with federated view not working

1 Answers1