Forum for Federated Storage Engine. http://forums.mysql.com/list.php?105 Fri, 24 May 2013 09:39:16 +0000 Phorum 5.2.19 http://forums.mysql.com/read.php?105,585281,585281#msg-585281 http://forums.mysql.com/read.php?105,585281,585281#msg-585281
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]]> Ruaraidh Hamilton Federated Storage Engine Thu, 02 May 2013 00:53:52 +0000 http://forums.mysql.com/read.php?105,582603,582603#msg-582603 http://forums.mysql.com/read.php?105,582603,582603#msg-582603
thank you.

(EDIT) I realized I could simply reconstruct the federated table without changing the other table.]]> Nicholas Dovidio Federated Storage Engine Fri, 29 Mar 2013 10:57:32 +0000 http://forums.mysql.com/read.php?105,581760,581760#msg-581760 http://forums.mysql.com/read.php?105,581760,581760#msg-581760
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.]]> joel guich horcajo Federated Storage Engine Tue, 19 Mar 2013 20:49:43 +0000 http://forums.mysql.com/read.php?105,579276,579276#msg-579276 http://forums.mysql.com/read.php?105,579276,579276#msg-579276
"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,]]> Pradeep Ganapathy Federated Storage Engine Sat, 16 Feb 2013 06:33:44 +0000 http://forums.mysql.com/read.php?105,577782,577782#msg-577782 http://forums.mysql.com/read.php?105,577782,577782#msg-577782
I would like to copy one federated table because I can't Alter it.

This is what I want to do:
CREATE TABLE produit LIKE general.produit;
ALTER TABLE produit ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://login:pwd@127.0.0.1/general/produit';

When I do that, table is copied but I have an error for the Alter Table:
#1031 - Table storage engine for 'produit' doesn't have this option

I try in one line but it doesn't work.

CREATE TABLE produit LIKE general.produit;
ALTER TABLE produit ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://login:pwd@127.0.0.1/general/produit';

I hope someone can help me !

Thanks !]]> thibaut heudelot Federated Storage Engine Wed, 23 Jan 2013 17:33:05 +0000 http://forums.mysql.com/read.php?105,575752,575752#msg-575752 http://forums.mysql.com/read.php?105,575752,575752#msg-575752
MySQL 5.1.39 on Linux.

Any ideas?]]> Andrew Nicholls Federated Storage Engine Mon, 10 Dec 2012 16:29:13 +0000 http://forums.mysql.com/read.php?105,575503,575503#msg-575503 http://forums.mysql.com/read.php?105,575503,575503#msg-575503



Thanks IN Advance,]]> vasudhaika world Federated Storage Engine Sat, 15 Dec 2012 06:50:04 +0000 http://forums.mysql.com/read.php?105,575467,575467#msg-575467 http://forums.mysql.com/read.php?105,575467,575467#msg-575467
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]]> Juan Nino Federated Storage Engine Fri, 07 Dec 2012 00:58:55 +0000 http://forums.mysql.com/read.php?105,574467,574467#msg-574467 http://forums.mysql.com/read.php?105,574467,574467#msg-574467 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.]]> anderson jozaf Federated Storage Engine Fri, 23 Nov 2012 10:31:21 +0000 http://forums.mysql.com/read.php?105,570015,570015#msg-570015 http://forums.mysql.com/read.php?105,570015,570015#msg-570015 Hans Won Federated Storage Engine Wed, 07 Nov 2012 16:15:15 +0000 http://forums.mysql.com/read.php?105,563525,563525#msg-563525 http://forums.mysql.com/read.php?105,563525,563525#msg-563525
Are there any good alternatives.

MySQL has a replication feature. If that could be extended, so I could pin point one table to be replicated.
I know that this is possible - however I already run a master master replication on the server(s) that needs the federaded table.

