Facebook  Twitter 

SMFHacks.com

+-

SMFHacks.com

+- User Information

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

+- Forum Stats

Members
Total Members: 4260
Latest: Verrick
New This Month: 0
New This Week: 0
New Today: 0
Stats
Total Posts: 43319
Total Topics: 7526
Most Online Today: 236
Most Online Ever: 2482
(April 09, 2011, 07:02:45 pm)
Users Online
Members: 0
Guests: 226
Total: 226

Author Topic: SMF Gallery locking my database  (Read 5251 times)

0 Members and 1 Guest are viewing this topic.

Offline rpd1960

  • Member
  • *
  • Posts: 19
    • View Profile
SMF Gallery locking my database
« on: June 06, 2010, 07:58:49 am »
I've been having an intermidiate problem with SMF Gallery locking up my SMF database.

I'm using SMF Gallery Pro v3.0.3 with SMF 1.1.11

Every now and then I receive this message: There has been a problem with the database!

This is a notice email to let you know that SMF could not connect to the database, contact your host if this continues.
=========================================================================

So I contacted my host and we've been working to find what was causing my SMF database to lock up and we finally found what the cause was.

SMF Gallery is the most used program on my website. Here is what my host was able to find.
==========================================================================
Below this you will find all the gallery tables that were locked. After I had killed those process your site was able to come back online.

