Locked tables / Copying to tmp table / mySQL crashes

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Locked tables / Copying to tmp table / mySQL crashes

Post by NewBuyer »

URL: http://forums.alpinezone.com/
Template(s) used: subSilver, modified subSilver templates, subTrail
Any and all MODs: some custom mods. easymod, post_img_resize, search_titles_only, simple_quick_reply. embedded Gallery v1.5.2-pl2, phpBB fetch all
Do you use a port of phpBB: no
Version of phpBB: 2.0.19
Version of PHP: 4.4.2
Which database server and version: mySQL 4.0.25-standard
Host: JaguarPC
Did someone install this for you/who: no
Is this an upgrade/from what to what: originally yes, from around 2.0.1, but started fresh with 2.0.19
Is this a conversion/from what to what: no
Have you searched for your problem: yes, endlessly.
If so, what terms did you try: locked tables, query, sessions, Copying to tmp table
State the nature of your problem: Something has gone awry with my forum. My host indicated the following queries were "stuck":
| 474493 | user_user | localhost | user_phpbb | Query | 79 | Locked | SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt,
|
| 474596 | user_user | localhost | user_phpbb | Query | 101 | Copying to tmp table | SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt,
|
| 474612 | user_user | localhost | user_phpbb | Query | 101 | Copying to tmp table | SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt,
|
| 474621 | user_user | localhost | user_phpbb | Query | 101 | Copying to tmp table | SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt,
|
| 474645 | user_user | localhost | user_phpbb | Query | 100 | Locked | UPDATE topics
SET topic_views = topic_views + 1
WHERE topic_id = 62 |
| 474657 | user_user | localhost | user_phpbb | Query | 98 | Locked | UPDATE users
SET user_session_time = 1140019866, user_session_page = 0, user_lastvisit = 11400 |
| 474659 | user_user | localhost | user_phpbb | Query | 98 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 474662 | user_user | localhost | user_phpbb | Query | 97 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474663 | user_user | localhost | user_phpbb | Query | 96 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = 'f742baa25b901a4729d016a66ca41 |
| 474668 | user_user | localhost | user_phpbb | Query | 95 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474672 | user_user | localhost | user_phpbb | Query | 94 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 474674 | user_user | localhost | user_phpbb | Query | 93 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474685 | user_user | localhost | user_phpbb | Query | 92 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '1ae92b8bbc72558f3cdb422c17dba |
| 474687 | user_user | localhost | user_phpbb | Query | 92 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474691 | user_user | localhost | user_phpbb | Query | 91 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474698 | user_user | localhost | user_phpbb | Query | 89 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474701 | user_user | localhost | user_phpbb | Query | 87 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '870b605fb532d13ef7ffa879543cf |
| 474715 | user_user | localhost | user_phpbb | Query | 81 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '6bb673b8fbc62a832e6e10104e652 |
| 474716 | user_user | localhost | user_phpbb | Query | 81 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474717 | user_user | localhost | user_phpbb | Query | 80 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474722 | user_user | localhost | user_phpbb | Query | 79 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = 'f16a64ecb5cbfba2229493517c9bc |
| 474725 | user_user | localhost | user_phpbb | Query | 77 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474731 | user_user | localhost | user_phpbb | Query | 76 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474736 | user_user | localhost | user_phpbb | Query | 72 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474741 | user_user | localhost | user_phpbb | Query | 69 | Locked | SELECT u.*
FROM users u, sessions_keys k
WHERE u.user_id = 1823
AND u.user_active = |
| 474750 | user_user | localhost | user_phpbb | Query | 66 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474755 | user_user | localhost | user_phpbb | Query | 62 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474756 | user_user | localhost | user_phpbb | Query | 62 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474758 | user_user | localhost | user_phpbb | Query | 61 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474759 | user_user | localhost | user_phpbb | Query | 61 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474763 | user_user | localhost | user_phpbb | Query | 61 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474771 | user_user | localhost | user_phpbb | Query | 55 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = 'bd82bce367c6dae17376b5b070b84 |
| 474782 | user_user | localhost | user_phpbb | Query | 53 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474812 | user_user | localhost | user_phpbb | Query | 39 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474815 | user_user | localhost | user_phpbb | Query | 39 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '821645cafa78e4b1967baf50aa23a |
| 474816 | user_user | localhost | user_phpbb | Query | 38 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '6474ee709cdcc87a0cb19d4acec97 |
| 474822 | user_user | localhost | user_phpbb | Query | 37 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = 'f16a64ecb5cbfba2229493517c9bc |
| 474825 | user_user | localhost | user_phpbb | Query | 35 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474832 | user_user | localhost | user_phpbb | Query | 31 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474833 | user_user | localhost | user_phpbb | Query | 31 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474834 | user_user | localhost | user_phpbb | Query | 30 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474835 | user_user | localhost | user_phpbb | Query | 29 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 474837 | user_user | localhost | user_phpbb | Query | 29 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474838 | user_user | localhost | user_phpbb | Query | 29 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = 'f16a64ecb5cbfba2229493517c9bc |
| 474841 | user_user | localhost | user_phpbb | Query | 27 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474850 | user_user | localhost | user_phpbb | Query | 24 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474862 | user_user | localhost | user_phpbb | Query | 18 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474867 | user_user | localhost | user_phpbb | Query | 16 | Locked | SELECT u.*, s.*
FROM sessions s, users u
WHERE s.session_id = '5198abfbef3b9f2496ccebb38b48b |
| 474879 | user_user | localhost | user_phpbb | Query | 12 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474882 | user_user | localhost | user_phpbb | Query | 11 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474883 | user_user | localhost | user_phpbb | Query | 11 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474884 | user_user | localhost | user_phpbb | Query | 11 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474892 | user_user | localhost | user_phpbb | Query | 8 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 474893 | user_user | localhost | user_phpbb | Query | 8 | Locked | SELECT *
FROM users
WHERE user_id = -1 |
| 474903 | user_user | localhost | user_phpbb | Query | 4 | Locked | SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.top |
| 474904 | user_user | localhost | user_phpbb | Query | 4 | Locked | SELECT *
FROM users
WHERE user_id = -1 |

