SQL error in Category Experts plugin

Question

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

Answer 1

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.

Comments

Thank you pupi. I'll do the same. Sorry, the query was posted from the log and hence looks ugly.

Answer 2

@Arjun , do You know how to resolve this problem?

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

Peter

Comments

Hopefully yes, I have tried the changes suggested by pupi. Testing it on my server. So far good. Will update the code by tomorrow.

---

great, I'm waiting - it's really important for me . Thanks !

---

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 :(

---

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.