Posts in Results Duplicated Dozens of Times

  • Resolved tflight

    (@tflight)


    1 year, 4 months ago

    I’ve encountered a weird issue recently where the search results are duplicated when this plugin is enabled. For example if I search for “hello search”, the first match is duplicated a dozen or so times in the search results. Disabling the plugin resolves the issue.

    This only started happening recently and isn’t happening on my local or staging sites, only the production site. The few differences in the production site are things like object caching. Of course debugging the live site without access to a step debugger, etc is a bit tricky. Likewise disabling plugins on a busy live site isn’t fun either.

    I’ve worked around the issue with this:

    function cc_search_distinct($where)
{
    if (is_search()) {
        return "DISTINCT";
    }
    return $where;
}
add_filter('posts_distinct', 'cc_search_distinct');

    That fixes the issue for me, but I’m not certain what changed in my setup where I suddenly need this. I suspect it has something to do with the sql_distinct function in act-better-search/src/Search/Request.php but inspecting those conditions on the live site isn’t easy.

Viewing 15 replies - 1 through 15 (of 24 total)
  • Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    Hi @tflight,

    Thank you for your message.

    The DISTINCT value does just that so that there are no duplicate results. The Request.php file you mentioned adds DISTINCT to the request.

    I honestly admit that I have never encountered the problem you are talking about, nor has anyone reported such a problem before. It’s hard for me to say anything more.

    Could you check it on a different server with a different database?

    Best,
    Mateusz

    Same Issue here. Happening in all sites where plugin is enabled.

    Disabling the plugin solves the issue.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @tonyko Can I ask you to run a little test? Download and install this plugin version. Then do some test searches. An acf-better-search.log file should be created in the root directory of your WordPress. Please send me its contents, it will allow me to try to understand the problem.

    Hi with the new version the search appears to work normaly.

    Please find below the LOG results:

    @hitninc:/data/s931109/dom44572/dom44572$ cat acf-better-search.log2023-08-01 12:59:30 : SELECT DISTINCT SQL_CALC_FOUND_ROWS  wp_posts.*    FROM wp_posts  LEFT  JOIN wp_icl_translations wpml_translations        ON wp_posts.ID = wpml_translations.element_id         AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)  INNER JOIN wp_postmeta AS a ON ( a.post_id = wp_posts.ID ) LEFT JOIN wp_postmeta AS b ON ( (( b.post_id = a.post_id )) AND (( b.meta_key LIKE CONCAT( '\_', a.meta_key ) )) ) LEFT JOIN wp_posts AS c ON ( ( c.post_name = b.meta_value ) AND ( c.post_type = 'acf-field' ) AND (( c.post_content LIKE '%:"text"%' ) OR ( c.post_content LIKE '%:"textarea"%' ) OR ( c.post_content LIKE '%:"wysiwyg"%' )) )     WHERE 1=1  AND ( ( ( b.meta_id IS NOT NULL ) AND ( c.ID IS NOT NULL ) AND ( a.meta_value LIKE '%padres%' ) ) OR ( ( wp_posts.post_title LIKE '%padres%' ) OR ( wp_posts.post_content LIKE '%padres%' ) OR ( wp_posts.post_excerpt LIKE '%padres%' ) ) )  AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'es' OR 0 ) AND wp_posts.post_type  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  ) OR wp_posts.post_type  NOT  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  )        ORDER BY wp_posts.post_title LIKE '{fd6f07307bfb07e1e3b3c6128599779c43a99cad7aadd4f421be852af116a455}padres{fd6f07307bfb07e1e3b3c6128599779c43a99cad7aadd4f421be852af116a455}' DESC, wp_posts.post_date DESC    LIMIT 0, 102023-08-01 12:59:40 : SELECT DISTINCT SQL_CALC_FOUND_ROWS  wp_posts.*    FROM wp_posts  LEFT  JOIN wp_icl_translations wpml_translations        ON wp_posts.ID = wpml_translations.element_id         AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)  INNER JOIN wp_postmeta AS a ON ( a.post_id = wp_posts.ID ) LEFT JOIN wp_postmeta AS b ON ( (( b.post_id = a.post_id )) AND (( b.meta_key LIKE CONCAT( '\_', a.meta_key ) )) ) LEFT JOIN wp_posts AS c ON ( ( c.post_name = b.meta_value ) AND ( c.post_type = 'acf-field' ) AND (( c.post_content LIKE '%:"text"%' ) OR ( c.post_content LIKE '%:"textarea"%' ) OR ( c.post_content LIKE '%:"wysiwyg"%' )) )     WHERE 1=1  AND ( ( ( b.meta_id IS NOT NULL ) AND ( c.ID IS NOT NULL ) AND ( a.meta_value LIKE '%juntos%' ) ) OR ( ( wp_posts.post_title LIKE '%juntos%' ) OR ( wp_posts.post_content LIKE '%juntos%' ) OR ( wp_posts.post_excerpt LIKE '%juntos%' ) ) )  AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'es' OR 0 ) AND wp_posts.post_type  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  ) OR wp_posts.post_type  NOT  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  )        ORDER BY wp_posts.post_title LIKE '{ba73c975ab5ee76f6c54cc9c0f2448f2d8d30cf4fc917c482066769b08fb180e}juntos{ba73c975ab5ee76f6c54cc9c0f2448f2d8d30cf4fc917c482066769b08fb180e}' DESC, wp_posts.post_date DESC    LIMIT 0, 102023-08-01 13:00:00 : SELECT DISTINCT SQL_CALC_FOUND_ROWS  wp_posts.*    FROM wp_posts  LEFT  JOIN wp_icl_translations wpml_translations        ON wp_posts.ID = wpml_translations.element_id         AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)  INNER JOIN wp_postmeta AS a ON ( a.post_id = wp_posts.ID ) LEFT JOIN wp_postmeta AS b ON ( (( b.post_id = a.post_id )) AND (( b.meta_key LIKE CONCAT( '\_', a.meta_key ) )) ) LEFT JOIN wp_posts AS c ON ( ( c.post_name = b.meta_value ) AND ( c.post_type = 'acf-field' ) AND (( c.post_content LIKE '%:"text"%' ) OR ( c.post_content LIKE '%:"textarea"%' ) OR ( c.post_content LIKE '%:"wysiwyg"%' )) )     WHERE 1=1  AND ( ( ( b.meta_id IS NOT NULL ) AND ( c.ID IS NOT NULL ) AND ( a.meta_value LIKE '%padres%' ) ) OR ( ( wp_posts.post_title LIKE '%padres%' ) OR ( wp_posts.post_content LIKE '%padres%' ) OR ( wp_posts.post_excerpt LIKE '%padres%' ) ) )  AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private')) OR (wp_posts.post_type = 'attachment' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled'  OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'es' OR 0 ) AND wp_posts.post_type  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  ) OR wp_posts.post_type  NOT  IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','edye_books','edye_coloring','edye_games','edye_puzzles' )  )        ORDER BY wp_posts.post_title LIKE '{a220129d3dcca9dc68f41679a41eae73c1521c32bc26d0e4c989776532b59bc9}padres{a220129d3dcca9dc68f41679a41eae73c1521c32bc26d0e4c989776532b59bc9}' DESC, wp_posts.post_date DESC    LIMIT 0, 10
    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @tonyko What version were you using before? I haven’t changed anything in this version other than adding debugging to the acf-better-search.log file.

    I had 4.1.1 installed.
    turning it off restored my search results

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @tonyko How should I understand that?

    Having this same issue. Tried deactivating/activating the plugin but didn’t resolve. How can I help to troubleshoot?

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @swb1192 Can you test it in another environment? On a completely different server or hosting.

    I’m able to replicate on my local machine as well as hosting environment. WP 6.3 + PHP 8.0.22

    Tried all different PHP versions with the same results.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @swb1192 What database are you using and what version?

    MariaDB 10.6 or MySQL 8.0.16

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @swb1192 I checked it on a similar configuration and I don’t have this type of problem. It’s hard for me to say anything because I can’t replicate this problem in my local environment.

    Does the problem go away after deactivating the WPML plugin?

    I don’t have WPML installed.

    Plugin Author Mateusz Gbiorczyk

    (@mateuszgbiorczyk)

    @tflight, @tonyko, @swb1192, do you have access to phpMyAdmin? If so, could you do some test SQL queries? For each of them, please give me the number of rows received – this will allow me to better understand the source of the problem.

    1. Test #1: SELECT wp_posts.* FROM wp_posts;
    2. Test #2: SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID;
    3. Test #3: SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID INNER JOIN wp_postmeta AS b ON b.post_id = wp_posts.ID;
    4. Test #4: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID;
    5. Test #5: SELECT DISTINCT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID;
    6. Test #6: SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID;
    7. Test #7: SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID INNER JOIN wp_postmeta AS b ON b.post_id = wp_posts.ID;
    8. Test #8: SELECT DISTINCT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID INNER JOIN wp_postmeta AS b ON b.post_id = wp_posts.ID;
    9. Test #9: SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta AS a ON a.post_id = wp_posts.ID INNER JOIN wp_postmeta AS b ON b.post_id = wp_posts.ID;

    Thank you very much in advance!

Viewing 15 replies - 1 through 15 (of 24 total)
  • The topic ‘Posts in Results Duplicated Dozens of Times’ is closed to new replies.