MySqlConnector


Connection Options

MySqlConnector supports a subset of Oracle’s Connector/NET connection options.

Base Options

These are the basic options that need to be defined to connect to a MySQL database.

Name Default Description
Host, Server, Data Source, DataSource, Address, Addr, Network Address localhost The host name or network address of the MySQL Server to which to connect. Multiple hosts can be specified in a comma-delimited list.
On Unix-like systems, this can be a fully qualified path to a MySQL socket file, which will cause a Unix socket to be used instead of a TCP/IP socket. Only a single socket name can be specified.
Port 3306 The TCP port on which MySQL Server is listening for connections.
User Id, UserID, Username, Uid, User name, User The MySQL user ID.
Password, pwd The password for the MySQL user.
Database, Initial Catalog (Optional) The case-sensitive name of the initial database to use. This may be required if the MySQL user account only has access rights to particular databases on the server.
Protocol, ConnectionProtocol, Connection Protocol Socket How to connect to the MySQL Server. This option has the following values:
  • Socket (default): Use TCP/IP sockets.
  • Unix: Use a Unix socket.
  • Pipe: Use a Windows named pipe.
Pipe, PipeName, Pipe Name MYSQL The name of the Windows named pipe to use to connect to the server. You must also set ConnectionProtocol=pipe to used named pipes.

SSL/TLS Options

These are the options that need to be used in order to configure a connection to use SSL/TLS.

Name Default Description
SSL Mode, SslMode Preferred This option has the following values:
  • Preferred - (this is the default). Use SSL if the server supports it.
  • None - Do not use SSL.
  • Required - Always use SSL. Deny connection if server does not support SSL. Does not validate CA or hostname.
  • VerifyCA - Always use SSL. Validates the CA but tolerates hostname mismatch.
  • VerifyFull - Always use SSL. Validates CA and hostname.
Certificate File, CertificateFile Specifies the path to a certificate file in PKCS #12 (.pfx) format containing a bundled Certificate and Private Key used for Mutual Authentication. To create a PKCS #12 bundle from a PEM encoded Certificate and Key, use openssl pkcs12 -in cert.pem -inkey key.pem -export -out bundle.pfx. This option should not be specified if SslCert and SslKey are used.
Certificate Password, CertificatePassword Specifies the password for the certificate specified using the CertificateFile option. Not required if the certificate file is not password protected.
SslCert, Ssl-Cert Specifies the path to the client’s SSL certificate file in PEM format. SslKey must also be specified, and CertificateFile should not be. This option is not supported on the netstandard1.3 or netstandard2.0 platforms.
SslKey, Ssl-Key Specifies the path to the client’s SSL private key in PEM format. SslCert must also be specified, and CertificateFile should not be.
CA Certificate File, CACertificateFile, SslCa, Ssl-Ca This option specifies the path to a CA certificate file in a PEM Encoded (.pem) format. This should be used with SslMode=VerifyCA or SslMode=VerifyFull to enable verification of a CA certificate that is not trusted by the Operating System’s certificate store.
Certificate Store Location, CertificateStoreLocation None Specifies whether the connection should be encrypted with a certificate from the Certificate Store on the machine. The default value of None means the certificate store is not used; a value of CurrentUser or LocalMachine uses the specified store.
Certificate Thumbprint, CertificateThumbprint Specifies which certificate should be used from the Certificate Store specified in the setting above. This option must be used to indicate which certificate in the store should be used for authentication.

Connection Pooling Options

Connection pooling is enabled by default. These options are used to configure it.

