Templatetiger-Updating: Lost connection to MySQL server during query
Open, Needs TriagePublic

Description

I want to update my templatetiger[1] database with a longer running mysql import query.

I use jsub for it:
"jsub -mem 1000m public_html/einspielen.sh"

So I start "einspielen.sh" with following content to start mysql:
mysql -h tools-db --local --database=s51071__templatetiger_p < ~/public_html/einspielen.sql

The sql-file is with 126 kB not small, but it works in the past.

But after some time I get:
"ERROR 2013 (HY000) at line 23: Lost connection to MySQL server during query"

What can I do?

[1]https://tools.wmflabs.org/templatetiger/

Kolossos created this task.Jan 3 2018, 10:14 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJan 3 2018, 10:14 PM
Kolossos updated the task description. (Show Details)Jan 3 2018, 10:58 PM
Kolossos updated the task description. (Show Details)Jan 3 2018, 11:01 PM

It's really seems related to T180380, but there is no solution.

bd808 moved this task from Backlog to ToolsDB on the Data-Services board.Mar 2 2018, 1:44 PM
Bstorm added a subscriber: Bstorm.Jun 8 2018, 8:25 PM

There is a long-running query killer on these servers that you might be running afoul of, depending on the length of time we are talking.

The import of some GB need some time. This was no problem in the past because I use "/* SLOW_OK */" in the sql-statement[1].

What can I do now to update the service?

[1] http://tools.wmflabs.org/templatetiger/einspielen.sql

Bstorm added a subscriber: jcrespo.Jun 22 2018, 5:40 PM

Taking a look at where I think the query killer lives, it seems like the comment won't have any affect. However, it could be that some exception is needed for LOAD DATA LOCAL type statements? @jcrespo Am I close to the mark here?

There is no query killer on toolsdb, users are free to create long queries as long as they don't affect other users. In any case, a 200KB is unlikely to be a case of long queries, as it should be pretty much instant (I do consider it very small).

LOAD DATA from server is restricted as it has been a cause of vulnerabilities in the past, but I don't think it should affect LOAD DATA LOCAL at all. Maybe it is trying to execute a higher level command (?) like CHANGE MASTER or something else-- they need to share the original .sql to know more. The point is that there is nothing limiting or preventing that that we are aware of. Normally those errors happen when a command that is not compatible with the existing configuration or there is a database issue- isolating the specific command causing the issue is the way to help solve it.

Oh, I just saw the sql up here, @Kolossos please execute it line by line and tell us where it fails, and we will be able to help you better, thank you!

Kolossos added a comment.EditedJun 24 2018, 5:29 PM

The line by line execution shows that the LOAD DATA LOCAL line was the point where it breaks.

Than I saw that this line was inside a

set autocommit = 0;  
            LOAD DATA LOCAL... ;
commit; set autocommit = 1;

After removing this part, that I added years ago, it seems to work.

But it's seem relatively slow, it need's 20h to import a 3GB file.

Kolossos added a comment.EditedJun 25 2018, 8:32 PM

After starting it with jsub (without autocommit=0) it lose the connection to the database during the LOAD DATA LOCAL.