Unable to increase the column length of the security_string column in the Spectrum Report Manager database

Document ID : KB000047212
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

Spectrum Report Manager does not show updated content recently and we see a buildup of security bucket files in the SPECROOT/mysql/data/reporting directory. We have also found mySQL data truncation exceptions in the tomcat log as follows:

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

I followed the instructions in tec1988276, to increase the table security_sting from 255 to 512, which works on other tables:

mysql> alter table devicemodel modify security_string varchar(512);

Query OK, 1074 rows affected (0.20 sec)

Records: 1074 Duplicates: 0 Warnings: 0

 

but not on the security_string table: 

 

mysql> alter table security_string modify security_string varchar(512);

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

 

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.  Thos 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;

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

| Field           | Type         | Null | Key | Default | Extra |

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

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

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

1 row in set (0.00 sec)

 

mysql> describe  devicemodel  security_string;

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

| Field           | Type         | Null | Key | Default | Extra |

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

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

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

1 row in set (0.00 sec)

 

Workaround:

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:

a.       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.

 

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

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

 

3.    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. 

 

4.    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);

5.    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. 

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

 

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

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

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

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

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

 

 

 

 

Additional Information:

http://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1988276.html

http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html