Mysql federated engine table creation error

MySQLstorage-engine

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
---------------------------------------------------

Best Answer

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 !!!

Related Question