Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


Connectors and APIs Manual  /  ...  /  Connector/ODBC Connection Parameters

5.5.2 Connector/ODBC Connection Parameters

You can specify the parameters in the following tables for Connector/ODBC when configuring a DSN:

Users on Windows can use the ODBC Data Source Administrator to set these parameters; see Section 5.5.3, “Configuring a Connector/ODBC DSN on Windows” on how to do that, and see Table 5.1, “Connector/ODBC DSN Configuration Options” for information on the options and the fields and check boxes they corrrespond to on the graphical user interface of the ODBC Data Source Administrator. On Unix and macOS, use the parameter name and value as the keyword/value pair in the DSN configuration. Alternatively, you can set these parameters within the InConnectionString argument in the SQLDriverConnect() call.

Table 5.1 Connector/ODBC DSN Configuration Options

Parameter GUI Option Default Value Comment
user User ODBC The user name used to connect to MySQL.
uid User ODBC Synonymous with user. Added in 3.51.16.
server TCP/IP Server localhost The host name of the MySQL server. Can define multiple hosts if MULTI_HOST is enabled.
database Database - The default database.
option - 0 Options that specify how Connector/ODBC works. See Table 5.3, “Connector/ODBC Option Parameters” and Table 5.4, “Recommended Connector/ODBC Option Values for Different Configurations”.
port Port 3306 The TCP/IP port to use if server is not localhost.
initstmt Initial Statement - Initial statement. A statement to execute when connecting to MySQL. In version 3.51 the parameter is called stmt. The driver supports the initial statement being executed only at the time of the initial connection.
password Password - The password for the user account on server. pwd is an alias.
password1, password2, password3 Password - For Multi-Factor Authentication (MFA); password1 is an alias for password. There'as also the pwd1, pwd2, and pwd3 aliases. These were added in 8.0.28.
socket - - The Unix socket file or Windows named pipe to connect to; only define socket if server is set to localhost
openid-token-file - - Defines a path to a file containing the JWT formatted identity token. Added in 9.1.0.
ssl-ca SSL Certificate - Alias of SSLCA as an eventual replacement; added in v8.0.29.
SSLCA SSL Certificate -

The path to a file with a list of trust SSL CAs.

An ssl-ca alias was added in 8.0.29, which is preferred over SSLCA.

ssl-capath SSL CA Path - Alias of SSLCAPATH as an eventual replacement; added in v8.0.29.
SSLCAPATH SSL CA Path -

The path to a directory that contains trusted SSL CA certificates in PEM format.

An ssl-capath alias was added in 8.0.29, which is preferred over SSLCAPATH.

ssl-cert SSL Certificate - Alias of SSLCERT as an eventual replacement; added in v8.0.29.
SSLCERT SSL Certificate -

The name of the SSL certificate file to use for establishing a secure connection.

An ssl-cert alias was added in 8.0.29, which is preferred over SSLCERT.

ssl-cipher SSL Cipher - Alias of SSLCIPHER as an eventual replacement; added in v8.0.29.
SSLCIPHER SSL Cipher -

The list of permissible ciphers for SSL encryption. The cipher list has the same format as the openssl ciphers command.

An ssl-cipher alias was added in 8.0.29, which is preferred over SSLCIPHER.

ssl-key SSL Key - Alias of SSLKEY as an eventual replacement; added in v8.0.29.
SSLKEY SSL Key -

The name of the SSL key file to use for establishing a secure connection.

An ssl-key alias was added in 8.0.29, which is preferred over SSLKEY.

ssl-crl The path name of the file containing certificate revocation lists in PEM format. - Added in 8.0.31
ssl-crlpath The path of the directory that contains certificate revocation list files in PEM format. - Added in 8.0.31
rsakey RSA Public Key - The full-path name of the PEM file that contains the RSA public key for using the SHA256 authentication plugin of MySQL. Added in 5.3.4.
sslverify Verify SSL 0 If set to 1, the SSL certificate will be verified when used with the MySQL connection. If not set, then the default behavior is to ignore SSL certificate verification.
Note

The option is deprecated since Connector/ODBC 5.3.7. It is preferable to use the SSLMODE option parameter instead.

