Posted 3 years ago #
First, fantastic product! This is really good stuff!
I recently updated YARPP to 2.0.5. I had to revert back to 2.0.4 this afternoon. It turns out that the newest version was pegging out MySQL. I'm guessing this has something to do with recent changes regarding subquerries. mysqladmin showed it hanging on a copy to temporary table.
Feel free to reply with questions.
Cheers,
Jason
http://wordpress.org/extend/plugins/yet-another-related-posts-plugin/
Posted 3 years ago #
Yes, there was a query bug in 2.0.5 that I'm in the process of resolving right now... but it doesn't create any temporary tables. Are you sure that was due to YARPP??
If you send me a little more about your set up or the errors you're getting, I'd appreciate it.
Posted 3 years ago #
I have a problem with 2.0.5, using previous version no issues.
This is what appears at top of an article:
WordPress database error: [Unknown column 'wp_posts.ID' in 'on clause']
SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST [....]
Posted 3 years ago #
vfvalent is showing the exact same problem. Here's output from mysqladmin on one of our domains jlbreport.com.
[root@beasleyallen1 jlbreport]# mysqladmin processlist
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 23329 | jlbreport | localhost | jlbreport | Query | 7 | Copying to tmp table | SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan in |
| 23335 | jlbreport | localhost | jlbreport | Sleep | 0 | | |
| 23337 | root | localhost | | Query | 0 | | show processlist |
+-------+-----------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
[root@beasleyallen1 jlbreport]# mysqladmin processlist --verbose
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 23329 | jlbreport | localhost | jlbreport | Query | 10 | Copying to tmp table | SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 1 + COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1 + COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) AS score
FROM wp_posts left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 499 )
left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
AND tagrel.object_id = wp_posts.ID)
left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
AND tagtax.taxonomy = 'post_tag')
left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 499 )
left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
AND catrel.object_id = wp_posts.ID)
left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
AND cattax.taxonomy = 'category')
where (post_status IN ( 'publish', 'static' ) && ID != '499') and post_password ='' and post_type IN ('post') GROUP BY id having score >= 5 order by score DESC limit 5 |
| 23338 | root | localhost | | Query | 0 | | show full processlist |
+-------+-----------+-----------+-----------+---------+------+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Posted 3 years ago #
No, @vfvalent's issue is a different one--it's a boneheaded bug I introduced in 2.0.5. @vfvalent, if you email me I'll send you a development version which fixes that bug. My apologize. It will be released soon as 2.0.6, in case you don't mind waiting a couple days.
@wohlfordba - I see... it's just copying to a temporary table as one of the join gets too big, not because I use a create temporary. Could you try running this query?
explain SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 1 + COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1 + COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) AS score
FROM wp_posts left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 499 )
left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id
AND tagrel.object_id = wp_posts.ID)
left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id
AND tagtax.taxonomy = 'post_tag')
left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 499 )
left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id
AND catrel.object_id = wp_posts.ID)
left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id
AND cattax.taxonomy = 'category')
where (post_status IN ( 'publish', 'static' ) && ID != '499') and post_password ='' and post_type IN ('post') GROUP BY id having score >= 5 order by score DESC limit 5This will give me some information on why this query is slow and how large the intermediate joins are.
Thanks!
mitcho
Posted 3 years ago #
It works for me. Thanks
Posted 3 years ago #
@mitchoyoshitaka output follows:
mysql> explain SELECT ID, post_title, post_date, post_content, (MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) as bodyscore, (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) as titlescore, COUNT( DISTINCT tagtax.term_taxonomy_id ) AS tagscore, COUNT( DISTINCT cattax.term_taxonomy_id ) AS catscore, ((MATCH (post_content) AGAINST ('payday loans loan interest lending ohio state lenders capped people states % rate ? financial 36% industry close cap cost')) * 1 + (MATCH (post_title) AGAINST ('ohio lenders payday crackdown')) * 1 + COUNT( DISTINCT tagtax.term_taxonomy_id ) * 1 + COUNT( DISTINCT cattax.term_taxonomy_id ) * 1) AS score FROM wp_posts left JOIN wp_term_relationships AS thistag ON (thistag.object_id = 499 ) left JOIN wp_term_relationships AS tagrel on (tagrel.term_taxonomy_id = thistag.term_taxonomy_id AND tagrel.object_id = wp_posts.ID) left JOIN wp_term_taxonomy AS tagtax ON ( tagrel.term_taxonomy_id = tagtax.term_taxonomy_id AND tagtax.taxonomy = 'post_tag') left JOIN wp_term_relationships AS thiscat ON (thiscat.object_id = 499 ) left JOIN wp_term_relationships AS catrel on (catrel.term_taxonomy_id = thiscat.term_taxonomy_id AND catrel.object_id = wp_posts.ID) left JOIN wp_term_taxonomy AS cattax ON ( catrel.term_taxonomy_id = cattax.term_taxonomy_id AND cattax.taxonomy = 'category') where (post_status IN ( 'publish', 'static' ) && ID != '499') and post_password ='' and post_type IN ('post') GROUP BY id having score >= 5 order by score DESC limit 5;
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | wp_posts | ref | PRIMARY,post_status,type_status_date | type_status_date | 20 | const | 329 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | thistag | ref | PRIMARY | PRIMARY | 8 | const | 176 | Using where; Using index |
| 1 | SIMPLE | tagrel | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | jlbreport.wp_posts.ID,jlbreport.thistag.term_taxonomy_id | 1 | Using index |
| 1 | SIMPLE | tagtax | eq_ref | PRIMARY | PRIMARY | 8 | jlbreport.tagrel.term_taxonomy_id | 1 | |
| 1 | SIMPLE | thiscat | ref | PRIMARY | PRIMARY | 8 | const | 176 | Using where; Using index |
| 1 | SIMPLE | catrel | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | jlbreport.wp_posts.ID,jlbreport.thiscat.term_taxonomy_id | 1 | Using index |
| 1 | SIMPLE | cattax | eq_ref | PRIMARY | PRIMARY | 8 | jlbreport.catrel.term_taxonomy_id | 1 | |
+----+-------------+----------+--------+--------------------------------------+------------------+---------+----------------------------------------------------------+------+----------------------------------------------+
7 rows in set (0.00 sec)
mysql>
Posted 3 years ago #
@mitchoyoshitaka P.S. I'm out til Tuesday. Will try to email you.
Posted 3 years ago #
@wohlfordba - Hm... that query doesn't look like it should take long, and it looks pretty well optimized. Let me know if you continue to feel the queries are taking too long.
mitcho
Posted 3 years ago #
I have like 8k posts on my blog, 20k unique visitors a day, I just don't know what happened because it died, it's showing me "Error Connecting to Database" error messages. I just found out that it was the YARPP 2.0.5 update, I had to temporarily deactivate the plugin until it gets fix. I love this plugin. Thanks
Posted 3 years ago #
@unseenmortal I'd suggest using the 2.0.4 version. This one has issues.
Posted 3 years ago #
@unseenmortal - Please try the new 2.0.6 version, as WP may have interpreted the bug in 2.0.5 as a connection error. Otherwise, I would like to know more about your database set up in order to help resolve the problem. If you could tell me what version of MySQL you are running, I would appreciate that... in addition if you email me, I can get you a debug version that will help better identify what that "connection error" seems to be.
Sorry for the troubles--
mitcho
This topic has been closed to new replies.
