MySQL Error 2013: How to increase server timeout for long-running queries

Question

I’m running a query on a large MySQL table and consistently get Error Code: 2013 – Lost connection to MySQL server during query.

Query example:

WITH duplicates AS (
 SELECT
 event_time,
 event_type,
 product_id,
 category_id,
 category_code,
 brand,
 price,
 user_id,
 user_session,
 COUNT(*) AS count
 FROM temp_feb_2020
 GROUP BY 1,2,3,4,5,6,7,8,9
 HAVING count > 1
)
SELECT SUM(count) 
FROM duplicates;

Error:

Error Code: 2013. Lost connection to MySQL server during query 30.016 sec

Context:

Table has ~4 million rows (9 columns).

Full dataset is ~20 million rows.

Cleaning/merging requires CTEs and window functions, but they time out.

What I’ve tried:

Edited my.ini file:

[mysqld]
net_read_timeout = 3600
net_write_timeout = 3600
max_allowed_packet = 128M

Tried session settings:

SET SESSION interactive_timeout = 3600;
SET SESSION wait_timeout = 3600;
SET SESSION net_read_timeout = 3600;
SET SESSION net_write_timeout = 3600;

I've also tried to to adjust the max_execution_time

set global max_execution_time = 400000;
set session max_execution_time = 400000;

Restarted MySQL after changes.

Question:
What is the correct way to increase MySQL’s server timeout so that long-running queries (30–60s+) can complete without Error 2013?

• Bill Karwin: It's not a MySQL Server timeout. The MySQL Workbench (the GUI client) has its own client-side timeout. You already asked this question on Stack Overflow, and you were directed to this answer showing how to change the client timeout in MySQL Workbench: stackoverflow.com/questions/10563619/…

Answer 1

I do not know what in particular fixed it, but i will walk through everything i did.

I went to server > status and system variables > system variables > scrolled to other/x > located mysqlx_connection_timeout and increased it. This is where you should be

Note: You have to triple click on the value to edit it.

This is the code now:

with duplicates as 
(select
 event_time,
 event_type,
 product_id,
 category_id,
 category_code,
 brand,
 price,
 user_id,
 user_session,
 count(*) as count from temp_feb_2020 group by 1,2,3,4,5,6,7,8,9 having count > 1
 )
 select sum(count) from duplicates;

Response:

1 row(s) returned 155.469 sec / 0.000 sec.

The reason for my confusion:

After I changed the mysqlx_connection_timeout and the query worked, I tested to see if that was the issue by changing it back to 30, but the query still worked. I had tried the query just before making the change and it failed that’s why I’m not entirely sure what actually fixed the problem.

• Rohit Gupta: BTW, Here you say thanks by upvoting 🙂
• Okereke Precious: i need to gain 15 reputation points first, i have just 11 🫠. yesterday was my first time ever using this platform. ill give you an upvote once i can.
• Bill Karwin: The mysqlx variables are for the MySQL X Plugin, which I don't think you're using if you're using MySQL Workbench (or almost any other client).
• Okereke Precious: like i said, i am not sure what caused it to work. I changed that and it just worked.

Answer 2

Try this to prove if its timeout. I dont think it is.

max_execution_time = 0

So,

• Increase all table size and buffer parameters.
• Increase buffer and timeouts on your client
• See if you can add indexes to improve the speed of the query

• Okereke Precious: I tried that, but it didn't have any effect. Thanks for the suggestion and the corrections.