MySQL 8.0 Issue

  • Resolved paschoolboards

    (@paschoolboards)


    4 years ago

    I recently had an issue with a bunch of wordpress sites that use the Pods plugin after migrating to a hosted database server. After finding nothing in any searches or support forums, tried to replicate the issue using Navicat’s query tools. So when I built the query using the gui builder, the query worked, but not the one from wordpress. After examining it more closely I found the problem. The pods plugin uses double quotes (“) in the sql statement. This was fine in MySQL 5.x, but in version 8.0 they added ANSI_QUOTES option to sql_mode. this is a database wide setting, so it’s not something i can update for just a couple sites. Is their a reason Pods is using ” in statements instead of the pretty-much industry-wide single quote (‘) character for identifying strings? If possible could these double quotes please be replaced with the SQL standard single quote. I’m sure I am not the only person who has encountered this issue and won’t be the last

Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Contributor Scott Kingsley Clark

    (@sc0ttkclark)

    Can you give an example of some queries you are seeing that are causing the problem?

    Thread Starter paschoolboards

    (@paschoolboards)

    sure i will turn back on ansi_quotes and post an example

    Thread Starter paschoolboards

    (@paschoolboards)

    On Line 12, you can see how the double quotes are used to depict a strict but when ANSI_QUOTES is enabled, it sees “blahblah” as an identifired same as ` is used. So it’s looking for a column instead of seeing it as a string value. just changing it to ‘product_type’ makes it ANSI SQL compliant.

    01. WordPress database error Unknown column ‘product_type’ in ‘where clause’ for query 02. SELECT DISTINCT
    03. *, t.term_id AS pod_item_id
    04. FROM
    05. wp_terms AS t
    06. LEFT JOIN wp_term_taxonomy AS tt
    07. ON tt.term_id = t.term_id
    08. LEFT JOIN wp_term_relationships AS tr
    09. ON tr.term_taxonomy_id = tt.term_taxonomy_id
    10. WHERE
    11. ( ( t.term_id = 19 )
    12. AND ( tt.taxonomy = “product_type” ) )

    made by require(‘wp-blog-header.php’), wp, WP->main, WP->parse_request, do_action_ref_array(‘parse_request’), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, PSBA_Sync_Service->psba_product_postHandler, wp_update_post, wp_insert_post, apply_filters(‘wp_insert_post_data’), WP_Hook->apply_filters, PodsMeta->save_post_track_changed_fields, PodsAPI::handle_changed_fields, Pods->export, PodsAPI->export_pod_item, PodsAPI->export_pod_item_level, Pods->field, PodsData->select, pods_query, PodsData::query

    Plugin Contributor Scott Kingsley Clark

    (@sc0ttkclark)

    Thanks I’ll check into this and evaluate our usage here.

    Plugin Contributor Scott Kingsley Clark

    (@sc0ttkclark)

    Found the usage areas and will fix. This will be fixed/released in Pods 2.8 which is now in Beta status. I should have the fixes in today, but obviously I’m not proposing using Pods 2.8 Beta on your production site — just being clear that it’s coming in this next major release.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘MySQL 8.0 Issue’ is closed to new replies.