Smarts NCM: database-utility.pl tool fails with ERROR: value too long for type character varying(500)

Products

• VMware Smart Assurance

Issue/Introduction

Symptoms:


Attempts to run the $VOYENCE_HOME/tools/db-utility/database-utility.pl tool fail with the following or substantially similar error message (actual numeric or time interval values may vary):
 

 DBD::Pg::st execute failed: ERROR:  value too long for type character varying(500) [for Statement "INSERT INTO archive.cm_cel_audit_record (id,action_type,arrival_time,description,device_id,device_idx,device_name,event_owner,event_time,event_type,job_id,job_name,network_id,network_name,payload,severity_type,site_id,site_name,source_type,task_id,task_name,view_id,view_name,workspace_id,workspace_name,service_name,version) SELECT id,action_type,arrival_time,description,device_id,device_idx,device_name,event_owner,event_time,event_type,job_id,job_name,network_id,network_name,payload,severity_type,site_id,site_name,source_type,task_id,task_name,view_id,view_name,workspace_id,workspace_name,service_name,version FROM voyence.cm_cel_audit_record WHERE event_time < (now() - '60 days'::interval)"] at database-utility.pl line 1217.

Environment

VMware Smart Assurance - NCM

Cause

This error is caused when the maximum column width for one or more columns in the voyence.cm_cel_audit_record table does not match the column width for that same column in the corresponding acrhive.cm_cel_audit_record table.

Resolution

To resolve the issue, do as follows:

1.  Log into a Linux shell on the NCM PostgreSQL Control Database (controldb) host as 'root'.
2.  Run the following command to launch a controldb shell (the current controldb password will be required):
 

     su - pgdba -s /bin/bash -c 'psql voyencedb voyence'

3.  Run the following query to find out which column widths are mismatched between the voyence and archive versions of the table (where {table name} is the name of the table cited in any error messages):
 

     PREPARE voyence_vs_archive_type_compare( text ) AS
       SELECT
         a.table_name,
         a.column_name,
         v.voyence_type,
         a.archive_type
       FROM (
         SELECT
           table_name,
           column_name,
           'varchar(' || character_maximum_length || ')' AS archive_type
         FROM information_schema.columns
         WHERE 
           table_schema = 'archive' AND
           table_name = $1
       ) AS a
       JOIN (
         SELECT
           table_name,
           column_name,
           'varchar(' || character_maximum_length || ')' AS voyence_type
         FROM information_schema.columns
         WHERE 
           table_schema = 'voyence' AND
           table_name = $1
       ) AS v
         ON a.column_name = v.column_name
       WHERE voyence_type != archive_type;

     EXECUTE voyence_vs_archive_type_compare('{table name}');

  
    Example: (Note: column_name value below may vary according which columns are mismatched between the voyence and archive schema versions of the table):
 

     voyencedb=# EXECUTE voyence_vs_archive_type_compare('cm_cel_audit_record');
          table_name      |  column_name   | voyence_type  | archive_type
     ---------------------+----------------+---------------+--------------
      cm_cel_audit_record | description    | varchar(1024) | varchar(500)
     (1 row)

    Note: The prepared statement can be executed repeatedly using different table names without re-pasting the query.


4.  Run the following SQL command to verify the max space required to store a value in the column identified in Step 3 above in the voyence schema version of the table column (Note: Results may differ by file system, however, we generally expect the result to be the sum of the character width of the column and additional overhead space needed that equates to approximately the same size as it would take to store 4 additional characters - in the example above it would be 1028; substitute the column_name value identified in Step 3 above in place of the highlighted {column name} reference in all steps below):
 

     SELECT atttypmod 
     FROM pg_attribute 
     WHERE 
       attrelid = '{schema_name.table_name}'::regclass AND 
       attname = '{column name}';

    Example (Continuation of previous example using the description column in the voyence version of the cm_cel_audit_record table, assuming Step 2 above style login):
 

     voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'voyence.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
     atttypmod
     -----------
           1028
     (1 row)

5.  Run the following command to increase the maximum width in characters for the mismatched column in the archive version of the table where:

    {new size} is the maximum size of the data allowed for the column in the voyence schema version of the table as determined in Step 4 above.
    {schema_name.table_name} is the name of the archive schema qualified name of the table cited in the error message.
    {column name} is the name of the column in question.
  

     UPDATE pg_attribute 
     SET atttypmod = {new size}
     WHERE attrelid = '{schema_name.table_name}'::regclass
     AND attname = '{column name}';

    Example (Continuation of previous step examples using the description column in the archive version of the cm_cel_audit_record table):
 

     voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
      atttypmod
     -----------
            500
     (1 row)
     voyencedb=# UPDATE pg_attribute
     voyencedb-# SET atttypmod = 1028
     voyencedb-# WHERE attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# AND attname = 'description';
     UPDATE 1
     voyencedb=# SELECT atttypmod
     voyencedb-# FROM pg_attribute
     voyencedb-# WHERE
     voyencedb-# attrelid = 'archive.cm_cel_audit_record'::regclass AND
     voyencedb-# attname = 'description';
      atttypmod
    -----------
          1028
    (1 row)

Additional Information

The above process cannot be used to reduce the size of any table column where any of the records contain data. This is not a problem because the PostgreSQL engine does not consume storage space with empty padding for column values that do not reach the maximum data length set for a given column. Instead, for larger data lengths, having a maximum value set is primary useful to the PostgreSQL engine in terms of determining the most efficient way of storing inserted data in a way that maximizes efficient read/write operations against the data.

Before expanding the data length of any column in any table in NCM, it is advisable to understand why the original limit is being exceeded. That is best accomplished by engaging Dell EMC technical support. It is appropriate to open a service request before undertaking any such action unless the issue can be tied exactly to the error message cited above, in which case these steps are required to resolve the issue.