A storage engine that looks into the binlog on a master machine, would do exactly what I need.]]> Kjeld Flarup Federated Storage Engine Wed, 01 Aug 2012 08:32:32 +0000 http://forums.mysql.com/read.php?105,548968,548968#msg-548968 http://forums.mysql.com/read.php?105,548968,548968#msg-548968 Jacky Shu Federated Storage Engine Mon, 04 Jun 2012 15:37:49 +0000 http://forums.mysql.com/read.php?105,513362,513362#msg-513362 http://forums.mysql.com/read.php?105,513362,513362#msg-513362

I added the following line to the [mysqld] section of /etc/my.cnf:
federated=1

I added --federated to the startup options for mysqld. Here are the active running processes:

# ps -ef | grep mysqld
root 2456 1 0 11:30 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --user=mysql --federated
mysql 2511 2456 0 11:30 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock --federated

However, it appears that federated is still not available:

mysql> 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 |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

So, I need some guidance on what else needs to be done.

Thanks in advance.]]> Derek Wickersham Federated Storage Engine Thu, 02 Feb 2012 10:39:28 +0000 http://forums.mysql.com/read.php?105,507889,507889#msg-507889 http://forums.mysql.com/read.php?105,507889,507889#msg-507889
I solved my problem like this:


install plugin federated soname 'ha_federated.so'

after this federated engine will appear when you try 'show engines;'

now, all you have to do is edit /etc/my.cnf and add 'federated' in the [mysqld] area.

Cheers,
Andi.]]> Andi Constantin Federated Storage Engine Fri, 06 Jan 2012 13:08:29 +0000 http://forums.mysql.com/read.php?105,504811,504811#msg-504811 http://forums.mysql.com/read.php?105,504811,504811#msg-504811
it work but I find it pointless cause I can't call stored procedure/function on the remote server.

I have a question. if I run a stored procedure in my local server in which using tables in remote server , what will happen?

my assumption is that the local server will retrieve all records in the remote table and process it in local server when the stored procedure execute .

is this going to happen ?

is there such things as federated database?]]> charles candy Federated Storage Engine Tue, 06 Dec 2011 12:27:41 +0000 http://forums.mysql.com/read.php?105,490711,490711#msg-490711 http://forums.mysql.com/read.php?105,490711,490711#msg-490711
I have 3 databases: A, B, C. On database B there are some federated tables associated with tables of database A. This works fine.

Database C is a replication of database B. But when I execute a select in a federated table on database C it brings empty result. I created on database A, 2 users with the same login and password, first with host = database B and second with host = database C. And for both I grant select privilege.

Am I missing something?
If anyone could help me, I'll be grateful.

Thanks,
Daniele]]> Daniele Ivasse Federated Storage Engine Tue, 25 Oct 2011 19:29:53 +0000 http://forums.mysql.com/read.php?105,431408,431408#msg-431408 http://forums.mysql.com/read.php?105,431408,431408#msg-431408 ie, whether there is a possibility to specify the master and failover server details while creating a federated table.

I have two remote master servers (with master-master replication)

Remote Master 1 : 192.168.1.1
Remote Master 2 : 192.168.1.2
Local Server : 192.168.1.3

If one master server is down, i will be able to connect to the other master.

I am tryting to create a federated table in the local server:

CREATE SERVER mymaster
FOREIGN DATA WRAPPER mysql
OPTIONS
(
HOST '192.168.1.1',
DATABASE 'masterdb',
USER 'myuser',
PASSWORD 'mypassword',
PORT 3306
);

CREATE TABLE `my_table`
(
my_column INT
) ENGINE=FEDERATED CONNECTION='mymaster';

In the above statement, the connection is created only to obe of the server in the master-master pair.

Is there any option to specify two servers while creating the server, or while creating the federated table ?]]> Minto Mohan Federated Storage Engine Mon, 22 Aug 2011 05:19:20 +0000 http://forums.mysql.com/read.php?105,430074,430074#msg-430074 http://forums.mysql.com/read.php?105,430074,430074#msg-430074
Thanks]]> Carlos Martinez Federated Storage Engine Tue, 09 Aug 2011 23:13:04 +0000 http://forums.mysql.com/read.php?105,424498,424498#msg-424498 http://forums.mysql.com/read.php?105,424498,424498#msg-424498 If there isn't a way, then could be a way to set the server to close auto?

