Facebook  Twitter 

SMFHacks.com

+- +-

SMFHacks.com

+- User Information

Welcome, Guest.
Please login or register.
 
 
 
Forgot your password?

+- Forum Stats

Members
Total Members: 12400
Latest: mikel
New This Month: 5
New This Week: 1
New Today: 1
Stats
Total Posts: 40609
Total Topics: 7132
Most Online Today: 180
Most Online Ever: 2482
(April 09, 2011, 07:02:45 pm)
Users Online
Members: 0
Guests: 127
Total: 127

Author Topic: Hang after upgrade MariaDB to version 10.5  (Read 226 times)

0 Members and 1 Guest are viewing this topic.

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Hang after upgrade MariaDB to version 10.5
« on: December 27, 2020, 10:43:40 am »
Hello SMFHacks,

I hope you are fine these days!

We've updated our MariaDB 5.5 to 10.5 today and all though the forum is working fine, the gallery makes the database using 300% memory and we need to kill MariaDB, PHP and httpd and start them again to bring the website alive. We tried to check and correct the database but there are no issues found...... and the most irritating thing is that there are no errors, not in the SMF log, not in the server logs.

Most strange is that I can access the Gallery as an Admin without any issue but as any other member (or guest) it makes the whole website unreachable by an crash or 'freeze' from the database. And because we're still using the good old SMFGalleryPro version 5, I updated today to version 9beta but still having the same issue. So I needed to disable the Gallery for now.

Do you have any Idea and can you help me with this? Thank you so much!!

Vincent Volmer

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #1 on: December 27, 2020, 10:49:37 am »
When run can see any database queries running via phpmyadmin that are taking a long time?
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #2 on: December 27, 2020, 11:33:37 am »
I see one query with a long time..... :

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.real_name, p.date, p.description, p.mature, c.view, v.id_picture as unread, mg.online_color, p.totallikes FROM (smf_gallery_pic as p, smf_gallery_comment as com) LEFT JOIN smf_members AS m ON (m.id_member = p.id_member) LEFT JOIN smf_membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP)) LEFT JOIN smf_gallery_usersettings AS s ON (s.id_member = m.id_member) LEFT JOIN smf_gallery_catperm AS c ON (c.id_group IN (0,6) AND c.id_cat = p.id_cat) LEFT JOIN smf_gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 5458 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 AND (c.view IS NULL || c.view = 1))) GROUP by p.id_picture ORDER BY lastcomment DESC LIMIT 12

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #3 on: December 27, 2020, 11:40:20 am »
Run this query and post back full results
Code: [Select]
EXPLAIN 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.real_name, p.date, p.description, p.mature, c.view, v.id_picture as unread, mg.online_color, p.totallikes FROM (smf_gallery_pic as p, smf_gallery_comment as com) LEFT JOIN smf_members AS m ON (m.id_member = p.id_member) LEFT JOIN smf_membergroups AS mg ON (mg.ID_GROUP = IF(m.ID_GROUP = 0, m.ID_POST_GROUP, m.ID_GROUP)) LEFT JOIN smf_gallery_usersettings AS s ON (s.id_member = m.id_member) LEFT JOIN smf_gallery_catperm AS c ON (c.id_group IN (0,6) AND c.id_cat = p.id_cat) LEFT JOIN smf_gallery_log_mark_view AS v ON (p.id_picture = v.id_picture AND v.id_member = 5458 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 AND (c.view IS NULL || c.view = 1))) GROUP by p.id_picture ORDER BY lastcomment DESC LIMIT 12
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #4 on: December 27, 2020, 11:47:14 am »
Hello,

please see attachment

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #5 on: December 27, 2020, 11:57:58 am »
After disabling "recent comments on index" the page is loading fine...

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #6 on: December 27, 2020, 11:59:56 am »

Yeah just was going to post that

I would disable the Recent Comments main page block under layout settings. That looks like the slowdown.

Not sure why mariadb is missing a key. I see in the explain the com table is Null


Test your stats page too in the gallery.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #7 on: December 27, 2020, 12:05:10 pm »
Stats is looking good: please see  https://digiscrap.nl/gallery/?sa=stats

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #8 on: December 27, 2020, 12:18:56 pm »
Run this query too
Code: [Select]
show index from smf_gallery_comment
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #9 on: December 27, 2020, 12:23:06 pm »
Hereby

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #10 on: December 27, 2020, 12:26:19 pm »
Ok that's the issue.

Run
Code: [Select]
Alter table smf_gallery_comment ADD index id_picture (id_picture);

That recent comment query should work fast now hopefully
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

Offline Vincent Volmer

  • SMF Gallery Pro Customer
  • Hero Member
  • *****
  • Posts: 512
  • SMF 2.0.17
    • View Profile
    • Digiscrap.nl
Re: Hang after upgrade MariaDB to version 10.5
« Reply #11 on: December 27, 2020, 12:41:55 pm »
Hello,

Thanks! Yes... it is working again. Thanks you! Is this error caused by the MariaDB upgrade?

Cheers! I'm really happy ;)

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 15291
    • View Profile
Re: Hang after upgrade MariaDB to version 10.5
« Reply #12 on: December 27, 2020, 12:56:07 pm »
No, was missing and index will fix for next update.
Get your Forum Ranked! at https://www.forumrankings.net - find out how your forum compares with others!

Like What I do? Support me at https://www.patreon.com/vbgamer45/

 

Related Topics

  Subject / Started by Replies Last post
37 Replies
23253 Views
Last post December 27, 2007, 10:22:10 pm
by SMFHacks
2 Replies
3428 Views
Last post August 28, 2009, 12:17:40 pm
by Grumpy Smurf
6 Replies
5599 Views
Last post November 28, 2009, 03:17:09 pm
by SMFHacks
2 Replies
2861 Views
Last post September 29, 2013, 01:20:39 pm
by dan4ever
1 Replies
1899 Views
Last post July 28, 2014, 12:58:26 pm
by Panoulis64

+- Recent Topics

Forum ToolBox by Monocero
Today at 11:47:48 am

Gallery Pro v8.0 and PHP 7.4 by SMFHacks
January 20, 2021, 10:21:25 pm

[Mod] Tagging System by SMFHacks
January 13, 2021, 08:43:33 pm

Using Alternate PayPal Address by KKOG
January 13, 2021, 07:24:46 pm

Assigning a BuyNow Button to a product by SMFHacks
January 12, 2021, 04:59:32 pm

SMF Links by Nicole
January 10, 2021, 12:02:39 pm

Error on the latest version by davejo
January 06, 2021, 08:04:05 am

Recent topics on board index by eugen
January 01, 2021, 11:08:16 am

Parser by swayforth
December 31, 2020, 12:24:23 am

Show Gallery in Users Profile by swayforth
December 29, 2020, 10:37:17 pm

Powered by EzPortal