MySQL Forums - Federated Storage Engine

How to use MySQL FEDERATED engine ? (no replies)

Edwin Desouza — Fri, 10 Mar 2023 18:25:50 +0000

https://blogs.oracle.com/mysql/post/how-to-use-mysql-federated-engine

GTID based Replication with Federated tables (no replies)

Dhruvin Dharamsey — Tue, 13 Jul 2021 16:17:58 +0000

Hi,

I am trying to replicate the database which has few federated tables.

The replication is getting set but when I perform DML operations like Insert, Delete etc. The replication breaks.

Also when I perform DELETE operation on primary server the slave mysql crashes.

The federated table data is on remote server.

I also tried pointing the slave server to different remote server with same data as main remote server. This time the Insert statement ran succesfully without breaking the replication. But as soon as I ran DELETE command on federated table the MySQL on slave server got crashed.

Can someone suggest me where I am going wrong?

Thanks,
Dhruvin Dharamsey.

Disabling SSL on Federated connection (1 reply)

Marc Bernard — Fri, 30 Jul 2021 13:57:24 +0000

Hello,

I'm trying to create a federated table on a brand new MySQL 8 server to an older 5.7.21 server and I keep getting this error;

#1429 - Unable to connect to foreign data source: SSL connection error: error:1425F102:SSL routines:ssl_choose_cli

The table definition uses a connection made with `CREATE SERVER`

Is there a way to disable SSL on Federated connection ?

memory issues with federated (no replies)

MIHAI RADU SANDU — Mon, 13 Apr 2020 17:27:42 +0000

Hello,

I have a personal project at work for a monitoring tool using a mysql server with federated engine and multi-master replication. It's really useful and I am a great (and probably) the only supporter of the federated engine in the company. The guys from dba always tell me the engine is not good.
So, basically it works really well, except I have some memory issues. If I'm not careful about how much data I bring trough the federated engine, my server will reach out of memory and the server will be restarted, which also crashes the replication sometimes.
I only have 7.6 gb of ram seen as available in linux, and the innodb buffer pool is set to 4gb.
Do you know how I could monitor how much data from the memory is used for the federated table caching?
Do you know if the federated engine also uses the swap file or is it only confined to actual memory?
Do you have any advice for me about how I could better manage these situations so that the server will not remain without memory?
Is there any other tool that you used,maybe similar functionalities in MariaDB or PostgreSQL, that work better and you would recommend?

Thank you,
Mihai

Does federated engine allows table creation for ipv6 ? (no replies)

tarun gupta — Fri, 05 Jul 2019 20:33:24 +0000

I have two server both connected to each other by ipv6.

1 can i create table on local server with ipv6 connection of remote server

What i have tried

1 i created a table on remote server which is on ipv6

2 i created a table on local server which is on ipv6 with connection details of
remote server as mentioned below :

This is how i created my local table

CREATE TABLE usageinfo
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(200) NULL,
controllerId VARCHAR(200) NULL,
slotId VARCHAR(200) NULL,
startdate DATE NULL,
starttime TIME NULL,
enddate DATE NULL,
endtime TIME NULL
)ENGINE=FEDERATED
CONNECTION='mysql://username:password@2222:2222:2:240a::10/dbName/usageinfo';

table created successfully.

After i perform select query got the below error :

ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '2222'

I believe ipv6 format that i have specified while creating table is incorrect.

can some one help me with the correct ipv6 format.

or let me know if ipv6 is not supported for federated engine.

Insert into Federated tables using mysql triggers (no replies)

Saravanan Ramasamy — Wed, 16 Jan 2019 22:01:36 +0000

Hi,
I have a local table A and a federated table B.
I'm trying to insert into federated table B using After trigger Insert on local table A.
Everything works as long as both servers are connected.
Whenever the other server is either down or unavailable during insert action it gets hung or something. I'm not sure whether it is waiting for the server. It gets hung and as long as i reconnect back to other server the action resumes.

I would like to throw an error and keep going.
I tried RESIGNAL as below
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RESIGNAL;
But doesn't seem to help. Any ideas?

Thanks.

federated enabled but not working (2 replies)

sakthi sridhar — Fri, 16 Mar 2018 16:19:38 +0000

Hi,
Federated not working after enabled in mysql config file

Config File