Do you have a test account for us: no. Could create one if need be.

Apparently this results in the entire mySQL server getting knocked offline. I tried completely reinstalling the forum script including the latest version mods available. I also completely rebuilt the database and am now using one from a fresh backup. I've run several repairs on the database via phpMyAdmin and have also run the "update_to_latest.php" script again. I even had my host move the site to a different server and it brought down the mySQL server shortly after the move (no problems on that server before then).

I can't find anything specific regarding "locked tables" or "Copying to tmp table". The forum is busy, but not by most standards - 70,000+ posts, 2,000+ members. Maybe 100-200 posts per day. I also use phpBB Fetch All heavily on the rest of the site to post components from the forums on other site pages (recent topics, news articles, rss, etc.). Little has changed recently in terms of code and traffic is actually a bit down from last month.

Any thoughts? Or places to begin looking? Thanks in advance!
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

This is something you have to bring up to whoever is in control of the MySQL server; errors like this normally require that the server be restarted, because it thinks one or more processes have locked tables, but those processes are no longer around to unlock them.

You may also need to run a repair against the tables in question.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

espicom wrote: This is something you have to bring up to whoever is in control of the MySQL server; errors like this normally require that the server be restarted, because it thinks one or more processes have locked tables, but those processes are no longer around to unlock them.

I understand that the mySQL server needed to be restarted to free up these locks. The problem is this keeps occurring and the host indicated I need to check with the script developers. I'm trying to find out why this keeps happening.
espicom wrote: You may also need to run a repair against the tables in question.

As I mentioned above, I've done several repairs and even recreated a new database from a backup.
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

The errors that are occuring are indicative of problems on the MySQL server, not in the scripts. PHPBB does not use locking... so the locks are being automatically applied by MySQL to cover inserts and such, but not being released automatically. There isn't a lot PHPBB can do about things being done behind its programatic back... :wink:

I'd recommend that they run a file system integrity check on the MySQL server, and check their system logs for events that could affect it. And I recommend you do frequent backups, just in case.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

espicom wrote: The errors that are occuring are indicative of problems on the MySQL server, not in the scripts. PHPBB does not use locking... so the locks are being automatically applied by MySQL to cover inserts and such, but not being released automatically. There isn't a lot PHPBB can do about things being done behind its programatic back... :wink:

I'd recommend that they run a file system integrity check on the MySQL server, and check their system logs for events that could affect it. And I recommend you do frequent backups, just in case.

Is it likely that the mySQL server is on one system, while my site is stored on another? I did have them move the site to a different semi-dedicated server, but perhaps it's still looking at the same mySQL server?
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

NewBuyer wrote:
espicom wrote:The errors that are occuring are indicative of problems on the MySQL server, not in the scripts. PHPBB does not use locking... so the locks are being automatically applied by MySQL to cover inserts and such, but not being released automatically. There isn't a lot PHPBB can do about things being done behind its programatic back... :wink:

I'd recommend that they run a file system integrity check on the MySQL server, and check their system logs for events that could affect it. And I recommend you do frequent backups, just in case.

Is it likely that the mySQL server is on one system, while my site is stored on another? I did have them move the site to a different semi-dedicated server, but perhaps it's still looking at the same mySQL server?

Here is the host's response:
The database is hosted on the same server where your website content is hosted, therefore it was moved and is not the same as was used in the old server.


Therefore, when the site was moved to a new server, the databases were moved as well. So the issue "followed" me during the server move. Is it possible the database is corrupted or something? If so, is there anything I could try short of starting over? Any other thoughts?
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 »

