Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create a federated table on my local machine which suppose to connect to my another machine which also in my local network. I have enabled federated engine on both machines and is shown when I run this command show engines.

Below is my syntax.

CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) ) 
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Below is the error I get when I run this on sqlyog.

Error Code: 1
Can't create/write to file 'server name: '' doesn't exist!' (Errcode: 1408685900)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.047 sec
---------------------------------------------------

Query: COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
---------------------------------------------------
share|improve this question
    
What version of MySQL is this ??? –  RolandoMySQLDBA Jul 19 '13 at 18:43
    
The version is mysql 5.1. –  biz14 Jul 19 '13 at 18:45

1 Answer 1

up vote 1 down vote accepted

OBSERVATION #1

According to the Book

MySQL 5.0 Certification Study Guide

Page 434 shows an example using COMMENT to establish a federated connection. Unfortunately, the book was written when MySQL 5.0.13 was released. Now, you use CONNECTION instead of COMMENT,

Here are my past answers (over a year ago) where I use CONNECTION instead of COMMENT:

OBSERVATION #2

You have this:

CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) ) 
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Remove the semicolon from the back of the word FEDERATED

EPILOGUE

Your code should look like this:

CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) ) 
ENGINE=FEDERATED
CONNECTION='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Give it a Try !!!

share|improve this answer
    
I just replace the word comment to connection and it created it without any error. But the remote machine must be power up rite? Another thing if remote machine I put the table engine as innodb is it fine or should I change to myisam? –  biz14 Jul 19 '13 at 18:58
    
My next step is that once the data is insert on my local machine and on the remote machine I want to trigger on insert to update some other table is that possible? –  biz14 Jul 19 '13 at 19:01
1  
Federated tables only work with MyISAM. No other storage engines. Before Sun bought MySQL and Oracle bought Sun, MySQL AB was planning to have the federate storage engine talk to different types of databases, including Oracle. Now than Oracle is the grandfather of MySQL, I don't believe the federated storage engine will be worked on to connect outside of MyISAM tables. –  RolandoMySQLDBA Jul 19 '13 at 19:01
    
You cannot define a trigger on a federated table, but you can make a trigger on a real table insert to or update a federated table. –  RolandoMySQLDBA Jul 19 '13 at 19:04
1  
Since MyISAM is not transactional, there are no rollback mechanisms to protect data from trickling over to the federated table. If you must have distributional transactional behavior, think about using Percona XtraDB Cluster. –  RolandoMySQLDBA Jul 19 '13 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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