Name Default Description
Pooling true Enables connection pooling. When pooling is enabled, MySqlConnection.Open retrieves an open connection from the pool if one is available (opening a new connection if not), and Close/Dispose returns the open connection to the pool.
Connection Lifetime, ConnectionLifeTime 0 Controls the maximum length of time a connection to the server can be open. Connections that are returned to the pool are destroyed if it's been more than ConnectionLifeTime seconds since the connection was created. The default value of zero (0) means pooled connections will never incur a ConnectionLifeTime timeout.
Connection Reset, ConnectionReset true If true, the connection state is reset when it is retrieved from the pool. The default value of true ensures that the connection is in the same state whether it's newly created or retrieved from the pool. A value of false avoids making an additional server round trip when obtaining a connection, but the connection state is not reset, meaning that session variables and other session state changes from any previous use of the connection are carried over.
Connection Idle Ping Time, Connection Idle Ping Time (Experimental) 0 When a connection is retrieved from the pool, and ConnectionReset is false, the server will be pinged if the connection has been idle in the pool for longer than ConnectionIdlePingTime seconds. If pinging the server fails, a new connection will be opened automatically by the connection pool. This ensures that the MySqlConnection is in a valid, open state after the call to Open/OpenAsync, at the cost of an extra server roundtrip. For high-performance scenarios, you may wish to set ConnectionIdlePingTime to a non-zero value to make the connection pool assume that recently-returned connections are still open. If the connection is broken, it will throw from the first call to ExecuteNonQuery, ExecuteReader, etc.; your code should handle that failure and retry the connection. This option has no effect if ConnectionReset is true, as that will cause a connection reset packet to be sent to the server, making ping redundant.
Connection Idle Timeout, ConnectionIdleTimeout 180 The amount of time (in seconds) that a connection can remain idle in the pool. Any connection above MinimumPoolSize connections that is idle for longer than ConnectionIdleTimeout is subject to being closed by a background task. The background task runs every minute, or half of ConnectionIdleTimeout, whichever is more frequent. A value of zero (0) means pooled connections will never incur a ConnectionIdleTimeout, and if the pool grows to its maximum size, it will never get smaller.
Maximum Pool Size, Max Pool Size, MaximumPoolsize, maxpoolsize 100 The maximum number of connections allowed in the pool.
Minimum Pool Size, Min Pool Size, MinimumPoolSize, minpoolsize 0 The minimum number of connections to leave in the pool if ConnectionIdleTimeout is reached.

Connection Pooling with Multiple Servers

The Server option supports multiple comma-delimited host names. When this is used with connection pooling, the LoadBalance option controls how load is distributed across backend servers.

  • RoundRobin (default), Random: A total of MaximumPoolSize connections will be opened, but they may be unevenly distributed across back ends.
  • LeastConnections: A total of MaximumPoolSize connections will be opened, and they will be evenly distributed across back ends. The active connections will be selected from the pool in least-recently-used order, which does not ensure even load across the back ends. You should set MaximumPoolSize to the number of servers multiplied by the desired maximum number of open connections per backend server.
  • Failover: All connections will initially be made to the first server in the list. You should set MaximumPoolSize to the maximum number of open connections you want per server.

Other Options

These are the other options that MySqlConnector supports. They are set to sensible defaults and typically do not need to be tweaked.

Name Default Description
AllowPublicKeyRetrieval, Allow Public Key Retrieval false If the user account uses sha256_password authentication, the password must be protected during transmission; TLS is the preferred mechanism for this, but if it is not available then RSA public key encryption will be used. To specify the server's RSA public key, use the ServerRSAPublicKeyFile connection string setting, or set AllowPublicKeyRetrieval=True to allow the client to automatically request the public key from the server. Note that AllowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled.
AllowUserVariables, Allow User Variables false Allows user-defined variables (prefixed with @) to be used in SQL statements. The default value (false) only allows @-prefixed names to refer to command parameters.
AllowZeroDateTime, Allow Zero DateTime false If set to true all `DATE`, `DATETIME` and `TIMESTAMP` columns are returned as `MySqlDateTime` objects instead of `DateTime`. This allows the special “zero” date value `0000-00-00` to be retrieved from the database. If false (the default) date columns are returned as `DateTime` values, and an exception is thrown for unrepresentable dates.
ApplicationName, Application Name null Sets the program_name connection attribute passed to MySQL Server. This value may be displayed by diagnostic tools, e.g., as the “Program” column in “Client Connections” in MySQL Workbench.
CharSet, Character Set, CharacterSet MySqlConnector always uses utf8mb4 to send and receive strings from MySQL Server. This option may be specified (for backwards compatibility) but it will be ignored.
Compress, Use Compression, UseCompression false If true (and if the server supports compression), compresses packets sent between client and server. This option is unlikely to be useful in practice unless there is a high-latency or low-bandwidth network link between the application and the database server. You should measure performance with and without this option to determine if it's beneficial in your environment.
Connect Timeout, Connection Timeout, ConnectionTimeout 15 The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Convert Zero Datetime, ConvertZeroDateTime false True to have MySqlDataReader.GetValue() and MySqlDataReader.GetDateTime() return DateTime.MinValue for date or datetime columns that have disallowed values.
DateTimeKind Unspecified The DateTimeKind used when MySqlDataReader returns a DateTime. If set to Utc or Local, a MySqlException will be thrown if a DateTime command parameter has a Kind of Local or Utc, respectively.
GuidFormat Default