One of your MODs are executing a really bad query performance wise on the Server. Unfortunately you didn't post the whole thing, but the root of the problem looks to be the query starting with

Code: Select all

SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt, 
Although I can't see the entire query, a 5 way join on the largest tables in the database is never good. Add to that, whatever is in the FROM clause can make it entirely worse. Authors who write queries like this give MODs a bad name... you'll need to find out where this query is and remove/fix it in your scripts.

You might have some luck searching your .php files for "SELECT f.*, p.*, pt.*, t.*, u.*"
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

R45 wrote: One of your MODs are executing a really bad query performance wise on the Server. Unfortunately you didn't post the whole thing, but the root of the problem looks to be the query starting with

Code: Select all

SELECT f.*, p.*, pt.*, t.*, u.*
FROM topics AS t,
users AS u,
posts_text AS pt, 
Although I can't see the entire query, a 5 way join on the largest tables in the database is never good. Add to that, whatever is in the FROM clause can make it entirely worse. Authors who write queries like this give MODs a bad name... you'll need to find out where this query is and remove/fix it in your scripts.

You might have some luck searching your .php files for "SELECT f.*, p.*, pt.*, t.*, u.*"

Okay. Great. A suggestion on a place to look. This query is part of the phpBB Fetch All script which I unfortunately rely on heavily on the entire site to pull bits of data from the forum and display them on pages outside the forum (recent discussions, news articles, RSS feeds, etc.). In essense, a visit to ANY page of my site results in some queries on the database for data stored there. Perhaps, I'm taxing the mySQL server by using this script. The query is in the phpbb_fetch_all\posts.php file here:

Code: Select all

<?php
###############################################################################
##                                                                           ##
## phpBB Fetch All - A modification to phpBB that displays data from the     ##
##                   forum on any page of a website.                         ##
## ------------------------------------------------------------------------- ##
## This module contains functions for fetching post related data.            ##
##                                                                           ##
###############################################################################
##                                                                           ##
## Authors: Volker 'Ca5ey' Rattel <webmaster@phpbbfetchall.com>              ##
##          http://www.phpbbfetchall.com/                                    ##
##                                                                           ##
## This file is free software; you can redistribute it and/or modify it      ##
## under the terms of the GNU General Public License as published  by the    ##
## Free Software Foundation; either version 2, or (at your option) any later ##
## version.                                                                  ##
##                                                                           ##
## This file is distributed in the hope that it will be useful, but WITHOUT  ##
## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or     ##
## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for  ##
## more details.                                                             ##
##                                                                           ##
###############################################################################

###############################################################################
## CONFIGURATION
###############################################################################

//
// limit number of fetched posts
// 0 = unlimited
//

$CFG['posts_limit'] = 5;

//
// exclude forums from fetching
// can be left blank to disable it, can contain a single forum id or an array
// with multiple id's
//

$CFG['posts_exclude_forums'] = '';

//
// Determine the order of fetched posts.
// Note: You can use this option to change the way the posts will be
// fetched/displayed.
// WARNING: BE CAREFUL WITH THE TEXT YOU ENTER HERE.
// p.post_time DESC = order by the post time descending
//
// Be sure to skim through the phpbb_fetch_posts() function to get
// familar with the field names and their prefixes.
//

$CFG['posts_order'] = 'p.post_time DESC';

//
// hide normal postings
// true / false
//

$CFG['posts_hide_normal'] = false;

//
// hide sticky postings
// true / false
//

$CFG['posts_hide_sticky'] = false;

//
// hide announcements
// true / false
//

$CFG['posts_hide_announcements'] = false;

//
// hide locked postings
// true / false
//

$CFG['posts_hide_locked'] = false;

//
// hide moved postings
// true / false

$CFG['posts_hide_moved'] = false;

//
// hide polls
// true / false
//

$CFG['posts_hide_polls'] = false;

//
// hide user ranks
// true / false
// note: if you enable this option _every_ poster needs
// to have a rank otherwise you will not see any output
//

$CFG['posts_hide_ranks'] = true;

//
// enable bbcode
// true / false

$CFG['posts_enable_bbcode'] = true;

//
// enable html
// true / false

$CFG['posts_enable_html'] = true;

//
// hide images in post text
// true / false
//

$CFG['posts_hide_images'] = false;

//
// replace images with this text
// only works when posts_hide_images = true
//

$CFG['posts_replace_images'] = '[image]';

//
// trim post text after this character combination
// empty = disabled
//

$CFG['posts_trim_text_character'] = '';

//
// trim post text after this amount of characters
// 0 = disabled
//

$CFG['posts_trim_text_number'] = 0;

//
// trim post text after this amount of words
// 0 = disabled
//

$CFG['posts_trim_text_words'] = 0;

