需求说明
应用程序报错，但是没有记录相关的SQL日期，所以想要查MySQL服务器最近的错误SQL来进一步排查。
实现
在performance_schema中语句时间记录表中针对每一条语句的执行状态都记录了较为详细的信息，其中就包含了执行错误信息。
一、查看语句记录功能是否开启
记录语句信息的表一般为performance_schema中的四张表，分别为
events_statements_current，默认记录每个线程最近的一条SQL信息events_statements_history，默认记录每个线程最近的十条SQL信息events_statements_history_long，默认记录每个线程最近的10000条SQL信息
mysql> select * from performance_schema.setup_consumers where name like 'events_statements%'; +--------------------------------+---------+ | NAME | ENABLED | +--------------------------------+---------+ | events_statements_current | YES | | events_statements_history | YES | | events_statements_history_long | NO | +--------------------------------+---------+ 3 rows in set (0.00 sec)
events_statements_history_long的值是NO，说明没有启用。
二、开启语句记录功能
生产环境中，每个线程执行的SQL很多，我们启用events_statements_history_long表记录更多的SQL信息(可以通过修改performance_schema.threads针对特定的线程进行记录信息，降低对性能的影响程度)
启用events_statements_history_long表
mysql > update performance_schema.setup_consumers set enabled = 'YES' where name ='events_statements_history_long'; -- 排查好了问题，还可以再关闭 -- update performance_schema.setup_consumers set enabled = 'NO' where name ='events_statements_history_long';
三、模拟执行SQL失败
3.1 情况1:明确知道SQL错误号
会话1，执行明显格式错误的SQL，这里可以看到错误号是1064
mysql> select abc from table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
会话2，可以看到执行的对应内部线程号等具体信息，环境是测试环境，所以刚好查出只有这一条，生产中应该查出来不止一条，会有很多条。
mysql> select * from performance_schema.events_statements_history_long where mysql_errno=1064;
*************************** 1. row ***************************
THREAD_ID: 3233584 <-- 内部线程号
EVENT_ID: 3
END_EVENT_ID: 3
EVENT_NAME: statement/sql/error
SOURCE: init_net_server_extension.cc:93
TIMER_START: 4385771996099357000
TIMER_END: 4385771996165172000
TIMER_WAIT: 703794000
LOCK_TIME: 0
SQL_TEXT: select abc from table
DIGEST:
DIGEST_TEXT:
CURRENT_SCHEMA: performance_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 1064 <-- 错误号
RETURNED_SQLSTATE: 42000
MESSAGE_TEXT: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.01 sec)
3.2 情况2：不知道错误号，只想看最近执行出错的SQL是哪些
mysql> select * from performance_schema.events_statements_history_long where errors != 0;
*************************** 1. row ***************************
THREAD_ID: 173475
EVENT_ID: 38480
END_EVENT_ID: 38497
EVENT_NAME: statement/sql/select
SOURCE:
TIMER_START: 1050983430258683000
TIMER_END: 1050983430962477000
TIMER_WAIT: 703794000
LOCK_TIME: 0
SQL_TEXT: select *
DIGEST: ed17d00b1d52bf7da4ae01c523c15c5a
DIGEST_TEXT: SELECT *
CURRENT_SCHEMA: performance_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 1096
RETURNED_SQLSTATE: HY000
MESSAGE_TEXT: No tables used
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0补充：
- 表中的
TIMER_WAIT与LOCK_TIME可以用sys.format_time()函数进行转换，变成微秒等我们能直观了解的时间大小 - 内部线程号：
THREAD_ID = sys.ps_thread_id(process_id)
文章评论