The List:
14663266 guysinto_GiDJ localhost guysinto_GiDJ Query 17621 Locked SELECT
max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rati 14663273 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked REPLACE INTO GiDJ_gallery_log_mark_view (id_picture, id_member, id_cat, user_id_cat) VALUES 14663280 guysinto_GiDJ localhost guysinto_GiDJ Query 711 Locked SELECT max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rati
14663293 guysinto_GiDJ localhost guysinto_GiDJ Query 17621 Locked UPDATE GiDJ_gallery_pic SET views = views + 1 WHERE ID_PICTURE = 27422 LIMIT 1
14663294 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked REPLACE INTO GiDJ_gallery_log_mark_view (id_picture, id_member, id_cat,
user_id_cat) VALUES 14663298 guysinto_GiDJ localhost guysinto_GiDJ Query
605 Locked SELECT max(com.ID_COMMENT) AS lastcomment, p.I_PICTURE, p.commenttotal, p.totalratings, p.rati 14663300 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked SELECT max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rati
14663302 guysinto_GiDJ localhost guysinto_GiDJ Query 669 Locked SELECT
max(com.ID_COMMENT) AS lastcomment, p.ID_PICTURE, p.commenttotal, p.totalratings, p.rati 14663304 guysinto_GiDJ localhost guysinto_GiDJ Query 21623 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com) 14663313 guysinto_GiDJ localhost guysinto_GiDJ Query 17619 Locked SELECT p.ID_PICTURE, p.commenttotal, p.totalratings, p.rating, p.filesize, p.views, p.thum 14663319 guysinto_GiDJ localhost guDysinto_GiDJ Query
17621 Locked UPDATE GiDJ_gallery_pic SET views = views + 1 WHERE ID_PICTURE = 973 LIMIT 1 14663324 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked REPLACE INTO GiDJ_gallery_log_mark_view (id_picture, id_member, id_cat, user_id_cat) VALUES 14663500 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked SELECT COUNT(*) AS total FROM GiDJ_gallery_pic as p WHERE p.USER_ID_CAT = 1042 AND p.appro 14663516 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked SELECT thumbfilename, ID_PICTURE, filename FROM GiDJ_gallery_pic WHERE approved = 1 ORDER 14663603 guysinto_GiDJ localhost guysinto_GiDJ Query 17621 Locked SELECT ID_PICTURE, USER_ID_CAT, ID_CAT FROM GiDJ_gallery_pic WHERE ID_PICTURE = 26084 LIMIT 14663783 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked SELECT thumbfilename, ID_PICTURE, filename FROM GiDJ_gallery_pic WHERE approved = 1 ORDER 14663793 guysinto_GiDJ localhost guysinto_GiDJ Query 17621 Locked SELECT ID_PICTURE, USER_ID_CAT, ID_CAT FROM GiDJ_gallery_pic WHERE ID_PICTURE =
26084 LIMIT 14663809 guysinto_GiDJ localhost guysinto_GiDJ Query 17621 Locked SELECT ID_PICTURE, USER_ID_CAT, ID_CAT FROM GiDJ_gallery_pic WHERE ID_PICTURE = 26084 LIMIT 14663930 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked SELECT thumbfilename, ID_PICTURE, filename FROM GiDJ_gallery_pic WHERE approved = 1 ORDER 14664198 guysinto_GiDJ localhost guysinto_GiDJ Query 584 Locked UPDATE GiDJ_members SET validation_code = '6b4f3e076c' WHERE ID_MEMBER = 4410 LIMIT 1 14690627 root localhost guysinto_GiDJ Query 583 Locked CHECK TABLE `GiDJ_gallery_pic` 14691224 root localhost guysinto_GiDJ Query 584 Locked REPAIR TABLE `GiDJ_gallery_comment` 14693019 root localhost guysinto_GiDJ Query 584 Locked CHECK TABLE `GiDJ_gallery_log_mark_view`
14696241 root localhost guysinto_GiDJ Query 583 Locked CHECK TABLE `GiDJ_gallery_comment` 14697052 root localhost guysinto_GiDJ Query 583 Locked REPAIR TABLE `GiDJ_gallery_comment` 14701228 root localhost guysinto_GiDJ Query 584 Locked CHECK TABLE `GiDJ_gallery_log_mark_view` 14701770 root localhost guysinto_GiDJ Query 584 Locked REPAIR TABLE `GiDJ_gallery_comment` 14702751 guysinto localhost guysinto_GiDJ Query
12262 Locked LOCK TABLES `GiDJ_arcade_categories` READ /*!32311 LOCAL */,`GiDJ_arcade_favorite` READ /*!32311 LOC 14736182 root localhost guysinto_GiDJ Query 584 Locked CHECK TABLE `GiDJ_gallery_comment`
14736394 root localhost guysinto_GiDJ Query 583 Locked REPAIR TABLE `GiDJ_gallery_comment` 14740458 guysinto_tumble localhost guysinto_GiDJ Query 584 Locked UPDATE GiDJ_members SET lastLogin = 1275534748, memberIP = '67.247.42.213', memberIP2 = ' 14740459 guysinto_tumble localhost guysinto_GiDJ Query 584 Locked UPDATE GiDJ_members SET lastLogin = 1275534748, memberIP = '67.247.42.213', memberIP2 = '
14740765 guysinto_tumble localhost guysinto_GiDJ Query 584 Locked UPDATE GiDJ_members SET lastLogin = 1275534748, memberIP = '67.247.42.213',
memberIP2 = ' 14742642 root localhost guysinto_GiDJ Query 584 Locked CHECK TABLE `GiDJ_gallery_comment` 14748752 guysinto_GiDJ localhost guysinto_GiDJ Query 582 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member 14748999 guysinto_GiDJ localhost guysinto_GiDJ Query 505 Locked UPDATE GiDJ_sessions SET data =
'rand_code|s:32:\"885ffc81a730d96f9679aac31bb56eb1\";ban|a:5:{s:12
14749090 guysinto_GiDJ localhost guysinto_GiDJ Query 470 Locked REPLACE INTO GiDJ_settings (variable, value) VALUES ('queryless_urls', '0'), ('enable
===========================================================================

Is there anything I can do to eliminate this problem?
« Last Edit: June 06, 2010, 08:11:46 am by rpd1960 »

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16463
    • View Profile
Re: SMF Gallery locking my database
« Reply #1 on: June 06, 2010, 08:18:45 am »
Can't tell from that list which one is causing it looks like they are all locked. There should be one query that is not locked that is doing something. Find that query and see what it is doing and if can be killed off.
I see a repair table process in there looks like a crashed table.

Locked normally occurs if something is being updated or repaired.
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 rpd1960

  • Member
  • *
  • Posts: 19
    • View Profile
Re: SMF Gallery locking my database
« Reply #2 on: June 06, 2010, 08:23:34 am »
Will send the info to my host and see what he can find


Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16463
    • View Profile
Re: SMF Gallery locking my database
« Reply #3 on: June 06, 2010, 08:30:06 am »
I think I found the query kill this one off in phpmyadmin if you find it
Code: [Select]
guysinto_GiDJ Query 21623 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com)
Its getting stuck i think since there is a repair_table attempting to go on comment table.
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 rpd1960

  • Member
  • *
  • Posts: 19
    • View Profile
Re: SMF Gallery locking my database
« Reply #4 on: June 13, 2010, 11:14:58 pm »
My host informed me that he had killed all locked items when the error occured,  but that only solved the problem temporarily.

Is there something that can be added to avoid SMF Gallery from locking my database ?

This is a real pain because when it does lock, my site is unaccessible.

It seems to be coming back by itself after awhile, but like I previously said, it is a real pain for both me and my users.

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16463
    • View Profile
Re: SMF Gallery locking my database
« Reply #5 on: June 14, 2010, 01:09:58 pm »
Looks like it from the Recent Comments block on the homepage if it is that same query that is locking up each time. If it keeps happening I would suggest to disable
I would do the following sql commands to show the indexes.
Code: [Select]
Show index from GiDJ_gallery_comment;
And
Code: [Select]
Show index from GiDJ_gallery_pic;

That query itself shouldn't lock anything else since it is indexed and not very complex.

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 rpd1960

  • Member
  • *
  • Posts: 19
    • View Profile
Re: SMF Gallery locking my database
« Reply #6 on: August 05, 2010, 04:26:00 pm »
I thought maybe it had something to do with members that had been deleted, so I added all the members that had previously been deleted and I am still having problems.

This is the latest information that my server provider has supplied

This is getting to be a real pain for both me and my members

If I can't find a solution, I will have to move onto a different Gallery program

331478 guysinto_GiDJ localhost guysinto_GiDJ Query 19726 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com)
331491 guysinto_GiDJ localhost guysinto_GiDJ Query 19720 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com)
331507 guysinto_GiDJ localhost guysinto_GiDJ Query 19716 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com)
331512 guysinto_GiDJ localhost guysinto_GiDJ Query 19715 Copying to tmp table SELECT COUNT(*) as total FROM (GiDJ_gallery_pic as p, GiDJ_gallery_comment as com)
331534 guysinto_GiDJ localhost guysinto_GiDJ Query 19708 Locked UPDATE GiDJ_members SET lastLogin = 1280509100, memberIP = '92.13.121.226', memberIP2 = '
331623 guysinto_GiDJ localhost guysinto_GiDJ Query 19684 Locked UPDATE GiDJ_members SET lastLogin = 1280509124, memberIP = '92.13.121.226', memberIP2 = '
331717 guysinto_GiDJ localhost guysinto_GiDJ Query 19659 Locked UPDATE GiDJ_members SET lastLogin = 1280509149, memberIP = '92.13.121.226', memberIP2 = '
333035 guysinto_GiDJ localhost guysinto_GiDJ Query 19247 Locked UPDATE GiDJ_members SET lastLogin = 1280509561, memberIP = '92.13.121.226', memberIP2 = '
333626 guysinto_GiDJ localhost guysinto_GiDJ Query 19095 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
333638 guysinto_GiDJ localhost guysinto_GiDJ Query 19093 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
334392 guysinto_GiDJ localhost guysinto_GiDJ Query 18824 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
335497 guysinto_GiDJ localhost guysinto_GiDJ Query 18482 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
335503 guysinto_GiDJ localhost guysinto_GiDJ Query 18481 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
335598 guysinto_GiDJ localhost guysinto_GiDJ Query 18450 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
335729 guysinto_GiDJ localhost guysinto_GiDJ Query 18411 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
336174 guysinto_GiDJ localhost guysinto_GiDJ Query 18232 Locked SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress, additionalGroups, memberNam
336491 guysinto_GiDJ localhost guysinto_GiDJ Query 18133 Locked SELECT passwd, ID_MEMBER, ID_GROUP, lngfile, is_activated, emailAddress, additionalGroups, memberNam
336772 guysinto_GiDJ localhost guysinto_GiDJ Query 18018 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
338825 guysinto_GiDJ localhost guysinto_GiDJ Query 17272 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
338852 guysinto_GiDJ localhost guysinto_GiDJ Query 17262 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
338949 guysinto_GiDJ localhost guysinto_GiDJ Query 17234 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
339032 guysinto_GiDJ localhost guysinto_GiDJ Query 17208 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
341214 guysinto_GiDJ localhost guysinto_GiDJ Query 16425 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
342616 guysinto_GiDJ localhost guysinto_GiDJ Query 15996 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member
345794 guysinto_GiDJ localhost guysinto_GiDJ Query 15181 Locked SELECT mem.*, IFNULL(a.ID_ATTACH, 0) AS ID_ATTACH, a.filename, a.attachmentType FROM GiDJ_member

Offline SMFHacks

  • Administrator
  • Hero Member
  • *****
  • Posts: 16463
    • View Profile
Re: SMF Gallery locking my database
« Reply #7 on: August 05, 2010, 04:35:34 pm »
I would try running the two queries in the previous post.

Also disable the recent comments index blocks since that looks like the queries that are locking up
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
19 Replies
15154 Views
Last post January 11, 2007, 07:29:54 pm
by sanax
3 Replies
4547 Views
Last post February 05, 2008, 06:52:00 am
by SMFHacks
10 Replies
10614 Views
Last post February 24, 2009, 08:08:08 pm
by Beltazar
Database Error

Started by pioneer « 1 2 » Support

22 Replies
13356 Views
Last post April 18, 2009, 11:58:55 am
by SMFHacks
5 Replies
6949 Views
Last post January 22, 2010, 11:26:48 am
by SMFHacks

+- Recent Topics

Attachment images Upsidedown or Sideways by SMFHacks
June 18, 2024, 10:53:24 am

JSON decode error: The maximum stack depth has been exceeded by SMFHacks
June 14, 2024, 10:15:16 pm

“Insert from Gallery” by donk
June 10, 2024, 01:26:57 pm

Pretty Urls extras by SMFHacks
May 13, 2024, 10:28:54 am

Mod question by Verrick
May 11, 2024, 02:42:50 pm

i have paid but cant download ad pro. by SMFHacks
May 02, 2024, 11:29:38 pm

Please Help! by SMFHacks
April 17, 2024, 08:04:55 am

Rate own images by fvlog19
April 11, 2024, 10:56:53 am

Tidy Child Boards on 2.1.4 by SMFHacks
April 04, 2024, 03:54:12 pm

Problems SMF 2.0.19 > 2.1.4 SMF Gallery Pro - Recents Images to overall header by Michel68
March 30, 2024, 12:41:08 pm

Powered by EzPortal