[mysqld]
federated

after enabled and i restart the server

in SQLYog

show engines;

Engine Support Comment
---------- ------- ----------------------------------------------------------------
MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance
MEMORY YES Hash based, stored in memory, useful for temporary tables
InnoDB YES Supports transactions, row-level locking, and foreign keys
BerkeleyDB YES Supports transactions and page-level locking
BLACKHOLE NO /dev/null storage engine (anything you write to it disappears)
EXAMPLE NO Example storage engine
ARCHIVE NO Archive storage engine
CSV NO CSV storage engine
ndbcluster NO Clustered, fault-tolerant, memory-based tables
FEDERATED NO Federated MySQL storage engine
MRG_MYISAM YES Collection of identical MyISAM tables
ISAM NO Obsolete storage engine

still FEDERATED not enabled.

please suggest to slove this problem..

Problems updating federated table (no replies)

Daniel Ortiz — Wed, 21 Jun 2017 16:31:57 +0000

Scenario:

Server A: create view federated_view as SELECT ....

Server B: create table tabela_federada (A, B, C) ENGINE=FEDERATED CONNECTION='mysql://user:paswd@:3306/database/federated_view';

When i perform a update on federated table, mysql return
OK, but the value was not updated:

mysql> update tabela_federada set incidentes_utilizados = 1 where customer_id = 'teste' and incidentes_utilizados = 10;
Query OK, 1 row affected (0.17 sec)

mysql> select * from tabela_federada where customer_id = 'teste'\G
incidentes_utilizados: 10
bugs_qualificados: 3
requisicoes_utilizadas: 2
horas_disponiveis: 4.00

Linked Servers of MS SQL; CONNECT Storage Engine of MariaDB; or Oracle Database Gateway (1 reply)

Juan Telleria — Fri, 30 Sep 2016 08:26:53 +0000

Nowadays, most data necessary isn't in our servers, and it is hosted in other RDBMS making FEDERATED engine unuseful, that is why I would like to suggest the possibility to add one (or all :-D) of the following features to MySQL:

a) MS SQL like "Linked Servers", which support ODBC connections. This would enable to connect MySQL to SQL Server to obtain data in an easy way.

b) A CONNECT Storage Engine like the one of MariaDB.

c) Or ODBC connecting capabilities such as the ones of "Oracle Database Gateway".

Thank you :-)

Unknown storage engine 'FEDERATED' (1 reply)

Tharpa roberts — Wed, 11 Nov 2015 20:52:10 +0000

I am attempting to create a table using query like so:

CREATE TABLE our_content.`acq_documents` (
	`acq_documents_id` INT (11) NOT NULL AUTO_INCREMENT
	,`accession_no` VARCHAR(100) NOT NULL DEFAULT ''
	,`title` mediumtext
	,`product_code` VARCHAR(45) DEFAULT NULL
	,PRIMARY KEY (`acq_documents_id`)
	,UNIQUE KEY `accession_no_UNIQUE`(`accession_no`)
	,KEY `title`(`title`(100))
	,KEY `product_code`(`product_code`)
	) ENGINE=FEDERATED AUTO_INCREMENT = 174244 DEFAULT CHARSET = utf8
	CONNECTION='www@nsamysql101.sl4.as:3306/lnh_acquisitions/acq_documents'
	;
I am getting the following warning message:

1286 Unknown storage engine 'FEDERATED'
Using storage engine InnoDB for table 'acq_documents'

What is the cause of this message?

Does MRR fit for opimizing the range scan of federated table (no replies)

zkong kong — Fri, 10 Jul 2015 05:24:24 +0000

Hi all:
The federated engine supports range scan, I wander that whether the MRR can speed up the process and is there any plan to support MRR for federated engine.

FEDERATED out of connections (no replies)

Martin Wilmes — Mon, 13 Apr 2015 12:45:16 +0000

I have created about 150 tables with engine FEDERATED. After a while of using about 30 of them I get the error "ERROR 1429 (HY000) at line 3: Unable to connect to foreign data source: Can_t create a new thread (errno 11); ..."

Is there any way of checking how many different connections are open except from looking at the remote server?

Is there any way of flushing the connections except from dropping all the tables or killing the connections at the remote database?

Transaction handling in federated tables via stored proc (3 replies)