//
// trim topic title after this amount of characters
// 0 = disabled
//

$CFG['posts_trim_topic_number'] = 0;

//
// do not fetch postings which were posted before this date
// empty = disabled
//

$CFG['posts_date_offset_start'] = '';

//
// do not fetch postings which are newer than this date
// time() = fetch right until now
//

$CFG['posts_date_offset_end'] = time();

//
// fetch only postings which contain this search string
// empty = disabled
//

$CFG['posts_search_string'] = '';

//
// use span pages
// true / false
//

$CFG['posts_span_pages'] = false;

//
// remove target=blank links to make a new link open in the
// same browser window
// true / false
//

$CFG['posts_enable_local_links'] = false;

###############################################################################
## NO CHANGES NEEDED BELOW
###############################################################################

if (!defined('IN_PHPBB'))
{
	die('hacking attempt');
}

define('POSTS_FETCH_FIRST', 0);
define('POSTS_FETCH_LAST', 1);

$CFG['posts_span_pages_offset']  = 0;
$CFG['posts_span_pages_numrows'] = 0;
$CFG['posts_offset']             = 0;

###############################################################################
##                                                                           ##
## phpbb_fetch_posts()                                                       ##
## ------------------------------------------------------------------------- ##
## This function will fetch the first or the last posting from one or more   ##
## topics.                                                                   ##
##                                                                           ##
## PARAMETER                                                                 ##
##                                                                           ##
##     forum_id                                                              ##
##                                                                           ##
##         Can be left blank to fetch from all forums or set to a single     ##
##         forums id to fetch that specific forum. To fetch from multiple    ##
##         forums you can parse an array to it.                              ##
##                                                                           ##
##     fetch_mode                                                            ##
##                                                                           ##
##         Set it to POSTS_FETCH_FIRST to fetch the first postings of a      ##
##         topic (i.e. the posts which started the topic) or set it to       ##
##         POSTS_FETCH_LAST to fetch the last postings of a topic.           ##
##                                                                           ##
## EXAMPLE                                                                   ##
##                                                                           ##
##     $news = phpbb_fetch_posts();                                          ##
##                                                                           ##
##     for ($i = 0; $i < count($news); $i++)                                 ##
##     {                                                                     ##
##         echo $news[$i]['topic_title'] . '<br>';                           ##
##     }                                                                     ##
##                                                                           ##
###############################################################################

