Jump to content

I need a MySQL expert to help configure my servers. Site errors caused by queries, my.ini?


Recommended Posts

Profile statistics like profile views keep getting stuck and stop counting.

IPB support told me the following:

The views are updated hourly via a system task, like all tasks it relies on board activity to run. I've ran it manually to test it and there did not seem to be any immediate issue. I've enabled logging for it so we'll be able to check it does run when required now.

There are some database errors present although they do appear not directly related to the profile update they could well be hindering its operation:

Although these are host issues rather than anything amiss in the software. I'll provide the latest one here:

Error: 2013 - Lost connection to MySQL server during query


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: SELECT m.*,p.* FROM members m LEFT JOIN profile_portal p ON ( p.pp_member_id=m.member_id ) WHERE (m.members_l_display_name LIKE '%darkcypres%' OR p.signature LIKE '%darkcypres%' OR p.pp_about_me LIKE '%darkcypres%') ORDER BY member_id desc LIMIT 0,25


What I would suggest next is the following:

1. Contact your host and provide the data above to them. It has happened a few times it seems, different queries although the error remains. That one was generated during a search. I would suspect the cause may be max_allowed_packet size in mysql's configuration my.ini but your host will be able to determine this.

I contacted my VPS provider, Bluehost - they said I had to do it myself. I have NO idea what to do... Can anyone help?

Link to comment
Share on other sites

One would think a managed hosting would manage mysql... :hmm: Cuz that's like... what do they "manage" then? <_<

The loss of connection during a query usually happens when you run out of resources. Whether that be the maximum your mysql is allowed to use or w/e resource is left in you system.

I do not believe that the query you highlight is cause of your profile views getting stuck as I can't imagine why anyone would request for such during profile view.

So, I believe that's an independent issue. But, it does show a problem. That query you quoted is extremely heavy. I'm kind of surprised IPB even created an unreasonable query like that... (or possibly plugin). At a glance, it looks like it's a search query. But it does a full search for the text in both signature and about me sections. Search on member's display name isn't much of an issue because that's an indexed (has a lookup table) column.

Anyway, a single bad query doesn't really tell much. Because it could have been a fluke. But it does make your disk usage suspicious.

This is what you should do to at least diagnose the issue.

1. SSH Login as root and run: (preferably during peak / when issue is readily visible)

iostat -x 60 2

That will take a minute, literally. Post both here. It will show possible issue with your disk.

If you get command not found, install sysstat

2. Go to your forum cache directory. Something like /forums/cache I'd imagine. You'll see a bunch of error logs made by IPB. (also accessible from your admin panel)

Open your most recent date's sql error log and see if you have more of "lost connection..." errors. See if it's frequent. See what kind of errors show up most and how often. And then respond here with it.

3. Post output from top too. (type top -n 1)

Link to comment
Share on other sites

This is what you should do to at least diagnose the issue.

Thanks very much for the response.

Trying to do #1, but the console URL cant be obtained atm... will try again later, bluehost seems to be having some issues.

