0

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:[email protected]/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:[email protected]/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:[email protected]/mptest/fedTransfer'' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
---------------------------------------------------
Improve this question
2
  • What version of MySQL is this ???
    – RolandoMySQLDBA
    Commented Jul 19, 2013 at 18:43
  • The version is mysql 5.1.
    – biz14
    Commented Jul 19, 2013 at 18:45

1 Answer 1

Reset to default
1

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:[email protected]/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:[email protected]/mptest/fedTransfer';

Give it a Try !!!

Improve this answer
9
  • 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
    Commented Jul 19, 2013 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
    Commented Jul 19, 2013 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
    Commented Jul 19, 2013 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
    Commented Jul 19, 2013 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
    Commented Jul 19, 2013 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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