function phpbb_fetch_posts($forum_id = null, $fetch_mode = POSTS_FETCH_FIRST)
{
	global $CFG, $userdata;

	//
	// sanity check for dates
	//

	if ($CFG['posts_date_offset_start'] >= $CFG['posts_date_offset_end'])
	{
		phpbb_raise_error('\'posts_date_offset_start\' has to be smaller '
			. 'than \'posts_date_offset_end\'');
	}

	//
	// create a list of forums with read permission
	// (only takes action when auth_check is enabled)
	//

	phpbb_get_auth_list();

	//
	// determine the forum list based on the user input
	// and/or permissions (depends on auth check)
	//

	$forum_list = phpbb_get_forum_list($forum_id);

	//
	// if read permissions do not allow us to fetch anything
	// we return nicely
	//

	if (!$forum_list and $CFG['auth_check'])
	{
		return;
	}

	$sql = 'SELECT f.*, p.*, pt.*, t.*, u.*';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ', r.*';
	}

	$sql .= '
				FROM ' . TOPICS_TABLE     . ' AS t,
					 ' . USERS_TABLE      . ' AS u,
					 ' . POSTS_TEXT_TABLE . ' AS pt,
					 ' . POSTS_TABLE      . ' AS p,
					 ' . FORUMS_TABLE     . ' AS f';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ', ' . RANKS_TABLE . ' AS r';
	}

	$sql .= '
				WHERE';

	if ($forum_list)
	{
		$sql .= ' t.forum_id IN (' . $forum_list . ') AND';
	}

	if ($fetch_mode == POSTS_FETCH_FIRST)
	{
		$sql .= ' t.topic_first_post_id = pt.post_id
					AND t.topic_first_post_id = p.post_id AND';
	}
	else
	{
		$sql .= ' t.topic_last_post_id = pt.post_id
					AND t.topic_last_post_id = p.post_id AND';
	}

	if ($CFG['posts_date_offset_start'])
	{
		$sql .= ' p.post_time >= ' . $CFG['posts_date_offset_start'] . ' AND';
	}

	if ($CFG['posts_date_offset_end'])
	{
		$sql .= ' p.post_time <= ' . $CFG['posts_date_offset_end'] . ' AND';
	}

	if ($CFG['posts_hide_normal'])
	{
		$sql .= ' t.topic_type <> 0 AND';
	}

	if ($CFG['posts_hide_sticky'])
	{
		$sql .= ' t.topic_type <> 1 AND';
	}

	if ($CFG['posts_hide_announcements'])
	{
		$sql .= ' t.topic_type <> 2 AND';
	}

	if ($CFG['posts_hide_locked'])
	{
		$sql .= ' t.topic_status <> 1 AND';
	}

	if ($CFG['posts_hide_moved'])
	{
		$sql .= ' t.topic_status <> 2 AND';
	}

	if ($CFG['posts_hide_polls'])
	{
		$sql .= ' t.topic_vote <> 1 AND';
	}

	if ($CFG['posts_search_string'])
	{
		$sql .= ' (' . $CFG['posts_search_string'] . ') AND';
	}

	$sql .= ' t.forum_id = f.forum_id AND';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ' r.rank_id = u.user_rank AND';
	}

	$sql .= ' u.user_id = p.poster_id';

	$sql .= ' ORDER BY ' . $CFG['posts_order'];

	if ($CFG['posts_span_pages'])
	{
		$CFG['posts_span_pages_numrows'] = phpbb_numrows(phpbb_query($sql));
		if ($CFG['posts_span_pages_offset'] > $CFG['posts_span_pages_numrows'])
		{
			$CFG['posts_span_pages_offset'] =
				 $CFG['posts_span_pages_numrows'] - 1;
		}
		$CFG['posts_offset'] = $CFG['posts_span_pages_offset'];
	}
	else
	{
		$CFG['posts_offset'] = 0;
	}

	if ($CFG['posts_limit'] != 0)
	{
		$sql .= ' LIMIT ' . $CFG['posts_offset'] . ',' . $CFG['posts_limit'];
	}

	$result = phpbb_fetch_rows($sql);

	if ($result)
	{
		$orig_word        = array();
		$replacement_word = array();
		obtain_word_list($orig_word, $replacement_word);

		for ($i = 0; $i < count($result); $i++)
		{
			$result[$i]['post_time']  =
				$result[$i]['post_time']  + $CFG['time_zone'];
			$result[$i]['topic_time'] =
				$result[$i]['topic_time'] + $CFG['time_zone'];

			$result[$i]['date']       =
				date($CFG['date_format'], $result[$i]['post_time']);
			$result[$i]['time']       =
				date($CFG['time_format'], $result[$i]['post_time']);

			if (isset($result[$i]['post_edit_time']))
			{
				$result[$i]['edit_date'] =
					date($CFG['date_format'], $result[$i]['post_edit_time']);
				$result[$i]['edit_time'] =
					date($CFG['time_format'], $result[$i]['post_edit_time']);
			}
			else
			{
				$result[$i]['edit_date'] = '';
				$result[$i]['edit_time'] = '';
			}

			$result[$i]['post_text'] = phpbb_parse_text(
				$result[$i]['post_text'],
				$result[$i]['bbcode_uid'],
				$result[$i]['enable_smilies'],
				$CFG['posts_enable_bbcode'],
				$CFG['posts_enable_html'],
				$CFG['posts_hide_images'],
				$CFG['posts_replace_images'],
				$CFG['posts_enable_local_links']);

			if (count($orig_word))
			{
				$result[$i]['topic_title'] = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['topic_title']);
				$result[$i]['post_text']   = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['post_text']);
			}

			$result[$i]['trimmed'] = false;
			phpbb_trim_text($result[$i]['post_text'],
				$result[$i]['trimmed'],
				$CFG['posts_trim_text_character'],
				$CFG['posts_trim_text_number'],
				$CFG['posts_trim_text_words']);

			$result[$i]['topic_trimmed'] = false;
			phpbb_trim_text($result[$i]['topic_title'],
				$result[$i]['topic_trimmed'],
				'',
				$CFG['posts_trim_topic_number'],
				'');
		}
	}

	return $result;
} // end func phpbb_fetch_posts

###############################################################################
##                                                                           ##
## phpbb_fetch_topics()                                                      ##
## ------------------------------------------------------------------------- ##
## This function will fetch the first or the last posting from one or more   ##
## topics specified by topic id. The only difference to phpbb_fetch_posts()  ##
## is that this function will fetch by topic id instead of forum id.         ##
##                                                                           ##
## PARAMETER                                                                 ##
##                                                                           ##
##     topic_id                                                              ##
##                                                                           ##
##         Can be left blank to fetch from all topicsor set to a single      ##
##         topic id to fetch that specific topic To fetch from multiple      ##
##         topics you can parse an array to it.                              ##
##                                                                           ##
##     fetch_mode                                                            ##
##                                                                           ##
##         Set it to POSTS_FETCH_FIRST to fetch the first postings of a      ##
##         topic (i.e. the posts which started the topic) or set it to       ##
##         POSTS_FETCH_LAST to fetch the last postings of a topic.           ##
##                                                                           ##
## EXAMPLE                                                                   ##
##                                                                           ##
##     $news = phpbb_fetch_topics();                                         ##
##                                                                           ##
##     for ($i = 0; $i < count($news); $i++)                                 ##
##     {                                                                     ##
##         echo $news[$i]['topic_title'] . '<br>';                           ##
##     }                                                                     ##
##                                                                           ##
###############################################################################

