Skip to main content

Nexus Repository 3 database migration fails due to PSQLException value too long for type character varying(250)

Updated

Problem 

When running the Nexus Repository nxrm-db-migrator tool to migrate from one database to another, the migration process may immediately fail with an error message similar to:

ERROR o.s.boot.SpringApplication - Application run failed
java.lang.IllegalStateException: Failed to execute ApplicationRunner
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_EXECUTION_PARAMS(JOB_EXECUTION_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL, IDENTIFYING) values (?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: value too long for type character varying(250); nested exception is org.postgresql.util.PSQLException: ERROR: value too long for type character varying(250)
Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(250)

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_EXECUTION_PARAMS(JOB_EXECUTION_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL, IDENTIFYING) values (?, ?, ?, ?, ?, ?, ?, ?)]; ERROR: value too long for type character varying(250); nested exception is org.postgresql.util.PSQLException: ERROR: value too long for type character varying(250)

 

Diagnosis

This error usually corresponds to launching the DB migrator with a long db_url param value, for example when specifying additional params to enable SSL features such as sslkey, sslrootcert.

Example as logged in the db migration log:

INFO c.s.n.d.migrator.MigratorApplication - Migrator arguments: --add-exports java.base/sun.nio.ch=ALL-UNNAMED --migration_type=postgres --db_url=jdbc:postgresql://postgresql.example.com/nexus?user=nexus&currentSchema=nexus&sslmode=verify-full&sslcert=/u02/nexus/migration/postgresql.crt&sslkey=/u02/nexus/migration/postgresql.key.der&sslrootcert=/u02/nexus/migration/root.crt&sslpassword=**** --content_migration=true --export_json=false --force=false

The db migrator uses an older version of Spring Batch version 4 to perform the migration. This library creates a BATCH_JOB_EXECUTION_PARAMS table inside the target database with a STRING_VAL column of varchar(250).

When the migration tool is fed a long db_url param value, then the value that is attempted to be inserted into the STRING_VAL column exceeds 250 chars, and the error is thrown.

In newer Spring Batch versions the column size is increased to 2500 varchar, but upgrading this dependency in our migration tool is not yet an option.

https://github.com/spring-projects/spring-batch/wiki/Spring-Batch-5.0-Migration-Guide#column-change-in-batch_job_execution_params

 

Solution 

Modify the db migration tool db_url value to be shorter so that it fits into the STRING_VAL column.

A common way to do this is to make any file system paths fed into the db_url for sslkey, sslrootcert to be as short as possible.

Related articles