Upgrade failed with error ORA-12899: value too large for column
search cancel

Upgrade failed with error ORA-12899: value too large for column

book

Article ID: 262372

calendar_today

Updated On:

Products

CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

We tried to upgrade CARA(CA Release Automation) to 6.8 and faced an issue where we see an Upgrade script failure with below error

The upgrade failed due to Column limit constraint on the Table TABLE_NAME

Error: Caused by: java.sql.BatchUpdateException: ORA-12899: value too large for column "COLUMN_NAME"."TABLE_NAME"."DEFAULTVALUE" (actual: 1064, maximum: 1023

Environment

Release : 6.8

Component: CA RELEASE AUTOMATION

Cause

The problem happened in the migration script which is supposed to re-encrypt data in the TABLE_NAME table. Earlier CARA was using the DES algorithm to encrypt secured data but addressing vulnerability around weak algorithm the product is updated to use the AES algorithm for which the group of migration scripts were created, which will re-encrypt the data with new algorithm.

As there is always restriction from the UI not allowing to create such big strings, we are not sure how such big string exist in those tables.

Resolution

We recommend our customers to run below pre-upgrade query/DB-scripts to ascertain pre-hand any data may result in long strings post re-encryption.

  Oracle MSSQL
1 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'FTP' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_ftp ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'FTP' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_ftp ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
2 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'GIT' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_git ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'GIT' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_git ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
3 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'HTTP' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_http ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'HTTP' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_http ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
4 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'REMOTE' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_remote ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'REMOTE' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_remote ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
5 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'REPOSITORY' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_repository ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'REPOSITORY' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_repository ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
6 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'SSH' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_ssh ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'SSH' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_ssh ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
7 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'SVN' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_svn ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'SVN' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_svn ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
8 select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'TFS' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       length(ra.password) as artifact_getter_pass_len
  from rc_artifact_tfs ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where length(ra.password) > 195;
select ap.id as application_id,
       ap.app_name as application_name,
       at.id as artifacttype_id,
       at.name as artifacttype_name,
       ad.id as artifactdefinition_id,
       ad.name as artifactdefinition_name,
       rart.artifact_version,
       'TFS' as getter_type,
       ra.id as artifact_getter_id,
       ra.password as artifact_getter_password,
       len(ra.password) as artifact_getter_pass_len
  from rc_artifact_tfs ra
  left outer join rc_artifacts rart
    on rart.artifact_getter = ra.id
  left outer join artifact_definition ad
    on ad.id = rart.artifactdefinition
  left outer join artifact_type at
    on at.id = ad.artifacttype_id
  left outer join applications ap
    on ap.id = ad.applicationid
 where len(ra.password) > 195;
9 select ap.id as application_id,
        ap.app_name as application_name,
        td.id as tokendefinition_id,
        td.name as tokendefinition_name,
        td.defaultvalue,
        length(td.defaultvalue) as defaultvalue_length
   from rc_token_definition td
   left outer join applications ap
     on ap.id = td.application
  where td.type = 3
    and td.defaultvalue is not null
    and length(td.defaultvalue) > 963;
 
select ap.id as application_id,
        ap.app_name as application_name,
        td.id as tokendefinition_id,
        td.name as tokendefinition_name,
        td.defaultvalue,
        len(td.defaultvalue) as defaultvalue_length
   from rc_token_definition td
   left outer join applications ap
     on ap.id = td.application
  where td.type = 3
    and td.defaultvalue is not null
    and len(td.defaultvalue) > 963;
10 select ap.id as application_id,
        ap.app_name as application_name,
        td.id as tokendefinition_id,
        td.name as tokendefinition_name,
        tv.id as tokenvalue_id,
        tv.value as token_value,
        length(tv.value) as value_length
   from rc_token_value tv
  inner join rc_token_definition td
     on td.id = tv.token
    and td.type = 3
   left outer join applications ap
     on ap.id = td.application
  where tv.value is not null
    and length(tv.value) > 963;
select ap.id as application_id,
        ap.app_name as application_name,
        td.id as tokendefinition_id,
        td.name as tokendefinition_name,
        tv.id as tokenvalue_id,
        tv.value as token_value,
        len(tv.value) as value_length
   from rc_token_value tv
  inner join rc_token_definition td
     on td.id = tv.token
    and td.type = 3
   left outer join applications ap
     on ap.id = td.application
  where tv.value is not null
    and len(tv.value) > 963;
11 select s.id,
       s.name,
       s.value,
       length(s.value) as value_length
  from str_params s
where s.is_password = 1
  and length(s.value) > 195;
select s.id,
       s.name,
       s.value,
       len(s.value) as value_length
  from str_params s
where s.is_password = 1
  and len(s.value) > 195;
12 select env.id as environment_id,
       env.name as environment_name,
       sned.id as servicenow_env_def_id,
       sned.password as servicenow_env_def_password,
       length(sned.password) as pass_length
  from rc_servicenow_a_env_def sned
  left outer join environments env
    on env.id = sned.environment
 where sned.password is not null
   and length(sned.password) > 195;
select env.id as environment_id,
       env.name as environment_name,
       sned.id as servicenow_env_def_id,
       sned.password as servicenow_env_def_password,
       len(sned.password) as pass_length
  from rc_servicenow_a_env_def sned
  left outer join environments env
    on env.id = sned.environment
 where sned.password is not null
   and len(sned.password) > 195;
13 select dft.id as deployment_file_token_id,
       dft.name as deployment_file_token_name,
       dft.value as token_value,
       length(dft.value) as token_value_length,
       df.filepath,
       r.name as release_name,
       app.id as application_id,
       app.app_name as application_name
  from rc_deployment_file_token dft
  left outer join rc_deployment_file df
    on df.id = dft.deploymentfile
  left outer join rc_releases r
    on r.id = df.deployment
  left outer join applications app
    on app.id = r.application
 where dft.type = 3
   and dft.value is not null
   and length(dft.value) > 963;
select dft.id as deployment_file_token_id,
       dft.name as deployment_file_token_name,
       dft.value as token_value,
       len(dft.value) as token_value_length,
       df.filepath,
       r.name as release_name,
       app.id as application_id,
       app.app_name as application_name
  from rc_deployment_file_token dft
  left outer join rc_deployment_file df
    on df.id = dft.deploymentfile
  left outer join rc_releases r
    on r.id = df.deployment
  left outer join applications app
    on app.id = r.application
 where dft.type = 3
   and dft.value is not null
   and len(dft.value) > 963;
14 select ds.id as directoryserver_id,
       ds.name as directoryserver_name,
       ds.admin_password,
       length(ds.admin_password) as admin_password_length
  from directory_server ds
 where length(ds.admin_password) > 195;
select ds.id as directoryserver_id,
       ds.name as directoryserver_name,
       ds.admin_password,
       len(ds.admin_password) as admin_password_length
  from directory_server ds
 where len(ds.admin_password) > 195;
15 select cbc.id,
       cbc.chef_environment,      
       cbc.chef_runlist,
       cbc.chef_runlist_detail,
       cbc.configuration_id,
       cbc.chef_role,
       length(cbc.chef_role) as chef_role_length
  from cmm_baseline_chef cbc
 where length(cbc.chef_role) > 195;
select cbc.id,
       cbc.chef_environment,      
       cbc.chef_runlist,
       cbc.chef_runlist_detail,
       cbc.configuration_id,
       cbc.chef_role,
       len(cbc.chef_role) as chef_role_length
  from cmm_baseline_chef cbc
 where len(cbc.chef_role) > 195;
16 select ccc.id as configuration_chef_id,
       ccc.hash_key,
       ccc.chef_organization,
       ccc.chef_repository_path,
       ccc.chef_server,
       ccc.chef_workstation,
       ccc.connectable,
       ccc.knife_command_path,
       ccc.hash_key,
       length(ccc.hash_key) as hash_key_length
  from cmm_configuration_chef ccc
 where length(ccc.hash_key) > 195;
select ccc.id as configuration_chef_id,
       ccc.hash_key,
       ccc.chef_organization,
       ccc.chef_repository_path,
       ccc.chef_server,
       ccc.chef_workstation,
       ccc.connectable,
       ccc.knife_command_path,
       ccc.hash_key,
       len(ccc.hash_key) as hash_key_length
  from cmm_configuration_chef ccc
 where len(ccc.hash_key) > 195;

 

Additional Information

Note:

  • AES-encrypted representation of the string is longer than the DES-encrypted representation of the same string by 56 characters maximum.
  • We used the 60 characters threshold to identify the candidates for validation.