How to Set MySQL query timeout

Welcome to the Linux community forum and interact with 2 million technical staff. Here we will explain what is non-interactiveconnectionNon-interactivecommandsjustdoaquicklookuponatablewithoutlogginggatetheclient, runningthequerythenloggingbackoutaga

Welcome to the Linux community forum, interact with 2 million technical staff> here we will explain what is non-interactive connection Non-Interactive Commands Just do a quick look up on a table without logging into the client, running the query then logging back out aga

Welcome to the Linux community forum and interact with 2 million technicians>

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.

[SQL]

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:

[Java]

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.

[1] [2]