authentication-kerberos-mode Kerberos implementation SSPI Acceptable values are "SSPI" (default) or "GSSAPI". For functionality details, see Kerberos Pluggable Authentication. The SSPI option is only supported by Windows, whereas GSSAPI is supported by both Windows and other operating systems. Added in Connector/ODBC 8.0.32.
OPENTELEMETRY OpenTelemetry implementation PREFERRED Acceptable values are PREFERRED (default) or DISABLED. For functionality details, see Section 5.5.8, “OpenTelemetry Tracing Support”. Added in Connector/ODBC 8.1.0.
MULTI_HOST Whether to enable multiple host functionality 0 Enable new connections to try multiple hosts until a successful connection is established. A list of hosts is defined with SERVER in the connection string. For example, SERVER=address1[:port1],address2[:port2];MULTI_HOST=1 -- option added in 8.0.19.
ENABLE_DNS_SRV Whether to use DNS+SRV usage in the DSN 0 If set to 1, enables DNS+SRV usage in the DSN; the host is passed for SRV lookup without a port and with a full lookup name. Example usage: DRIVER={MySQL ODBC 9.0 Driver};SERVER=_mysql._tcp.foo.abc.com;ENABLE_DNS_SRV=1;USER=user;PWD=passwd; -- option added in Connector/ODBC 8.0.19.
charset Character Set - The character set to use for the connection. Added in 3.51.17. Note: executing SET NAMES is not allowed as of 5.1. This option is deprecated for the Unicode driver as of 9.0.0.
readtimeout - - The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout 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 only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_READ_TIMEOUT option of the MySQL Client Library. Added in 3.51.27.
writetimeout - - The timeout in seconds for attempts to write to the server. Each attempt uses this timeout value and there are net_retry_count retries if necessary, so the total effective timeout value is net_retry_count times the option value. This option works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12. Corresponds to the MYSQL_OPT_WRITE_TIMEOUT option of the MySQL Client Library. Added in 3.51.27.
interactive Interactive Client 0 If set to 1, the CLIENT_INTERACTIVE connection option of mysql-real-connect() is enabled. Added in 5.1.7.
OCI_CONFIG_FILE Oracle Clound Infastructure configuration file path ~/.oci/config on Linux and macOS, and %HOMEDRIVE%%HOMEPATH%\.oci\config on Windows. Used by the authentication_oci_client plugin for the Oracle Cloud Infrastructure (OCI) to support ephemeral key pairs and security tokens. The default profile is DEFAULT and can be configured using OCI_CONFIG_PROFILE. Option added in Connector/ODBC 8.0.27.
OCI_CONFIG_PROFILE Oracle Clound Infastructure configuration profile name DEFAULT Defaults to DEFAULT, optionally specify a specific profile as defined in OCI_CONFIG_FILE. Option added in Connector/ODBC 8.0.33.
prefetch Prefetch from server by _ rows at a time 0

When set to a non-zero value N, causes all queries in the connection to return N rows at a time rather than the entire result set. Useful for queries against very large tables where it is not practical to retrieve the whole result set at once. You can scroll through the result set, N records at a time.

This option works only with forward-only cursors. It does not work when the option parameter MULTI_STATEMENTS is set. It can be used in combination with the option parameter NO_CACHE. Its behavior in ADO applications is undefined: the prefetching might or might not occur. Added in 5.1.11.

no_ssps - 0

In Connector/ODBC 5.2 and after, by default, server-side prepared statements are used. When this option is set to a non-zero value, prepared statements are emulated on the client side, which is the same behavior as in 5.1 and 3.51. Added in 5.2.0.

can_handle_exp_pwd Can Handle Expired Password 0 Indicates that the application can deal with an expired password, which is signalled by an SQL state of 08004 (Server rejected the connection) and a native error code ER_MUST_CHANGE_PASSWORD_LOGIN (1862). The connection is sandboxed, and can do nothing other than issue a SET PASSWORD statement. To establish a connection in this case, your application must either use the initstmt connection option to set a new password at the start, or issue a SET PASSWORD statement immediately after connecting. Once the expired password is reset, the restrictions on the connection are lifted. See ALTER USER Statement for details about password expiration for MySQL server accounts. Added in 5.2.4.
ENABLE_CLEARTEXT_PLUGIN Enable Cleartext Authentication 0 Set to 1 to enable cleartext authentication. Added in 5.1.13 and 5.2.5.
ENABLE_LOCAL_INFILE Enable LOAD DATA operations 0 A connection string, DSN, and GUI option. Set ENABLE_LOCAL_INFILE=1 to enable LOAD DATA operations. This toggles the MYSQL_OPT_LOCAL_INFILE mysql_options() option. The connection string overrides the DSN value if both are set. Added in 5.3.12 and 8.0.14.
LOAD_DATA_LOCAL_DIR Restrict LOAD DATA operations A connection string, DSN, and GUI option. Set LOAD_DATA_LOCAL_DIR to a specific directory, such as LOAD_DATA_LOCAL_DIR=/tmp, to restrict uploading files to a specific path. This sets the MYSQL_OPT_LOAD_DATA_LOCAL_DIR mysql_options() option. The connection string overrides the DSN value if both are set. This option has no effect if ENABLE_LOCAL_INFILE=1. Added in 8.0.22.
GET_SERVER_PUBLIC_KEY Get Server Public Key 0

