3

For the first time ever could not find a solution on internet! The problem is when I'm trying to (on local server):

CREATE TABLE `DB_1`.`testView`
ENGINE=FEDERATED CONNECTION='remote_server'; 

I'm getting an error:

#1939 - Engine FEDERATED failed to discover table `DB_1`.`testView` with 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testView` AS select 'test_line' AS `test_line` CONNECTION='remote_server''

Obviously you can see that VIEW is very simple for testing purposes (executed on remote server):

 CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

The user in remote_server has all privileges. View was tested with different options and content. Federated tables work fine. Server version on both machines: 10.0.29-MariaDB-0+deb8u1

Am I missing something?

2

You are using FederatedX engine and since you are not specifying column definitions explicitly, it is trying to perform assisted discovery to retrieve the structure of the table. It cannot do it with the underlying "table" being a view, so you are getting the error.

Instead, try to specify the columns:

-- remote
CREATE VIEW `DB_1`.`testView` AS SELECT "test_line"

-- local
CREATE TABLE `DB_1`.`testView` (test_line varchar(16))
ENGINE=FEDERATED CONNECTION='remote_server'; 
  • Thank you, indeed that was exactly the reason! Additionally thanks for linking related manual! No idea how I haven't tried explicit column definiton! – ouunp Jan 30 '17 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.