mysql workbench "Lost connection to mysql server"
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
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