Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: How to connect to an old MySQL server with FireDac in XE7


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 6 - Last Post: Dec 3, 2014 10:26 AM Last Post By: Jorge Lopez
Jorge Lopez

Posts: 13
Registered: 9/12/01
How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2014 7:20 AM
I'm trying to connect to an old mysql server (pre-4.1) that does not support secure passwords, from a client machine that has libmysql.dll 6.1.5 client library, using FireDAC in DelphiXE7. The connection is refused BY THE CLIENT (not server), giving the error: [FireDac][Phys][MySQL] Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled).

On command line client of mysql, getting this to work is as simple as adding the --skip-secure-auth parameter. Also in MySQL Workbench you need to add in the connection settings:. useLegacyAuth=1.

However, in FireDAC I am unable to find a way to pass that parameter to client library. Adding "secure-auth=off" or "useLegacyAuth=1" or even "useLegacyAuth=True" to TFDConnection parameters does not work, that parameter is simply ignored.

Those old MySQL servers are common in hostings so I figure there's more people affected with this issue. The only way to solve this situation is using an old libmysql.dll (any of versión 4.X.X) library that has no implementation for the new secure passwords but in my case, the application is connecting at the same time to new and old mySQL servers so I want to benefit of the improvements in speed and security of the new libraries and not being obligued to use the old library for all connections.

So there's any way to solve this passing a parameter to the FireDac connection ?
Uwe Raabe

Posts: 180
Registered: 10/20/00
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2014 3:05 PM   in response to: Jorge Lopez in response to: Jorge Lopez
Am 02.12.2014 um 16:20 schrieb Jorge Lopez:
On command line client of mysql, getting this to work is as simple as adding the --skip-secure-auth parameter.

Have you simply tried --skip-secure-auth?

--
Uwe Raabe
Embarcadero MVP
Certified Delphi Master Developer
Uwe's Blog: The Art of Delphi Programming <http://www.uweraabe.de/>
Jorge Lopez

Posts: 13
Registered: 9/12/01
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2014 1:59 AM   in response to: Uwe Raabe in response to: Uwe Raabe
Uwe Raabe wrote:
Am 02.12.2014 um 16:20 schrieb Jorge Lopez:
On command line client of mysql, getting this to work is as simple as adding the --skip-secure-auth parameter.

Have you simply tried --skip-secure-auth?

--
Uwe Raabe
Embarcadero MVP
Certified Delphi Master Developer
Uwe's Blog: The Art of Delphi Programming <http://www.uweraabe.de/>

Sure, I've tried that and also: MYSQL_SECURE_AUTH=false and many others but always the same result. Maybe this option is not implemented in the FireDac driver ...
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2014 2:07 AM   in response to: Jorge Lopez in response to: Jorge Lopez
Most simple and almost correct solution will be to use libmysql.dll of the same
version as the MySQL server. Other option to try to patch the FireDAC sources:
- open FireDAC.Phys.MySQL.pas
- locate there TFDPhysMySQLConnection.InternalConnect method
- there find the lines:
    if FClientVersion >= mvMySQL050000 then
      uiClientFlag := uiClientFlag or FD_50_CLIENT_PROTOCOL_41 or CLIENT_MULTI_QUERIES or
        CLIENT_MULTI_RESULTS
    else if FClientVersion >= mvMySQL040100 then
      uiClientFlag := uiClientFlag or CLIENT_PROTOCOL_41 or CLIENT_MULTI_STATEMENTS;

- try to remove FD_50_CLIENT_PROTOCOL_41 and CLIENT_PROTOCOL_41 flags
- add path to FireDAC sources to IDE Library Path

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Jorge Lopez

Posts: 13
Registered: 9/12/01
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2014 4:42 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Most simple and almost correct solution will be to use libmysql.dll of the same
version as the MySQL server. Other option to try to patch the FireDAC sources:
- open FireDAC.Phys.MySQL.pas
- locate there TFDPhysMySQLConnection.InternalConnect method
- there find the lines:
    if FClientVersion >= mvMySQL050000 then
      uiClientFlag := uiClientFlag or FD_50_CLIENT_PROTOCOL_41 or CLIENT_MULTI_QUERIES or
        CLIENT_MULTI_RESULTS
    else if FClientVersion >= mvMySQL040100 then
      uiClientFlag := uiClientFlag or CLIENT_PROTOCOL_41 or CLIENT_MULTI_STATEMENTS;

