search cancel

SRM Reporting Stale Bucket tables

book

Article ID: 257569

calendar_today

Updated On:

Products

CA Spectrum

Issue/Introduction

When reviewing the $SPECROOT/mysql/data/reporting directory there are thousands of bucket tables, like NCM.

Environment

Release : 21.2

Resolution

1. Stop tomcat.
2. Run the below from mysql/bin. These will create two file with drop tables and update activity tables.
$ mysql -uroot -proot reporting -e 'select concat ("drop table if exists ", bucket_table_name, ";") from bucketactivitylog where bucket_table_name like "modelcreatedes%" and destroy_time is null and creation_time < "2022-08-15 23:00:00";' > dropBuckets.sql$ mysql -uroot -proot reporting -e 'select concat ("update bucketactivitylog set destroy_time=now() where bucket_table_name like ", QUOTE(bucket_table_name),";") from bucketactivitylog where bucket_table_name like "modelcreatedes%" and  destroy_time is null and creation_time < "2022-08-15 23:00:00";' > updateBucketactivityLog.sql
3. Open the two sql files and delete the first line, which is a column name.
4. And then run those sql files to clean the stale buckets older than specific date.
./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting; < <File_name1>.sql
./mysql --defaults-file=../my-spectrum.cnf -uroot -pMySqlR00t reporting; < <File_name2>.sql

Queries can be adjusted above for different types of buckets.

Example - NCM would be ncmconfigbucket%