mysql workbench "Lost connection to mysql server"

Question

First I need to indicate that I ran the sql file for the Wikipedia dump on my machine, and to be able to run that I needed to update many settings regarding the index size available on memory and some other settings. I just want to mention that those large sql queries were run successfully and I didn't have any problem regarding memory or time out.

Now I have a table pagelinks (pl_from, pl_title) that shows the links that appears in each wikipedia page, for example the data can be (1, "title1"), (1,"title2"), (2, "title3"), (2, "title1"). I want to create a table that concat the titles group by pl_from. For that this is my sql query (I am using workbench):

SET @@group_concat_max_len=150000;
create table concatpagelinks 
(SELECT pl_from, GROUP_CONCAT(pl_title , ' ') as links FROM pagelinks GROUP BY pl_from)

Running this query I got the error : "Lost connection to mysql server during query" and the system asked me again for the password. So I search and found this. Therefore I changed net_read_timeout to 1000 and connect_timeout to 60. It didn't solve the problem, SO I changed the query to :

SET @@group_concat_max_len=150000;
create table concatpagelinks 
(SELECT pl_from, GROUP_CONCAT(pl_title , ' ') as links FROM pagelinks GROUP BY pl_from limit 0,1000)

Still the same problem, and the amazing thing is that every time the query is run for 600.495 sec and the error happens.

Answer 1

You can try to change the timeout value on Workbench. Go to: Edit → Preferences → SQL Editor → DBMS connection read time out

See this post for more details:

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

Or try to increase the value in: Edit → Preferences → SQL Editor → DBMS_Connection keep alive interval