WordPress.org

Ready to get started?Download WordPress

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 2 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

About this Topic