PHP - MySQL expert advice needed

admin · Post by **b0b** » 10 Feb 2010 12:50 pm

Experts: it appears that the following code is causing the forum to stall. The MySQL connection sometimes hangs in a "Sending Data" state on the SELECT statement.

Code: Select all

// Obtain a list of topic ids which contain
// posts made since user last visited
//
if ($userdata['session_logged_in'])
{
	// 60 days limit
	if ($userdata['user_lastvisit'] < (time() - 5184000))
	{
		$userdata['user_lastvisit'] = time() - 5184000;
	}

	$sql = "SELECT t.forum_id, t.topic_id, p.post_time 
		FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p 
		WHERE p.post_id = t.topic_last_post_id 
			AND p.post_time > " . $userdata['user_lastvisit'] . " 
			AND t.topic_moved_id = 0"; 

	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not query new topic info', '', __LINE__, __FILE__, $sql);
	}

	$new_topic_data = array();
	while( $topic_data = $db->sql_fetchrow($result) )
	{
			$new_topic_data[$topic_data['forum_id']][$topic_data['topic_id']] = $topic_data['post_time'];
	}

	$db->sql_freeresult($result);
}

Does anything look inherently wrong in this code?

Cal Sharp · Post by **Cal Sharp** » 10 Feb 2010 3:33 pm

I'm not an expert, just learning the basics, but you can run it thru http://www.meandeviation.com/tutorials/ ... tax-check/
to check it. PHP has built-in error checking, something like this, that you would put at the beginning of a global include file.

<code>
// Set error reporting
if (defined('DEBUG') && DEBUG == TRUE) {
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
} else {
ini_set('display_errors', FALSE);
error_reporting(0);
}
</code>

admin · Post by **b0b** » 10 Feb 2010 4:12 pm

The syntax is correct. This code works most of the time. The problem is that sometimes the SELECT statement hangs.

I've commented out the entire block. Now the Forum is running very smoothly BUT we don't get the "new messages" status icon on the Forum Index page.

This problem has been plaguing us for months. It gets worse as traffic increases and people keep refreshing the Forum Index page to check for new messages.

Richard Sinkler · Post by **Richard Sinkler** » 10 Feb 2010 4:34 pm

Have you checked into having another PHP/MySQL programmer look at your code. I would check and see how much it would cost. I'm sure that you would get more than enough donations to cover the cost.

And, try to think what change you made about the time we started having all these problems. Equipment, software, etc... These problems showed up suddenly and have been here since. I can't remember how long ago it showed up.

EDIT:

I've commented out the entire block. Now the Forum is running very smoothly BUT we don't get the "new messages" status icon on the Forum Index page.

It is running better, but I really miss those icons. If it runs most of the time, it's hard to believe it's code related. From my schooling in programming, I've never heard of code that only works sometimes. It either works or it doesn't, unless it gets corrupted then it would stop working all together. It sounds like hardware problems. But... then why would commenting out that section make a difference?

Cal Sharp · Post by **Cal Sharp** » 10 Feb 2010 4:34 pm

I suppose you've tried Repair Database in the control panel. Maybe tweaking the KILL statement, which allows the optional CONNECTION or QUERY modifier:

KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread_id.

KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.

Have you ever considered a mirror site?

admin · Post by **b0b** » 10 Feb 2010 4:49 pm

Richard, I didn't write that code - it's part of phpbb. I just discovered that it's the source of the problem. I have never modified ANY of the code in that file. I don't agree that the problem showed up suddenly. I only became aware of it gradually as traffic increased.

Cal, what control panel? I have no idea how to "tweak the KILL statement". I'm not an SQL guy, which is why I'm asking for help here. I don't know anything about mirror sites. How would using a mirror site help?

Cal Sharp · Post by **Cal Sharp** » 10 Feb 2010 5:02 pm

There's some good info here:
http://dev.mysql.com/doc/
Might be more than you want to get into, though.

Back when I had a job I went here http://www.phpbb.com/ to answer a lot of my questions about the phpBB that I had to maintain.

On my host (YRHost) I can log into my account and look at stats, add applications, etc. and also create and modify mysql databases in the control panel. But I guess you're your own server, so you go to http://localhost/

A mirror site is just another location for the forum, to spread out the load. http://php.net/mirrors.php

John Cipriano · Post by **John Cipriano** » 10 Feb 2010 8:25 pm

