Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.0Mb
PDF (A4) - 38.0Mb
PDF (RPM) - 32.9Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.9Kb
Man Pages (Zip) - 189.5Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

27.7.7.50 mysql_options()

int mysql_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, use mysql_get_option().

Call mysql_options() after mysql_init() and before mysql_connect() or mysql_real_connect().

The option argument is the option that you want to set; the arg argument is the value for the option. If the option is an integer, specify a pointer to the value of the integer as the arg 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 in Section 27.7.18, “C API Encrypted Connection Support”.

The following list describes the possible options, their effect, and how arg is used for each option. For option descriptions that indicate arg 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 the mysql_clear_password cleartext authentication plugin. See Section 6.5.1.4, “Client-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. See Section 6.3.9, “Server Handling of Expired Passwords”.

  • MYSQL_OPT_COMPRESS (argument: not used)

    Use the compressed client/server protocol.

  • 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 the MYSQL_OPT_CONNECT_ATTR_RESET option, as well as the description for the MYSQL_OPT_CONNECT_ATTR_ADD option in the description of the mysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 25.11.9, “Performance 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 the MYSQL_OPT_CONNECT_ATTR_DELETE option, as well as the description for the MYSQL_OPT_CONNECT_ATTR_ADD option in the description of the mysql_options4() function. That function description also includes a usage example.

    The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 25.11.9, “Performance 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 the caching_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.

    If MYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence over MYSQL_OPT_GET_SERVER_PUBLIC_KEY.

    For information about the caching_sha2_password plugin, see Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.

  • MYSQL_OPT_LOCAL_INFILE (argument type: optional pointer to unsigned int)

    This option affects client-side LOCAL capability for LOAD DATA operations. By default, LOCAL capability is determined by the default compiled into the MySQL client library (see Section 13.2.7, “LOAD DATA INFILE Syntax”). To control this capability explicitly, invoke mysql_options() to set the MYSQL_OPT_LOCAL_INFILE option:

    • LOCAL is disabled if the pointer points to an unsigned int that has a zero value.

    • LOCAL is enabled if no pointer is given or if the pointer points to an unsigned int that has a nonzero value.

    Successful use of a LOCAL load operation by a client also requires that the server permits it.

  • MYSQL_OPT_MAX_ALLOWED_PACKET (argument: unsigned long *)

    This option sets the max_allowed_packet system variable. If the mysql argument is non-NULL, the call sets the session system variable value for that session. If mysql is NULL, the call sets the global system variable value.

  • 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 net_buffer_length system variable. If the mysql argument is non-NULL, the call sets the session system variable value for that session. If mysql is NULL, the call sets the global system variable value.

  • MYSQL_OPT_OPTIONAL_RESULTSET_METADATA (argument type: bool *)

    This flag makes result set metadata optional. It is an alternative way of setting the CLIENT_OPTIONAL_RESULTSET_METADATA connection flag for the mysql_real_connect() function. For details about managing result set metadata transfer, see Section 27.7.23, “C API Optional Result Set Metadata”.

  • MYSQL_OPT_PROTOCOL (argument type: unsigned int *)

    Type of protocol to use. Specify one of the enum values of mysql_protocol_type defined in mysql.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/IP Close_Wait_Timeout value of 10 minutes.

  • MYSQL_OPT_RECONNECT (argument type: bool *)

    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. See Section 27.7.24, “C API 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. The default value is 1 (1 retry if the initial call is interrupted for 2 tries total).

  • 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 permitted 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 *)

    Controls whether to enable FIPS mode on the client side. The MYSQL_OPT_SSL_FIPS_MODE option differs from other MYSQL_OPT_SSL_xxx options in that it is not used to establish encrypted connections, but rather to affect which cryptographic operations are permitted. See Section 6.6, “FIPS Support”.

    Permitted option values are SSL_FIPS_MODE_OFF, SSL_FIPS_MODE_ON, and SSL_FIPS_MODE_STRICT.

    Note

    If the OpenSSL FIPS Object Module is not available, the only permitted value for MYSQL_OPT_SSL_FIPS_MODE is SSL_FIPS_MODE_OFF. In this case, setting MYSQL_OPT_SSL_FIPS_MODE to SSL_FIPS_MODE_ON or SSL_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. The default is SSL_MODE_PREFERRED. These modes are the permitted values of the mysql_ssl_mode enumeration defined in mysql.h. For more information about the security states, see the description of --ssl-mode in Section 6.4.2, “Command Options for Encrypted Connections”.

  • MYSQL_OPT_TLS_VERSION (argument type: char *)

    The protocols permitted by the client for encrypted connections. The value is a comma-separated list containing one or more protocol names. The protocols that can be named for this option depend on the SSL library used to compile MySQL. For details, see Section 6.4.6, “Encrypted Connection 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_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 from my.cnf.

  • MYSQL_READ_DEFAULT_GROUP (argument type: char *)

    Read options from the named group from my.cnf or the file specified with MYSQL_READ_DEFAULT_FILE.

  • MYSQL_REPORT_DATA_TRUNCATION (argument type: bool *)

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

  • MYSQL_SERVER_PUBLIC_KEY (argument type: char *)

    The path name of the file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. The file must be in PEM format. This option applies to clients that authenticate with the sha256_password or caching_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.

    If MYSQL_SERVER_PUBLIC_KEY is given and specifies a valid public key file, it takes precedence over MYSQL_OPT_GET_SERVER_PUBLIC_KEY.

    For information about the sha256_password and caching_sha2_password plugins, see Section 6.5.1.2, “SHA-256 Pluggable Authentication”, and Section 6.5.1.3, “Caching 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 be MYSQL_AUTODETECT_CHARSET_NAME to cause the character set to be autodetected based on the operating system setting (see Section 10.4, “Connection 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 the --shared-memory-base-name option used for the mysqld server you want to connect to.

The client group is always read if you use MYSQL_READ_DEFAULT_FILE or MYSQL_READ_DEFAULT_GROUP.

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

Option Description
character-sets-dir=dir_name The directory where character sets are installed.
compress Use the compressed client/server protocol.
connect-timeout=seconds The connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
database=db_name Connect to this database if no database was specified in the connect command.
debug Debug options.
default-character-set=charset_name The default character set to use.
disable-local-infile Disable use of LOAD DATA LOCAL INFILE.
enable-cleartext-plugin Enable the mysql_clear_password cleartext authentication plugin.
host=host_name Default host name.
init-command=stmt Statement to execute when connecting to MySQL server. Automatically re-executed if reconnection occurs.
interactive-timeout=seconds Same as specifying CLIENT_INTERACTIVE to mysql_real_connect(). See Section 27.7.7.54, “mysql_real_connect()”.
local-infile[={0|1}] If no argument or nonzero argument, enable use of LOAD DATA LOCAL; otherwise disable.
max_allowed_packet=bytes Maximum size of packet that client can read from server.
multi-queries, multi-results Enable multiple result sets from multiple-statement executions or stored procedures.
multi-statements Enable the client to send multiple statements in a single string (separated by ; characters).
password=password Default password.
pipe Use named pipes to connect to a MySQL server on Windows.
port=port_num Default port number.
protocol={TCP|SOCKET|PIPE|MEMORY} The protocol to use when connecting to the server.
return-found-rows Tell mysql_info() to return found rows instead of updated rows when using UPDATE.
shared-memory-base-name=name Shared-memory name to use to connect to server.
socket={file_name|pipe_name} Default socket file.
ssl-ca=file_name Certificate Authority file.
ssl-capath=dir_name Certificate Authority directory.
ssl-cert=file_name Certificate file.
ssl-cipher=cipher_list Permissible SSL ciphers.
ssl-key=file_name Key file.
timeout=seconds Like connect-timeout.
user Default user.

timeout has been replaced by connect-timeout, but timeout is still supported for backward compatibility.

For more information about option files used by MySQL programs, see Section 4.2.6, “Using Option Files”.

Return Values

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

Example

The following mysql_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));
}