- try to remove FD_50_CLIENT_PROTOCOL_41 and CLIENT_PROTOCOL_41 flags
- add path to FireDAC sources to IDE Library Path

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Hi Dmitry thanks for your time answering this question. I've tried the workarround but still not working, I've made the chanches you exposed and also added the FireDAC.Phys.MySQL.pas file to my sample project but still same message with the secure_auth issue.
I've tried different libmysql.dll libraries and I've found that the issue is happening from version 6.1.3 and up, so the last stable libmysql.dll version without the issue is 6.0.2 ( from 2009)

Sure there is no any other way to solve this ? The other question is can I have two different libmysql.dll libraries for the same application ?, so I can arrange one FDConnection with the old library and another with the new library for connecting to different servers.

Regards.
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2014 5:00 AM   in response to: Jorge Lopez in response to: Jorge Lopez
I've tried different libmysql.dll libraries and I've found that the issue is happening from version 6.1.3 and up, so the last stable libmysql.dll version without the issue is 6.0.2 ( from 2009)

Ok. Then as I sayd, just use an "old" libmysql.dll version.

Sure there is no any other way to solve this ?

I doubt, that there will be any ...

The other question is can I have two different libmysql.dll libraries for the same application ?
so I can arrange one FDConnection with the old library and another with the new library for connecting to different servers.

Yes, you can. You should use two virtual drivers:
http://docwiki.embarcadero.com/RADStudio/XE7/en/Configuring_Drivers_(FireDAC)

1) Using FDDrivers.ini:
[MySQL_old]
BaseDriverID=MySQL
VendorLib=<path to "old" libmysql.dll>
 
[MySQL_new]
BaseDriverID=MySQL
VendorLib=<path to "old" libmysql.dll>


Then use DriverID=MySQL_old to connect to "old" MySQL server. And MySQL_new to connect to "new" one.

2) Similar you can do with TFDPhysMySQLDriverLink.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Jorge Lopez

Posts: 13
Registered: 9/12/01
Re: How to connect to an old MySQL server with FireDac in XE7  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 3, 2014 10:26 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
I've tried different libmysql.dll libraries and I've found that the issue is happening from version 6.1.3 and up, so the last stable libmysql.dll version without the issue is 6.0.2 ( from 2009)

Ok. Then as I sayd, just use an "old" libmysql.dll version.

Sure there is no any other way to solve this ?

I doubt, that there will be any ...

The other question is can I have two different libmysql.dll libraries for the same application ?
so I can arrange one FDConnection with the old library and another with the new library for connecting to different servers.

Yes, you can. You should use two virtual drivers:
http://docwiki.embarcadero.com/RADStudio/XE7/en/Configuring_Drivers_(FireDAC)

1) Using FDDrivers.ini:
[MySQL_old]
BaseDriverID=MySQL
VendorLib=<path to "old" libmysql.dll>
 
[MySQL_new]
BaseDriverID=MySQL
VendorLib=<path to "old" libmysql.dll>


Then use DriverID=MySQL_old to connect to "old" MySQL server. And MySQL_new to connect to "new" one.

2) Similar you can do with TFDPhysMySQLDriverLink.

--
With best regards,
Dmitry Arefiev / FireDAC Architect

Ok, using two TFDPhysMySQLDriverLink each one pointing to the corresponding version of libmysql.dll solves the issue for me.

I've reviewed the mysql documentation about authentication ( http://dev.mysql.com/doc/internals/en/capability-flags.html and http://dev.mysql.com/doc/internals/en/determining-authentication-method.html) and clearly its a bug in the MySQL driver as with the changes proposed by Dmitry FireDac is not sending any flags forcing the new authentication method.

Thanks and regards.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02