GOPIKANTH GOVINDARAJ — Thu, 05 Mar 2015 06:34:16 +0000

I am using Stored Procedure to archive some data from few tables in master database to remote back up database. The back up tables are existing as federated tables in master tables.
However when i move the data from master to back up tables one by one, transaction is not working as expected as I see records are committed in remote back up database irrespective of issuing a rollback in the end of store proc on interpreting the error.

Eg: Say Tables A, B, C exist in master and back up database.
When records are moved from Master A, B, C to back up A, B, C, if some exception happens while processing Table C, i am issuing a rollback in the end, which doesn't rollback A,B tables.
I use START TRANSACTION, SET AUTOCOMMIT=0 in the beginning of store proc and issuing a COMMIT / ROLLBACK in the end based on processing status.

Please advice how to handle this scenario.

Rows are not getting deleted (no replies)

Muhammad Nawaz Sohail — Wed, 15 Oct 2014 08:03:52 +0000

Hi,

We do have federated tables implemented and while deleting rows from remote server it displays that 24-rows affected but the rows are not deleted from actual table.

Can anyone help?

Regards,
Nawaz

Make Federated Supported (no replies)

Shawn Ott — Thu, 09 Oct 2014 16:34:32 +0000

I am brand new to the DBA side of stuff here.
I have installed MySQL 5.6 and use Workbench.
I am trying to create tables on my local instance from the corporate server/datawarehouse.

My understanding is that I can use Federated Tables
When I run Show Engines, it says NO to Federated being supported.

How do I change this?
I saw one answer on a google search, but I cannot find my INI or MY.CNF files to added the federated line.

Thanks

Wait Federated tables are depricated? (no replies)

curtis pastore — Wed, 02 Apr 2014 22:16:18 +0000

Hi guys
My apologies if I've been living under a rock and this is old news, but, are federated tables going away?

If so, does anyone know where I can find arguments rational for doing so?

Aside from reading entire tables for read operations, I think as a "write to a remote source" utility, it works really well.

Hopefully someone was pulling an april fools joke on me but I wanted to go to the source. I couldn't find too much on it online (all searches just point to the mysql doc)

Anyway, anyones thoughts are greatly appriciated!
Thanks Much!
Kurt

BIT column protect Federated table from delete or update (no replies)

Xavier S — Wed, 28 Aug 2013 13:32:46 +0000

I tried doing a delete of some rows on a federated table that contains one column called `read` of type BIT.
Those ones having a 0 (zero) value on that column, where successful deleted, but the ones with a positive value (1000000) remained on the table.

Is it a desirable behavior?

I've tried later on the origin table (InnoDB engine) and all of them where deleted.

My MySQL version is 5.5.32-0ubuntu0.13.04.1

Thanks everyone

order by in query extremely slow (no replies)

Rogier IJperen — Tue, 23 Jul 2013 12:02:20 +0000

I have a table with federated engine which is linked to the same table on a remote server. All works very fine and quite fast. The table has sth like 1000 records and a primary key on the number.

When I do a select query, all works fine but when I order this query it takes up to 35 seconds to get the result. I think I can call this slow....

When I use the same query on the remote server: 0 seconds...

Thanks for any advise!

Rogier

Select Remote Server Database Table data From Select Query (no replies)

Ramaeshkumar k — Fri, 19 Jul 2013 14:36:28 +0000

hi all,
I m trying to access table which is in another server database.Consider server A and B. Just I need to do copy the Server A's Database Table Test to Server B's Database Table Test from Server B in Timely Manner for Some Report using by any query or store procedure.I know about Federated Table and also tried ,its working fine but i don't need to connect the federated table from remote server always.Please Reply as Soon as possible.

Thanks

Geometry field for federated table (1 reply)

David Babin — Mon, 03 Jun 2013 15:24:51 +0000

I'm afraid to see that FEDERATED engine doesn't support GEOMETRY field :
"The storage engine for the table doesn't support GEOMETRY"

I can't find any note about this limitation in federated, does somebody can confirm me ?

Thanks.

Problem creating a federated table (no replies)

Jacek Szymanski — Sat, 25 May 2013 20:24:48 +0000

