SMF Galllery causing MySQL to hang

[spikeweb]

Running SMF Gallery for sometime already, I face an issue from a few days where MySQL server hangs due to a SMF Gallery query, as investigated by my sysadmin.

Here is the query wher forum1 is the database:

Query_time: 495  Lock_time: 0  Rows_sent: 11692  Rows_examined: 203822242
SELECT
                               COUNT(*) as total
                       FROM (forum1_gallery_pic as p, forum1_gallery_comment as com)

               LEFT JOIN forum1_members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
               LEFT JOIN forum1_gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
               LEFT JOIN forum1_gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = 51080 AND v.user_id_cat = p.USER_ID_CAT)
                                       WHERE ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved =1 AND p.USER_ID_CAT =0)   AND com.ID_PICTURE = p.ID_PICTURE GROUP BY p.ID_PICTURE;

I have some more queries causing the same default. Available on demand but same structure.

Any idea of what could be the cause of the problem and how to correct it ?

THX a lot for your support,

[SMFHacks]

Try this query changed the order run via phpmyadmin I want to see how many are rows are examined

Code: [Select]

explain SELECT
                               COUNT(*) as total
                       FROM (forum1_gallery_pic as p, forum1_gallery_comment as com)

               LEFT JOIN forum1_members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
               LEFT JOIN forum1_gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
               LEFT JOIN forum1_gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = 51080 AND v.user_id_cat = p.USER_ID_CAT)
                                       WHERE com.ID_PICTURE = p.ID_PICTURE AND ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved =1 AND p.USER_ID_CAT =0)   GROUP  BY p.ID_PICTURE;


[spikeweb]

Just ran and got this :

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra

1    SIMPLE    com    ALL    NULL    NULL    NULL    NULL    2301    Using temporary; Using filesort
1    SIMPLE    p    ALL    PRIMARY,USER_ID_CAT    NULL    NULL    NULL    12100    Using where
1    SIMPLE    m    eq_ref    PRIMARY    PRIMARY    3    npnforum1.p.ID_MEMBER    1    Using index
1    SIMPLE    s    eq_ref    PRIMARY    PRIMARY    3    npnforum1.m.ID_MEMBER    1    Using where
1    SIMPLE    v    ref    user_id_cat,id_picture,id_member    id_picture    4    npnforum1.p.ID_PICTURE    7    

[SMFHacks]

Ok that might be the fix will make an update to the gallery 4.0 beta tonight with that change.

[spikeweb]

Please note that it is not 4.0 beta but 3.0.25

Any chance to get a fix for this specific release as we cannot upgrade to a beta release until it has passed our tests ...

[SMFHacks]

I can probably provide a code change what SMF version are you using.

[spikeweb]

SMF is 1.1.16 

[SMFHacks]

Open Sources/Gallery.php
Find

Code: [Select]

function ListAll()
{
global $db_prefix, $user_info, $context, $txt, $ID_MEMBER,  $mbname, $modSettings, $scripturl;

// Is the user allowed to view the gallery?
isAllowedTo('smfgallery_view');

if (!$context['user']['is_guest'])
$groupsdata = implode($user_info['groups'], ',');
else
$groupsdata = -1;

StoreGalleryLocation();

// Show top Tabs
TopButtonTabs();

$type = $_REQUEST['type'];

$context['sub_template']  = 'listall';

$orderby = 'desc';
if (isset($_REQUEST['orderby']))
{
if ($_REQUEST['orderby'] == 'desc')
$orderby = 'desc';
else
$orderby = 'asc';
}

if (allowedTo('smfgallery_manage'))
{
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
WHERE p.approved =1";
}
else
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1)  || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)) ";


switch ($type)
{
case 'recent':
// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.ID_PICTURE $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_last'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_last'];

break;
case 'comments':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];


$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.commenttotal $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_mostcomments'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_mostcomments'];
break;

case 'views':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby  , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName,
p.date, p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.views $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] =  $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_viewed'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_viewed'];
break;

case 'toprated':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.rating $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_toprated'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_stats_toprated'];
break;

case 'random':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY RAND() $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_main_random'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_main_random'];


break;

case 'recentcomments':


