We are using filebeat with active mysql module to send to ES slow query multiline.
On kibana mysql slowlog discover view, the field mysql.slowlog.query is EMPTY in the Data Table (because of ^M or \n in the sql query). But in the JSON document, it detects the mysql>slowlog>query as below
"mysql": {
"thread_id": "38049881",
"slowlog": {
"schema": "negomarkmmbobonw",
"lock_time": {
"sec": 0.00015
},
"rows_affected": 0,
"rows_sent": 100,
"rows_examined": 418758,
"query": "SELECT SQL_CALC_FOUND_ROWS\n a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`\n , \n\t\ta.id_currency,\n\t\ta.id_order AS id_pdf, \n\t\tCONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,\n\t\tCONCAT(a.`date_add`) AS `date_commande`,\n\t\tosl.`name` AS `osname`,\n\t\tos.`color`,\n\t\tIF((SELECT so.id_order FROM `aw_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, \n\t\tcountry_lang.name as cname,\n\t\tIF(a.valid, 1, 0) badge_success,\n concat(LEFT(address.firstname, 1), '. ', address.lastname) AS customer_delivery,\n coalesce((select lo.marketplace_sku from aw_lengow_orders lo where lo.id_order=a.id_order), '') as marketplace ,\n coalesce((select lo2.marketplace_name from aw_lengow_orders lo2 where lo2.id_order=a.id_order), '') as `marketplace_nameA` ,\n coalesce((select GROUP_CONCAT(DISTINCT s.name SEPARATOR ' - ') from aw_order_detail od JOIN aw_product p ON od.product_id = p.id_product JOIN aw_supplier s ON p.id_supplier = s.id_supplier where a.id_order=od.id_order), '') as supplier,\n coalesce((select CONCAT(\"(\", type_date_expected_shipment, \") \", DATE_FORMAT(date_expected_shipment, '%d/%m/%Y')) from aw_supplieram_order_comment soc WHERE a.id_order=soc.id_order AND soc.date_expected_shipment IS NOT NULL and soc.date_expected_shipment!='0000-00-00 00:00:00' ORDER BY soc.datec DESC LIMIT 1), '') as date_expected_shipment, \n coalesce((select c.external_module_name from aw_carrier c join aw_order_carrier oc on oc.id_carrier = c.id_carrier where oc.id_order=a.id_order), 'n.c.') as carrier_module, shop.name as shop_name \n FROM `aw_orders` a \n \n \n\t\tLEFT JOIN `aw_customer` c ON (c.`id_customer` = a.`id_customer`)\n\t\tLEFT JOIN `aw_address` address ON address.id_address = a.id_address_delivery\n\t\tLEFT JOIN `aw_country` country ON address.id_country = country.id_country\n\t\tLEFT JOIN `aw_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1)\n\t\tLEFT JOIN `aw_order_state` os ON (os.`id_order_state` = a.`current_state`)\n\t\tLEFT JOIN `aw_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1) \n LEFT JOIN aw_shop shop\n ON a.id_shop = shop.id_shop WHERE 1 AND a.id_shop IN (1) \n \n ORDER BY `customer` desc LIMIT 0, 50;"",
"query_cache_hit": false,
"current_user": "XXXXXX"
}
in the mysql query log, here is what i see for the same query :
SELECT SQL_CALC_FOUND_ROWS^M
a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`^M
, ^M
a.id_currency,^M
a.id_order AS id_pdf, ^M
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,^M
CONCAT(a.`date_add`) AS `date_commande`,^M
osl.`name` AS `osname`,^M
os.`color`,^M
IF((SELECT so.id_order FROM `aw_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new, ^M
country_lang.name as cname,^M
IF(a.valid, 1, 0) badge_success,
concat(LEFT(address.firstname, 1), '. ', address.lastname) AS customer_delivery,
coalesce((select lo.marketplace_sku from aw_lengow_orders lo where lo.id_order=a.id_order), '') as marketplace ,
coalesce((select lo2.marketplace_name from aw_lengow_orders lo2 where lo2.id_order=a.id_order), '') as `marketplace_nameA` ,
coalesce((select GROUP_CONCAT(DISTINCT s.name SEPARATOR ' - ') from aw_order_detail od JOIN aw_product p ON od.product_id = p.id_product JOIN aw_supplier s ON p.id_supplier = s.id_supplier where a.id_order=od.id_order), '') as supplier,
coalesce((select CONCAT("(", type_date_expected_shipment, ") ", DATE_FORMAT(date_expected_shipment, '%d/%m/%Y')) from aw_supplieram_order_comment soc WHERE a.id_order=soc.id_order AND soc.date_expected_shipment IS NOT NULL and soc.date_expected_shipment!='0000-00-00 00:00:00' ORDER BY soc.datec DESC LIMIT 1), '') as date_expected_shipment,
coalesce((select c.external_module_name from aw_carrier c join aw_order_carrier oc on oc.id_carrier = c.id_carrier where oc.id_order=a.id_order), 'n.c.') as carrier_module, shop.name as shop_name ^M
FROM `aw_orders` a ^M
^M
^M
LEFT JOIN `aw_customer` c ON (c.`id_customer` = a.`id_customer`)^M
LEFT JOIN `aw_address` address ON address.id_address = a.id_address_delivery^M
LEFT JOIN `aw_country` country ON address.id_country = country.id_country^M
LEFT JOIN `aw_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1)^M
LEFT JOIN `aw_order_state` os ON (os.`id_order_state` = a.`current_state`)^M
LEFT JOIN `aw_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1) ^M
LEFT JOIN aw_shop shop^M
ON a.id_shop = shop.id_shop WHERE 1 AND a.id_shop IN (1) ^M
^M
ORDER BY `customer` desc LIMIT 0, 50;
I think the troubleshooting is coming from the encoding of the SQL query that comes from php source files sent by a windows user. In the php file i see the ^M in some sql query lines.
Filebeat, seems to do the jobs correctly and send to ES the multiline, but there is a conflict with the ^M inside kibana.
Anyway to remove those ^M at the origin side on filebeat module ?
Regards
tony