function phpbb_fetch_topics($topic_id = null, $fetch_mode = POSTS_FETCH_FIRST)
{
	global $CFG, $userdata;

	$topic_list = '';

	if (!is_array($topic_id))
	{
		$topic_list = $topic_id;
	}
	else
	{
		for ($i = 0; $i < count($topic_id); $i++)
		{
			$topic_list .= $topic_id[$i] . ',';
		}

		if ($topic_list)
		{
			$topic_list = substr($topic_list, 0, strlen($topic_list) -1);
		}
	}

	$sql = 'SELECT f.*, p.*, pt.*, t.*, u.*';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ', r.*';
	}

	$sql .= '
				FROM ' . TOPICS_TABLE     . ' AS t,
					 ' . USERS_TABLE      . ' AS u,
					 ' . POSTS_TEXT_TABLE . ' AS pt,
					 ' . POSTS_TABLE      . ' AS p,
					 ' . FORUMS_TABLE     . ' AS f';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ', ' . RANKS_TABLE      . ' AS r';
	}

	$sql .= '
				WHERE u.user_id = p.poster_id AND';

	if ($topic_list)
	{
		$sql .= ' t.topic_id IN (' . $topic_list . ') AND';
	}

	if ($fetch_mode == POSTS_FETCH_FIRST)
	{
		$sql .= ' t.topic_first_post_id = pt.post_id
					AND t.topic_first_post_id = p.post_id AND';
	}
	else
	{
		$sql .= ' t.topic_last_post_id = pt.post_id
					AND t.topic_last_post_id = p.post_id AND';
	}

	$sql .= ' t.forum_id = f.forum_id';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= '
					AND r.rank_id = u.user_rank';
	}

	$result = phpbb_fetch_rows($sql);

	if ($result)
	{
		if ($CFG['auth_check'])
		{
			phpbb_get_auth_list();

			$authed = array();

			for ($i = 0; $i < count($result); $i++)
			{
				if (in_array($result[$i]['forum_id'], $CFG['auth_list']))
				{
					$authed[] = $result[$i];
				}
			}

			$result = $authed;
		}

		$orig_word = array();
		$replacement_word = array();
		obtain_word_list($orig_word, $replacement_word);

		for ($i = 0; $i < count($result); $i++)
		{
			$result[$i]['post_time']      =
				$result[$i]['post_time']      + $CFG['time_zone'];
			$result[$i]['topic_time']     =
				$result[$i]['topic_time']     + $CFG['time_zone'];
			$result[$i]['post_edit_time'] =
				$result[$i]['post_edit_time'] + $CFG['time_zone'];

			$result[$i]['date'] =
				date($CFG['date_format'], $result[$i]['post_time']);
			$result[$i]['time'] =
				date($CFG['time_format'], $result[$i]['post_time']);

			$result[$i]['edit_date'] =
				date($CFG['date_format'], $result[$i]['post_edit_time']);
			$result[$i]['edit_time'] =
				date($CFG['time_format'], $result[$i]['post_edit_time']);

			$result[$i]['post_text'] = phpbb_parse_text(
				$result[$i]['post_text'],
				$result[$i]['bbcode_uid'],
				$result[$i]['enable_smilies'],
				$CFG['posts_enable_bbcode'],
				$CFG['posts_enable_html'],
				$CFG['posts_hide_images'],
				$CFG['posts_replace_images'],
				$CFG['posts_enable_local_links']);

			if (count($orig_word))
			{
				$result[$i]['topic_title'] = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['topic_title']);
				$result[$i]['post_text'] = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['post_text']);
			}

			$result[$i]['trimmed'] = false;
			phpbb_trim_text($result[$i]['post_text'],
				$result[$i]['trimmed'],
				$CFG['posts_trim_text_character'],
				$CFG['posts_trim_text_number'],
				$CFG['posts_trim_text_words']);

			$result[$i]['topic_trimmed'] = false;
			phpbb_trim_text($result[$i]['topic_title'],
				$result[$i]['topic_trimmed'],
				'',
				$CFG['posts_trim_topic_number'],
				'');
		}

		if (is_array($topic_id))
		{
			$sorted = array();

			for ($i = 0; $i < count($topic_id); $i++)
			{
				for ($j = 0; $j < count($result); $j++)
				{
					if ($topic_id[$i] == $result[$j]['topic_id'])
					{
						$sorted[] = $result[$j];
					}
				}
			}

			$result = $sorted;
		}
	}

	return $result;
} // end func phpbb_fetch_topics

