java.sql.SQLException: Schema upgrade failed

[Varun Chand]

Hi,

I am trying to point external Oracle DB blank schema from Devtest 10.3 but the registry is not getting started and getting below error.

 

Devtest Version : 10.3

Oracle Version :  Oracle 12c Enterprise Edition Release 12.2.0.1.0 – 64 bit

 

2019-09-18 13:02:43,512Z (06:02) [main] ERROR System.err                     - [DevTest AGENT:C][ERROR][13054][1][main][Sep 18, 2019 6:02:43 AM] Unable to get schema version from the database

2019-09-18 13:02:43,527Z (06:02) [main] ERROR System.err                     - [DevTest AGENT:C][ERROR][13054][1][main][Sep 18, 2019 6:02:43 AM] Schema upgrade failed: java.sql.SQLException

2019-09-18 13:02:43,538Z (06:02) [main] ERROR System.err                     - [DevTest AGENT:C][ERROR][13054][1][main][Sep 18, 2019 6:02:43 AM] DB error for jdbc:oracle:thin:@isvtp1.unix.gsm1900.org:4684:isvtp1... No transactions will be saved there

2019-09-18 13:02:43,544Z (06:02) [main] ERROR com.itko.lisa.coordinator.TestRegistryImpl - Schema upgrade failed

java.sql.SQLException: Schema upgrade failed

        at com.itko.lisa.remote.persist.SchemaPersister.createOrUpgradeSchemaIfNeeded(SchemaPersister.java:949)

        at com.itko.lisa.remote.persist.PersistManager.createSchemaIfNeeded(PersistManager.java:383)

        at com.itko.lisa.remote.persist.PersistManager.getSafeConnection(PersistManager.java:592)

        at com.itko.lisa.coordinator.TestRegistryImpl.main(TestRegistryImpl.java:2171)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

        at com.exe4j.runtime.LauncherEngine.launch(Unknown Source)

        at com.install4j.runtime.launcher.UnixLauncher.main(Unknown Source)

2019-09-18 13:02:43,548Z (06:02) [main] INFO  System.out                     - Unexpected Exception: java.sql.SQLException: Schema upgrade failed

2019-09-18 13:02:43,551Z (06:02) [Thread-9] INFO  com.itko.lisa.net.ActiveMQFactory - Closing AMQ connections

------------------------------
Thanks,
Varun Chand
------------------------------

[Vaibhav Jain]

Hi Varun,

Check below points:
1. Ensure that the character set of the Oracle database supports Unicode 
2. Is the DB already created? If not, Do the user that you are using is having writes to create the DB. If Yes, then do the user have permissions to write to it.
3. For Manually executing the DB create statements : oracle.ddl file in the LISA_HOME\database directory contains SQL statements 
4. Ensure that  LISA_HOME\lib\shared  contains the JDBC driver like ojdbc7/8/9.jar
5. I assume you are making changes in site.properties file in the LISA_HOME as below

lisadb.pool.common.driverClass=oracle.jdbc.driver.OracleDriver
## Select one of the two connection URLs depending on usage of SID or SERVICE
lisadb.pool.common.url=jdbc:oracle:thin:@[HOST]:1521:[SID]
lisadb.pool.common.url=jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
lisadb.pool.common.user=[USERNAME]
lisadb.pool.common.password=[PASSWORD]

Regards,
Vaibhav J

------------------------------
Regards,
Vaibhav Jain
Capgemini
------------------------------

[Sankar Natarajan]

HI Varun
           This error indicates that the DB may already have a table that contains the version of DB schema. Please run the following query against the DB and check the result. Since you said the DB is blank, I do not expect the table to be present, still its a good check to do.

select SCHEMA_VERSION from LISA_AGENT_VERSION

If this table exists and if there are records, which I hope not; it may mean the DB was in use and in which case you may choose to create a new DB and use it.

-Sankar

------------------------------
Sankar Natarajan
Service Virtualization Product Engineering Team
Broadcom
------------------------------

[Varun Chand]

Hi All,

Thanks for replying.

After updating the schema version as 9 in the LISA_AGENT_VERSION table the registry started.

------------------------------
Thanks,
Varun Chand
------------------------------

[Koustubh Warty]

When the Registry starts, it first goes thru a series of checks including if there is any existing schema in the database. If none is found, it will create a new one. If it finds an existing schema, it will check if the version of that schema is at the latest level that this Registry can support. If not, it will try to upgrade to the latest. That's what I am seeing in here. Very interesting that you mentioned that it is blank. 

Please follow Sankar's advice on running that query to see if the table for schema currently exists. 

Thanks,
 - Koustubh

------------------------------
Head of Product Management: Service Virtualization
CA Technologies - A Broadcom Company
------------------------------