$result = db_query("
SELECT
COUNT(*) as total
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com)  
$wherestats  AND com.ID_PICTURE = p.ID_PICTURE GROUP BY p.ID_PICTURE
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE (((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)))  ";


$result = db_query("
SELECT
max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com)

$wherestats AND com.ID_PICTURE = p.ID_PICTURE GROUP BY p.ID_PICTURE
ORDER BY lastcomment $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);



$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_main_recentcomment'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_main_recentcomment'];
break;


}


}
Replace with

Code: [Select]

function ListAll()
{
global $db_prefix, $user_info, $context, $txt, $ID_MEMBER,  $mbname, $modSettings, $scripturl;

// Is the user allowed to view the gallery?
isAllowedTo('smfgallery_view');

if (!$context['user']['is_guest'])
$groupsdata = implode($user_info['groups'], ',');
else
$groupsdata = -1;

StoreGalleryLocation();

// Show top Tabs
TopButtonTabs();

$type = $_REQUEST['type'];

$context['sub_template']  = 'listall';

$orderby = 'desc';
if (isset($_REQUEST['orderby']))
{
if ($_REQUEST['orderby'] == 'desc')
$orderby = 'desc';
else
$orderby = 'asc';
}

if (allowedTo('smfgallery_manage'))
{
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
WHERE p.approved =1";
}
else
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1)  || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)) ";


switch ($type)
{
case 'recent':
// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.ID_PICTURE $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_last'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_last'];

break;
case 'comments':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];


$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.commenttotal $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_mostcomments'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_mostcomments'];
break;

case 'views':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby  , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName,
p.date, p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.views $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] =  $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_viewed'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_viewed'];
break;

case 'toprated':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.rating $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_toprated'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_stats_toprated'];
break;

case 'random':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY RAND() $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_main_random'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_main_random'];


break;

case 'recentcomments':


if (allowedTo('smfgallery_manage'))
{
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
WHERE   com.ID_PICTURE = p.ID_PICTURE AND p.approved =1 ";
}
else
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE  com.ID_PICTURE = p.ID_PICTURE AND ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1)  || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)) ";



$result = db_query("
SELECT
COUNT(*) as total
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com)  
$wherestats  AND com.ID_PICTURE = p.ID_PICTURE GROUP BY p.ID_PICTURE
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE (((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)))  ";


$result = db_query("
SELECT
max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread  
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com)

$wherestats  GROUP BY p.ID_PICTURE
ORDER BY lastcomment $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);



$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_main_recentcomment'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_main_recentcomment'];
break;


}


}


[spikeweb]

Thanks for fix, testing, will let you know

[spikeweb]

We implemented the fix and the corresponding query does not cause any problem until now.

But,

This one stills causes MySQL to hang :

Query_time: 622  Lock_time: 0  Rows_sent: 20  Rows_examined: 49299284
SELECT
                               max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
                               p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
                               p.description, p.mature, v.ID_PICTURE as unread
                       FROM (forum1_gallery_pic as p, forum1_gallery_comment as com)


               LEFT JOIN forum1_members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
               LEFT JOIN forum1_gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
               LEFT JOIN forum1_gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = 0 AND v.user_id_cat = p.USER_ID_CAT)
                               LEFT JOIN forum1_gallery_catperm AS c ON (c.id_group IN (-1) AND c.id_cat = p.id_cat)
               WHERE (((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)))    GROUP BY p.ID_PICTURE
                       ORDER BY lastcomment desc
                       LIMIT 0, 20;

Any new fix ?

Is there any possibility to prevent users not to query the all gallery ?

[SMFHacks]

Try this listall function instead this should solve all those problems

Code: [Select]


function ListAll()
{
global $db_prefix, $user_info, $context, $txt, $ID_MEMBER,  $mbname, $modSettings, $scripturl;

// Is the user allowed to view the gallery?
isAllowedTo('smfgallery_view');

if (!$context['user']['is_guest'])
$groupsdata = implode($user_info['groups'], ',');
else
$groupsdata = -1;

StoreGalleryLocation();

// Show top Tabs
TopButtonTabs();

$type = $_REQUEST['type'];

$context['sub_template']  = 'listall';

$orderby = 'desc';
if (isset($_REQUEST['orderby']))
{
if ($_REQUEST['orderby'] == 'desc')
$orderby = 'desc';
else
$orderby = 'asc';
}

if (allowedTo('smfgallery_manage'))
{
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
WHERE p.approved =1";
}
else
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1)  || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)) ";


