Updating JTRAC 2.1.0 to 2.3.2

[VOUILLON Laurent]

Dear,

We use the JTRAC on 2.1.0 and we want to upgradate on 2.3.2 -> have you a process to realize these operation ?

Thanks

Laurent VOUILLON

[Ulf Dittmer]

That depends on how you're running JTrac now. If you're using a standalone version (like what is downloadable as a ZIP now), you should be able to copy your "data" folder to that, and be up and running quickly. Current JTrac versions will update the database schema as needed.

[Sarlacii]

Greetings @udittmer... it's so funny, but I am trying once again to update JTrac from the very obsolete V2.1.0, so this post is bang on topic for me!
I am running on Ubuntu 18.04.6 LTS, and I have a /usr/local/ directory that contains the different versions of JTrac.
I have unzipped the latest v2.3.2, and copied over the data directory from the old install, except for the json.properties file, which I edited (I run on port 54545, not 8888). I also modified the start.ini file to reflect the port change.
However, when I run the start.sh, I get errors about an incompatible old database, like so:

2024-01-09 11:28:33.355:INFO:oejshC.ROOT:main: Set web app root system property: 'jtrac.root' = [/tmp/jetty-0_0_0_0-54545-ROOT_war-_-any-6703636296418390906/webapp]
2024-01-09 11:28:33.357:INFO:oejshC.ROOT:main: Initializing log4j from [/tmp/jetty-0_0_0_0-54545-ROOT_war-_-any-6703636296418390906/webapp/WEB-INF/log4j.properties]
2024-01-09 11:28:33.484:INFO:oejshC.ROOT:main: Initializing Spring root WebApplicationContext
2024-01-09 11:28:33,671 [main] INFO [info.jtrac.config.JtracConfigurer] - found 'jtrac-init.properties' on classpath, processing...
2024-01-09 11:28:33,672 [main] INFO [info.jtrac.config.JtracConfigurer] - locales available configured are 'en,ja,es_MX,ru,el,ko,sv,tr,es,nl,hu,es_AR,sl,en,fr,uk,cs,it,zh_CN,zh_TW,ar,no,de,pt_BR,pl'
2024-01-09 11:28:33,672 [main] INFO [info.jtrac.config.JtracConfigurer] - valid 'jtrac.home' property not available in 'jtrac-init.properties', trying system properties.
2024-01-09 11:28:33,672 [main] INFO [info.jtrac.config.JtracConfigurer] - 'jtrac.home' property initialized from system properties as 'data'
2024-01-09 11:28:33,672 [main] INFO [info.jtrac.config.JtracConfigurer] - directory already exists: 'data'
2024-01-09 11:28:33,672 [main] INFO [info.jtrac.config.JtracConfigurer] - directory already exists: 'data/attachments'
2024-01-09 11:28:33,673 [main] INFO [info.jtrac.config.JtracConfigurer] - directory already exists: 'data/indexes'
2024-01-09 11:28:33,673 [main] INFO [info.jtrac.config.JtracConfigurer] - 'jtrac.properties' file exists: 'data/jtrac.properties'
2024-01-09 11:28:33,673 [main] INFO [info.jtrac.config.JtracConfigurer] - found 'jtrac-version.properties' on classpath, processing...
2024-01-09 11:28:33,673 [main] INFO [info.jtrac.config.JtracConfigurer] - jtrac.version = '2.3.2'
2024-01-09 11:28:33,673 [main] INFO [info.jtrac.config.JtracConfigurer] - jtrac.timestamp = '2023-07-30 14:58 UTC'
2024-01-09 11:28:33,674 [main] INFO [info.jtrac.config.JtracConfigurer] - Loading properties file from file [/usr/local/jtrac/jtrac-2.3.2/data/jtrac.properties]
2024-01-09 11:28:33,749 [main] INFO [info.jtrac.config.DataSourceFactoryBean] - embedded HSQLDB mode detected, using Spring single connection data source
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.dom4j.io.SAXContentHandler (file:/tmp/jetty-0_0_0_0-54545-ROOT_war-_-any-6703636296418390906/webapp/WEB-INF/lib/dom4j-1.6.1.jar) to method com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$LocatorProxy.getEncoding()
WARNING: Please consider reporting this to the maintainers of org.dom4j.io.SAXContentHandler
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
2024-01-09 11:28:34,536 [main] WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -453, SQLState: S1000
2024-01-09 11:28:34,537 [main] ERROR [org.hibernate.util.JDBCExceptionReporter] - wrong database file version: unsupported old database version
2024-01-09 11:28:34,537 [main] WARN [org.hibernate.cfg.SettingsFactory] - Could not obtain connection metadata
java.sql.SQLException: wrong database file version: unsupported old database version