Determines which column type (if any) should be read as a System.Guid. The options include:

Char36
All CHAR(36) columns are read/written as a Guid using lowercase hex with hyphens, which matches UUID().
Char32
All CHAR(32) columns are read/written as a Guid using lowercase hex without hyphens.
Binary16
All BINARY(16) columns are read/written as a Guid using big-endian byte order, which matches UUID_TO_BIN(x).
TimeSwapBinary16
All BINARY(16) columns are read/written as a Guid using big-endian byte order with time parts swapped, which matches UUID_TO_BIN(x,1).
LittleEndianBinary16
All BINARY(16) columns are read/written as a Guid using little-endian byte order, i.e. the byte order used by Guid.ToByteArray() and the Guid(byte[]) constructor.
None
No column types are automatically read as a Guid.
Default
Same as Char36 if OldGuids=False; same as LittleEndianBinary16 if OldGuids=True.
Default Command Timeout, Command Timeout, DefaultCommandTimeout 30 The length of time (in seconds) each command can execute before timing out and throwing an exception, or zero to disable timeouts. See the note in the Microsoft documentation for more explanation of how this is determined.
IgnoreCommandTransaction, Ignore Command Transaction false If true, the value of MySqlCommand.Transaction is ignored when commands are executed. This matches the Connector/NET behaviour and can make porting code easier. For more information, see Transaction Usage.
Interactive, Interactive Session, InteractiveSession false If true, the session wait_timeout variable is initialized from the global interactive_timeout value instead of the global wait_timeout value.
Keep Alive, Keepalive 0 TCP Keepalive idle time. A value of 0 indicates that the OS Default keepalive settings are used. On Windows, a value greater than 0 is the idle connection time, measured in seconds, before the first keepalive packet is sent. Due to limitations in .NET Core, Unix-based Operating Systems will always use the OS Default keepalive settings.
Load Balance, LoadBalance RoundRobin

The load-balancing strategy to use when Host contains multiple, comma-delimited, host names. The options include:

RoundRobin
Each new connection opened for this connection pool uses the next host name (sequentially with wraparound). Requires Pooling=True. This is the default if Pooling=True.
FailOver
Each new connection tries to connect to the first host; subsequent hosts are used only if connecting to the first one fails. This is the default if Pooling=False.
Random
Servers are tried in a random order.
LeastConnections
Servers are tried in ascending order of number of currently-open connections in this connection pool. Requires Pooling=True.
Old Guids, OldGuids false Obsolete; use the GuidFormat option instead.
Persist Security Info, PersistSecurityInfo false When set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values, including the password. Recognized values are true, false, yes, and no.
ServerRSAPublicKeyFile, Server RSA Public Key File For sha256_password authentication. See comments under AllowPublicKeyRetrieval.
ServerSPN, Server SPN For MariaDB auth_gssapi_client authentication. Specifies the server’s Service Principal Name (to verify that authentication is occurring with the correct server).
Treat Tiny As Boolean, TreatTinyAsBoolean true When set to true, TINYINT(1) values are returned as booleans. Setting this to false causes TINYINT(1) to be returned as sbyte/byte.
Use Affected Rows, UseAffectedRows false When false (default), the connection reports found rows instead of changed (affected) rows. Set to true to report only the number of rows actually changed by UPDATE or INSERT … ON DUPLICATE KEY UPDATE statements.
Use XA Transactions, UseXaTransactions true When true (default), using TransactionScope or MySqlConnection.EnlistTransaction will use a XA Transaction. This allows true distributed transactions, but may not be compatible with server replication; there are other limitations. When set to false, regular MySQL transactions are used, just like Connector/NET.