Thanks for your help]]> Carlos Martinez Federated Storage Engine Thu, 23 Jun 2011 18:47:15 +0000 http://forums.mysql.com/read.php?105,409074,409074#msg-409074 http://forums.mysql.com/read.php?105,409074,409074#msg-409074 i want create a fedrated table with following connection option,
problem is, "Access denied for user" because the target dababase requests socket information "/tmp/mysql5.sock", otherweise the connection will be denied.
but for creating a fedrated table, where can i put the socket information?
PS: Mysql Version 5.0.67
---------------------------------------------
CREATE TABLE `xxx_history` (
`id` int(10) unsigned NOT NULL auto_increment,
`clientid` int(11) NOT NULL default '0',
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://user:passowrt@localhost/databasename/xxx_history';
------------------------------------------------]]> z.. zz Federated Storage Engine Wed, 23 Feb 2011 10:15:31 +0000 http://forums.mysql.com/read.php?105,404933,404933#msg-404933 http://forums.mysql.com/read.php?105,404933,404933#msg-404933 I'm maintaining replica of a table in first system in second system using federated concept. but now i want third system data to automatically updated to second system.

If i'm altering table structure in second system (alter table table_name ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION = 'mysql://username:password@hostname:3306/db_name/tablename';), it is giving error mysql error 1031: Table storage engine for 'table_name' doesn't have this option.


Regards
Susmitha]]> susmitha m Federated Storage Engine Thu, 27 Jan 2011 12:39:33 +0000 http://forums.mysql.com/read.php?105,399336,399336#msg-399336 http://forums.mysql.com/read.php?105,399336,399336#msg-399336 I am using MySQL 5.4 DB. I googled a lot and searched in forums also for enabling FEDERATED storage engine which is in disabled mode by default.

When i am executing query regarding Federated engine, it is showing error "Unknown storage engine".

query i executed is as:
CREATE TABLE `federated`.`federated_table1` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
`other` int(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `other_key` (`other`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://user1:user1@localhost:3306/federated/test_table';]]> susmitha m Federated Storage Engine Fri, 28 Jan 2011 07:11:24 +0000 http://forums.mysql.com/read.php?105,395042,395042#msg-395042 http://forums.mysql.com/read.php?105,395042,395042#msg-395042 remote ip : 192.168.5.6

I tried to practice from this source http://dev.mysql.com/doc/refman/5.0/en/federated-use.html. I created two tables like the instructions.

on a remote server:
CREATE TABLE test_table (
id INT (20) NOT NULL AUTO_INCREMENT,
name VARCHAR (32) NOT NULL DEFAULT'',
other INT (20) NOT NULL DEFAULT '0 ',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE = MyISAM
DEFAULT charset = latin1;

on the local server:
CREATE TABLE federated_table (
id INT (20) NOT NULL AUTO_INCREMENT,
name VARCHAR (32) NOT NULL DEFAULT'',
other INT (20) NOT NULL DEFAULT '0 ',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE = Federated
DEFAULT charset = latin1
CONNECTION = 'mysql: / / root@192.168.5.6: 9306/federated/test_table';

but after the command is executed an error occurs: Unable to the connect to foreign data source: Can not the connect to MySQL server on '192 .168.5.6 '(10 061)
(2921 sec)

what is wrong and what's the solution?
thank you]]> Dedy Black Federated Storage Engine Wed, 24 Nov 2010 19:03:22 +0000 http://forums.mysql.com/read.php?105,392163,392163#msg-392163 http://forums.mysql.com/read.php?105,392163,392163#msg-392163
I am not a php guy.

what I want to know about is as under

I have two different scripts installed at separate sub-domains

Now I want that user of one script should be able to get login at other with the same login data.

or in other words once you register you may have access to all scripts or websites.

in my case one is classified script and the other is file sharing script.

both being at one place does not make sense but I installed both for test purpose only.

also if you can please let me know how more than two databases or scripts can be attached in terms of login

***** I am not a developer or programmer ***** please answer as I am newbie to mysql data bases]]> abubakar madni Federated Storage Engine Thu, 28 Oct 2010 22:35:40 +0000 http://forums.mysql.com/read.php?105,391945,391945#msg-391945 http://forums.mysql.com/read.php?105,391945,391945#msg-391945
ERROR 1429 (HY000): Unable to connect to foreign data source: Can't connect to MySQL server on 'remotehost' (13)


