MySQL :: MySQL 5.6 Reference Manual :: 23.8.7.49 mysql

MySQL 5.6 Reference Manual / ... / mysql_options()

23.8.7.49 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.

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.

The following list describes the possible options, their effect, and how arg is used for each option. Several of the options apply only when the application is linked against the libmysqld embedded server library and are unused for applications linked against the libmysqlclient client library. 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: my_bool *)
Enable the mysql_clear_password cleartext authentication plugin. (See Section 6.5.1.7, “The Cleartext Client-Side Authentication Plugin”.) This option was added in MySQL 5.6.7.
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). This option was added in MySQL 5.6.1.
MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS (argument type: my_bool *)
Indicate whether the client can handle expired passwords. For more information, see Section 6.3.6, “Password Expiration and Sandbox Mode”. This option was added in MySQL 5.6.10.
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. These options were added in MySQL 5.6.6.
The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 22.9.8, “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. These options were added in MySQL 5.6.6.
The Performance Schema exposes connection attributes through the session_connect_attrs and session_account_connect_attrs tables. See Section 22.9.8, “Performance Schema Connection Attribute Tables”.
MYSQL_OPT_CONNECT_TIMEOUT (argument type: unsigned int *)
The connect timeout in seconds.
MYSQL_OPT_GUESS_CONNECTION (argument: not used)
For an application linked against the libmysqld embedded server library, this enables the library to guess whether to use the embedded server or a remote server. “Guess” means that if the host name is set and is not localhost, it uses a remote server. This behavior is the default. MYSQL_OPT_USE_EMBEDDED_CONNECTION and MYSQL_OPT_USE_REMOTE_CONNECTION can be used to override it. This option is ignored for applications linked against the libmysqlclient client library.
MYSQL_OPT_LOCAL_INFILE (argument type: optional pointer to unsigned int)
If no pointer is given or if pointer points to an unsigned int that has a nonzero value, the LOAD DATA LOCAL INFILE statement is enabled.
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_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.
Implementation of this timeout uses mechanisms that may not be available on all platforms. On such a platform, a client that issues a read call might under certain circumstances wait without timing out. For example, a client might not time out if the server is not responding because it is waiting for a “disk full” condition to clear.
MYSQL_OPT_RECONNECT (argument type: my_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.
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).
This option can be used only by clients that link against a C client library compiiled with MySQL Cluster support. It is available as of MySQL Cluster 7.4.11.
MYSQL_OPT_SSL_CA (argument type: char *)
The path to a file in PEM format that contains a list of trusted SSL CAs. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_CAPATH (argument type: char *)
The path to a directory that contains trusted SSL CA certificates in PEM format. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_CERT (argument type: char *)
The name of an SSL certificate file in PEM format to use for establishing a secure connection. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_CIPHER (argument type: char *)
A list of permissible ciphers to use for SSL encryption. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_CRL (argument type: char *)
The path to a file containing certificate revocation lists in PEM format. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_CRLPATH (argument type: char *)
The path to a directory that contains files containing certificate revocation lists in PEM format. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_KEY (argument type: char *)
The name of an SSL key file in PEM format to use for establishing a secure connection. This option was added in MySQL 5.6.3.
MYSQL_OPT_SSL_VERIFY_SERVER_CERT (argument type: my_bool *)
Enable or disable verification of the server's Common Name value in its certificate against the host name used when connecting to the server. The connection is rejected if there is a mismatch. For encrypted connections, this feature can be used to prevent man-in-the-middle attacks. Verification is disabled by default.
MYSQL_OPT_USE_EMBEDDED_CONNECTION (argument: not used)
For an application linked against the libmysqld embedded server library, this forces the use of the embedded server for the connection. This option is ignored for applications linked against the libmysqlclient client library.
MYSQL_OPT_USE_REMOTE_CONNECTION (argument: not used)
For an application linked against the libmysqld embedded server library, this forces the use of a remote server for the connection. This option is ignored for applications linked against the libmysqlclient client library.
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: my_bool *)
Enable or disable reporting of data truncation errors for prepared statements using the error member of MYSQL_BIND structures. (Default: enabled.)
MYSQL_SECURE_AUTH (argument type: my_bool *)
Whether to connect to a server that does not support the password hashing used in MySQL 4.1.1 and later. As of MySQL 5.6.7, this option is enabled by default.
MYSQL_SERVER_PUBLIC_KEY (argument type: char *)
The path name to a file containing the server RSA public key. The file must be in PEM format. The public key is used for RSA encryption of the client password for connections to the server made using accounts that authenticate with the sha256_password plugin. This option is ignored for client accounts that do not authenticate with that plugin. It is also ignored if password encryption is not needed, as is the case when the client connects to the server using an SSL connection.
The server sends the public key to the client as needed, so it is not necessary to use this option for RSA password encryption to occur. It is more efficient to do so because then the server need not send the key.
For additional discussion regarding use of the sha256_password plugin, including how to get the RSA public key, see Section 6.5.1.4, “The SHA-256 Authentication Plugin”.
This option was added in MySQL 5.6.6.
MYSQL_SET_CHARSET_DIR (argument type: char *)
The path name to 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.1.5, “Connection Character Sets and Collations”).
MYSQL_SET_CLIENT_IP (argument type: char *)
For an application linked against the libmysqld embedded server library (when libmysqld is compiled with authentication support), this means that the user is considered to have connected from the specified IP address (specified as a string) for authentication purposes. This option is ignored for applications linked against the libmysqlclient client library.
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. Added in MySQL 5.6.7.
`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 23.8.7.53, “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 of 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));
}

This code requests that the client use the compressed client/server protocol and read the additional options from the odbc section in the my.cnf file.

PREV HOME UP NEXT

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.
}