SMF Galllery causing MySQL to hang

   Community Suite
   SMF Gallery Pro
   SMF Store
   SMF Classifieds
   Newsletter Pro
   Downloads System Pro
   Ad Seller Pro
  
   Hacks and Mods
   Latest SMF Hacks
   TopTen Hacks
   Styles and Themes
   Add a Hack
   Manage Hacks
  
   Earn Money from Your Forum with these tips
   SMF Theme Generator
   SMF Package Parser
   Free SMF Hosting
   HostRocket Webhosting for SMF
  
   Site Showcase

Advanced search

[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:

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:

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:

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:

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

Help Support the SMFHacks.com mod making.