###############################################################################
##                                                                           ##
## phpbb_fetch_new_posts()                                                   ##
## ------------------------------------------------------------------------- ##
## Fetches the number of new posts since the last visit.                     ##
##                                                                           ##
## EXAMPLE                                                                   ##
##                                                                           ##
##     $new_posts = phpbb_fetch_new_posts();                                 ##
##                                                                           ##
##     echo 'There are ' . $new_posts . ' new posts for you.';               ##
##                                                                           ##
###############################################################################

function phpbb_fetch_new_posts()
{
	global $userdata;

	$result['total'] = 0;

	if ($userdata['session_logged_in'])
	{
		$sql = 'SELECT COUNT(post_id) AS total
					FROM ' . POSTS_TABLE . '
					WHERE post_time >= ' . $userdata['user_lastvisit'];

		$result = phpbb_fetch_row($sql);
	}

	return $result;
}

###############################################################################
##                                                                           ##
## phpbb_fetch_thread()                                                      ##
## ------------------------------------------------------------------------- ##
## This function will fetch an entire thread by topic id.                    ##
##                                                                           ##
## PARAMETER                                                                 ##
##                                                                           ##
##     topic_id                                                              ##
##                                                                           ##
##         Must be set to a single topic id.                                 ##
##                                                                           ##
## EXAMPLE                                                                   ##
##                                                                           ##
##     $topic = phpbb_fetch_thread(1);                                       ##
##                                                                           ##
##     for ($i = 0; $i < count($topic); $i++)                                ##
##     {                                                                     ##
##         echo $topic[$i]['post_text'] . '<hr>';                            ##
##     }                                                                     ##
##                                                                           ##
###############################################################################

function phpbb_fetch_thread($topic_id = null)
{
	global $CFG, $userdata;

	if (!$topic_id)
	{
		phpbb_raise_error('no topic id specified', __FILE__, __LINE__);
	}

	$sql = 'SELECT p.*, pt.*, u.*';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ', r.*';
	}

	$sql .= '
				FROM ' . USERS_TABLE      . ' AS u,
					 ' . POSTS_TEXT_TABLE . ' AS pt,
					 ' . POSTS_TABLE      . ' AS p';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= ',
					 ' . RANKS_TABLE      . ' AS r';
	}

	$sql .= '
				WHERE p.topic_id = ' . $topic_id . '
					AND u.user_id  = p.poster_id
					AND pt.post_id = p.post_id
					AND u.user_id  = p.poster_id';

	if (!$CFG['posts_hide_ranks'])
	{
		$sql .= '
					AND r.rank_id = u.user_rank';
	}

	if ($CFG['posts_search_string'])
	{
		$sql .= '
					AND (' . $CFG['posts_search_string'] . ')';
	}

	$sql .= '
				ORDER BY ' . $CFG['posts_order'];

	if ($CFG['posts_span_pages'])
	{
		$CFG['posts_span_pages_numrows'] = phpbb_numrows(phpbb_query($sql));
		if ($CFG['posts_span_pages_offset'] > $CFG['posts_span_pages_numrows'])
		{
			$CFG['posts_span_pages_offset'] =
				$CFG['posts_span_pages_numrows'] - 1;
		}
		$CFG['posts_offset'] = $CFG['posts_span_pages_offset'];
	}
	else
	{
		$CFG['posts_offset'] = 0;
	}

	if ($CFG['posts_limit'] != 0)
	{
		$sql .= ' LIMIT ' . $CFG['posts_offset'] . ',' . $CFG['posts_limit'];
	}

	$result = phpbb_fetch_rows($sql);

	if ($result)
	{
		if ($CFG['auth_check'])
		{
			phpbb_get_auth_list();

			$authed = array();

			for ($i = 0; $i < count($result); $i++)
			{
				if (in_array($result[$i]['forum_id'], $CFG['auth_list']))
				{
					$authed[] = $result[$i];
				}
			}

			$result = $authed;
		}

		$orig_word = array();
		$replacement_word = array();
		obtain_word_list($orig_word, $replacement_word);

		for ($i = 0; $i < count($result); $i++)
		{
			$result[$i]['post_time']      =
				$result[$i]['post_time']      + $CFG['time_zone'];
			$result[$i]['topic_time']     =
				$result[$i]['topic_time']     + $CFG['time_zone'];
			$result[$i]['post_edit_time'] =
				$result[$i]['post_edit_time'] + $CFG['time_zone'];

			$result[$i]['date'] =
				date($CFG['date_format'], $result[$i]['post_time']);
			$result[$i]['time'] =
				date($CFG['time_format'], $result[$i]['post_time']);

			$result[$i]['edit_date'] =
				date($CFG['date_format'], $result[$i]['post_edit_time']);
			$result[$i]['edit_time'] =
				date($CFG['time_format'], $result[$i]['post_edit_time']);

			$result[$i]['post_text'] = phpbb_parse_text(
				$result[$i]['post_text'],
				$result[$i]['bbcode_uid'],
				$result[$i]['enable_smilies'],
				$CFG['posts_enable_bbcode'],
				$CFG['posts_enable_html'],
				$CFG['posts_hide_images'],
				$CFG['posts_replace_images'],
				$CFG['posts_enable_local_links']);

			if (count($orig_word))
			{
				$result[$i]['topic_title'] = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['topic_title']);
				$result[$i]['post_text'] = preg_replace($orig_word,
					$replacement_word,
					$result[$i]['post_text']);
			}

			$result[$i]['trimmed'] = false;
			phpbb_trim_text($result[$i]['post_text'],
				$result[$i]['trimmed'],
				$CFG['posts_trim_text_character'],
				$CFG['posts_trim_text_number'],
				$CFG['posts_trim_text_words']);

			$result[$i]['topic_trimmed'] = false;
			phpbb_trim_text($result[$i]['topic_title'],
				$result[$i]['topic_trimmed'],
				'',
				$CFG['posts_trim_topic_number'],
				'');
		}

		if (is_array($topic_id))
		{
			$sorted = array();

			for ($i = 0; $i < count($topic_id); $i++)
			{
				for ($j = 0; $j < count($result); $j++)
				{
					if ($topic_id[$i] == $result[$j]['topic_id'])
					{
						$sorted[] = $result[$j];
					}
				}
			}

			$result = $sorted;
		}
	}

	return $result;
} // end func phpbb_fetch_thread

