为什么重置连接？ Joomla和MySQL

Question

SELECT t1.id,t1.tx_id,t1.tx_date,t1.bx_date,t1.method,t1.theater_id,t1.showtime_id,t1.category_id,t1.amount,t1.fname,t1.status,t1.mobile,(CASE WHEN (t4.type = '1') THEN ( (t1.full_tickets * 2 ) + (t1.half_tickets)) ELSE ( t1.full_tickets + t1.half_tickets ) END) as no_seats ,u.username FROM 'reservation` as t1 LEFT JOIN `theatercategories` as t4 ON t1.category_id=t4.id JOIN `users` AS u ON u.id = t1.user_id WHERE t1.bx_date >= '2012-08-01' AND t1.bx_date <= '2012-08-31' ORDER BY t1.id desc

Above query returns "The connection was reset" error.

It loads 75,000 records(75,195 total, Query took 15.2673 sec). I use MYSQL with Joomla. What seemes be the issue ?

Please guide me. Thanks

Answer 1

There are a number of possible solutions ... depends on the "why" ... so it ends up being a bit of trial and error. On a fresh install, that's tricky to determine. But, if you made a recent "major" change that's a place to start looking - like modifying virtual hosts or adding/enabling XDebug.

Here's a list of things I've used/done/tried in the past

• check for infinite loops ... in particular looping through a SQL fetch result which works 99% of the time except the 1% it doesn't. In one case, I was using the results of two previous queries as the upper and lower bounds of a for loop ... and occasionally got a upper bound of a UINT max ... har har har (vomit)

  copying the ./php/libmysql.dll to the windows/system32 directory (Particularly if you see Parent: child process exited with status 3221225477 -- Restarting in your log files ... check out: http://www.java-samples.com/showtutorial.php?tutorialid=1050)

  if you modify PHP's error_reporting at runtime ... in certain circumstances this can cause PHP to degenerate into an unstable state if, say, in your PHP code you modify the superglobals or fiddle around with other deep and personal background system variables (Nah, who would ever do such evil hackery? ahem)

  if you convert your MySQL to something other than MyISAM or mysqli

  There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes (http://bugs.mysql.com/bug.php?id=4541) Solution is to use InnoDB dialect (eg sql set GLOBAL storage_engine='InnoDb';)

  Doing that changes how new tables are created ... which might slightly alter the way results are returned to a fetch statement ... leading to an infinite loop, a malformed dataset, etc. (although this change should not hang the database itself)

  Other helpful items are to ramp up the debug reporting for PHP and apache in their config files and restart the servers. The log files sometimes give a clue as to at least where the problem might reside. If it happens after your page content was finished it's more likely in the php settings. If it's during page construction, check your PHP code. Etc. etc.

Hope the above laundry list helps ...