[Err] 2013 - Lost connection to MySQL server during query

[Miguel61]

Hello everyone, I need your help.

I need update the field `tbl_1.Class` in my DB MySQL, and try this query update, but I have this error, can you help me?

[Err] 2013 - Lost connection to MySQL server during query

UPDATE tbl_1
LEFT JOIN tbl_2 ON tbl_1.doDate = tbl_2.doDate
AND concat(
	tbl_1.doDate,
	' ',
	LEFT(tbl_1.doHour, 5),
	':00'
)>= subtime(
	concat(
		tbl_2.doDate,
		' ',
		LEFT(tbl_2.doHour, 5)
	),
	'0:01:00'
)
AND concat(
	tbl_1.doDate,
	' ',
	LEFT(tbl_1.doHour, 5),
	':00'
)<= addtime(
	concat(
		tbl_2.doDate,
		' ',
		LEFT(tbl_2.doHour, 5)
	),
	'0:01:00'
)
AND tbl_1.myCodeLine = tbl_2.myCodeLine
AND tbl_1.id <> tbl_2.id
SET tbl_1.Class = 'ok'
WHERE
	tbl_2.eLClass = 'tm';

[Jake Arkinstall]

That query would probably take quite some time with large datasets, because of the amount of conditions being applied in the JOIN. Especially with more complex expressions appearing before some quick conditions. That might explain losing the connection - but don't bet your socks on it.

What I would, instead, suggest is to build the table with a faster (really simple commands) JOIN, and then filter out unwanted data with a where clause.

Now, in your first JOIN condition you have a tbl_1.doDate = tbl2.doDate, however later on you include the dates in a calculation.

There are also ways you can make it more efficient by subtracting the dates and seeing if they lie between - 1 minute and + 1 minute, rather than running that concatenation/addtime stuff multiple times per line.

Make a backup before trying this, but I'd guess at something like:

UPDATE tbl_1
	LEFT JOIN tbl_2 ON
			tbl_1.myCodeLine = tbl_2.myCodeLine
		AND
			tbl_1.doDate = tbl_2.doDate
		AND
			tbl_2.eLClass = 'tm';
		AND
			tbl_1.id <> tbl_2.id
	SET tbl_1.Class = 'ok'
	WHERE
	(
		CAST(SUBTIME(LEFT(tbl_1.doHour, 5), LEFT(tbl_2.doHour, 5)) AS TIME) BETWEEN CAST("-0:01:00" AS TIME) AND CAST("0:01:00" AS TIME)
	)
)

Other things may be the cause of your problems, and it's definitely possible that query isn't the cause of it. But it's worth a shot.

[Miguel61]

Hi Jake.
Your help would be very appreciated.

I try your suggestion query, I don't have the old message error, but the time of execution is almost 25 minutes... eternity time for MySQL...

Affected rows: 11816
Time: 1392.154ms

Thank you for any answer and help.