MySQL Bugs: #14810: Password cannot contain @ in federated connection string

Bug #14810	Password cannot contain @ in federated connection string
Submitted:	10 Nov 2005 3:21	Modified:	25 Jan 2007 17:26
Reporter:	Rob Blick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.0.17-BK, 5.0.15 max	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
The connection string in a federated table creation cannot contain a @ character in the user's password.

How to repeat:
create table F(a int) ENGINE=FEDERATED CONNECTION = "mysql://test:p@ss@hostname.tld/test/T";

yields:

ERROR 1432 (HY000): Can't create federated table. The data source connection string 'mysql://test:p@ss@hostname.tld/test/T' is not in the correct format

Suggested fix:
Allow the @ character to be escaped, or at least annotate this in the docs (I imagine other chars will also cause a problem, such as / and :.  Perhaps the connection string could be specified as: "mysql://'username':'password'@'hostname.tld'/db/table"?

Thank you for a bug report. Verified just as described on 5.0.17-BK build (ChangeSet@1.1957, 2005-11-09 20:31:01+03:00, konstantin@mysql.com):

mysql> desc tt;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
| show  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0,01 sec)

mysql> create table ftt(c1 int, `show` int) engine=federated connection='mysql://user1:p@ss@Fedora/test/tt';
ERROR 1432 (HY000): Can't create federated table. The data source connection string 'mysql://user1:p@ss@Fedora/test/tt' is not in the correct format

There is no way to use such a passwords described in the manual (http://dev.mysql.com/doc/refman/5.0/en/federated-use.html).

This is fixed with latest push of WL #3031. Use the new servers table. The URL format will not allow '@', but the servers table will. I will provide docs team with information on WL #3031 and how it is used.

The documentation has been updated with the CREATE SERVER syntax, which enables you to create a server connection that can include an @ sign in the password. There is also a note about this limitation and the workaround in the documentation for the FEDERATED engine.

Note that this occurs anytime you use a URL connection string, from ANY connector program and to ANY MySQL table type. In my case I was using the ADOdb library for PHP to connect to an MyISAM table. This isn't limited to just the Federated table type, but rather to the URL connection scheme in general. AFAIK there is no way to escape these "special" characters in any connector program.