Is there anything useful in the PHP error log? Also, does MySQL have an error log? I use SQLite so I'm not familiar. Obviously if it does then it needs to be checked.

The CSS [edit: I mean PHP] code is generating SQL code. Some of the queries fail (or perhaps take too long) and some don't. In order to debug, you need to print out the actual SELECT statement on the page before querying the database. That way, if it hangs, you'll already have the statement printed. If you can't run a separate instance of the site to do this with, then I guess you could hide it with the CSS visibility property.

Common problems are that the is a semi-colon or apostrophe in the middle of the generated query, although in that case I would expect the script to throw the "Could not query" error and die. It's also possible that the query is too many characters long, although I doubt that as well. Maybe $userdata['user_lastvisit'] is some very small number, and the query is returning almost all of the posts in the forum.

You may also want to use the mysql shell to throw some test queries at the database that resemble what the above code would generate (there's generally no harm in doing this with a SELECT statement).

And it probably goes without saying but there may be people in the phpBB community that have seen this problem before. Or maybe if there is an upgrade available, you could try just doing the upgrade, or reverting to the stock set of scripts for this version, remove any mods, etc.

One shot in the dark thing to try would be changing the if{} block regarding the "60 day limit" to maybe a 15 day limit (that number is the number of seconds in 60 days, just swap both instances for 1296000).

admin · Post by **b0b** » 11 Feb 2010 8:16 am

Since this code works almost all of the time, it stands to reason that one of the variables in the $sql query statement is bad. I did try changing the 60 day limit to 7 days, and I tried removing the "AND t.topic_moved_id = 0" part of the statement. In both cases, a stall occurred with an hour of the change.

I think that no real error is generated by this bug. I can't seem to find the MySQL error log on this system (an Apple XServe server). It's not like a standard Windows or Linux file structure.

John Cipriano · Post by **John Cipriano** » 11 Feb 2010 8:29 am

From what I am reading it's named something like yourhostname.err, located in the data directory, but it could also be going to syslog.

You may not see anything unless it's actually throwing errors.

I wrote CSS earlier when I meant PHP, whoops.

I don't know. It's going to be hard to debug it without seeing the actual SQL statements though. What I said before about printing them to the page was dumb though. You can just log them in a separate file.

Maybe try this: create a separate log file, and before you execute the query, log it. Then, after the query is done, log some sort of "query complete" messsage. Uncomment that code for a while and scan the log file for the part that looks different.

Read this for how to do that: http://www.tizag.com/phpT/filewrite.php
It's very simple, but note that you should create two separate handles. Each one gets created just before a line is written to the log and closed immediately after. This prevents a situation where you are leaving open a file handle every time the page stalls.

It could be, as Cal mentioned, an issue of scaling, which is going to be harder to track down. But for now maybe just treat by first assuming it's the result of bad queries and then see if you can figure out what makes those queries different.

Cal Sharp · Post by **Cal Sharp** » 11 Feb 2010 8:56 am

what control panel?

Locally, I use MAMP (with web sharing turned off) on OSX to make my computer a server. Then you can type http://localhost/MAMP/ in a browser and get to your PHP/MySQL stuff.
edit: But I suppose that's irrelevant if you're using Apple XServe server.

admin · Post by **b0b** » 11 Feb 2010 9:27 am

John, understand that this query code is part of the index.php file of the forum - the Main Index page. It gets run several times every second, and it's only been stalling maybe once an hour during peak traffic. The stall clears itself after about 5 minutes.

Steve Norman · Post by **Steve Norman** » 11 Feb 2010 10:24 am

Could the problem be in the data base that sql is retrieving from? wrong format or something to that effect? Have you changed anything in the data base since the problem started?

admin · Post by **b0b** » 11 Feb 2010 2:13 pm

Steve, I don't think that the problem is the database. I think that it might be caused by people leaning on their Refresh button at the Forum Index page, waiting to pounce on new posts. Just a hunch.

I've changed the page now to be less dynamic, avoiding that computationally expensive query that caused the problem.

Steve Norman · Post by **Steve Norman** » 11 Feb 2010 3:15 pm

looks good on this end now.

This may be relevant
http://forums.opensuse.org/applications ... erver.html

Gwyneth Morgan · Post by **Gwyneth Morgan** » 20 Feb 2010 5:48 am

Coming to this late, I know, but it occurs to me that you might add a LIMIT clause to your SELECT. A large result coming back from this query could very well hang things up for a bit.