Search code examples
mysqlconnection

How can I expire a MySQL wait timeout?

I've read the MySQL docs, and other SO questions didn't answer my question.

I've set MySQL's GLOBAL wait_timeout to 600 seconds, which means it'll end in 10 minutes. and it was ending in 14 minutes, as I've tested; I've attached the response error image. enter image description here

Here is image of setting global wait_timeout enter image description here

What is happening now? I've visited some questions to implement connection polling, where I found some solutions. but when I performed those solutions, wait_timeout stopped working. which is becoming my headache. I want wait_timeout to be ended at the time I've set, but it's not ending. see the picture below the wait_timeout is not getting updated

enter image description here

Solution

  • Well, after researching more deeply, I've found my mistake was to not use the workbench and knowledge of wait_timeout working.

    So, here is what I found.

    When a global variable is modified in the current session but not persistent, MySQL displays the persistent value rather than the updated value (in the terminal). (But one can see it changing on the workbench.)

    A workbench image

    enter image description here

    Mean While in the terminal,

    enter image description here

    Both in the same time

    enter image description here

    When one uses the SET GLOBAL command to change a global variable, changes are made instantly for the current session but are not saved to the MySQL configuration file. If one ends the session or restarts the MySQL server, the variable's original value will be utilized.

    MySql waits information,

    wait_timeout is a global variable that sets the timeout for idle client connections. It applies to all client connections, regardless of the protocol used (including X Protocol connections). When a client connection is idle (i.e., not executing any queries) for longer than the specified wait_timeout, the connection is closed by the server.

    mysqlx_interactive_timeout is also a global variable that sets the timeout for interactive sessions that use the MySQL X Protocol. It applies only to X Protocol connections and is used to set the maximum duration of an interactive session between the client and the server. An interactive session is defined as a session that has issued at least one statement, but has not issued a statement that requires a longer timeout, such as a long-running query or a transaction. When the mysqlx_interactive_timeout value is exceeded, the session is closed by the server.

    mysqlx_wait_timeout is another global variable that sets the timeout for client connections that use the MySQL X Protocol. It applies only to X Protocol connections and is used to set the maximum duration of a client's wait time for the server to send a response. When the mysqlx_wait_timeout value is exceeded, the client connection is closed by the server.

    In summary, wait_timeout sets the timeout for idle client connections, mysqlx_interactive_timeout sets the timeout for interactive sessions that use the X Protocol, and mysqlx_wait_timeout sets the timeout for client connections that use the X Protocol while waiting for a response.