Any thoughts on how to migrate? Or is it actually some other issue, eg. JRE version?

Go well

Sarlacii

[Ulf Dittmer]

You have a database created using HSQLDB version 1, which HSQLDB 2 can't read. If you replace the hsqldb jar file that comes with JTrac with the one from https://mvnrepository.com/artifact/org.hsqldb/hsqldb/1.8.0.10, it should work.

[Sarlacii]

Hmm, ok, makes sense... but my issue is that I can't see any HSQLDB file in the unzipped v2.3.2 version? As such I downloaded the suggested version from your link (although I also found matching copies in the old installation directory) and since I couldn't find the newer version to replace, I simply dropped the driver into the ./lib/ directory, as it is included when loading.

So I have this:

root@server:/usr/local/jtrac/jtrac-2.1.0# find ./ -iname '*hsqldb*' -type f -printf '"%p"\n'
"./work/Jetty_0_0_0_0_80_jtrac.war__jtrac__qk44r3/webapp/WEB-INF/lib/hsqldb-1.8.0.1.jar"
"./work/Jetty_0_0_0_0_54545_jtrac.war__jtrac__kifiic/webapp/WEB-INF/lib/hsqldb-1.8.0.1.jar"
"./etc/jtrac-hsqldb-to-mysql.bsh"
root@server:/usr/local/jtrac/jtrac-2.1.0# cd -
/root/finmon-dir/jtrac-2.3.2
root@server:/usr/local/jtrac/jtrac-2.3.2# find ./ -iname '*hsqldb*' -type f -printf '"%p"\n'
"./lib/hsqldb-1.8.0.10.jar"
root@server:/usr/local/jtrac/jtrac-2.3.2#

When I run start.sh, I still get the error for the database, although the server does actually start up, on the IP and port I specified. However, you get an error if you try to load jtrac, as it didn't manage to open the DB, so the app isn't actually running, even if Jetty is - which all makes sense.

HTTP ERROR 503 Service Unavailable
URI: /jtrac/app/
STATUS: 503
MESSAGE: Service Unavailable
SERVLET: -
Powered by Jetty:// 9.4.44.v20210927

The log still shows the DB error:

2024-01-09 16:15:18,989 [main] INFO [info.jtrac.config.JtracConfigurer] - Loading properties file from file [/usr/local/jtrac/jtrac-2.3.2/data/jtrac.properties]
2024-01-09 16:15:19,087 [main] INFO [info.jtrac.config.DataSourceFactoryBean] - embedded HSQLDB mode detected, using Spring single connection data source
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.dom4j.io.SAXContentHandler (file:/tmp/jetty-192_168_0_3-54545-ROOT_war-_-any-6699684577768769965/webapp/WEB-INF/lib/dom4j-1.6.1.jar) to method com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser$LocatorProxy.getEncoding()
WARNING: Please consider reporting this to the maintainers of org.dom4j.io.SAXContentHandler
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
2024-01-09 16:15:19,850 [main] WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -453, SQLState: S1000
2024-01-09 16:15:19,850 [main] ERROR [org.hibernate.util.JDBCExceptionReporter] - wrong database file version: unsupported old database version
2024-01-09 16:15:19,851 [main] WARN [org.hibernate.cfg.SettingsFactory] - Could not obtain connection metadata
java.sql.SQLException: wrong database file version: unsupported old database version

My next thought is to try and migrate the HSSQLDB to MySQL, using the script in the V2.1.0 directory, as I have a MariaDB instance available. Then I will copy the data to V2.3.2. Thoughts? But I'd love to just use the embedded one, as it makes it all nice and closed.

Thank you for the help BTW.

[Ulf Dittmer]

The hsqldb jar file is in the war file. Jetty doesn't generally unpack the war file (unlike Tomcat), so it's a bit tricky to update. But a war file is just a ZIP, so any ZIP tool should be able to remove the existing hsqldb 2 jar and add the new (actually, old) hsqldb 1 one.