Connect to MySQL Server (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to MySQL Server.

Supported Versions

The FireDAC native driver supports:

  • MySQL Server Community, Enterprise, and Embedded editions, from version 3.21 up to version 8.0.23.
  • MariaDB, from version 5.5 up to version 10.6. Also, RAD Studio 11.1 added support for 10.6 server with a 10.6 client.

Client Software

Windows Client Software

Tip: RAD Studio IDE works in 32-bit, that is why it cannot connect with MySQL 8.0, wich is only available in 64-bit. To avoid this connection issue you can use MySQL 5.7.29, which is the most recent 32-bit version of MySQL.

FireDAC requires one of the following libraries:

  • The LIBMYSQL.DLL client library for connecting to MySQL server.

You can take them from a server (details) installation Bin or Lib folders and place them in:

  • a folder listed in your PATH environment variable (for example, <Windows>\SYSTEM32)
  • your application EXE folder.

To connect to a 64-bit version of MySQL 8.0 at runtime you need:

  • LIBMYSQL.DLL.
  • VS2015 runtime 64-bit.
  • Two libraries from MySQL MSVC++ Connectors pack of the 8.0 version:
  • LIBCRYPTO-1_1-x64.DLL.
  • LIBSSL-1_1-x64.DLL.
Note: If you use a 64-bit SQL server, the client DLLs will also be 64-bit. To connect at design time or to use this server with a 32-bit application, download 32-bit DLLs from http://dev.mysql.com/downloads/connector/c/#downloads

Alternatively you may put the required files in any other folder, and specify their path in FDDrivers.ini: 

[MySQL]
VendorLib=<folder>\libmysql.dll

If the MySQL client library has not been installed properly, an exception is raised when you try to connect:

[FireDAC][Phys][MySQL]-314. Cannot load vendor library [libmysql.dll]. The specified module could not be found. Check [libmysql.dll], which is located in one of the PATH directories or in application EXE directory.

macOS Client Software

FireDAC requires:

  • the libmysqlclient.dylib x86 client library.

You can download it as Connector/C for macOS (here) and extract it into /usr/local folder, using the command: 

sudo tar -C /usr/local -zxvf mysql-connector-c-6.1.6-osx10.8-x86.tar.gz

iOS Client Software

The article (more) explains how to build libmysqlclient.dylib for iOS.

Note, Embarcadero Technologies has not tested this and does not provide assistance with it.

Linux Client Software

To install the MySQL client library:

  1. On Ubuntu Server 16.04 LTS, run: 
    sudo apt-get install libmysqlclient20
sudo ln -s /usr/lib/x86_64-linux-gnu/libmysqlclient.so.20 /usr/lib/x86_64-linux-gnu/libmysqlclient.so
  2. On Red Hat Enterprise Linux 7, run: 
    sudo yum install mysql-libs
sudo ln -s /usr/lib64/mysql/libmysqlclient.so.18 /usr/lib64/mysql/libmysqlclient.so

Driver Linkage

To link the driver:

Connection Definition Parameters

To connect to a MySQL DBMS, most applications require that you specify DriverID, Server, Database, User_Name, Password, and CharacterSet (see Defining Connection (FireDAC) for details).

DriverID=MySQL

Parameter Description Example value
Server The TCP/IP address or host name of the server running a MySQL server. 127.0.0.1
Port The TCP/IP port on which the MySQL server is listening. 3306
Database Name of the current database for the connection. If the Database is not specified, no current database is set up. MyDB
User_Name The MySQL user ID. root
Password The MySQL user password.
CharacterSet The default character set for the connection. The connection collation becomes the default collation of the character set. For details, see the SET NAMES statement. cp1251
Compress Specify True to enable network traffic compression. By default, it is set to False.
UseSSL

Specify True to enable SSL connection. By default, it is set to False. Setting UseSSL=True requires you to additionally specify the connection definition parameters:

  • SSL_key -- is the path name to the key file.
  • SSL_cert -- is the path name to the certificate file.
  • SSL_ca -- is the path name to the certificate authority file.
  • SSL_capath -- is the path name to a directory that contains trusted SSL CA certificates in pem format.
  • SSL_cipher -- is a list of permissible ciphers to use for SSL encryption.

For additional details, see the MySQL Documentation:

True
LoginTimeout Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 30
ReadTimeout The time-out in seconds for attempts to read from the server. Each attempt uses this time-out value and there are retrials if necessary, so the total effective time-out value is three times the option value. You can set the value so that a lost connection can be detected earlier than the TCP/IP Close_Wait_Timeout value of 10 minutes. This option works only for TCP/IP connections and, prior to MySQL 5.1.12, only for Windows. By default, it is not set. 5
WriteTimeout The time-out in seconds for attempts to write to the server. Each attempt uses this time-out value and there are net_retry_count retrials if necessary, so the total effective time-out value is net_retry_count times the option value. This option works only for TCP/IP connections and, prior to MySQL 5.1.12, only for Windows. By default, it is not set. 5
ResultMode

Controls the fetching of a result set to a client. The default value is Store.

  • Store -- fetches all rows right after query execution and stores them on a client, making the server process ready for new requests.
  • Use -- rows are fetched on demand from a client. Until all rows from the result set are fetched, the server cannot handle new requests. It allows you to reduce memory usage on very big result sets.
  • Choose -- FireDAC automatically chooses a mode. If the FetchOption.Mode is either fmAll or fmExactRecsMax, then "Store", otherwise "Use".
 Use
TinyIntFormat

Controls the TINYINT(1) representation. The default value is Boolean.

  • Boolean - TINYINT(1) columns are represented as dtBoolean.
  • Integer - TINYINT(1) columns are represented as dtSByte or dtByte.
 Integer
MetaDefCatalog Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. Setting the MetaDefCatalog does not change the current database in the MySQL session. MyDB

MySQL Embedded Server

Important:

  • All path argument values use Unix backslashes. You can specify paths relatively to the application EXE folder, which simplifies the deployment.
  • libmysqld.dll and errmsg.sys must be of the same version.
  • libmysqld.dll v 5.1.34, as probably some other versions, are broken and do not work. Update to other version.
  • The general MySQL Embedded setup may be hard. For simplified setup, see examples later.
  • In case of incorrect arguments for libmysqld.dll initialization, the application may silently terminate.

To prepare your application to work with a MySQL Embedded server, you should take the following actions:

  • Include the TFDPhysMySQLDriverLink component in your application. Note that "add argument" means "add argument to TFDPhysMySQLDriverLink.EmbeddedArgs list". Optionally you can use the driver configuration file.
  • Copy LIBMYSQLD.DLL to the application executable folder. If this folder differs from the application executable folder, add the --basedir=<LIBMYSQLD folder> argument and set TADPhysMySQLDriverLink.VendorLib to <LIBMYSQLD folder>\LIBMYSQLD.DLL.
  • If only English messages are used, copy share\english\errmsg.sys to <LIBMYSQLD folder> and add the --language=<LIBMYSQLD folder> argument. If multiple language messages are used, copy the appropriate folders from share\* to <LIBMYSQLD folder>, preserving the directory structure. There is no need to add the --language argument, because <LIBMYSQLD folder>\share\* is the default location.
  • If the used character sets are different from ASCII, copy share\english\charsets to <LIBMYSQLD folder>, preserving the directory structure. There is no need to add the --character-sets-dir argument, because <LIBMYSQLD folder>\share\charsets is the default location.
  • If the database files are located in different folders than <LIBMYSQLD folder>, add the --datadir=<database files folder> argument. With 5.6.11 it is required to specify this argument.
  • If the InnoDB engine is not used, add the --skip-innodb argument. Otherwise, add the --innodb_data_home_dir=<database files folder> argument, where the value is the InnoDB data space directory.
  • If your application does not connect to a remote MySQL server, add the --skip-networking argument.
  • If your application uses an external settings file, add the --defaults-file=my.ini argument.
  • If your application uses MySQL plugins, add the --plugin_dir=<plugin folder> argument. Normally, plugins are located in <LIBMYSQLD folder>\lib\plugin.
  • Add appropriate arguments to tune performance.


To connect to the MySQL Embedded server, you do not need to specify Server, Host, Port, User_Name, Password connection definition parameters. If you do not specify the --skip-networking argument, then using libmysqld.dll you can connect to the remote MySQL servers, the same as with normal libmysql.dll.

Simple setup example:

  • Charsets: ASCII only
  • Messages: English only
  • Database: the EXE folder
App folder:
  <app>.EXE
  LIBMYSQLD.DLL
  errmsg.sys
  <DB files>
Arguments:
  --datadir=./
  --language=./
  --skip-innodb
  --skip-networking

Extended setup example:

  • Charsets: multiple
  • Messages: multiple
  • Database: "data" subfolder
App folder:
  <app>.EXE
  LIBMYSQLD.DLL
  share\*
  share\charsets
  data\*
  data\<DB files>
Arguments:
  --datadir=./data
  --skip-innodb
  --skip-networking

Usage Cases

Using a Secure Connection

To use TLS/SSL encryption, define the UseSSL parameter with True as value and then specify the SSL_key, SSL_cert, and SSL_ca parameters as follows: 

DriverID=MySQL
Server=mysrv
Port=3307
Database=addemo
UseSSL=True
SSL_ca=ca-cert.pem
SSL_cert=client-cert.pem
SSL_key=client-key.pem

You may also use the SSL_capath and SSL_cipher parameters, see the UseSSL entry in the Connection Definition Parameters table above for more information.

Note: You can also use the my.cnf configuration file.

Other Usage Cases

  • Connect to a locally running server, listening on the default (3306) port:
DriverID=MySQL
Database=addemo
User_Name=root
Password=
  • Connect to a remote server, listening on a non-default port, using Unicode for character data:
DriverID=MySQL
Server=mysrv
Port=3307
Database=addemo
CharacterSet=utf8
User_Name=me
Password=123
  • Connect to an embedded server, using Unicode for character data:
DriverID=MySQL
Database=addemo
CharacterSet=utf8

See Also

Samples

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Athens/e/index.php?title=Connect_to_MySQL_Server_(FireDAC)&oldid=273539"