When connecting to accounts that use caching_sha2_password authentication over non-secure connection (TLS disabled), Connector/ODBC requests the RSA public key required to perform the authentication from the server. The option is ignored if the authentication mechanism used for the connection is different from caching_sha2_password. This option corresponds to the MYSQL_OPT_GET_SERVER_PUBLIC_KEY option for the mysql_options() C API function. The value is a boolean.

The option is added in Connector/ODBC versions 8.0.11 and 5.3.11. It requires Connector/ODBC built using OpenSSL-based MySQL client library. If MySQL client library used by Connector/ODBC was built with YaSSL, as is the case for GPL distributions of Connector/ODBC 5.3, the option does not function and is ignored

NO_TLS_1_0 Disable TLS 1.0 0 This option was removed in v8.0.28. It disallowed the use of TLS 1.0 for connection encryption. All versions of TLS are allowed by default, and this option exluded version 1.0 from being used. Added in 5.3.7. TLS 1.0 support was deprecated in v8.0.26 before removal in v8.0.28.
NO_TLS_1_1 Disable TLS 1.1 0 This option was removed in v8.0.28. It disallowed the use of TLS 1.1 for connection encryption. All versions of TLS are allowed by default, and this option exluded version 1.1 from being used. Added in 5.3.7. TLS 1.1 support was deprecated in v8.0.26 before removal in v8.0.28.
NO_TLS_1_2 Disable TLS 1.2 0 Disallows the use of TLS 1.2 for connection encryption. All versions of TLS are allowed by default, and this option exludes version 1.2 from being used. Added in 5.3.7.
NO_TLS_1_3 Disable TLS 1.3 0 Disallows the use of TLS 1.3 for connection encryption. All versions of TLS are allowed by default, and this option exludes version 1.3 from being used. Added in 8.0.26.
tls-versions Define the allowed TLS protocol versions TLSv1.2,TLSv1.3 (set by libmysqlclient) Accepts TLSv1.2 and/or TLSv1.3; while other values generate an error. It has no effect if ssl-mode=DISABLED, and overrides (disables) the related NO_TLS_X_Y connection options such as NO_TLS_1_2. Added in 8.0.30.
SSL_ENFORCE Enforce SSL 0 Enforce the requirement to use SSL for connections to server. See Table 5.2, “Combined Effects of SSL_ENFORCE and DISABLE_SSL_DEFAULT ”. Added in 5.3.6.
Note

This option is deprecated since Connector/ODBC 5.3.7 and removed in 8.0.13. It is preferable to use the SSLMODE option parameter instead.

DISABLE_SSL_DEFAULT Disable default SSL 0 Disable the default requirement to use SSL for connections to server. When set to 0 [default], Connector/ODBC tries to connect with SSL first, and falls back to unencrypted connection if it is not possible to establish an SSL connection. When set to 1, Connection with SSL is not attempted, and unencrypted connection is used, unless SSL_ENFORCE is also set to 1. See Table 5.2, “Combined Effects of SSL_ENFORCE and DISABLE_SSL_DEFAULT ”. Added in 5.3.6.
Note

The option is deprecated since Connector/ODBC 5.3.7 and removed in 8.0.13. Use the SSLMODE option parameter instead.

ssl-mode SSL Mode - Alias of SSLMODE as an eventual replacement; added in v8.0.29.
SSLMODE SSL Mode -

Sets the SSL mode of the server connection. The option can be set to any of the following values: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, or VERIFY_IDENTITY. See description for the --ssl-mode option in the MySQL 8.0 Reference Manual for the meaning of each of the option values.

An ssl-mode alias was added in 8.0.29, which is preferred over SSLMODE.

If SSLMODE is not explicitly set, use of the SSLCA or SSLCAPTH option implies SSLMODE=VERIFY_CA.

Added in 5.3.7. This option overrides the deprecatedsslverify and SSL_ENFORCE options.


Note

The SSL configuration parameters can also be automatically loaded from a my.ini or my.cnf file. See Using Option Files.

Table 5.2 Combined Effects of SSL_ENFORCE and DISABLE_SSL_DEFAULT

DISABLE_SSL_DEFAULT = 0 DISABLE_SSL_DEFAULT = 1
SSL_ENFORCE = 0 (Default) Connection with SSL is attempted first; if not possible, fall back to unencrypted connection. Connection with SSL is not attempted; use unencrypted connection.
SSL_ENFORCE = 1 Connect with SSL; throw an error if an SSL connection cannot be established. Connect with SSL; throw an error if an SSL connection cannot be established. DISABLE_SSL_DEFAULT=1 is overridden.

