WordPress.org

Forums

Yet Another Related Posts Plugin (YARPP)
MySQL error log filling with "[Warning] Statement may not be safe to log in stat (2 posts)

  1. We've been having some database issues since starting to use YARPP.

    Our /tmp directory starting filling up and eventually all the *.MYI tables crashed and had to be repaired.

    Now the mysql error log is filled with entries like this one:

    110723 9:28:03 [Warning] Statement may not be safe to log in statement format. Statement: insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 49640 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('patients patient doctor doctors communicate physician tell thought communication remember people says physicians paul may dorio hospitalized dont specialist said ')) * 3+ (MATCH (post_title) AGAINST ('say patients doctors attention close pay ')) * 3,1) as score
    from wp_posts
    where (post_status IN ( 'publish', 'static' ) and ID != '49640') and post_password ='' and post_
    110723 9:28:25 [Warning] Statement may not be safe to log in statement format. Statement: insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 24754 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('yikes ')) * 3+ (MATCH (post_title) AGAINST ('fattest man book guinness glad im ')) * 3,1) as score
    from wp_posts
    where (post_status IN ( 'publish', 'static' ) and ID != '24754') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'post'
    group by ID
    having score >= 5.00 order by score desc limit 6) union (SELECT 24754 as reference

    I've also noticed a large increase of joins not using indexes so I started logging them in the slow query log and it's filled with entries like this:

    # User@Host: xxxxxxx.xx[xxxxxx_wp] @ localhost []
    # Query_time: 0.035477 Lock_time: 0.000111 Rows_sent: 0 Rows_examined: 8831
    SET timestamp=1311439559;
    insert into wp_yarpp_related_cache (reference_ID,ID,score) (SELECT 49994 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('guidelines chronic pelvic antibiotics alpha pain mrsa dont oa treating icds guideline evidence practice prevention standard primary disregarding manage painkillers ')) * 3+ (MATCH (post_title) AGAINST ('january 2011 morning medicine stories health top ')) * 3,1) as score
    from wp_posts
    where (post_status IN ( 'publish', 'static' ) and ID != '49994') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'post'
    group by ID
    having score >= 5.00 order by score desc limit 6) union (SELECT 49994 as reference_ID, ID, ROUND(0+ (MATCH (post_content) AGAINST ('guidelines chronic pelvic antibiotics alpha pain mrsa dont oa treating icds guideline evidence practice prevention standard primary disregarding manage painkillers ')) * 3+ (MATCH (post_title) AGAINST ('january 2011 morning medicine stories health top ')) * 3,1) as score
    from wp_posts
    where (post_status IN ( 'publish', 'static' ) and ID != '49994') and post_password ='' and post_date > date_sub(now(), interval 18 month) and post_type = 'page'
    group by ID
    having score >= 5.00 order by score desc limit 6) on duplicate key update date = now();

    I've already made the changes suggested in the FAQ to make the plugin less database intensive. Is there anything else I can do to solve some of these issues? Thanks in advance!

  2. mitcho (Michael Yoshitaka Erlewine)
    Member
    Plugin Author

    Posted 4 years ago #

    These are indeed some of the YARPP queries. Which columns are being joined without indices?

Topic Closed

This topic has been closed to new replies.

About this Plugin

  • Yet Another Related Posts Plugin (YARPP)
  • Frequently Asked Questions
  • Support Threads
  • Reviews

About this Topic