We think this is related to the support ticket because of the following errors found in the log:
Nov 16 03:00:44 916567-MXS-D01 maxscale[64511]: (2173989) [readwritesplit] Slave server 'db02': response (0xff) differs from master's response (0x00) to COM_QUERY: `SELECT DISTINCT ti.hdr_id,IF(ti.is_void AND void_bus_date<='2018-11-16',void_hdr_new_status_id,hdr_new_status_id) AS hdr_new_status_id, ti.hdr_new_sub_status_id As hdr_new_sub_status_id INTO OUTFILE '/tmp/flppssmqjf_8197540_hdr.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM trans_item AS ti INNER JOIN _ti_ids_flppssmqjf_8197540 AS tmp ON ti.id=tmp.ti_id INNER JOIN trans_hdr AS th ON ti.hdr_id=th.id HAVING hdr_new_status_id IN (1) `. Closing slave connection due to inconsistent session state.
Nov 16 03:00:44 916567-MXS-D01 maxscale[64511]: (2173989) [readwritesplit] Slave server 'db03': response (0xff) differs from master's response (0x00) to COM_QUERY: `SELECT DISTINCT ti.hdr_id,IF(ti.is_void AND void_bus_date<='2018-11-16',void_hdr_new_status_id,hdr_new_status_id) AS hdr_new_status_id, ti.hdr_new_sub_status_id As hdr_new_sub_status_id INTO OUTFILE '/tmp/flppssmqjf_8197540_hdr.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM trans_item AS ti INNER JOIN _ti_ids_flppssmqjf_8197540 AS tmp ON ti.id=tmp.ti_id INNER JOIN trans_hdr AS th ON ti.hdr_id=th.id HAVING hdr_new_status_id IN (1) `. Closing slave connection due to inconsistent session state.
Nov 16 03:00:44 916567-MXS-D01 maxscale[64511]: (2173989) [readwritesplit] Slave server 'db05': response (0xff) differs from master's response (0x00) to COM_QUERY: `SELECT DISTINCT ti.hdr_id,IF(ti.is_void AND void_bus_date<='2018-11-16',void_hdr_new_status_id,hdr_new_status_id) AS hdr_new_status_id, ti.hdr_new_sub_status_id As hdr_new_sub_status_id INTO OUTFILE '/tmp/flppssmqjf_8197540_hdr.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM trans_item AS ti INNER JOIN _ti_ids_flppssmqjf_8197540 AS tmp ON ti.id=tmp.ti_id INNER JOIN trans_hdr AS th ON ti.hdr_id=th.id HAVING hdr_new_status_id IN (1) `. Closing slave connection due to inconsistent session state.
Nov 16 03:00:44 916567-MXS-D01 maxscale[64511]: (2173989) [readwritesplit] Slave server 'db06': response (0xff) differs from master's response (0x00) to COM_QUERY: `SELECT DISTINCT ti.hdr_id,IF(ti.is_void AND void_bus_date<='2018-11-16',void_hdr_new_status_id,hdr_new_status_id) AS hdr_new_status_id, ti.hdr_new_sub_status_id As hdr_new_sub_status_id INTO OUTFILE '/tmp/flppssmqjf_8197540_hdr.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM trans_item AS ti INNER JOIN _ti_ids_flppssmqjf_8197540 AS tmp ON ti.id=tmp.ti_id INNER JOIN trans_hdr AS th ON ti.hdr_id=th.id HAVING hdr_new_status_id IN (1) `. Closing slave connection due to inconsistent session state.
Following this error, we show a much higher percentage of queries going to the master host that would normally be handled by slave servers. We think this is because the connections to the slaves are listed as blocked (temporarily?). Client is concerned because of the very uneven load and because the have seen the master go offline because of load.
Example query going to master that should be routed to slave:
Nov 16 12:39:37 916567-MXS-D01 maxscale[64511]: (2216337) [readwritesplit] > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 2245, type: QUERY_TYPE_READ, stmt: SELECT tv.id,tv.company_id,tv.store_id,tv.customer_id,tv.prop_type,tv.vehicle_type,tv.coowner_id,tv.created_dt,tv.created_user_id,tv.updated_dt,#015#012 tv.updated_user_id,tv.owner_name,tv.v_bodystyle_id,tv.v_year,tv.v_make,tv.v_model,tv.v_color,tv.v_vin,IFNULL(tv.v_titlenum,' ') AS v_titlenum,#015#012 IFNULL(tv.v_tagnum,'') AS v_tagnum,tv.v_tag_state_id,tv.v_tag_county,tv.v_tag_expire_date,tv.ins_type,tv.ins_company,tv.ins_policy_num,#015#012 tv.serial_num, tv.bb_series, tv.bb_style, tv.est_value_src, tv.est_value, #015#012 tv.cvr_control_num,tv.cvr_request_num,tv.cvr_stateapp_num,tv.cvr_last_update,tv.max_loan_amt,tv.v_condition AS condition_text,#015#012 CAST(v_condition As UNSIGNED) As v_condition, tv.mileage, tv.description, tv.fee_percent, tv.fee_type, tv.have_key, tv.lien_placed, tv.has_writing, tv.sold_ttl_id, #015#012 IF(tv.prop_type=2,tv.v_vin,tv.serial_num) AS vin_serial,CASE prop_type WHEN 1 THEN 'PP' WHEN 2 THEN 'MP' END AS prop_type_txt,#015#012
Nov 16 12:39:37 916567-MXS-D01 maxscale[64511]: (2216337) [readwritesplit] Route query to master #011[172.24.100.81]:3306 <
We have verified these queries are not being wrapped in transactions.
We think this bug might be the cause of this ticket:
https://support.mariadb.com/view.php?id=23932