###############################################################################
##                                                                           ##
## phpbb_fetch_newposts()                                                    ##
## ------------------------------------------------------------------------- ##
## This function will fetch new postings based on the user session. It's has ##
## the same results as the search.php?search_id=newposts script.             ##
##                                                                           ##
## EXAMPLE                                                                   ##
##                                                                           ##
##     $newposts = phpbb_fetch_newposts();                                   ##
##                                                                           ##
##     for ($i = 0; $i < count($newposts); $i++)                             ##
##     {                                                                     ##
##         echo $newposts[$i]['post_text'] . '<br>';                         ##
##     }                                                                     ##
##                                                                           ##
###############################################################################

function phpbb_fetch_newposts()
{
	global $CFG, $userdata;

	if (!$userdata['session_logged_in'])
	{
		return;
	}

	$sql = 'SELECT post_id
				FROM ' . POSTS_TABLE . '
				WHERE post_time >= ' . $userdata['user_lastvisit'];

	$result = phpbb_fetch_rows($sql);

	if (!$result)
	{
		return;
	}

	$search_ids = array();
	for ($i = 0; $i < count($result); $i++)
	{
		$search_ids[] = $result[$i]['post_id'];
	}

	$sql = 'SELECT topic_id
				FROM ' . POSTS_TABLE . '
				WHERE post_id IN (' . implode(', ', $search_ids) . ')
				GROUP BY topic_id
				ORDER BY post_time DESC';

	$result = phpbb_fetch_rows($sql);

	if (!$result)
	{
		return;
	}

	$topic_ids = array();
	for ($i = 0; $i < count($result); $i++)
	{
		$topic_ids[] = $result[$i]['topic_id'];
	}

	$result = phpbb_fetch_topics($topic_ids, POSTS_FETCH_LAST);

	return $result;
} // end func phpbb_fetch_newposts

?>
Perhaps there is a way to rewrite that to make it more efficient?
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 »

You may want to talk to the author about it. At a glance, I would recommend just splitting each of those big queries into 3.

First would be to the Topics table joined to the Forums table (Select topic/forum ids as needed). Second would be to the posts/posts_text table to get the raw post data. Third to the users and ranks table to grab the userdata and rank information.

Doing it that way will most likely prevent the query from going into all sorts of looping inner joins, which I suspect it is doing now.
NewBuyer
Registered User
Posts: 248
Joined: Thu Nov 08, 2001 3:14 pm
Location: AlpineZone.com
Contact:

Post by NewBuyer »

Thanks for your feedback. I have remove use of this script from anywhere that I could. Hopefully this will reduce some load on the mySQL server. I still need to run it on a few pages/subdomains, but perhaps this will help. If stability returns, we've pretty much confirmed that phpBB Fetch All was the culprit. I've contacted him about making the code more efficient. Thanks again.
Greg B
AlpineZone.com: Northeast Skiing
COskiing.com: Colorado Skiing
egriz
Registered User
Posts: 441
Joined: Tue Jun 11, 2002 9:49 pm

Re: Locked tables / Copying to tmp table / mySQL crashes

Post by egriz »

did anyone figure out the issue with the sql queries with the phpbb_fetch_all mod?
Locked

Return to “2.0.x Support Forum”