switch ($type)
{
case 'recent':
// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread   
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.ID_PICTURE $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_last'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_last'];

break;
case 'comments':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];


$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread 
FROM {$db_prefix}gallery_pic as p
$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.commenttotal $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_mostcomments'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_mostcomments'];
break;

case 'views':

// Get total number of results
$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats

", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby  , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName,
p.date, p.description, p.mature, v.ID_PICTURE as unread 
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.views $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] =  $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_stats_viewed'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_stats_viewed'];
break;

case 'toprated':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread 
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY p.rating $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_stats_toprated'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_stats_toprated'];
break;

case 'random':

$result = db_query("
SELECT
COUNT(*) as total
FROM {$db_prefix}gallery_pic as p
$wherestats
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];

$result = db_query("
SELECT
p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread 
FROM {$db_prefix}gallery_pic as p

$wherestats GROUP BY p.ID_PICTURE
ORDER BY RAND() $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);

$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);

$context['gallery_stat_title'] = $txt['gallery_main_random'];
$context['page_title'] = $mbname . ' - ' .$txt['gallery_main_random'];


break;

case 'recentcomments':


if (allowedTo('smfgallery_manage'))
{
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
WHERE   com.ID_PICTURE = p.ID_PICTURE AND p.approved =1 ";
}
else
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE  com.ID_PICTURE = p.ID_PICTURE AND ((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1)  || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)) ";



$result = db_query("
SELECT
COUNT(*) as total
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com) 
$wherestats   GROUP BY p.ID_PICTURE
", __FILE__, __LINE__);
$row = mysql_fetch_assoc($result);
mysql_free_result($result);

$context['page_index'] = constructPageIndex($scripturl . '?action=gallery;sa=listall;type=' . $_REQUEST['type'] . ';orderby=' . $orderby , $_REQUEST['start'], $row['total'], $modSettings['gallery_set_images_per_page']);
$context['start'] = $_REQUEST['start'];
/*
$wherestats = "
LEFT JOIN {$db_prefix}members AS m  ON (m.ID_MEMBER = p.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_usersettings AS s ON (s.ID_MEMBER = m.ID_MEMBER)
LEFT JOIN {$db_prefix}gallery_log_mark_view AS v ON (p.ID_PICTURE = v.ID_PICTURE AND v.ID_MEMBER = $ID_MEMBER AND v.user_id_cat = p.USER_ID_CAT)
LEFT JOIN {$db_prefix}gallery_catperm AS c ON (c.id_group IN ($groupsdata) AND c.id_cat = p.id_cat)
WHERE  (((s.private =0 || s.private IS NULL ) AND (s.password = '' || s.password IS NULL ) AND p.USER_ID_CAT !=0 AND p.approved =1) || (p.approved = 1 AND p.user_id_cat = 0 AND (c.view IS NULL || c.view = 1)))  ";
*/

$result = db_query("
SELECT
max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize,
p.views, p.thumbfilename, p.title, p.ID_MEMBER, m.realName, p.date,
p.description, p.mature, v.ID_PICTURE as unread   
FROM ({$db_prefix}gallery_pic as p, {$db_prefix}gallery_comment as com)

$wherestats  GROUP BY p.ID_PICTURE
ORDER BY lastcomment $orderby
LIMIT $context[start], " . $modSettings['gallery_set_images_per_page']
, __FILE__, __LINE__);



$context['picture_list'] = array();
while($row = mysql_fetch_assoc($result))
{
$context['picture_list'][] = $row;
}
mysql_free_result($result);


$context['gallery_stat_title'] = $txt['gallery_main_recentcomment'];
$context['page_title'] = $mbname . ' - ' . $txt['gallery_main_recentcomment'];
break;


}


}


[spikeweb]

Implemented the fix this morning, test going on and will let you know