User Comments
  Posted by Rupert Peddle on December 15, 2008
When retrying a connection, I used mysql_options() to set a connection timeout then called mysql_real_connect in a while loop:

mysql_options(...);

while(!mysql_real_connect(...))
{
nanosleep(...);
}

Doing this, it appears that the connection timeout was lost after the first iteration. I had to re-set the connection timeout after every attempt:

mysql_options(...);

while(!mysql_real_connect(...))
{
nanosleep(...);
mysql_options(...);
}

Don't know if this is the case for other options....
  Posted by Uros Reljic on April 23, 2009
Set flag CLIENT_REMEMBER_OPTIONS in client_flag when you calling mysql_real_connect().
Without this flag, if mysql_real_connect() fails, you must repeat the mysql_options() calls before trying to connect again.

Speco
  Posted by Gary Wallis on April 15, 2010
Here is some example code that can provide usec timeout for mysql_real_connect(), is based on a non blocking test connection before doing the actual mysql_real_connect().

You can find the complete example here: http://venus.openisp.net/openisp/unxsVZ/browser/trunk/unxsBind/mysqlping.c

if(DBIP1!=NULL)
{
if((iSock=socket(AF_INET,SOCK_STREAM,IPPROTO_TCP))<0)
{
printf("Could not create socket\n");
exit(1);
}
// Set non-blocking
lFcntlArg=fcntl(iSock,F_GETFL,NULL);
lFcntlArg|=O_NONBLOCK;
fcntl(iSock,F_SETFL,lFcntlArg);

//Fallback to DBIP1
memset(&sockaddr_inMySQLServer,0,sizeof(sockaddr_inMySQLServer));
sockaddr_inMySQLServer.sin_family=AF_INET;
sockaddr_inMySQLServer.sin_addr.s_addr=inet_addr(DBIP1);
sockaddr_inMySQLServer.sin_port=htons(atoi(cPort));
iConRes=connect(iSock,(struct sockaddr *)&sockaddr_inMySQLServer,sizeof(sockaddr_inMySQLServer));
if(iConRes<0)
{
if(errno==EINPROGRESS)
{
tv.tv_sec=0;
tv.tv_usec=SELECT_TIMEOUT_USEC;
FD_ZERO(&myset);
FD_SET(iSock,&myset);
if(select(iSock+1,NULL,&myset,NULL,&tv)>0)
{
lon=sizeof(int);
getsockopt(iSock,SOL_SOCKET,SO_ERROR,(void*)(&valopt),&lon);
if(valopt)
{
printf("Error in connection() %d - %s\n",valopt,strerror(valopt));
}
else
{
//Valid fast connection
close(iSock);//Don't need anymore.
mysql_init(&gMysql);
if(mysql_real_connect(&gMysql,DBIP1,DBLOGIN,DBPASSWD,
DBNAME,DBPORT,DBSOCKET,0))
{
printf("Connected to %s:%s\n",(char *)DBIP1,cPort);
mysql_close(&gMysql);
exit(0);
}
}
}
else
{
printf("DBIP1 else if select()\n");
}
}
else
{
printf("DBIP1 else if errno==EINPROGRESS\n");
}
}
close(iSock);//Don't need anymore.
}

Sign Up Login You must be logged in to post a comment.