Cody Brown Cody Brown - 1 year ago 91
SQL Question

How to investigate MySQL errors

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

`DIGEST_TEXT` AS `query`,
`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`
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))

Is there some way to drill down into performance_schema to find the exact error message that is associated with the

I was also curious what it means if the
column or
column shows up as
. 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 Source

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 

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download