The behavior of Connector/ODBC can be also modified by using special option parameters listed in Table 5.3, “Connector/ODBC Option Parameters”, specified in the connection string or through the GUI dialog box. All of the connection parameters also have their own numeric constant values, which can be added up as a combined value for the option parameter for specifying those options. However, the numerical option value in the connection string can only enable, but not disable parameters enabled on the DSN, which can only be overridden by specifying the option parameters using their text names in the connection string.

Note

While the combined numerical value for the option parameter can be easily constructed by addition of the options' constant values, decomposing the value to verify if particular options are enabled can be difficult. We recommend using the options' parameter names instead in the connection string, because they are self-explanatory.

Table 5.3 Connector/ODBC Option Parameters

Parameter Name GUI Option Constant Value Description
FOUND_ROWS Return matched rows instead of affected rows 2 The client cannot handle when MySQL returns the true value of affected rows. If this flag is set, MySQL returns found rows instead. You must have MySQL 3.21.14 or newer for this to work.
BIG_PACKETS Allow big result set 8 Do not set any packet limit for results and bind parameters. Without this option, parameter binding will be truncated to 255 characters.
NO_PROMPT Don't prompt when connecting 16 Do not prompt for questions even if driver would like to prompt.
DYNAMIC_CURSOR Enable Dynamic Cursors 32 Enable or disable the dynamic cursor support.
NO_SCHEMA Disables support for ODBC schemas 64 Ignore use of database schema name in catalog.schema.table.column. See also the related NO_CATALOG option. This option was removed in Connector/ODBC 8.0.13 but served no function before then, and was reintroduced in Connector/ODBC 8.0.26. This option is enabled by default as of Connector/ODBC 8.0.27. For usage details, see Section 5.8.1.3, “Configuring Catalog and Schema Support”
NO_DEFAULT_CURSOR Disable driver-provided cursor support 128 Force use of ODBC manager cursors (experimental).
NO_LOCALE Don't use setlocale() 256 Disable the use of extended fetch (experimental).
PAD_SPACE Pad CHAR to full length with space 512 Pad CHAR columns to full column length.
FULL_COLUMN_NAMES Include table name in SQLDescribeCol() 1024 SQLDescribeCol() returns fully-qualified column names.
COMPRESSED_PROTO Use compression 2048 Use the compressed client/server protocol.
IGNORE_SPACE Ignore space after function names 4096 Tell server to ignore space after function name and before ( (needed by PowerBuilder). This makes all function names keywords.
NAMED_PIPE Named Pipe 8192 Connect with named pipes to a mysqld server running on NT.
NO_BIGINT Treat BIGINT columns as INT columns 16384 Change BIGINT columns to INT columns (some applications cannot handle BIGINT).
NO_CATALOG Disable catalog support 32768 Forces results from the catalog functions, such as SQLTables, to always return NULL and the driver to report that catalogs are not supported. See also the related NO_SCHEMA option. For usage details, see Section 5.8.1.3, “Configuring Catalog and Schema Support”
USE_MYCNF Read options from my.cnf 65536 Read parameters from the [client] and [odbc] groups from my.cnf.
SAFE Enable safe options 131072 Add some extra safety checks.
NO_TRANSACTIONS Disable transaction support 262144 Disable transactions.
LOG_QUERY Log queries to %TEMP%\myodbc.sql 524288 Enable query logging to c:\myodbc.sql(/tmp/myodbc.sql) file. (Enabled only in debug mode.)
NO_CACHE Don't cache results of forward-only cursors 1048576 Do not cache the results locally in the driver, instead read from server (mysql_use_result()). This works only for forward-only cursors. This option is very important in dealing with large tables when you do not want the driver to cache the entire result set.
FORWARD_CURSOR Force use of forward-only cursors 2097152 Force the use of Forward-only cursor type. In cases of applications setting the default static/dynamic cursor type and one wants the driver to use noncache result sets, this option ensures the forward-only cursor behavior.
AUTO_RECONNECT Enable automatic reconnect 4194304 Enables auto-reconnection functionality. Do not use this option with transactions, since an auto-reconnection during a incomplete transaction may cause corruption. An auto-reconnected connection will not inherit the same settings and environment as the original connection. MySQL Server deprecated this functionality in 8.0.34/8.1.0 and removed it in 8.3.0. This connection option was removed from Connector/ODBC 8.3.0 and setting it returns SQL_SUCCESS_WITH_INFO with an HY000 error stating that it's no longer supported.
AUTO_IS_NULL Enable SQL_AUTO_IS_NULL 8388608

When AUTO_IS_NULL is set, the driver does not change the default value of sql_auto_is_null, leaving it at 1, so you get the MySQL default, not the SQL standard behavior.

When AUTO_IS_NULL is not set, the driver changes the default value of SQL_AUTO_IS_NULL to 0 after connecting, so you get the SQL standard, not the MySQL default behavior.

Thus, omitting the flag disables the compatibility option and forces SQL standard behavior.

See IS NULL. Added in 3.51.13.

ZERO_DATE_TO_MIN Return SQL_NULL_DATA for zero date 16777216 Translates zero dates (XXXX-00-00) into the minimum date values supported by ODBC, XXXX-01-01. This resolves an issue where some statements will not work because the date returned and the minimum ODBC date value are incompatible. Added in 3.51.17.
MIN_DATE_TO_ZERO Bind minimal date as zero date 33554432 Translates the minimum ODBC date value (XXXX-01-01) to the zero date format supported by MySQL (XXXX-00-00). This resolves an issue where some statements will not work because the date returned and the minimum ODBC date value are incompatible. Added in 3.51.17.
NO_DATE_OVERFLOW Ignore data overflow error 0 Continue with the query execution rather then return error if the time portion is missing. The server will ignore the time component and the result is the same as if they were zeros. Added in 5.3.8.
MULTI_STATEMENTS Allow multiple statements 67108864 Enables support for batched statements. As of 8.0.24, preparing a query with multiple statements raises an error. The direct execution of parameter-less statements prepared using the SQLPrepare() function is not supported. Multiple statements can only be executed through the SQLExecDirec() ODBC function.
COLUMN_SIZE_S32 Limit column size to signed 32-bit range 134217728 Limits the column size to a signed 32-bit value to prevent problems with larger column sizes in applications that do not support them. This option is automatically enabled when working with ADO applications. Added in 3.51.22.
NO_BINARY_RESULT Always handle binary function results as character data 268435456 When set, this option disables charset 63 for columns with an empty org_table. Added in 3.51.26.
DFLT_BIGINT_BIND_STR Bind BIGINT parameters as strings 536870912 Causes BIGINT parameters to be bound as strings. Microsoft Access treats BIGINT as a string on linked tables. The value is read correctly, but bound as a string. This option is used automatically if the driver is used by Microsoft Access. Added in 5.1.3.
NO_I_S Don't use INFORMATION_SCHEMA for metadata 1073741824 Tells catalog functions not to use INFORMATION_SCHEMA, but rather use legacy algorithms. The trade-off here is usually speed for information quality. Added in 5.1.7, deprecated in 8.0.26, and removed (and now ignored) in 8.0.31.
CB_FIDO_GLOBAL Registers a global callback function for the authentication_webauthn connection 20480 User-defined constant (see Connector/ODBC WebAuthn and FIDO Information); the last registered global callback is reused in connections not defining a callback. Only use with connections that use the MySQL ODBC driver; using with other connections might lead to undefined behavior. Example usage: SQLSetConnectAttr(hdbc, CB_FIDO_GLOBAL, &my_user_callback, SQL_IS_POINTER);. Support added in 8.2.0.
CB_FIDO_CONNECTION Registers a per-connection callback function for the authentication_webauthn connection 20481 User-defined constant (see Connector/ODBC WebAuthn and FIDO Information); the callback is registered for a single connection. Only use with connections that use the MySQL ODBC driver; using with other connections might lead to undefined behavior. Support added in 8.2.0.

Table 5.4, “Recommended Connector/ODBC Option Values for Different Configurations” shows some recommended parameter settings and their corresponding option values for various configurations:

Table 5.4 Recommended Connector/ODBC Option Values for Different Configurations

Configuration Parameter Settings Option Value
Microsoft Access, Visual Basic FOUND_ROWS=1; 2
Microsoft Access (with improved DELETE queries) FOUND_ROWS=1;DYNAMIC_CURSOR=1; 34
Microsoft SQL Server COLUMN_SIZE_S32=1; 134217728
Large tables with too many rows COMPRESSED_PROTO=1; 2048
Sybase PowerBuilder IGNORE_SPACE=1;FLAG_SAFE=1; 135168
Query log generation (Debug mode) LOG_QUERY=1; 524288
Large tables with no-cache results NO_CACHE=1;FORWARD_CURSOR=1; 3145728
Applications that run full-table "SELECT * FROM ... " query, but read only a small number (N) of rows from the result PREFETCH=N Not Applicable