Additional languages
« 24.2.3.48 mysql_num_rows()
24.2.3.50 mysql_ping() »
Section Navigation      [Toggle]

24.2.3.49. mysql_options()

int mysql_options(MYSQL *mysql, enum mysql_option option, const char *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.

mysql_options() should be called 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, arg should point to the value of the integer.

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 libmysql client library. For option descriptions that indicate arg is unused, its value is irrelevant; it is conventional to pass 0.

  • MYSQL_INIT_COMMAND (argument type: char *)

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

  • MYSQL_OPT_COMPRESS (argument: not used)

    Use the compressed client/server protocol.

  • MYSQL_OPT_CONNECT_TIMEOUT (argument type: unsigned int *)

    Connect timeout in seconds.

  • MYSQL_OPT_GUESS_CONNECTION (argument: not used)

    For an application linked against the libmysqld embedded server library, this allows the library to guess whether to use the embedded server or a remote server. “Guess” means that if the hostname 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 non-zero value, the LOAD LOCAL INFILE statement is enabled.

  • MYSQL_OPT_NAMED_PIPE (argument: not used)

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

  • MYSQL_OPT_PROTOCOL (argument type: unsigned int *)

    Type of protocol to use. Should be one of the enum values of mysql_protocol_type defined in mysql.h.

  • MYSQL_OPT_READ_TIMEOUT (argument type: unsigned int *)

    Timeout for reads from server (works only for TCP/IP connections, and only for Windows prior to MySQL 5.1.12). You can this option 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: 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_SET_CLIENT_IP (argument type: char *)

    For an application linked against 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_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 hostname used when connecting to the server. The connection is rejected if there is a mismatch. This feature can be used to prevent man-in-the-middle attacks. Verification is disabled by default. Added in MySQL 5.1.11.

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

    Timeout for writes to server (works currently only on Windows on TCP/IP connections).

  • 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 via MYSQL_BIND.error. (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.

  • MYSQL_SET_CHARSET_DIR (argument type: char*)

    The pathname 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.

  • 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 connection. Should have the same value as the --shared-memory-base-name option used for the mysqld server you want to connect to.

Note that 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
connect-timeout Connect timeout in seconds. On Linux this timeout is also used for waiting for the first answer from the server.
compress Use the compressed client/server protocol.
database Connect to this database if no database was specified in the connect command.
debug Debug options.
disable-local-infile Disable use of LOAD DATA LOCAL.
host Default hostname.
init-command Statement to execute when connecting to MySQL server. Automatically re-executed if reconnection occurs.
interactive-timeout Same as specifying CLIENT_INTERACTIVE to mysql_real_connect(). See Section 24.2.3.52, “mysql_real_connect()”.
local-infile[=(0|1)] If no argument or argument != 0 then enable use of LOAD DATA LOCAL.
max_allowed_packet Max size of packet client can read from server.
multi-results Allow multiple result sets from multiple-statement executions or stored procedures.
multi-statements Allow the client to send multiple statements in a single string (separated by ‘;’).
password Default password.
pipe Use named pipes to connect to a MySQL server on NT.
protocol={TCP|SOCKET|PIPE|MEMORY} The protocol to use when connecting to the server.
port Default port number.
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 (default is "MYSQL").
socket Default socket file.
user Default user.

Note that timeout has been replaced by connect-timeout, but timeout is still supported in MySQL 5.1 for backward compatibility.

For more information about option files, see Section 4.3.2, “Using Option Files”.

Return Values

Zero for success. Non-zero if you specify an unknown option.

Example 

MYSQL mysql;

mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"odbc");
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.


User Comments

Posted by Bob Peterson on April 9 2004 10:42am[Delete] [Edit]

I could use LOAD DATA LOCAL INFILE from the mysql command interface perfectly, but when I did the same command from the C/C++ API, using version 4.0.18, I consistently got Error 1148 (The used command is not allowed with this MySQL version).

I could not get mysql_options (MYSQL_OPT_LOCAL_INFILE...) to work, no matter what parameters I passed, despite following the online manual to the letter, and yes, I was getting a "successful" return code from mysql_options().

The ONLY thing that worked was specifying MYSQL_OPT_LOCAL_INFILE as the last parameter on the mysql_real_connect call.

Posted by Zvonko Ilic on March 27 2005 1:27am[Delete] [Edit]

When I upgrade from 1.0.10 FreePascal to 2.0(1.9.8)
and from 3.23 to 4.1 MySQL, I have problem with database data /character code page (cp1251-cyrillic)/.

This need to be solved with MYSQL_SET_CHARSET_NAME option:
...
mysql_init(PMySQL(@qmysql));

outcome := mysql_options(PMySQL(@qmysql), MYSQL_SET_CHARSET_NAME,'cp1251');

sock := mysql_real_connect(PMysql(@qmysql), nil,zroot,zpasswd,nil,0,nil,0);
...

Posted by Peter Kovacs on January 11 2006 4:03pm[Delete] [Edit]

Even though the documentation says that you should call mysql_options before mysql_real_connect, when setting MYSQL_OPT_RECONNECT you MUST do so after the connection has been successfully established.

This is true as late as 5.0.16.

Posted by J Jorgenson on April 4 2006 8:34pm[Delete] [Edit]

TIP...
Utilize the 8th parameter of the mysql_real_connent(...,clientFlag), BEFORE using mysql_options().

There are options that are not accessable via mysql_options() like 'multi-statements'.


Posted by yan maolin on April 4 2007 3:08am[Delete] [Edit]

Look these code:

mysql_close(&mysql);
mysql_init(&mysql);
mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);
mysql_real_connect( &mysql, "192.168.1.2",username,password, "oss",3306, "/var/lib/mysql/mysql.sock", 0 );

when excuting some times, the funtion of msyql_real_connect returning time is over 1 minute, so I want to an answer professionally. That's why?

And if any ways to solve this problem ?
Wanting you best suggestions.

E-mail: sundyfps@163.com


Add your own comment.