How to set SQL query timeout (about timeout processing) _ MySQL

SQL query timeout setting method (about timeout processing) bitsCN.com in order to optimize the OceanBase's query timeout setting method, this article investigates MySQL's processing of timeout, recording as follows.

Mysql> show variables like '% time % ';
+ ---------------------------- + ------------------- +
| Variable_name | Value |
+ ---------------------------- + ------------------- +
| Connect_timeout | 10 |
| Datetime_format | % Y-% m-% d % H: % I: % s |
| Delayed_insert_timeout | 300 |
| Flush_time | 1800 |
| Innodb_lock_wait_timeout | 50 |
| Innodb_old_blocks_time | 0 |
| Innodb_rollback_on_timeout | OFF |
| Interactive_timeout | 28800 |
| Lc_time_names | en_US |
| Lock_wait_timeout | 31536000 |
| Long_query_time | 10.000000 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Slow_launch_time | 2 |
| System_time_zone |
| Time_format | % H: % I: % s |
| Time_zone | SYSTEM |
| Timed_mutexes | OFF |
| Timestamp | 1366027807 |
| Wait_timeout | 28800 |
+ ---------------------------- + ------------------- +
21 rows in set, 1 warning (0.00 sec)

Several parameters are described as follows:
Connect_timeout:
The number of seconds that the mysqld server waits for a connect packet before respondingwith Bad handshake. the default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that. increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.
Explanation: The timeout time for handshaking is only valid during logon when the link is obtained. the parameter "login successful" will be ignored. The main purpose is to prevent the number of connections from increasing too quickly due to application reconnection when the network is poor, which is generally the default value.
Interactive_timeout:
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect (). See alsowait_timeout.
Explanation: How long is a thread in SLEEP state closed. Every time a thread is used, it is awakened to the acriworkflow status. after the Query is executed, it becomes the interactive status and the timer starts again. Wait_timeout is different in that it only acts on the threads of TCP/IP and Socket links, and the meaning is the same.
MySQL can configure the connection timeout time. if the time is too long or even 10 minutes, this may happen. where are the 3000 connections full and sleep, the new link cannot be added, resulting in service failure. Therefore, configure a logical value, 60 s or 120s, and so on.
Speaker:
The interval before the next command arrives is interactive_time. If the interval exceeds interactive_timeout, the connection is automatically disconnected and the next command fails. However, mysql clients generally have an automatic reconnection mechanism. the next command will be executed after reconnection.

Mysql> set interactive_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
Mysql> show session variables like '% timeout % ';
+ ---------------------------- + ---------- +
| Variable_name | Value |
+ ---------------------------- + ---------- +
| Connect_timeout | 10 |
| Interactive_timeout | 1 |
| Wait_timeout | 28800 |
+ ---------------------------- + ---------- +
10 rows in set (0.00 sec)


Mysql> set wait_timeout = 1;
Query OK, 0 rows affected (0.00 sec)
[Go to a cup of tea and wait]
Mysql> show session variables like '% timeout % ';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 7
Current database: *** NONE ***
+ ---------------------------- + ---------- +
| Variable_name | Value |
+ ---------------------------- + ---------- +
| Connect_timeout | 10 |
| Interactive_timeout | 28800 |
| Wait_timeout | 28800 |
+ ---------------------------- + ---------- +
10 rows in set (0.01 sec)

Wait_timeout:
The number of seconds the server waits for activity on a noninteractive connection .) Before closing it. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client
Here we will explain what non-interactive connection is.
> Non-Interactive Commands
Just do a quick look up on a table without logging into the client, running the query then logging back out again.
You can instead just type one line using the '-E' flag.

C:/mysql/bin/mysql-u admin-p myDatabase-e 'select * FROM employee'

Net_read_timeout/net_write_timeout
The number of seconds to wait for more data from a connection before aborting the read. before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. when the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. when the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. see also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.
Explanation: This parameter is only valid for TCP/IP links. it is the timeout time when the database waits for the receiving client to send the network packet and sends the network packet to the client, this parameter is valid only for threads in the Activity state.
JDBC setQueryTimeout function:
To avoid endless query or long time, and cause thread blocking, stmt is used after obtaining the Statement instance. setQueryTimeout (10); avoid thread blocking caused by queries.
But yesterday it was found that the program encountered an exception, "ORA-01013: User request to cancel the current operation. Manual execution of an error SQL statement found that the statement took more than 20 seconds. Because setQueryTimeout (10), an exception is thrown before the query statement is executed. When using setQueryTimeout (10), you must set the time to be longer, for example, 60 seconds or more. As long as the thread is not blocked for a long time, you can. Too short it is easy to throw, an exception in "ORA-01013: User request to cancel the current operation"
How JDBC implements setQueryTimeout:

Class IfxCancelQueryImpl extends TimerTask
Implements IfmxCancelQuery
{
IfxStatement stmt;
Timer t = null;
Public void startCancel (IfxStatement paramIfxStatement, int paramInt)
Throws Exception
{
This. stmt = paramIfxStatement;
This. t = new Timer (true );
This. t. schedule (this, paramInt * 1000 );
}
Public void run ()
{
Try
{
This. stmt. cancel ();
This. t. cancel ();
}
Catch (SQLException localSQLException)
{
This. t. cancel ();
Throw new Error (localSQLException. getErrorCode () + ":" + localSQLException. getMessage ());
}
}
}

It can be seen that query timeout is implemented through the client solution, and does not need to be known on the server side. A timer thread is used to monitor the execution time. if the execution time times out, the schedule run () function is used.
Reference:
Http://wangwei.cao.blog.163.com/blog/static/10236252620111119115540534/
Http://sls8204.blog.163.com/blog/static/62979632200741683453114/
OceanBase supports query timeout through the server, and can set query timeout without interrupting the current session. This is more advanced than MySQL. BitsCN.com