creating Federated mysql table with username password and host

Question

My db password is Dhanu@1 contains @,

While creating FEDERATED table, I'm getting error like

can't create federeted table. The data source connection string mysql://dhanu:Dhanu@1@localhost:3306/mydb/items is not in the correct format

Please help me!

Answer 1

Usage of character as like these @ is not suggested in passwords and a similar discussion on this issue is available here.

You can work-around the above limitation by using CREATE SERVER statement as shown in the example below:

CREATE SERVER federatedTablelink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'USERNAME', HOST 'Host_IP', DATABASE 'DB_NAME', 
PORT '3306',Password 'PASSWORD');

Once the server Link is created using the step above, you can use the following to create table that uses this connection:

CREATE TABLE test (
id     INT(20) NOT NULL AUTO_INCREMENT,
name   VARCHAR(32) NOT NULL DEFAULT '',
PRIMARY KEY  (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='federatedTablelink/test';

MySQL documentation that refers to similar topics are as follows:

https://dev.mysql.com/doc/refman/5.6/en/federated-usagenotes.html

https://dev.mysql.com/doc/refman/5.1/en/federated-create.html