Audit Archiver still shows greater than 90% even after audit_purge.sh

Products

• CA API Gateway

Issue/Introduction

We have no audit records in our internal database (audit_detail and audit_main are showing a count of 0).

The ibdata file is set to an initial size of 100M with autoextend max size of 15776M.

The ibdata file is currently only 104M in size.

The Audit Archiver is running and it keeps coming up with a message that the Database is at 96%.

I have restarted both the SSG and MYSQLD services but this is still coming up with this error. 

How do I get this to reset?

Environment

Release : 10.x 11.x

Component : API GATEWAY

Cause

Changes in how mysql defragments things to recover space from mysql 5.x to mysql 8

Resolution

Mysql 8 is different from the older version, optimize is not used anymore.


First set FOREIGN KEY 0
Truncate each AUDIT tables - 0 rows
Set FOREIGN KEY 1
Then analyze each Audit table

Commands tested to recover space mysql DB

Review the current size:

mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table                                | size  |
+--------------------------------------+-------+
| audit_main                           | 79.30 |
| audit_admin                          | 26.67 |
.

To truncate the audit tables :

SET FOREIGN_KEY_CHECKS = 0;
truncate table audit_admin;
truncate table audit_detail;
truncate table audit_detail_params;
truncate table audit_main;
truncate table audit_message;
truncate table audit_system;
SET FOREIGN_KEY_CHECKS = 1;


analyze table ssg.audit_admin;
analyze table ssg.audit_detail;
analyze table ssg.audit_detail_params;
analyze table ssg.audit_main;
analyze table ssg.audit_message;
analyze table ssg.audit_system;

Review new size

mysql> SELECT table_name AS "Table", round(((data_length + index_length) / 1024 / 1024), 2) as size FROM information_schema.TABLES WHERE table_schema = "ssg" ORDER BY size DESC;
+--------------------------------------+-------+
| Table                                | size  |
+--------------------------------------+-------+
| audit_admin                          |  4.55 |
| audit_main                           |  0.06 |
.
.