MySQL 查看最近执行失败的SQL语句

2022-07-27 60点热度 0人点赞 0条评论

需求说明

应用程序报错,但是没有记录相关的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_WAITLOCK_TIME可以用sys.format_time()函数进行转换,变成微秒等我们能直观了解的时间大小
  • 内部线程号:THREAD_ID = sys.ps_thread_id(process_id)

admin

这个人很懒,什么都没留下

文章评论

您需要 登录 之后才可以评论