Hi all,
I am trying to create a federated table. I am testing with a very simple table with only two fields. SHOW ENGINES shows that FEDERATED is enabled. I think I also have correct settings for the user which I am using for the user who connects because I am able to connect to the host database from the guest machine with -h option without problem.

Now the issue is that I CAN create the federated table and get no error. However, when I run a simple SELECT * from MYFEDERATEDTABLE; then the whole thing hangs and after a long while I get:
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '196.168.2.231' (110).

The interesting thing is that there is NO entry in the transaction log on the host database when the federated table is created (if I understand some websites about federated tables right there should be something there). I also get no error when the federated table is created and to what I have been able to read, there should be something if my syntax is incorrect.

So, to sum up: I have the table on host, the federated table on gues, the user but can not get it to work.

What can be the reason?

Best regards

Jacek

Error 1156 on federated table (no replies)

Ruaraidh Hamilton — Thu, 02 May 2013 00:53:52 +0000

Can anyone explain "Error Code: 1156. Got packets out of order" when querying a federated table? More details:

First on the remote server with an SSH connection:

/********************************************************/
CREATE TABLE `testremote`.`test` (
`c1` varchar(10) NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `testremote`.`test` (`c1`) VALUES ('a');
/********************************************************/

Then on the local server:

/********************************************************/
CREATE TABLE `test`.`test` (
`c1` varchar(10) NOT NULL,
PRIMARY KEY (`c1`)
) engine=federated DEFAULT CHARSET=utf8
CONNECTION='mysql://user:password@IP_address:port/testremote/test';

select c1 from test.test;
/********************************************************/

That final select generates the error.

I am using MySQL Workbench 5.2.39 CE. Variable version=5.5.28-log for the remote server, and 5.6.11-log for the local server. user is the "SSH Username", and IP_address:port is the SSH Hostname as shown in the "Connect to Database" window of MySQL Workbench

Changing connection param of Federated Table (Resolved) (no replies)

Nicholas Dovidio — Fri, 29 Mar 2013 10:57:32 +0000

I currently have one database with a federated table. The database it connects to recently had a host name change and I need to update the connection string. Based on the documentation, federated tables do not support ALTER TABLE. What is the suggested way to change the connection string?

thank you.

(EDIT) I realized I could simply reconstruct the federated table without changing the other table.

FEDERATED engine not supported (1 reply)

joel guich horcajo — Tue, 19 Mar 2013 20:49:43 +0000

Hi, my problem is that once activated the FEDERATED engine, run the commands "SHOW ENGINES" and the FEDERATED engine as unsupported too.

I am using a version of MySQL 5.5. In ubuntu 12.04.

I activate the FEDERATED engine like that:

in my my.cfg
under [mysqld]
write "FEDERATED"

Restart the service and nothing.

the command "SHOW ENGINES" continues to show:
FEDERATED engine as unsupported.

Thanks for your help and your time.

ERROR 1429 : Unable to connect to foreign data source: Can't connect to MySQL seto (no replies)

Pradeep Ganapathy — Sat, 16 Feb 2013 06:33:44 +0000

While i am using 'FEDERATED' Engine to sync with remote server, i am getting this following error.

"ERROR 1429 : Unable to connect to foreign data source: Can't connect to MySQL server"

Source Database version: mysql 5.5
Remote Database version: mysql 5.6 (Enterprise edition)
port # : 3306

Thanks,

Removal of federated tables (1 reply)

Andrew Nicholls — Mon, 10 Dec 2012 16:29:13 +0000

I have a database that has a number of federated tables which were no longer used. The host for the tables was taken down. Now I can't even open information_schema.tables to view the table names. I get an error "Unable to connect to foreign data source". Tools like AQT no longer work because they rely on data from information_schema. I tried issuing a DROP TABLE on the tables I knew of, but I am still unable to get to my schema.

MySQL 5.1.39 on Linux.

Any ideas?

How to enable/Activate Federated Engine in MYsql 5.5? (1 reply)

vasudhaika world — Sat, 15 Dec 2012 06:50:04 +0000

Could any one tell me the step by step procedure To enable the Federated Engine in mysql 5.5?

Thanks IN Advance,

Unable to connect to foreign data source (no replies)

Juan Nino — Fri, 07 Dec 2012 00:58:55 +0000

I have set the federated table successfully however when I try to query the federated table I have the following error

select i_account from Accounts;
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on '203.160.8.116' (13)

I have checked the auth details and they are correct. I have actually connected to the remote server using
mysql -u`user` -p`password `database` -h `host` -P`port`
from the linux console to confirm I have the right details
I have put a tcpdump in the local server to see if it sends any packet to the remote server when I execute the select query and it does not.

I have tried setting up the federated table using the full string in the connection attribute of the table and also with the create server command, both have the same unsuccessful result. do you have any ideas on what can I try next?

thanks

Storage Engine using MySQL Pro in Windows (no replies)

anderson jozaf — Fri, 23 Nov 2012 10:31:21 +0000

Degrassi The Next Generation Season 12 Episode 27 iCarly and Spencer are about to leave their apartment on April Fool's Day because they got evicted by Mr. Bushwell. Sam, Freddie, and Gibby arrive and Carly says that they all had good times in this apartment and remembers one time when they were all siting in the couch watching on TV an episode where the characters were doing flashbacks from past episodes (present in wacky iCarly Season 6 Episode 8 recreations and not actual footage from past episodes). By the end of the episode, Mr. Bushwell comes with Lewbert to tell them to leave the building. Then, Sam asks them to remember that time where they came there and changed their minds about Carly and Spencer leaving, which turns out to be a replay of the scene with Lewbert and Bushwell entering the door.

Mike Baxter, a director of Last Man Standing Season 2 Episode 4 marketing at an outdoor sporting goods store, returns home from traveling to a new assignment at work and to his house filled with women: his wife Vanessa, his oldest daughter Kristin (a single mother caring for her son in the Baxter family home), his middle daughter Mandy Whisker Wars Season 2 Episode 1 (an unintelligent, popularity-driven teenager) and his youngest daughter Eve (a tomboy inspired by her father). While Mike gives Eve advice for her soccer game, which results in her injuring a boy she has a crush on, he also plays Cupid to set up Kyle, a young employee at his store, with Mandy. However, Kyle mistakes Kristin for Mandy, and dates Kristin instead. Malibu Country Season 1 Episode 4

After finding out that her Watch Twilight Breaking Dawn Part 2 husband had an affair with a co-worker, Reba Mackenzie moves her, her family, and her mother to Malibu to a house a that she never knew husband had, to restart her music career that she left years ago so she could take care of her family, and let her husbands career to the spot light. After moving in, she immediatley meets her neighbor Kim, who Watch Twilight Breaking Dawn Part 2 comes off as a woman who can't keep anything to herself; After finally scheduling a meeting with a record producer, Reba goes down to only find out that she isn't what they are looking for because she isn't 'young and sexy' as the record producers assistant, Geoffrey, says. Reba soon realizes that Malibu is going to be a much different place, and a big adjust for her Nashville native family, as her daughter makes a new friend, Kim's step-son Sage, and her son adjusting to his girlfriend breaking up with him, not wanting a long distance Watch Twilight Breaking Dawn Part 2 relationship.

Kim finds an article in a magazine about Bobby, Reba's ex, saying that he was dating a woman, and shows it to Watch Twilight Breaking Dawn Part 2 Lillie insisting that they shouldn't show Reba, only for Reba to come around and Lillie to show her. When Cash brings over Sages' computer to him, he finds Sage kissing another girl, when Sage insists that he's gay, Cash threatens to tell June, implying that he can't be gay. Kim sets Reba up a man that she knows, saying she needs to get out of the house, with a talk from Lillie Reba agrees to go on the date. When Cash tells June about what he saw earlier, she tells him that she's fine with it; When Geoffrey stops by to get papers signed, Reba enlist his help to help pick out an outfit. Reba gets caught up talking to June about her feeling for Sage before her date; When she shows up Lillie and Kim are there and tell her that Watch Twilight Breaking Dawn Part 2 Tom believed that she stood him up and left. When Reba gets home she has to explain to Cash and June about her dating.

Federation perfomance much slower (1 reply)

Han So — Wed, 07 Nov 2012 16:15:15 +0000

We are finding performance of a federation query to be 4-5 times slower than running the query directly on the server. we have 2 instances running on the same server, where one is federating off the other. if you run select * from table a (fairly large table > 200K rows) on the federated server then it runs 5x slower than running it directly on the source server. why?