SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

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

    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

    Code:
    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';

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code SQL:
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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...
    Code:
    Affected rows: 11816
    Time: 1392.154ms
    Thank you for any answer and help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •