thingsboard: [Question] Upgrade from 3.1.1 to 3.4.1 problems

Component

  • Installation

Description Our company server had 3.1.1 version installed with previous setup and config (by previous employees), I am using first time Thingsboard. The problem was that our UI will drop in a week and restart was required, as I read through the other Git Issues I was supposed to upgrade. First I upgraded the openJDK version and then I started upgrading version by version (download and install as you recommend on your website). On the end the installation was not working and I couldn’t access Thingsboard. We also have purchased licenses as well. The database is still populated with the old data (PSQL), but it seems I am missing some columns. If you can please tell me exactly which tables to alter. I am confused because in terminal output gives external_id and in log file its adminseei0_.tenant_id

Terminal Output:

 sudo /usr/share/thingsboard/bin/install/install.sh --loadDemo -debug
  ______    __      _                              ____                               __
 /_  __/   / /_    (_)   ____    ____ _   _____   / __ )  ____   ____ _   _____  ____/ /
  / /     / __ \  / /   / __ \  / __ `/  / ___/  / __  | / __ \ / __ `/  / ___/ / __  /
 / /     / / / / / /   / / / / / /_/ /  (__  )  / /_/ / / /_/ // /_/ /  / /    / /_/ /
/_/     /_/ /_/ /_/   /_/ /_/  \__, /  /____/  /_____/  \____/ \__,_/  /_/     \__,_/
                              /____/

 ===================================================
 :: ThingsBoard ::       (v3.4.1)
 ===================================================

Starting ThingsBoard Installation...
Installing DataBase schema for entities...
Installing SQL DataBase schema part: schema-entities.sql
Installing SQL DataBase schema indexes part: schema-entities-idx.sql
Unexpected error during ThingsBoard installation!
org.postgresql.util.PSQLException: FEHLER: Spalte »external_id« existiert nicht
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.executeQueryFromFile(SqlAbstractDatabaseSchemaService.java:82)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseIndexes(SqlAbstractDatabaseSchemaService.java:74)
        at org.thingsboard.server.service.install.SqlEntityDatabaseSchemaService.createDatabaseIndexes(SqlEntityDatabaseSchemaService.java:37)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseSchema(SqlAbstractDatabaseSchemaService.java:66)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseSchema(SqlAbstractDatabaseSchemaService.java:57)
        at org.thingsboard.server.install.ThingsboardInstallService.performInstall(ThingsboardInstallService.java:247)
        at org.thingsboard.server.ThingsboardInstallApplication.main(ThingsboardInstallApplication.java:48)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:108)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
        at org.springframework.boot.loader.PropertiesLauncher.main(PropertiesLauncher.java:467)
Unexpected error during ThingsBoard installation!
ThingsBoard installation failed!
ketag@srvketag40:~$  sudo /usr/share/thingsboard/bin/install/install.sh --loadDemo -debug
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.executeQueryFromFile(SqlAbstractDatabaseSchemaService.java:82)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseIndexes(SqlAbstractDatabaseSchemaService.java:74)
        at org.thingsboard.server.service.install.SqlEntityDatabaseSchemaService.createDatabaseIndexes(SqlEntityDatabaseSchemaService.java:37)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseSchema(SqlAbstractDatabaseSchemaService.java:66)
        at org.thingsboard.server.service.install.SqlAbstractDatabaseSchemaService.createDatabaseSchema(SqlAbstractDatabaseSchemaService.java:57)
        at org.thingsboard.server.install.ThingsboardInstallService.performInstall(ThingsboardInstallService.java:247)
        at org.thingsboard.server.ThingsboardInstallApplication.main(ThingsboardInstallApplication.java:48)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:108)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
        at org.springframework.boot.loader.PropertiesLauncher.main(PropertiesLauncher.java:467)
Unexpected error during ThingsBoard installation!
ThingsBoard installation failed!

Environment

  • OS: Ubuntu 18.04.5 LTS
  • ThingsBoard: 3.4.1
  • java -version output:
openjdk version "11.0.16" 2022-07-19
OpenJDK Runtime Environment (build 11.0.16+8-post-Ubuntu-0ubuntu118.04)
OpenJDK 64-Bit Server VM (build 11.0.16+8-post-Ubuntu-0ubuntu118.04, mixed mode, sharing)

Config file /etc/thingsboard/conf/thingsboard.conf:


export JAVA_OPTS="$JAVA_OPTS -Dplatform=deb -Dinstall.data_dir=/usr/share/thingsboard/data"
export JAVA_OPTS="$JAVA_OPTS -Xlog:gc*,heap*,age*,safepoint=debug:file=/var/log/thingsboard/gc.log:time,uptime,level,tags:filecount=10,filesize=10M"
export JAVA_OPTS="$JAVA_OPTS -XX:+IgnoreUnrecognizedVMOptions -XX:+HeapDumpOnOutOfMemoryError"
export JAVA_OPTS="$JAVA_OPTS -XX:-UseBiasedLocking -XX:+UseTLAB -XX:+ResizeTLAB -XX:+PerfDisableSharedMem -XX:+UseCondCardMark"
export JAVA_OPTS="$JAVA_OPTS -XX:+UseG1GC -XX:MaxGCPauseMillis=500 -XX:+UseStringDeduplication -XX:+ParallelRefProcEnabled -XX:MaxTenuringThreshold=10"
export LOG_FILENAME=thingsboard.out
export LOADER_PATH=/usr/share/thingsboard/conf,/usr/share/thingsboard/extensions
export SQL_DATA_FOLDER=/usr/share/thingsboard/data/sql

# DB Configuration
export DATABASE_TS_TYPE=sql
export SPRING_DATASOURCE_URL=jdbc:postgresql://localhost:5432/thingsboard
export SPRING_DATASOURCE_USERNAME=postgres
export SPRING_DATASOURCE_PASSWORD=xxxxxxx
export SPRING_DATASOURCE_MAXIMUM_POOL_SIZE=5
# Specify partitioning size for timestamp key-value storage. Allowed values: DAYS, MONTHS, YEARS, INDEFINITE.
export SQL_POSTGRES_TS_KV_PARTITIONING=MONTHS

Result from: cat /var/log/thingsboard/thingsboard.log | grep ERROR

 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - FEHLER: Spalte adminsetti0_.tenant_id existiert nicht
2022-10-04 13:54:18,316 [main] ERROR o.s.b.w.e.tomcat.TomcatStarter - Error starting Tomcat context. Exception: org.springframework.beans.factory.UnsatisfiedDependencyException. Message: Error creating bean with name 'thingsboardSecurityConfiguration': Unsatisfied dependency expressed through field 'oauth2AuthenticationSuccessHandler'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'oauth2AuthenticationSuccessHandler' defined in URL [jar:file:/usr/share/thingsboard/bin/thingsboard.jar!/BOOT-INF/classes!/org/thingsboard/server/service/security/auth/oauth2/Oauth2AuthenticationSuccessHandler.class]: Unsatisfied dependency expressed through constructor parameter 6; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'defaultSystemSecurityService': Unsatisfied dependency expressed through field 'mailService'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'defaultMailService': Invocation of init method failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
2022-10-04 13:54:18,447 [main] ERROR o.s.boot.SpringApplication - Application run failed

Thank you, and if you require any further info please don’t hesitate to ask for it.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 15 (6 by maintainers)

Most upvoted comments

Thank you for the great support people, I managed to solve this one based on your comment feedback. There were some mistakes on my side but also documentation can be improved (below I will mark what I was doing wrong):

I did this for all the versions (version by version), but I will explain here only one cycyle. The correct steps to upgrade are:

  1. sudo service thingsboard stop OR sudo systemctl stop thingsboard
  2. Download the version with wget (commands can be found here https://thingsboard.io/docs/user-guide/install/upgrade-instructions) e.g. wget https://github.com/thingsboard/thingsboard/releases/download/v3.4.1/thingsboard-3.4.1.deb
  3. run the service upgrade command sudo dpkg -i thingsboard-3.4.1.deb
  4. [I WAS DOING WRONG] check if the DB credentials have been overwritten in sudo nano /etc/thingsboard/conf/thingsboard.conf
  5. [I WAS NOT DOING] Run the installation script to apply the changes to the DB e.g. sudo /usr/share/thingsboard/bin/install/upgrade.sh --fromVersion=3.4.0. From the documentation I got the feeling that this step is optional.
  6. Test if the service is working with sudo service thingsboard start OR sudo systemctl startthingsboard

On the end I just wanted to upgrade to the PE (Professional Edition) so I used this link and followed the instructions how to upgrade the web component: https://thingsboard.io/docs/user-guide/install/pe/upgrade-instructions

Please note: You need to do a backup, this is a good instruction page: https://thingsboard.io/docs/user-guide/install/pe/upgrade-instructions/

[THE DOCUMENTATION DOESN’T MENTION]: The backup of the configuration is not mentioned anywhere before upgrading, so please make backup from: /etc/thingsboard/conf/thingsboard.conf AND don’t forget if you are using web report please backup /usr/share/tb-web-report/conf/tb-web-report.conf

[THE DOCUMENTATION DOESN’T EXPLAIN] For the version 3.3.3 upgrade there is a note saying:


ThingsBoard UI was migrated to Angular 12. You need to re-build your custom widgets and rule nodes (which use UI) on Angular 12.

In my case I have no idea what has happened previously so I wanted to do this re-build (I do not understand if I even need to, because I am not Angular dev) but there were no clear instructions how to do so.

Thank you to everyone and I hope this helps someone ❤️

As the error messages states, the column external_id is missing. This column is used in multiple tables and was introduced with the Version control feature. Probably there were issues while updating the Postgresql tables during installation process.

To fix the issue, try running the sql script located in the installation directory at \thingsboard\data\upgrade\3.3.4