Buildup of security bucket files in on the Spectrum Report Manager (SRM) server

Products

CA Spectrum

Issue/Introduction

Spectrum Report Manager (SRM) does not show updated content recently and we see a buildup of security bucket files in the SPECROOT/mysql/data/reporting directory.

The following mySQL data truncation exceptions seen in the $SPECROOT/tomcat/logs/stdout.log (Windows) or catalina.out (Linux) file:

Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO security_string( security_string ) VALUES( ? ) ON DUPLICATE KEY UPDATE security_string = security_string]; Data truncation: Data too long for column 'security_string' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'security_string' at row 1

Environment

Release: Any
Component:

Cause

The length of the security_string column in the security_string table, should not exceed 255 characters otherwise it will stop the processing of security buckets.

The character limit depends on the character set you use, the type of MySQL table and the indexing used. Those limits are:

1) If you use latin1 then the largest column you can index is varchar(767).

2) If you use utf8 then the limit is varchar(255).

3) MyISAM is a little different. It has a 1000 byte index length limit.

Be warned! Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables. This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or regenerate them by reinitializing the data directory.

4) innodb_large_prefix, which was introduced in MySQL 5.5.14 and allows you to include columns up to 3072 bytes long in InnoDB indexes.

The security_string column is used in as many as 20 different tables in the reporting database. However the security_string column of the security_string table has a unique key constraint. As referenced above, an index key for a single-column index, can be up to 767 bytes on innodb tables. MySQL assumes 3 bytes per utf8 character, so 255 characters is the maximum index size you can specify per column, because 256x3=768, which breaks the 767 byte limit.

We can compare 2 of the tables as follows:

mysql> describe security_string security_string;

+-----------------+--------------+------+-----+---------+-------+

+-----------------+--------------+------+-----+---------+-------+

| security_string | varchar(676) | YES | UNI | NULL | |

+-----------------+--------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> describe devicemodel security_string;

+-----------------+--------------+------+-----+---------+-------+

+-----------------+--------------+------+-----+---------+-------+

| security_string | varchar(255) | NO | | NULL | |

+-----------------+--------------+------+-----+---------+-------+

1 row in set (0.00 sec)

Resolution

1. Before we make any changes to the database table, we will first take a backup as below:

create table temp_security_string as select * from security_string;

2. We have various options to choose from depending on how large our security_strings are. Check in MySQL for the largest security string by running:

3. select max(length(security_string)) from security_string;

If the security_string are less than 767 bytes, we can use Latin1 tables. The Spectrum Report Manager database uses the UTF8 to support internationalization, as it allows for more space per character (3 bytes), so is suitable for larger Asian characters, as opposed to Latin1 which uses 1 byte per character. Customers who are not using Asian languages in the database, can change the character for this column to latin1 and increase the column size to a max of 767.

4. mysql> alter table security_string charset='latin1';

5. mysql> alter table security_string model modify column security_string varchar(767);

6. The above command will change the charset as well as increase the column width. Once done restart the tomcat. This should allow the security buckets to get processed.

For customers using Asian characters, we will need to stay with UTF8 to support Asian characters. If the security string exceed 256 characters on UTF8 table or 767 for Latin1,we will need to remove the unique key and increase the column size temporarily, so that the buckets get processed.

a. alter table security_string drop index unq_securiting_string ;

b. alter table security_string modify security_string varchar(2000);

c. Once done restart the tomcat. This should allow the security buckets to get processed. Once all the buckets are processed for security handler, delete the security string from the OC which is longer than 255.

d. delete from security_string where length(security_string) > max_size_of_string;

eg: delete from security_string where length(security_string) > 255;

e. We then revert back the security string table to original.

f. mysql> alter table security_string modify security_string varchar(255);

g. mysql> alter table security_string charset='utf8';

h. mysql> alter table security_string add unique key unq_securiting_string(security_string) ;

This obviously means that customers with Asian characters in the sec string will need to limit sec string size to less than 256 to be able to use UTF8.

Additional Information

InnoDB Restrictions and Limitations

The security string is a file on many tables so this error may apply to another table.

e.g.

(SRM/SecurityHandler/bucketReader) (com.aprisma.spectrum.app.repmgr.dc.event.handler.SecurityHandler) - (ERROR) - Error occurred while processing Security event bucket
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [INSERT INTO security_string_accessibility_by_landscape

we would need the same steps on whatever table is referenced in the tomcat error. The size required would depend on the size of the security_string so increasing the table may be an option if not too large. This will increase data base size.

Take a backup of the table:
create table temp_security_string_accessibility_by_landscape as select * from security_string_accessibility_by_landscape;

verify backup
describe temp_security_string_accessibility_by_landscape;

increase the size of the table
alter table security_string_accessibility_by_landscape modify security_string varchar(450);

verify the size:
describe security_string_accessibility_by_landscape;

restart tomcat and this error will be resolved.