Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

SQL error in Category Experts plugin

0 votes
104 views
asked Oct 12 in Plugins by arjunsuresh
I'm getting the following error while the category Experts plugin update the points. Any help?

PHP Question2Answer MySQL query error 1100: Table 'userid_src' was not locked with LOCK TABLES - Query: replace into qa_catpoints (categoryid, userid, points, netvotes, aselects)  select  b.categoryid,'34869', 80*(select  COUNT(*) AS aselecteds FROM qa_posts AS userid_src JOIN qa_posts AS questions ON questions.selchildid=userid_src.postid WHERE userid_src.userid='34869' AND userid_src.type='A' AND NOT (questions.userid<=>userid_src.userid) and (userid_src.categoryid = b.categoryid or userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid = b.categoryid)\nor userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid in (select categoryid from qa_categories where parentid = b.categoryid))))+2*(select  COALESCE(SUM(LEAST(5*upvotes,40)-LEAST(1*downvotes,10)), 0) AS avoteds FROM qa_posts AS userid_src WHERE LEFT(type, 1)='A' AND userid='34869' and (userid_src.categoryid = b.categoryid or userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid = b.categoryid)\nor userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid in (select categoryid from qa_categories where parentid = b.categoryid))))\nas points,\n(select  COALESCE(SUM(LEAST(5*upvotes,40)-LEAST(1*downvotes,10)), 0) AS avoteds FROM qa_posts AS userid_src WHERE LEFT(type, 1)='A' AND userid='34869' and (userid_src.categoryid = b.categoryid or userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid = b.categoryid)\nor userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid in (select categoryid from qa_categories where parentid = b.categoryid)))) as netvotes,\n(select  COUNT(*) AS aselecteds FROM qa_posts AS userid_src JOIN qa_posts AS questions ON questions.selchildid=userid_src.postid WHERE userid_src.userid='34869' AND userid_src.type='A' AND NOT (questions.userid<=>userid_src.userid) and (userid_src.categoryid = b.categoryid or userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid = b.categoryid)\nor userid_src.categoryid  in  (\nselect categoryid from qa_categories where parentid in (select categoryid from qa_categories where parentid = b.categoryid)))) as aselects\n from  qa_categories b
Q2A version: 1.8

2 Answers

+2 votes
answered Oct 12 by pupi1985
selected Oct 12 by arjunsuresh
 
Best answer

That's some ugly piece of code :) Try to format it before posting with some online formatting tool.

Anyway, I think there might be some issue related to transactions (which Q2A nor you seem to be using there). Maybe more than one of those queries are running at the same time and messing with each other.

Maybe, splitting that huge query into smaller chunks will fix the issue: 1 (or maybe 2) queries for points, 1 for netvotes, 1 for aselects and 1 for the insert itself. Just keep track of the queries result in variables in PHP.

BTW, don't use REPLACE INTO. For some MySQListerious reason it runs considerably slower that INSERT ... ON DUPLICATE KEY UPDATE.

commented Oct 12 by arjunsuresh
Thank you pupi. I'll do the same. Sorry, the query was posted from the log and hence looks ugly.
0 votes
answered Nov 2 by Piotr Baranowski
@Arjun , do You know how to resolve this problem?

I have same problem with Your plugin, same code in error.log :/

Peter
commented Nov 2 by Piotr Baranowski
one more thing - can You add more variables to this plugin? I think also qposts, aposts, cposts, votes, etc. - all variables from standard points system in q2a? ;) Now it's only selected answers+votes for posts available to show points for category :(
commented Nov 3 by arjunsuresh
It is working now, but there is a bigger problem. The points update query is too heavy causing 3s and more waiting time on user upvotes etc. This is mainly due to multiple levels of categories and as of now I don't know a way to fix this.
Regarding other variables -- as the name suggests the plugin is meant for "experts" and not the contributor points per category.
...