Documentation Home
MySQL 8.0 C API Developer Guide
Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb


5.4.54 mysql_options()

intmysql_options(MYSQL *mysql,              enum mysql_option option,              const void *arg)

Description

Can be used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options. To retrieve option values, usemysql_get_option().

Callmysql_options() aftermysql_init() and beforemysql_connect() ormysql_real_connect().

Theoption argument is the option that you want to set; thearg argument is the value for the option. If the option is an integer, specify a pointer to the value of the integer as thearg argument.

Options for information such as SSL certificate and key files are used to establish an encrypted connection if such connections are available, but do not enforce any requirement that the connection obtained be encrypted. To require an encrypted connection, use the technique described inSection 3.6.1, “Support for Encrypted Connections”.

The following list describes the possible options, their effect, and howarg is used for each option. For option descriptions that indicatearg is unused, its value is irrelevant; it is conventional to pass 0.

  • MYSQL_DEFAULT_AUTH (argument type:char *)

    The name of the authentication plugin to use.

  • MYSQL_ENABLE_CLEARTEXT_PLUGIN (argument type:bool *)

    Enable themysql_clear_password cleartext authentication plugin. SeeClient-Side Cleartext Pluggable Authentication.

  • MYSQL_INIT_COMMAND (argument type:char *)

    SQL statement to execute when connecting to the MySQL server. Automatically re-executed if reconnection occurs.

  • MYSQL_OPT_BIND (argument:char *)

    The network interface from which to connect to the server. This is used when the client host has multiple network interfaces. The argument is a host name or IP address (specified as a string).

  • MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS (argument type:bool *)

    Indicate whether the client can handle expired passwords. SeeServer Handling of Expired Passwords.

  • MYSQL_OPT_COMPRESS (argument: not used)

    Compress all information sent between the client and the server if possible. SeeConnection Compression Control.

    As of MySQL 8.0.18,MYSQL_OPT_COMPRESS becomes a legacy option, due to the introduction of theMYSQL_OPT_COMPRESSION_ALGORITHMS option for more control over connection compression (seeConfiguring Connection Compression). The meaning ofMYSQL_OPT_COMPRESS depends on whetherMYSQL_OPT_COMPRESSION_ALGORITHMS is specified:

    • WhenMYSQL_OPT_COMPRESSION_ALGORITHMS is not specified, enablingMYSQL_OPT_COMPRESS is equivalent to specifying a client-side algorithm set ofzlib,uncompressed.

    • WhenMYSQL_OPT_COMPRESSION_ALGORITHMS is specified, enablingMYSQL_OPT_COMPRESS is equivalent to specifying an algorithm set ofzlib and the full client-side algorithm set is the union ofzlib plus the algorithms specified byMYSQL_OPT_COMPRESSION_ALGORITHMS. For example, withMYSQL_OPT_COMPRESS enabled andMYSQL_OPT_COMPRESSION_ALGORITHMS set tozlib,zstd, the permitted-algorithm set iszlib pluszlib,zstd; that is,zlib,zstd. WithMYSQL_OPT_COMPRESS enabled andMYSQL_OPT_COMPRESSION_ALGORITHMS set tozstd,uncompressed, the permitted-algorithm set iszlib pluszstd,uncompressed; that is,zlib,zstd,uncompressed.

    As of MySQL 8.0.18,MYSQL_OPT_COMPRESS is deprecated. It is subject to removal in a future MySQL version. SeeConfiguring Legacy Connection Compression.

  • MYSQL_OPT_COMPRESSION_ALGORITHMS (argument type:const char *)

    The permitted compression algorithms for connections to the server. The available algorithms are the same as for theprotocol_compression_algorithms system variable. If this option is not specified, the default value isuncompressed.

    For more information, seeConnection Compression Control.

    This option was added in MySQL 8.0.18. Forasynchronous operations, the option has no effect until MySQL 8.0.21.

  • MYSQL_OPT_CONNECT_ATTR_DELETE (argument type:char *)

    Given a key name, this option deletes a key-value pair from the current set of connection attributes to pass to the server at connect time. The argument is a pointer to a null-terminated string naming the key. Comparison of the key name with existing keys is case-sensitive.

    See also the description for theMYSQL_OPT_CONNECT_ATTR_RESET option, as well as the description for theMYSQL_OPT_CONNECT_ATTR_ADD option in the description of themysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through thesession_connect_attrs andsession_account_connect_attrs tables. SeePerformance Schema Connection Attribute Tables.

  • MYSQL_OPT_CONNECT_ATTR_RESET (argument not used)

    This option resets (clears) the current set of connection attributes to pass to the server at connect time.

    See also the description for theMYSQL_OPT_CONNECT_ATTR_DELETE option, as well as the description for theMYSQL_OPT_CONNECT_ATTR_ADD option in the description of themysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through thesession_connect_attrs andsession_account_connect_attrs tables. SeePerformance Schema Connection Attribute Tables.

  • MYSQL_OPT_CONNECT_TIMEOUT (argument type:unsigned int *)

    The connect timeout in seconds.

  • MYSQL_OPT_GET_SERVER_PUBLIC_KEY (argument type:bool *)

    Enables the client to request from the server the public key required for RSA key pair-based password exchange. This option applies to clients that authenticate with thecaching_sha2_password authentication plugin. For that plugin, the server does not send the public key unless requested. This option is ignored for accounts that do not authenticate with that plugin. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

    IfMYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence overMYSQL_OPT_GET_SERVER_PUBLIC_KEY.

    For information about thecaching_sha2_password plugin, seeCaching SHA-2 Pluggable Authentication.

  • MYSQL_OPT_LOAD_DATA_LOCAL_DIR (argument type:char *)

    This option affects the client-sideLOCAL capability forLOAD DATA operations. It specifies the directory in which files named inLOAD DATA LOCAL statements must be located. The effect ofMYSQL_OPT_LOAD_DATA_LOCAL_DIR depends on whetherLOCAL data loading is enabled or disabled:

    • IfLOCAL data loading is enabled, either by default in the MySQL client library or by explicitly enablingMYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option has no effect.

    • IfLOCAL data loading is disabled, either by default in the MySQL client library or by explicitly disablingMYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option can be used to designate a permitted directory for locally loaded files. In this case,LOCAL data loading is permitted but restricted to files located in the designated directory. Interpretation of theMYSQL_OPT_LOAD_DATA_LOCAL_DIR value is as follows:

      • If the value is the null pointer (the default), it names no directory, with the result that no files are permitted forLOCAL data loading.

      • If the value is a directory path name,LOCAL data loading is permitted but restricted to files located in the named directory. Comparison of the directory path name and the path name of files to be loaded is case-sensitive regardless of the case-sensitivity of the underlying file system.

    For example, to explicitly disable local data loading except for files located in the/my/local/data directory, invokemysql_options() like this:

    unsigned int i = 0;mysql_options(&mysql,MYSQL_OPT_LOCAL_INFILE,&i);mysql_options(&mysql,MYSQL_OPT_LOAD_DATA_LOCAL_DIR,"/my/local/data");

    TheMYSQL_OPT_LOAD_DATA_LOCAL_DIR option can be set any time during the life of themysql connection handler. Once set, the value applies to all subsequentLOCAL load operations until such time as the value is changed.

    TheENABLED_LOCAL_INFILECMake option controls the client library default for local data loading (seeMySQL Source-Configuration Options).

    Successful use ofLOCAL load operations by a client also requires that the server permits local loading; seeSecurity Considerations for LOAD DATA LOCAL

    TheMYSQL_OPT_LOAD_DATA_LOCAL_DIR option was added in MySQL 8.0.21.

  • MYSQL_OPT_LOCAL_INFILE (argument type: optional pointer tounsigned int)

    This option affects client-sideLOCAL capability forLOAD DATA operations. By default,LOCAL capability is determined by the default compiled into the MySQL client library. To control this capability explicitly, invokemysql_options() to enable or disable theMYSQL_OPT_LOCAL_INFILE option:

    • To enableLOCAL data loading, set the pointer to point to anunsigned int that has a nonzero value, or omit the pointer argument.

    • To disableLOCAL data loading, set the pointer to point to anunsigned int that has a zero value.

    IfLOCAL capability is disabled, theMYSQL_OPT_LOAD_DATA_LOCAL_DIR option can be used to permit restricted local loading of files located in a designated directory.

    TheENABLED_LOCAL_INFILECMake option controls the client library default for local data loading (seeMySQL Source-Configuration Options).

    Successful use ofLOCAL load operations by a client also requires that the server permits local loading; seeSecurity Considerations for LOAD DATA LOCAL

  • MYSQL_OPT_MAX_ALLOWED_PACKET (argument:unsigned long *)

    This option sets the client-side maximum size of the buffer for client/server communication. If themysql argument is non-NULL, the call sets the option value for that session. Ifmysql isNULL, the call sets the option value globally for all subsequent sessions for which a session-specific value is not specified.

    Because it is possible to set a session or global maximum buffer size, depending on whether themysql argument is non-NULL orNULL,mysql_get_option() similarly returns the session or global value depending on itsmysql argument.

  • MYSQL_OPT_NAMED_PIPE (argument: not used)

    Use a named pipe to connect to the MySQL server on Windows, if the server permits named-pipe connections.

  • MYSQL_OPT_NET_BUFFER_LENGTH (argument:unsigned long *)

    This option sets the client-side buffer size for TCP/IP and socket communication.

  • MYSQL_OPT_OPTIONAL_RESULTSET_METADATA (argument type:bool *)

    This flag makes result set metadata optional. It is an alternative to setting theCLIENT_OPTIONAL_RESULTSET_METADATA connection flag for themysql_real_connect() function. For details about managing result set metadata transfer, seeSection 3.6.7, “Optional Result Set Metadata”.

  • MYSQL_OPT_PROTOCOL (argument type:unsigned int *)

    Transport protocol to use for connection. Specify one of the enum values ofmysql_protocol_type defined inmysql.h.

  • MYSQL_OPT_READ_TIMEOUT (argument type:unsigned int *)

    The timeout in seconds for each attempt to read from the server. 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/IPClose_Wait_Timeout value of 10 minutes.

  • MYSQL_OPT_RECONNECT (argument type:bool *)

    Note

    TheMYSQL_OPT_RECONNECT option is still available but is deprecated; expect it to be removed in a future version of MySQL.

    Enable or disable automatic reconnection to the server if the connection is found to have been lost. Reconnect is off by default; this option provides a way to set reconnection behavior explicitly. SeeSection 3.6.8, “Automatic Reconnection Control”.

  • MYSQL_OPT_RETRY_COUNT (argument type:unsigned int *)

    The retry count for I/O-related system calls that are interrupted while connecting to the server or communicating with it. If this option is not specified, the default value is 1 (1 retry if the initial call is interrupted for 2 tries total).

    This option can be used only by clients that link against a C client library compiled with NDB Cluster support.

  • MYSQL_OPT_SSL_CA (argument type:char *)

    The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server.

  • MYSQL_OPT_SSL_CAPATH (argument type:char *)

    The path name of the directory that contains trusted SSL CA certificate files.

  • MYSQL_OPT_SSL_CERT (argument type:char *)

    The path name of the client public key certificate file.

  • MYSQL_OPT_SSL_CIPHER (argument type:char *)

    The list of permissible ciphers for SSL encryption.

  • MYSQL_OPT_SSL_CRL (argument type:char *)

    The path name of the file containing certificate revocation lists.

  • MYSQL_OPT_SSL_CRLPATH (argument type:char *)

    The path name of the directory that contains files containing certificate revocation lists.

  • MYSQL_OPT_SSL_FIPS_MODE (argument type:unsigned int *)

    TheMYSQL_OPT_SSL_FIPS_MODE option is deprecated as of MySQL 8.0.34 and is subject to removal in a future version of MySQL.

    Controls whether to enable FIPS mode on the client side. TheMYSQL_OPT_SSL_FIPS_MODE option differs from otherMYSQL_OPT_SSL_xxx options in that it is not used to establish encrypted connections, but rather to affect which cryptographic operations to permit. SeeFIPS Support.

    Permitted option values areSSL_FIPS_MODE_OFF,SSL_FIPS_MODE_ON, andSSL_FIPS_MODE_STRICT.

    Note

    If the OpenSSL FIPS Object Module is not available, the only permitted value forMYSQL_OPT_SSL_FIPS_MODE isSSL_FIPS_MODE_OFF. In this case, settingMYSQL_OPT_SSL_FIPS_MODE toSSL_FIPS_MODE_ON orSSL_FIPS_MODE_STRICT causes the client to produce a warning at startup and to operate in non-FIPS mode.

  • MYSQL_OPT_SSL_KEY (argument type:char *)

    The path name of the client private key file.

  • MYSQL_OPT_SSL_MODE (argument type:unsigned int *)

    The security state to use for the connection to the server:SSL_MODE_DISABLED,SSL_MODE_PREFERRED,SSL_MODE_REQUIRED,SSL_MODE_VERIFY_CA,SSL_MODE_VERIFY_IDENTITY. If this option is not specified, the default isSSL_MODE_PREFERRED. These modes are the permitted values of themysql_ssl_mode enumeration defined inmysql.h. For more information about the security states, see the description of--ssl-mode inCommand Options for Encrypted Connections.

  • MYSQL_OPT_SSL_SESSION_DATA (argument type:void *)

    The session data to use for session reuse when establishing the next encrypted connection. It should be set beforemysql_real_connect() and aftermysql_init(). It expects the PEM session data as returned bymysql_get_ssl_session_data() and copies the result into the MYSQL handle. It is reset tonullptr (the default) aftermysql_real_connect(), unless specified otherwise through theCLIENT_REMEMBER_OPTIONS flag.

    If specified, an attempt is made to reuse the session at TLS establishment time.mysql_get_option() returns the handle set bymysql_options(), if any, and it does not increase the number reference counts.

    This option was added in MySQL 8.0.29.

  • MYSQL_OPT_TLS_CIPHERSUITES (argument type:char *)

    Which ciphersuites the client permits for encrypted connections that use TLSv1.3. The value is a list of one or more colon-separated ciphersuite names. The ciphersuites that can be named for this option depend on the SSL library used to compile MySQL. For details, seeEncrypted Connection TLS Protocols and Ciphers.

    This option was added in MySQL 8.0.16.

  • MYSQL_OPT_TLS_VERSION (argument type:char *)

    Which protocols the client permits for encrypted connections. The value is a list of one or more comma-separated protocol versions. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, seeEncrypted Connection TLS Protocols and Ciphers.

  • MYSQL_OPT_USE_RESULT (argument: not used)

    This option is unused.

  • MYSQL_OPT_WRITE_TIMEOUT (argument type:unsigned int *)

    The timeout in seconds for each attempt to write to the server. There is a retry if necessary, so the total effective timeout value is two times the option value.

  • MYSQL_OPT_ZSTD_COMPRESSION_LEVEL (argument type:unsigned int *)

    The compression level to use for connections to the server that use thezstd compression algorithm. The permitted levels are from 1 to 22, with larger values indicating increasing levels of compression. If this option is not specified, the defaultzstd compression level is 3. The compression level setting has no effect on connections that do not usezstd compression.

    For more information, seeConnection Compression Control.

    This option was added in MySQL 8.0.18. Forasynchronous operations, the option has no effect until MySQL 8.0.21.

  • MYSQL_PLUGIN_DIR (argument type:char *)

    The directory in which to look for client plugins.

  • MYSQL_READ_DEFAULT_FILE (argument type:char *)

    Read options from the named option file instead of frommy.cnf.

  • MYSQL_READ_DEFAULT_GROUP (argument type:char *)

    Read options from the named group frommy.cnf or the file specified withMYSQL_READ_DEFAULT_FILE.

  • MYSQL_REPORT_DATA_TRUNCATION (argument type:bool *)

    Enable or disable reporting of data truncation errors for prepared statements using theerror member ofMYSQL_BIND structures. (Default: enabled.)

  • MYSQL_SERVER_PUBLIC_KEY (argument type:char *)

    The path name to a file in PEM format containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. This option applies to clients that authenticate with thesha256_password orcaching_sha2_password authentication plugin. This option is ignored for accounts that do not authenticate with one of those plugins. It is also ignored if RSA-based password exchange is not used, as is the case when the client connects to the server using a secure connection.

    IfMYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence overMYSQL_OPT_GET_SERVER_PUBLIC_KEY.

    For information about thesha256_password andcaching_sha2_password plugins, seeSHA-256 Pluggable Authentication, andCaching SHA-2 Pluggable Authentication.

  • MYSQL_SET_CHARSET_DIR (argument type:char *)

    The path name of the directory that contains character set definition files.

  • MYSQL_SET_CHARSET_NAME (argument type:char *)

    The name of the character set to use as the default character set. The argument can beMYSQL_AUTODETECT_CHARSET_NAME to cause the character set to be autodetected based on the operating system setting (seeConnection Character Sets and Collations).

  • MYSQL_SHARED_MEMORY_BASE_NAME (argument type:char *)

    The name of the shared-memory object for communication to the server on Windows, if the server supports shared-memory connections. Specify the same value as used for theshared_memory_base_name system variable. of themysqld server you want to connect to.

