Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Mysql Query -> Error

mysql mysql_query

  • Please log in to reply
6 replies to this topic

#1 lol33d

lol33d

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 149 posts

Posted 11 May 2012 - 01:58 AM

Hi guys

i have 2 tables and is joins

my records with active status is max up 2000 Records in days

this is my query
:

SELECT `tms`.`mxID`, `tms`.`mxNAME`, `tms`.`mxBACKID`,

(SELECT COUNT(`tmh21`.`mxlID`) FROM `tbl01` `tms21` LEFT OUTER JOIN `tbl02` `tmh21` ON `tms21`.`mxID` = `tmh21`.`mxlBACKID` WHERE `tmh21`.`mxlBACKID` = `tmh`.`mxlBACKID` AND `tmh21`.`mxlCNAME` = `tmh`.`mxlCNAME` AND `tmh21`.`mxlSTATUS` = 'ACTIVE' AND `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms21`.`mxBACKID`, `tms21`.`mxNAME`) AS `dbsActive`,

(SELECT COUNT(`tmh221`.`mxlID`) FROM `tbl01` `tms221` LEFT OUTER JOIN `tbl02` `tmh221` ON `tms221`.`mxID` = `tmh221`.`mxlBACKID` WHERE `tmh221`.`mxlBACKID` = `tmh`.`mxlBACKID` AND `tmh221`.`mxlCNAME` = `tmh`.`mxlCNAME` AND `tmh221`.`mxlSTATUS` = 'WIN' AND `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms221`.`mxBACKID`, `tms221`.`mxNAME`) AS `dbsWin`,

(SELECT COUNT(`tmh22`.`mxlID`) FROM `tbl01` `tms22` LEFT OUTER JOIN `tbl02` `tmh22` ON `tms22`.`mxID` = `tmh22`.`mxlBACKID` WHERE `tmh22`.`mxlBACKID` = `tmh`.`mxlBACKID` AND `tmh22`.`mxlCNAME` = `tmh`.`mxlCNAME` AND `tmh22`.`mxlSTATUS` = 'INACTIVE' AND `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms22`.`mxBACKID`, `tms22`.`mxNAME`) AS `dbsInActive`,

(SELECT COUNT(`tmh23`.`mxlID`) FROM `tbl01` `tms23` LEFT OUTER JOIN `tbl02` `tmh23` ON `tms23`.`mxID` = `tmh23`.`mxlBACKID` WHERE `tmh23`.`mxlBACKID` = `tmh`.`mxlBACKID` AND `tmh23`.`mxlCNAME` = `tmh`.`mxlCNAME` AND `tmh23`.`mxlSTATUS` = 'CANCEL' AND `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms23`.`mxBACKID`, `tms23`.`mxNAME`) AS `dbsCanc`,

(SELECT ROUND(EXP(SUM(LOG(`mxlNUM`))), 2) FROM `tbl01` `tms233` LEFT OUTER JOIN `tbl02` `tmh233` ON `tms233`.`mxID` = `tmh233`.`mxlBACKID` WHERE `tmh233`.`mxlBACKID` = `tmh`.`mxlBACKID` AND `tmh233`.`mxlCNAME` = `tmh`.`mxlCNAME` AND `tmh233`.`mxlSTATUS` = 'WIN' AND `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms233`.`mxBACKID`, `tms233`.`mxNAME`) AS `dbsOdds`

FROM `tbl01` `tms` LEFT OUTER JOIN `tbl02` `tmh` ON `tms`.`mxID` = `tmh`.`mxlBACKID` WHERE `tms`.`mxSTATUS` = 'ACTIVE' GROUP BY `tms`.`mxBACKID`, `tms`.`mxNAME`


i have 2 errors in some hosting:

err1:
#1053 - Server shutdown in progress

err2:

#1317 - Query execution was interrupted

please help me
  • 1

#2 wim DC

wim DC

    Roar

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 2681 posts
  • Programming Language:Java, JavaScript, PL/SQL
  • Learning:Python

Posted 11 May 2012 - 03:18 AM

Can you get the logs from the server?

Judging by the size of that query you may be reaching the max_packet size of the server.
  • 0

#3 lol33d

lol33d

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 149 posts

Posted 11 May 2012 - 04:14 AM

i dont access to server log.

how to change max_packet size with manualy?

this problem only in cpanel hostings, in direct admin no problem.

please help me for cpanel
  • 0

#4 wim DC

wim DC

    Roar

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 2681 posts
  • Programming Language:Java, JavaScript, PL/SQL
  • Learning:Python

Posted 11 May 2012 - 04:18 AM

I think it's the "max_allowed_packet = 1M" line in the mysql-medium.ini file. Could also be small or large, I don't know which one it uses as default.
  • 0

#5 lol33d

lol33d

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 149 posts

Posted 11 May 2012 - 04:57 AM

i add max_allowed_packet = 512M in php.ini

but my problem not solved :worry:
  • 0

#6 anago

anago

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts
  • Programming Language:C

Posted 11 May 2012 - 05:40 AM

If possible, then try this on another server, or reinstall the mysql server.

http://offlajn.com
  • 0

#7 papabear

papabear

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 472 posts
  • Location:DarkSide

Posted 11 May 2012 - 06:53 AM

the error #1053 - Server shutdown in progress might be because of your mysql server running for so much time. Please take a look at your phpmyadmin and check if how long the server is running. Have you tried restarting the servers? Sometimes it's a problem on the bad host that can't accept a large number of query because they are over loaded. You can also optimize your mysql queries to make it faster.
  • 0
Life has no CTRL+Z
Never Forget To HIT "LIKE" If I Helped





Also tagged with one or more of these keywords: mysql, mysql_query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download