PURGE_LOG()

Description

PURGE_LOG() deletes logs recorded in MatrixOne database system tables. Returning 0 means the deletion is successful; if the deletion fails, an error message will be returned.

Note

Only the root user (cluster administrator with MOADMIN privilege) can execute the PURGE_LOG() function for log deletion.

Syntax

  1. > PURGE_LOG('sys_table_name', 'date')

Arguments

ArgumentsDescription
‘sys_table_name’Currently, there are only three system tables that can be deleted: metric, rawlog, and statement_info.
Note: ‘sys_table_name’ must be enclosed in quotes.
‘date’Select a date and delete logs generated before that date.
Note: ‘date’ must be enclosed in single quotes.

Note

MatrixOne has only three system log tables: metric, rawlog, and statement_info. For more information about these three tables, please refer to MatrixOne System Database and Tables.

Examples

  • Example 1:
  1. -- Delete the statement_info type logs before 2023-06-30
  2. mysql> select purge_log('statement_info', '2023-06-30') a;
  3. +------+
  4. | a    |
  5. +------+
  6. | 0    |
  7. +------+
  8. 1 row in set (0.01 sec)
  • Example 2:
  1. -- Query the time and quantity of metric log collection
  2. mysql> select date(collecttime), count(1) from system_metrics.metric group by date(collecttime);
  3. +-------------------+----------+
  4. | date(collecttime) | count(1) |
  5. +-------------------+----------+
  6. | 2023-07-07        |    20067 |
  7. | 2023-07-06        |    30246 |
  8. | 2023-07-05        |    27759 |
  9. +-------------------+----------+
  10. 3 rows in set (0.00 sec)
  12. -- Delete rawlog, statement_info, and metric logs before 2023-07-06
  13. mysql> select purge_log('rawlog, statement_info, metric', '2023-07-06');
  14. +-------------------------------------------------------+
  15. | purge_log(rawlog, statement_info, metric, 2023-07-06) |
  16. +-------------------------------------------------------+
  17. |                                                     0 |
  18. +-------------------------------------------------------+
  19. 1 row in set (0.33 sec)
  21. -- Query the number of metric logs for the three days of 2023-07-05, 2023-07-06 and 2023-07-07 again
  22. mysql> select date(collecttime), count(1) from system_metrics.metric group by date(collecttime);
  23. +-------------------+----------+
  24. | date(collecttime) | count(1) |
  25. +-------------------+----------+
  26. | 2023-07-06        |    30246 |
  27. | 2023-07-07        |    20121 |
  28. +-------------------+----------+
  29. 2 rows in set (0.01 sec)