Theclient group is always read if you useMYSQL_READ_DEFAULT_FILE orMYSQL_READ_DEFAULT_GROUP.

The specified group in the option file may contain the following options.

OptionDescription
character-sets-dir=dir_nameThe directory where character sets are installed.
compressUse the compressed client/server protocol.
connect-timeout=secondsThe connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
database=db_nameConnect to this database if no database was specified in the connect command.
debugDebug options.
default-character-set=charset_nameThe default character set to use.
disable-local-infileDisable use ofLOAD DATA LOCAL.
enable-cleartext-pluginEnable themysql_clear_password cleartext authentication plugin.
host=host_nameDefault host name.
init-command=stmtStatement to execute when connecting to MySQL server. Automatically re-executed if reconnection occurs.
interactive-timeout=secondsSame as specifyingCLIENT_INTERACTIVE tomysql_real_connect(). SeeSection 5.4.58, “mysql_real_connect()”.
local-infile[={0|1}]If no argument or nonzero argument, enable use ofLOAD DATA LOCAL; otherwise disable.
max_allowed_packet=bytesMaximum size of packet that client can read from server.
multi-queries,multi-resultsEnable multiple result sets from multiple-statement executions or stored procedures.
multi-statementsEnable the client to send multiple statements in a single string (separated by; characters).
password=passwordDefault password.
pipeUse named pipes to connect to a MySQL server on Windows.
port=port_numDefault port number.
protocol={TCP|SOCKET|PIPE|MEMORY}The protocol to use when connecting to the server.
return-found-rowsTellmysql_info() to return found rows instead of updated rows when usingUPDATE.
shared-memory-base-name=nameShared-memory name to use to connect to server.
socket={file_name|pipe_name}Default socket file.
ssl-ca=file_nameCertificate Authority file.
ssl-capath=dir_nameCertificate Authority directory.
ssl-cert=file_nameCertificate file.
ssl-cipher=cipher_listPermissible SSL ciphers.
ssl-key=file_nameKey file.
timeout=secondsLikeconnect-timeout.
userDefault user.

timeout has been replaced byconnect-timeout, buttimeout is still supported for backward compatibility.

For more information about option files used by MySQL programs, seeUsing Option Files.

Return Values

Zero for success. Nonzero if you specify an unknown option.

Example

The followingmysql_options() calls request the use of compression in the client/server protocol, cause options to be read from the[odbc] group in option files, and disable transaction autocommit mode:

MYSQL mysql;mysql_init(&mysql);mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");mysql_options(&mysql,MYSQL_INIT_COMMAND,"SET autocommit=0");if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0)){    fprintf(stderr, "Failed to connect to database: Error: %s\n",          mysql_error(&mysql));}