Cody Brown Cody Brown - 5 months ago 7
SQL Question

How to investigate MySQL errors

Below is a query I found to display my errors and warnings in MySQL:

SELECT
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))
ORDER BY
`SUM_ERRORS` DESC,
`SUM_WARNINGS` DESC;


Is there some way to drill down into performance_schema to find the exact error message that is associated with the
errors
or
warnings
above?

I was also curious what it means if the
db
column or
query
column shows up as
NULL
. Below are a few specific examples of what I'm talking about

+---------------------+--------+------------+--------+----------+--------+
| query | db | exec_count | errors | warnings | digest |
+---------------------+--------+------------+--------+----------+--------+
| SHOW MASTER LOGS | NULL | 192 | 192 | 0 | ... |
+---------------------+--------+------------+--------+----------+--------+
| NULL | NULL | 553477 | 64 | 18783 | NULL |
+---------------------+--------+------------+--------+----------+--------+
| SELECT COUNT ( * ) | NULL | 48 | 47 | 0 | ... |
|FROM `mysql` . `user`| | | | | |
+---------------------+--------+------------+--------+----------+--------+


I am also open to using a different query that someone may have to display these errors/warnings

Answer

The message will be in performance_schema.events_statements_history.message_text column. You do need to make sure that performance_schema_events_statements_history_size config variable is set to a positive and sufficiently large value, and that the history collection is enabled. To enable the history collection, run:

update performance_schema.setup_consumers set enabled='YES' 
where name='events_statements_history';

To check if it is enabled:

select * from performance_schema.setup_consumers where 
name='events_statements_history';

NULL value of db means the there was no active database selected. Note that the active database does not have to be the same as the database of the table involved in a query. The active database is used as default when it is not explicitly specified in a query.

This will only give you error messages, not warning messages. From a brief look at the code it appears that the warning text does not get logged anywhere - which is understandable given that one statement could produce millions of them. So you have a couple of options:

  • Extract the statement from events_statements_history.sql_text, re-execute it, and then run SHOW WARNINGS
  • Extract the statement, track it down in your application code, then instrument your code to log the output of SHOW WARNINGS in hopes of catching it live if manual execution fails to reproduce the warnings