Support » Plugin: BA Book Everything » 2 database errors

  • Resolved getin2magic

    (@getin2magic)


    hy there,my monitoring plugin shows me 2 database errors. I dont know, how and where i have to fix it. can you please have a look on it and help me?

    SELECT *, (t_rate.price_from*(100-COALESCE(pmd.discount, 0))*(100+COALESCE(tm2.categories_add_taxes, 0)*COALESCE(tm3.categories_tax, 0)))/(100*100) AS discount_price_from
    FROM wp_posts posts
    INNER JOIN #every our post assigned to terms from categories and other taxonomies (
    SELECT object_id AS tr_object_id, term_taxonomy_id AS tr_term_taxonomy_id
    FROM wp_term_relationships ) tr
    ON posts.ID = tr.tr_object_id
    INNER JOIN #we need only our post type with categories (
    SELECT term_taxonomy_id AS ct_term_taxonomy_id, term_id AS ct_term_id
    FROM wp_term_taxonomy
    WHERE taxonomy = ‘categories’ ) ct
    ON ct.ct_term_taxonomy_id = tr.tr_term_taxonomy_id
    INNER JOIN #get category slug (
    SELECT slug AS category_slug, term_id AS ctt_term_id
    FROM wp_terms ) ctt
    ON ctt.ctt_term_id = ct.ct_term_id
    INNER JOIN # get rates (
    SELECT rate_id, booking_obj_id AS rate_booking_obj_id, rate_title, date_from AS rate_date_from, date_to AS rate_date_to, apply_days, start_days, min_booking_period, max_booking_period, price_from, price_general, prices_conditional, rate_order
    FROM wp_babe_rates
    WHERE ( date_to >= ‘2020-09-24 00:00’
    OR date_to IS NULL
    OR date_to = ” )
    AND ( date_from <= ‘2021-09-24 00:00’
    OR date_from IS NULL
    OR date_from = ” ) #
    GROUP BY rate_booking_obj_id
    ORDER BY rate_booking_obj_id ASC, rate_order ASC, price_from ASC, rate_date_from DESC, rate_date_to DESC
    LIMIT 10000 ) t_rate
    ON posts.ID = t_rate.rate_booking_obj_id
    LEFT JOIN #add categories_booking_rule meta (
    SELECT meta_value AS categories_booking_rule, term_id AS tm_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_booking_rule’ ) tm
    ON ct.ct_term_id = tm.tm_term_id
    LEFT JOIN #add categories_add_taxes meta (
    SELECT CAST(meta_value AS UNSIGNED) AS categories_add_taxes, term_id AS tm2_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_add_taxes’ ) tm2
    ON ct.ct_term_id = tm2.tm2_term_id
    LEFT JOIN #add categories_tax meta (
    SELECT CAST(meta_value AS UNSIGNED) AS categories_tax, term_id AS tm3_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_tax’ ) tm3
    ON ct.ct_term_id = tm3.tm3_term_id
    LEFT JOIN #get rating (
    SELECT CAST(meta_value AS DECIMAL(3,2)) AS rating, post_id AS pmr_post_id
    FROM wp_postmeta
    WHERE meta_key = ‘_rating’ ) pmr
    ON posts.ID = pmr.pmr_post_id
    LEFT JOIN #get discount (
    SELECT discount, date_from AS discount_date_from, date_to AS discount_date_to, booking_obj_id AS discount_obj_id
    FROM wp_babe_discount
    WHERE date_from <= ‘2020-09-24 02:58:07’
    AND date_to >= ‘2020-09-24 02:58:07’ ) pmd
    ON posts.ID = pmd.discount_obj_id
    LEFT JOIN # get rule (
    SELECT *
    FROM wp_babe_booking_rules ) rules
    ON rules.rule_id = tm.categories_booking_rule
    LEFT JOIN #get items number (
    SELECT GREATEST(CAST(COALESCE(meta_value, 1) AS DECIMAL), 1) AS items_number, post_id AS pmt_post_id, meta_key AS pmt_meta_key
    FROM wp_postmeta ) pmt
    ON posts.ID = pmt.pmt_post_id
    AND pmt.pmt_meta_key = CONCAT(‘items_number_’, ctt.category_slug)
    INNER JOIN #get max guests (
    SELECT CAST(meta_value AS DECIMAL) AS guests, post_id AS pm_post_id
    FROM wp_postmeta
    WHERE meta_key = ‘guests’ ) pm
    ON posts.ID = pm.pm_post_id
    AND pm.guests >= 0
    INNER JOIN # get av cal (
    SELECT booking_obj_id AS obj_id
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00’
    AND date_from <= ‘2021-09-24 00:00’
    GROUP BY booking_obj_id ) av_cal
    ON av_cal.obj_id = posts.ID
    LEFT JOIN # get av cal ex (
    SELECT booking_obj_id AS ex_obj_id, date_from AS av_date_from, MAX(guests) AS ex_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    GROUP BY booking_obj_id ) av_cal_ex
    ON av_cal_ex.ex_obj_id = posts.ID
    AND ( rules.booking_mode = ‘object’
    AND av_cal_ex.ex_guests_present > (pm.guests*pmt.items_number – 0 )
    OR rules.booking_mode != ‘object’
    AND av_cal_ex.ex_guests_present > (pm.guests – 0 ) )
    AND rules.basic_booking_period != ‘recurrent_custom’
    AND rules.basic_booking_period != ‘night’
    AND av_cal_ex.av_date_from >= DATE_SUB(‘2020-09-24 00:00:00’, INTERVAL rules.hold HOUR)
    AND av_cal_ex.av_date_from <= DATE_ADD(‘2021-09-24 00:00:00’, INTERVAL rules.hold HOUR)
    LEFT JOIN # get av cal ex2 (
    SELECT booking_obj_id AS ex2_obj_id, MAX(guests) AS ex2_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00’
    AND date_from < ‘2021-09-24 00:00’
    GROUP BY booking_obj_id ) av_cal_ex2
    ON av_cal_ex2.ex2_obj_id = posts.ID
    AND ( rules.booking_mode = ‘object’
    AND av_cal_ex2.ex2_guests_present > (pm.guests*pmt.items_number – 0 )
    OR rules.booking_mode != ‘object’
    AND av_cal_ex2.ex2_guests_present > (pm.guests – 0 ) )
    AND rules.basic_booking_period = ‘night’
    LEFT JOIN # get av cal ex3 for recurrent_custom (
    SELECT booking_obj_id AS ex3_obj_id, MIN(guests) AS ex3_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00:00’
    AND date_from <= ‘2021-09-24 00:00:00’
    GROUP BY booking_obj_id ) av_cal_ex3
    ON av_cal_ex3.ex3_obj_id = posts.ID
    AND av_cal_ex3.ex3_guests_present > (pm.guests – 0 )
    AND rules.basic_booking_period = ‘recurrent_custom’
    WHERE ( (posts.post_status = ‘publish’
    AND posts.post_type = ‘to_book’)
    AND av_cal_ex.ex_obj_id IS NULL
    AND av_cal_ex2.ex2_obj_id IS NULL
    AND av_cal_ex3.ex3_obj_id IS NULL )
    GROUP BY posts.ID
    ORDER BY discount_price_from ASC –
    BABE_Post_types::get_posts()
    wp-content/plugins/ba-book-everything/includes/class-babe-post-types.php:1151
    ssc_get_rooms_select_options()
    wp-content/plugins/secretlab_shortcodes/shortcodes/room.php:957
    ssc_room_params()
    wp-content/plugins/secretlab_shortcodes/shortcodes/room.php:35
    do_action(‘init’)
    wp-includes/plugin.php:478
    Plugin: ba-book-everything Incorrect DATETIME value: ” 1525
    SELECT COUNT(discount_price_from) AS total_count
    FROM (SELECT *, (t_rate.price_from*(100-COALESCE(pmd.discount, 0))*(100+COALESCE(tm2.categories_add_taxes, 0)*COALESCE(tm3.categories_tax, 0)))/(100*100) AS discount_price_from
    FROM wp_posts posts
    INNER JOIN #every our post assigned to terms from categories and other taxonomies (
    SELECT object_id AS tr_object_id, term_taxonomy_id AS tr_term_taxonomy_id
    FROM wp_term_relationships ) tr
    ON posts.ID = tr.tr_object_id
    INNER JOIN #we need only our post type with categories (
    SELECT term_taxonomy_id AS ct_term_taxonomy_id, term_id AS ct_term_id
    FROM wp_term_taxonomy
    WHERE taxonomy = ‘categories’ ) ct
    ON ct.ct_term_taxonomy_id = tr.tr_term_taxonomy_id
    INNER JOIN #get category slug (
    SELECT slug AS category_slug, term_id AS ctt_term_id
    FROM wp_terms ) ctt
    ON ctt.ctt_term_id = ct.ct_term_id
    INNER JOIN # get rates (
    SELECT rate_id, booking_obj_id AS rate_booking_obj_id, rate_title, date_from AS rate_date_from, date_to AS rate_date_to, apply_days, start_days, min_booking_period, max_booking_period, price_from, price_general, prices_conditional, rate_order
    FROM wp_babe_rates
    WHERE ( date_to >= ‘2020-09-24 00:00’
    OR date_to IS NULL
    OR date_to = ” )
    AND ( date_from <= ‘2021-09-24 00:00’
    OR date_from IS NULL
    OR date_from = ” ) #
    GROUP BY rate_booking_obj_id
    ORDER BY rate_booking_obj_id ASC, rate_order ASC, price_from ASC, rate_date_from DESC, rate_date_to DESC
    LIMIT 10000 ) t_rate
    ON posts.ID = t_rate.rate_booking_obj_id
    LEFT JOIN #add categories_booking_rule meta (
    SELECT meta_value AS categories_booking_rule, term_id AS tm_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_booking_rule’ ) tm
    ON ct.ct_term_id = tm.tm_term_id
    LEFT JOIN #add categories_add_taxes meta (
    SELECT CAST(meta_value AS UNSIGNED) AS categories_add_taxes, term_id AS tm2_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_add_taxes’ ) tm2
    ON ct.ct_term_id = tm2.tm2_term_id
    LEFT JOIN #add categories_tax meta (
    SELECT CAST(meta_value AS UNSIGNED) AS categories_tax, term_id AS tm3_term_id
    FROM wp_termmeta
    WHERE meta_key = ‘categories_tax’ ) tm3
    ON ct.ct_term_id = tm3.tm3_term_id
    LEFT JOIN #get rating (
    SELECT CAST(meta_value AS DECIMAL(3,2)) AS rating, post_id AS pmr_post_id
    FROM wp_postmeta
    WHERE meta_key = ‘_rating’ ) pmr
    ON posts.ID = pmr.pmr_post_id
    LEFT JOIN #get discount (
    SELECT discount, date_from AS discount_date_from, date_to AS discount_date_to, booking_obj_id AS discount_obj_id
    FROM wp_babe_discount
    WHERE date_from <= ‘2020-09-24 02:58:07’
    AND date_to >= ‘2020-09-24 02:58:07’ ) pmd
    ON posts.ID = pmd.discount_obj_id
    LEFT JOIN # get rule (
    SELECT *
    FROM wp_babe_booking_rules ) rules
    ON rules.rule_id = tm.categories_booking_rule
    LEFT JOIN #get items number (
    SELECT GREATEST(CAST(COALESCE(meta_value, 1) AS DECIMAL), 1) AS items_number, post_id AS pmt_post_id, meta_key AS pmt_meta_key
    FROM wp_postmeta ) pmt
    ON posts.ID = pmt.pmt_post_id
    AND pmt.pmt_meta_key = CONCAT(‘items_number_’, ctt.category_slug)
    INNER JOIN #get max guests (
    SELECT CAST(meta_value AS DECIMAL) AS guests, post_id AS pm_post_id
    FROM wp_postmeta
    WHERE meta_key = ‘guests’ ) pm
    ON posts.ID = pm.pm_post_id
    AND pm.guests >= 0
    INNER JOIN # get av cal (
    SELECT booking_obj_id AS obj_id
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00’
    AND date_from <= ‘2021-09-24 00:00’
    GROUP BY booking_obj_id ) av_cal
    ON av_cal.obj_id = posts.ID
    LEFT JOIN # get av cal ex (
    SELECT booking_obj_id AS ex_obj_id, date_from AS av_date_from, MAX(guests) AS ex_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    GROUP BY booking_obj_id ) av_cal_ex
    ON av_cal_ex.ex_obj_id = posts.ID
    AND ( rules.booking_mode = ‘object’
    AND av_cal_ex.ex_guests_present > (pm.guests*pmt.items_number – 0 )
    OR rules.booking_mode != ‘object’
    AND av_cal_ex.ex_guests_present > (pm.guests – 0 ) )
    AND rules.basic_booking_period != ‘recurrent_custom’
    AND rules.basic_booking_period != ‘night’
    AND av_cal_ex.av_date_from >= DATE_SUB(‘2020-09-24 00:00:00’, INTERVAL rules.hold HOUR)
    AND av_cal_ex.av_date_from <= DATE_ADD(‘2021-09-24 00:00:00’, INTERVAL rules.hold HOUR)
    LEFT JOIN # get av cal ex2 (
    SELECT booking_obj_id AS ex2_obj_id, MAX(guests) AS ex2_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00’
    AND date_from < ‘2021-09-24 00:00’
    GROUP BY booking_obj_id ) av_cal_ex2
    ON av_cal_ex2.ex2_obj_id = posts.ID
    AND ( rules.booking_mode = ‘object’
    AND av_cal_ex2.ex2_guests_present > (pm.guests*pmt.items_number – 0 )
    OR rules.booking_mode != ‘object’
    AND av_cal_ex2.ex2_guests_present > (pm.guests – 0 ) )
    AND rules.basic_booking_period = ‘night’
    LEFT JOIN # get av cal ex3 for recurrent_custom (
    SELECT booking_obj_id AS ex3_obj_id, MIN(guests) AS ex3_guests_present
    FROM wp_babe_av_cal
    WHERE in_schedule=1
    AND date_from >= ‘2020-09-24 00:00:00’
    AND date_from <= ‘2021-09-24 00:00:00’
    GROUP BY booking_obj_id ) av_cal_ex3
    ON av_cal_ex3.ex3_obj_id = posts.ID
    AND av_cal_ex3.ex3_guests_present > (pm.guests – 0 )
    AND rules.basic_booking_period = ‘recurrent_custom’
    WHERE ( (posts.post_status = ‘publish’
    AND posts.post_type = ‘to_book’)
    AND av_cal_ex.ex_obj_id IS NULL
    AND av_cal_ex2.ex2_obj_id IS NULL
    AND av_cal_ex3.ex3_obj_id IS NULL )
    GROUP BY posts.ID ) AS a –
    BABE_Post_types::get_posts()
    wp-content/plugins/ba-book-everything/includes/class-babe-post-types.php:1154
    ssc_get_rooms_select_options()
    wp-content/plugins/secretlab_shortcodes/shortcodes/room.php:957
    ssc_room_params()
    wp-content/plugins/secretlab_shortcodes/shortcodes/room.php:35
    do_action(‘init’)
    wp-includes/plugin.php:478
    Plugin: ba-book-everything Incorrect DATETIME value: ” 1525

    thanl you

    The page I need help with: [log in to see the link]

Viewing 3 replies - 1 through 3 (of 3 total)
  • dale668

    (@dale668)

    Hello!
    Looks like you have an error in your query, because we use your methods to get booking objects BABE_Post_types::get_posts() with parameter array( ‘posts_per_page’ => – 1 ) and BABE_Post_types::get_post_price_from( $item[‘ID’] ); both of methods showing error: “Incorrect DATETIME value: ”” when we checking site with “Query Monitor”
    Check it ASAP, please.
    Best regards

    Plugin Author bookingalgorithms

    (@bookingalgorithms)

    Hello,

    It looks like an issue with MySQL 8 sql_mode NO_ZERO_IN_DATE, NO_ZERO_DATE. Please, update BA Book Everything plugin to version 1.3.20 and check all things. If you still have an issue, please, send us a support request via our contact form with site access credentials so that we can check and fix the issue.

    Thank you,
    ā€”
    Best Regards,
    Booking Algorithms team

    Plugin Author bookingalgorithms

    (@bookingalgorithms)

    Hello,

    All found issues “incorrect datetime value when using MySQL 8” are fixed in BA Book Everything v. 1.3.22

    Thank you,
    ā€”
    Best Regards,
    Booking Algorithms team

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.