PDF (US Ltr)
- 4.5Mb
PDF (A4)
- 4.5Mb
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-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-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-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
An |
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-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 |
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.1 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
This option works only with forward-only cursors. It
does not work when the option parameter
|
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
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
|
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 |
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:
An
If
Added in 5.3.7. This option overrides the
deprecated |
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
When Thus, omitting the flag disables the compatibility option and forces SQL standard behavior.
See |
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 |