Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2081
  • Last Modified:

Setting up FEDERATED Table recieve MySQL Database Error: Unable to connect to foreign data source:

Setting up a FEDERATED storage ENGINE to link a masted and remote table
I get the following error MySQL Database Error: Unable to connect to foreign data source: Unknown MySQL server host
when inserting into master, both master and remote then error out and can not drop table
## Create TABLE on Master
 
CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  `name` 
varchar(255) DEFAULT NULL,
  `description` text,
  `created_by_id` int(11) DEFAULT NULL,
  
`updated_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime 
DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT 
CHARSET=latin1;
 
 
## Create TABLE on Remote
 
CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  `name` 
varchar(255) DEFAULT NULL,
  `description` text,
  `created_by_id` int(11) DEFAULT NULL,
  
`updated_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime 
DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT 
CHARSET=latin1;
 
 
## Create Federated in Master
 
CREATE TABLE `events_F` (
  `id` int(11) NOT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `created_by_id` int(11) DEFAULT NULL,
  `updated_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://<user>:<password>@<host>:3306/Edge/events'
Commit;
 
## Create Trigger on Master
 
CREATE TRIGGER `Core`.`Events_insert` After INSERT
    ON Core.events FOR EACH ROW
BEGIN
insert into Core.events_F values
(
NEW.id,
NEW.event_type,
NEW.name,
NEW.description,
NEW.created_by_id,
NEW.updated_by_id, 
NEW.created_at,
NEW.updated_at,
NEW.vendor_id);
END;
Commit;

Open in new window

0
IcueTV
Asked:
IcueTV
  • 2
1 Solution
 
Tomas Helgi JohannssonCommented:
   Hi!

This can be due to several reasons
1) remote hostname is unknown to the calling host. -> Solved by altering dns records or adding the IP and hostname to hosts file.
2) Firewall is blocking the caller host to connect to the remote host.
3) Security in mysql on  the remote host does not allow the calling host to connect.

http://dev.mysql.com/doc/refman/5.0/en/federated-use.html
http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html
http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html
http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html
http://dev.mysql.com/doc/refman/5.0/en/server-administration.html

Regards,
    Tomas Helgi
0
 
Tomas Helgi JohannssonCommented:
Did my earlier comment help you ?

Regards,
    Tomas Helgi
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now