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
45 views
asked 5 days ago 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

1 Answer

+2 votes
answered 5 days ago by pupi1985
selected 5 days ago 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 5 days ago by arjunsuresh
Thank you pupi. I'll do the same. Sorry, the query was posted from the log and hence looks ugly.
...