I'm getting this error when trying to select data from a federated table.
The local server connects successfully to other federated tables on different servers, but this one is not working.

I can successfully connect from the local server to the remote server via

mysql -u myuser -p -h remotehost -P 33306 (Yes, that's the port I'm using)

The ENGINE statement on the CREATE TABLE looks like this:
ENGINE=FEDERATED
CONNECTION='mysql://myuser:mypass@remotehost:33306/remotedb/remotetable'

Any clue? looks like accepts connections but not from federated tables...

Thanks in advance!]]> Pedro Ten Federated Storage Engine Sat, 30 Oct 2010 14:41:32 +0000 http://forums.mysql.com/read.php?105,384861,384861#msg-384861 http://forums.mysql.com/read.php?105,384861,384861#msg-384861
My Database has 2 tables. One of them is InnoDB, the other is a Federated table that is linked to an InnoDB table in another database on the same server.

If I run an update like this, MySQL actually reboots!

update fedtable, innotable set fedtable.field = 1 where fedtable.ID = innotable.ID and innotable.Field = "XYZ"

Have I set something up wrong, or is this a bug?]]> Tyson Lowery Federated Storage Engine Thu, 09 Sep 2010 18:32:39 +0000 http://forums.mysql.com/read.php?105,384565,384565#msg-384565 http://forums.mysql.com/read.php?105,384565,384565#msg-384565 Sachin C Federated Storage Engine Wed, 08 Sep 2010 07:23:43 +0000 http://forums.mysql.com/read.php?105,375950,375950#msg-375950 http://forums.mysql.com/read.php?105,375950,375950#msg-375950
Thanks,
Mike]]> Mike D Federated Storage Engine Tue, 13 Jul 2010 00:29:13 +0000 http://forums.mysql.com/read.php?105,369282,369282#msg-369282 http://forums.mysql.com/read.php?105,369282,369282#msg-369282 I have read various documentation and followed relevant entries in various forums. It is somewhat frustrating that a lot of the questions on this same topic lack replies.

I have done targeted searches on google but cannot find a solution that works.
I have tried this on the command line:
mysqld start --federated
... then gone into mysql, showed engines and FEDERATED still has "Support = No" in the list of engines.

I tried putting "federated" below [mysqld] in the my.cnf file, then restarted the mysql server, same result.

Would really appreciated receiving any solutions or leads on this subject.

Thanks.]]> Ed VdeDios Federated Storage Engine Thu, 27 May 2010 02:58:51 +0000 http://forums.mysql.com/read.php?105,369190,369190#msg-369190 http://forums.mysql.com/read.php?105,369190,369190#msg-369190
I am using MySQL 5.1.46 and facing some issues in federated table. Let me give you brief description of it.

Server A has table test_table1(Federated) and test_table1 (InnoDB)

Server B has table test_table1(InnoDb) and test_table1 (InnoDB)

When I insert data in test_table1 on Server A I get foreign key fail message as both test_table2 has foreign key to test_table1 on Server A. Please note that record already (Parent record) exists on test_table1 on Server A.

When I disable foreign key check then it works fine. Please let me know if Federated storage engine supports foreign keys fully or not.

Regards,

Shirazi]]> Asad Raza Federated Storage Engine Wed, 26 May 2010 13:49:41 +0000