4 Replies Latest reply on Mar 10, 2017 1:02 PM by martin.kotala

    DPA 11.0.373 - Error during upgrade from 10.2 with Oracle repository

    nixon44

      Hello All,

       

      I have an error in repository database upgrade.

       

      DB is Oracle 12.1.0.2.

       

      Error is :

       

      DEBUG  (2017-03-09 10:37:23,353.EET) [repositoryManager-thread-1] RepositoryMigrationService - Migration task [patch111109_cond_name_resize.xml] failed due to [StatementCallback; uncategorized SQLException for SQL [ALTER TABLE COND MODIFY NAME VARCHAR2(400)]; SQL state [72000]; error code [1439]; ORA-01439: column to be modified must be empty to change datatype

      ; nested exception is java.sql.SQLException: ORA-01439: column to be modified must be empty to change datatype

      ].

      ERROR  (2017-03-09 10:37:23,360.EET) [repositoryManager-thread-1] RepositoryMigrationService - Repository Database upgrade failed due to [StatementCallback; uncategorized SQLException for SQL [ALTER TABLE COND MODIFY NAME VARCHAR2(400)]; SQL state [72000]; error code [1439]; ORA-01439: column to be modified must be empty to change datatype

      ; nested exception is java.sql.SQLException: ORA-01439: column to be modified must be empty to change datatype

      ].

      org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [ALTER TABLE COND MODIFY NAME VARCHAR2(400)]; SQL state [72000]; error code [1439]; ORA-01439: column to be modified must be empty to change datatype

      ; nested exception is java.sql.SQLException: ORA-01439: column to be modified must be empty to change datatype

       

       

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)

        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:443)

        at com.confio.idc.repo.migration.xml.changes.ModifyColumnChange.modifyColumn(SourceFile:164)

        at com.confio.idc.repo.migration.xml.changes.ModifyColumnChange.execute(SourceFile:57)

        at com.confio.idc.repo.migration.xml.XMLRepositoryMigrationTask.migrate(SourceFile:100)

        at com.confio.idc.repo.migration.support.RepositoryMigrationTaskSupport.migrate(SourceFile:92)

        at com.tacitknowledge.util.migration.MigrationProcess.applyPatch(MigrationProcess.java:315)

        at com.tacitknowledge.util.migration.MigrationProcess.doMigrations(MigrationProcess.java:227)

        at com.confio.idc.repo.migration.service.RepositoryMigrationService.doMigrations(SourceFile:220)

        at com.confio.idc.repo.RepositoryMonitor.startUp(SourceFile:1000)

        at com.confio.idc.repo.RepositoryMonitor.queryRepo(SourceFile:344)

        at com.confio.idc.repo.job.RepoPropsJob.execute(SourceFile:18)

        at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

      Caused by: java.sql.SQLException: ORA-01439: column to be modified must be empty to change datatype

       

       

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)

        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)

        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1029)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)

        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)

        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)

        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)

        at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)

        at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:435)

        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)

        ... 15 more

       

       

      This is caused by column been defined as NVArchar(100) in previous release.

       

      Upgrade tries to modify it to varchar2(400) which is change of type not only in size.

       

      This was resolved by modifying in file C:\Program Files\SolarWinds\DPA\iwc\tomcat\webapps\iwc\WEB-INF\classes\resources\repository\Patches\patch111109_cond_name_resize.xml type to NVARCHAR.

       

      Don't know is this best solution but works.

       

      Best regards,

       

      Nikolay