For #2, it's all over the place. There doesnt seem to be a significant repeating error other than the search, which appears more than most, but here are a few of the total ~19 errors logged over the last 2 weeks. At least one a day.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Date: Tue, 04 Mar 2014 17:32:54 +0000
Error: 2013 - Lost connection to MySQL server during query
IP Address: 199.8.66.191 - /index.php?app=core&module=search&do=search&fromMainBar=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT pid FROM posts WHERE topic_id IN (0,396386,398337,396279,22077,25262,25152,24519,22863,22086,19874,19825,17775,8124,17313,16305,14989,14311,13819,9738,8217,7985,7193,7107,5102,3768,3808,3648,3657) AND new_topic=1
*
-------------------------------------------------
Date: Sat, 08 Mar 2014 03:11:07 +0000
Error: 2013 - Lost connection to MySQL server during query
IP Address: 96.224.224.60 - /index.php?app=core&module=search&do=search&fromMainBar=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT m.*,p.* FROM members m LEFT JOIN profile_portal p ON ( p.pp_member_id=m.member_id ) WHERE (m.members_l_display_name LIKE '%mike trick%' OR p.signature LIKE '%mike trick%' OR p.pp_about_me LIKE '%mike trick%') ORDER BY member_id desc LIMIT 0,25
*
Date: Mon, 10 Feb 2014 19:55:11 +0000
Error: 126 - Incorrect key file for table '/tmp/#sql_5fd_1.MYI'; try to repair it
IP Address: 208.46.190.178 - /community/admin/index.php?adsess=4062bb0e0968256c227cd3a2b72d0508&app=members&module=members&section=members&do=members_overview
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT m.*, m.member_id as mem_id, m.ip_address as mem_ip,p.*,pp.*,par.*,val.* FROM members m LEFT JOIN pfields_content p ON ( p.member_id=m.member_id )
LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id )
LEFT JOIN members_partial par ON ( par.partial_member_id=m.member_id )
LEFT JOIN validating val ON ( val.member_id=m.member_id ) WHERE par.partial_member_id IS NULL ORDER BY m.joined desc LIMIT 0,20
**
Date: Sat, 08 Feb 2014 16:08:48 +0000
Error: 126 - Incorrect key file for table '/tmp/#sql_265d_3.MYI'; try to repair it
IP Address: 172.251.98.93 - /community/admin/index.php?adsess=8ee1931018a77b55f67ce87073154436&app=members&module=members&section=members&do=members_overview
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT m.*, m.member_id as mem_id, m.ip_address as mem_ip,p.*,pp.*,par.*,val.* FROM members m LEFT JOIN pfields_content p ON ( p.member_id=m.member_id )
LEFT JOIN profile_portal pp ON ( pp.pp_member_id=m.member_id )
LEFT JOIN members_partial par ON ( par.partial_member_id=m.member_id )
LEFT JOIN validating val ON ( val.member_id=m.member_id ) WHERE par.partial_member_id IS NULL ORDER BY m.joined desc LIMIT 0,20
**
Date: Thu, 06 Feb 2014 21:38:50 +0000
Error: 1054 - Unknown column 'member_id' in 'where clause'
IP Address: - mocfix.php
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: UPDATE profile_portal SET pp_about_me='Family, friends, music, coffee!' WHERE member_id = 874
**
------------------------------------------------
Date: Mon, 03 Feb 2014 21:10:39 +0000
Error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
IP Address: - mocfix.php
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: UPDATE pfields_content SET field_13='' WHERE member_id =
**
Date: Sat, 08 Mar 2014 22:12:35 +0000
Error: 2013 - Lost connection to MySQL server during query
IP Address: 99.14.0.137 - /index.php?app=core&module=search&do=search&fromMainBar=1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mySQL query error: SELECT p.pid, p.queued,t.approved, t.forum_id FROM posts p LEFT JOIN topics t ON ( p.topic_id=t.tid ) WHERE t.forum_id IN (6,9,4,23) AND p.queued=0 AND t.approved=1 AND t.topic_archive_status IN (0,3) AND MATCH( p.post ) AGAINST( '+soft +like +kitten' IN BOOLEAN MODE ) AND t.state != 'link' ORDER BY post_date desc LIMIT 0,200
Link to comment
Share on other sites

Numbering your logs from 1~7.

5 & 6 look like programming errors. Going to brush them aside for now.

The fact that search takes a long time is... obvious. And occasionally timing out is understandable. Not great, but understandable.

3 & 4. Uh... run repair? Should help.

Overall. I don't think your ipb generated error logs tell much. I could recommend you to run & install sphinx to reduce frequency of lost connection during these searches, but not really the problem you're trying to solve. You could post your mysql configuration to see if max_allowed_packet is the issue like ipb support suggests, but my guess is it just timed out. If max allowed packet was the limitation, I'd expect a lot more frequency of these issues. You have 138k members. Search all of their sigs, about me section, etc for a keyword in a vps disk causing timeout is perfectly plausible. Like the disk could have been busy for any reason for a moment and that could occur.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...