We used the percona server 5.7, and tried to get the sql text with the field trx_query by the following sql ,however I often found this field is empty, what's the reason and how to avoid get the empty sql text?
SELECT b.id AS processlist_id, trx_id, user, host, a.trx_state
, unix_timestamp(now()) - unix_timestamp(trx_started) AS trx_time
, db, trx_rows_modified
, SUBSTRING(CASE
WHEN trx_query IS NULL THEN c.SQL_TEXT
WHEN trx_query IN ('commit') THEN c.SQL_TEXT
ELSE trx_query
END, 1, 10240) AS trx_query
FROM information_schema.innodb_trx a
INNER JOIN information_schema.processlist b ON a.trx_mysql_thread_id = b.id
INNER JOIN performance_schema.threads t ON t.PROCESSLIST_ID = a.trx_mysql_thread_id
INNER JOIN performance_schema.events_statements_current c on t.THREAD_ID=c.THREAD_ID
WHERE trx_started > '0000-00-00 00:00:00'
AND unix_timestamp(now()) - unix_timestamp(trx_started) >= 10
AND trx_tables_locked > 0
ORDER BY trx_time DESC
LIMIT 0, 30;