Score:0

Server

How do I change MySQL wait_timeout in production environment at runtime?

Vincent

12/26/22, 6:20 PM

I'm running Windows, IIS, MySQL, PHP.

In my.ini under [mysqld] the value for wait_timeout is set to 60.

wait_timeout = 60

But when I execute the following:

show variables like 'wait_timeout';

It shows me that the value is 28800, which I know is the default.

So I tried to set the value by executing the following:

SET GLOBAL wait_timeout = 60;

But this doesn't seem to work. MySql Workbench tells me "0 rows(s) affected" and when I execute show variables like 'wait_timeout' it still tells me that the value is 28800.

I've also checked interactive_timeout and the story is the same. The value is 28800 and I can't change it.

What am I missing here?

1772

2 + 3

mysql

connections

MohammadReza moeini

12/27/22, 3:40 PM

Please check topic: https://stackoverflow.com/questions/22425463/set-global-variables-not-working-mysql

Wilson Hauck

12/29/22, 1:57 PM

When you want to know value of wait_timeout there are two distinct values. SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; and SHOW SESSION VARIABLES LIKE 'wait_timeout'. SHOW VARIABLES LIKE 'wait_timeout' assumes you are wanting the session variable. wait_timeout controls inactivity timeout for usual processing, interactive_timeout controls idle time tolerance (seconds) when using MySQL Command Prompt for processing. When you want to SET SESSION wait_timeout; you need to immediately follow with the request you want to run because a new MySQL command prompt is another session.

Wilson Hauck

12/29/22, 2:04 PM

Vince, Great to see you again on SF.

Score:0

Server

Vincent

12/27/22, 7:14 PM

The answer is to set the value without the GLOBAL keyword.

SET wait_timeout = 60;

0 + 0

Score:0

Server

Rick James

12/29/22, 4:39 AM

GLOBAL setting are copied into your SESSION settings when you connect. Use either of these syntaxes:

SET @@... = ...;
SET SESSION ... = ...;

wait_timeout refers to how soon before you will be disconnected. Is that what you want?

Furthermore, there is some confusion over "batch" versus "interactive" timeout. Good look.

0 + 1

Vincent

12/31/22, 10:17 PM

I was unable to get it to work with the "@@" but using the SESSION keyword was key! Thank you.

Rick James

2/28/23, 4:33 PM

@Vincent - I do make mistakes. Fixed.

Vincent

2/28/23, 5:18 PM

Sorry I changed my accepted answer. I was just reviewing this and realized that although your answer set me on the right path it was not the ultimate solution so I thought for future visitors it would be more helpful to highlight the correct solution with a green check mark.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How do I change MySQL wait_timeout in production environment at runtime?

TH: ฉันจะเปลี่ยน MySQL wait_timeout ในสภาพแวดล้อมการผลิตที่รันไทม์ได้อย่างไร

RO: Cum schimb MySQL wait_timeout în mediul de producție în timpul execuției?

RU: Как изменить MySQL wait_timeout в производственной среде во время выполнения?

VI: Làm cách nào để thay đổi MySQL wait_timeout trong môi trường sản xuất khi chạy?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.