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
Release : 6.8
Component: CA RELEASE AUTOMATION
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.
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; |
Note: