Unable to access the server on v0.5.0

[cbarond]

Avoid duplicated bug reports

• [X] I've found a bug and checked that there are no open or closed bug report related to this.

Description

I just updated to version 0.5.0 and made sure to follow the steps outlined on the release page. However, I am unable to access the server. When I put in the server ip and port in the frontend, it says it can't connect to the server. Additionally, when I try to curl the /api/info/ping page from the host, I get the following errors:

• localhost:8080: curl: (56) Recv failure: Connection reset by peer
• server-ip:8080: curl: (7) Failed to connect to 192.168.123.235 port 8080 after 0 ms: Connection refused

From what I can tell, this seems to indicate that the server did not properly start. I also tried restarting the containers (docker compose down and docker compose up).

Any help is appreciated!

P.S. From what I've seen in the docs, the new app looks great!

Local environment

Version: v0.5.0

docker-compose.yml:

version: "3"
name: plant-it
services:
  server:
    image: msdeluise/plant-it-server:latest
    env_file: server.env
    depends_on:
      - db
      - cache
    restart: unless-stopped
    volumes:
      - "./upload-dir:/upload-dir"
      - "./certs:/certificates"
    ports:
      - "8080:8080"
      - "4040:3000"

  db:
    image: mysql:8.0
    restart: always
    env_file: server.env
    volumes:
      - "./db:/var/lib/mysql"

  cache:
    image: redis:7.2.1
    restart: always

server.env:

#
# DB
#
MYSQL_HOST=db
MYSQL_PORT=3306
MYSQL_USERNAME=root
MYSQL_PSW=root
MYSQL_DATABASE=bootdb
MYSQL_ROOT_PASSWORD=root

#
# JWT
#
JWT_SECRET=secret
JWT_EXP=1

#
# Server config
#
USERS_LIMIT=-1
UPLOAD_DIR=/upload-dir
API_PORT=8080
TREFLE_KEY=key
LOG_LEVEL=DEBUG
ALLOWED_ORIGINS=*

#
# Cache
#
CACHE_TTL=86400
CACHE_HOST=cache
CACHE_PORT=6379

#
# SSL
#
SSL_ENABLED=false
CERTIFICATE_PATH=/certificates/

Logs: Output from docker container logs plant-it-server-1 plant-it-server-logs.txt When looking through the logs, I found a few interesting lines (starts at line 1580): org.springframework.context.ApplicationContextException: Unable to start web server Caused by: org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat and several instances of Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ...

[cbarond]

It seems like several of the error messages list Can't DROP 'fk_botanicalInfo_user'; check that column/key exists as a reason. That is also the final Caused by: line. Not sure if that helps.

[MDeLuise]

Hi @cbarond, thanks for opening the issue! Appreciate the detailed information provided. It does appear that there's an issue with the deletion of the foreign key fk_botanicalInfo_user. Could you please clarify if you updated the project versions iteratively or if there were any versions skipped during the upgrade process?

[cbarond]

I think I skipped multiple versions. If I recall correctly, the version info in the app prior to the update said v1.0.0 beta. Based on that, I'm guessing the actual version was v0.1.0. At a minimum, it was before SMTP was added. I'm guessing that jump may be part of the issue.

[MDeLuise]

I think that could be the source of the problem, but we can work on it. Could you please share the result of the following command?

docker exec <db_container_name> mysql -u<db_user> -p<db_password> -e "USE bootdb; SELECT * FROM DATABASECHANGELOG;"

For example, if using the default backend.env file, the command would be:

docker exec plant-it-db-1 mysql -uroot -proot -e "USE bootdb; SELECT * FROM DATABASECHANGELOG;"

[cbarond]

Here's the output:

ID      AUTHOR  FILENAME        DATEEXECUTED    ORDEREXECUTED   EXECTYPE        MD5SUM  DESCRIPTION     COMMENTS        TAG     LIQUIBASE       CONTEXTS        LABELS  DEPLOYMENT_ID
createUserTable MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:56     1       MARK_RAN        9:ae63375bcd533e698de5ad2a0d58d0bf      createTable tableName=application_users         NULL    4.26.0  NULL    NULL    4944115995
createApiKeyTable       MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:56     2       MARK_RAN        9:b55f7c85fabee65124ae87d84e7237ab      createTable tableName=api_keys; addUniqueConstraint constraintName=api_key_name_unique, tableName=api_keys              NULL    4.26.0  NULL    NULL    4944115995
createTemporaryPasswordTable    MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:56     3       MARK_RAN        9:2533481fa846a1abd0ff9132e32a282b      createTable tableName=temporary_passwords               NULL    4.26.0  NULL    NULL    4944115995
createBotanicalInfoTable        MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:57     4       MARK_RAN        9:65e67414d4bccafe21cd0fa3442c36c6      createTable tableName=botanical_infos; addUniqueConstraint constraintName=botanical_info_species_unique, tableName=botanical_infos; addUniqueConstraint constraintName=botanical_info_external_id_unique, tableName=botanical_infos             NULL    4.26.0  NULL    NULL    4944115995
createPlantTable        MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:57     5       MARK_RAN        9:9e3f4b13a6fecdafdd258e926d6f5fda      createTable tableName=plants; addUniqueConstraint constraintName=plant_name_unique, tableName=plants            NULL    4.26.0  NULL    NULL    4944115995
createEntityImageTable  MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:58     6       MARK_RAN        9:7f9ce3a09e21214d2f7e4d1d2df92255      createTable tableName=entity_images; addColumn tableName=plants         NULL    4.26.0  NULL    NULL    4944115995
createDiaryTable        MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:58     7       MARK_RAN        9:115305a3da9a78385d740a123fa607f0      createTable tableName=diaries           NULL    4.26.0  NULL    NULL    4944115995
createDiaryEntryTable   MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:58     8       MARK_RAN        9:578e7ca1da0639b34224b766cb103a9a      createTable tableName=diary_entries             NULL    4.26.0  NULL    NULL    4944115995
createSynonymsTable     MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:58     9       MARK_RAN        9:9544f2f19b273ee89cd16a44476a760a      createTable tableName=synonyms; addUniqueConstraint constraintName=synonym_unique, tableName=synonyms           NULL    4.26.0  NULL    NULL    4944115995
createRemindersTable    MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:58     10      MARK_RAN        9:b9c6175000fd710b903527520362260e      createTable tableName=reminders         NULL    4.26.0  NULL    NULL    4944115995
createUserNotificationDispatchersTable  MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:59     11      MARK_RAN        9:744dabd0237ded1ee72780f97b417227      createTable tableName=notification_dispatchers; addPrimaryKey constraintName=pk_userNotificationDispatchers, tableName=notification_dispatchers         NULL    4.26.0  NULL    NULL    4944115995
updateTo0.1.1   MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:59     12      MARK_RAN        9:0e27c109ca2f55a8d0340c0c94d4abcf      addColumn tableName=botanical_infos; addColumn tableName=plants         NULL    4.26.0  NULL    NULL    4944115995
updateTo0.3.0   MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:21:59     13      MARK_RAN        9:7ab2b2bc85c094c09d3f40d2e4f8079a      renameColumn newColumnName=create_on, oldColumnName=saved_at, tableName=entity_images; addColumn tableName=application_users; createTable tableName=temporary_passwords         NULL    4.26.0  NULL    NULL    4944115995
modifyReminderColumns   MDeLuise        dblogs/changelog/changes/v1-0-0.xml     2024-05-05 21:22:00     14      EXECUTED        9:ea2a6665f29e0927153d2f20a57177a0      modifyDataType columnName=reminder_start, tableName=reminders; modifyDataType columnName=reminder_end, tableName=reminders              NULL    4.26.0  NULL    NULL    4944115995

[MDeLuise]

Ok great. Now we can try to do manually what the liquibase tool is trying to do with the addDeleteCascadeToForeignKeys operation (that is failing in your migration).

• Run the last version of the project that you're currently using and that it's working fine (I assume the 0.4.3).
• Save a copy of db and upload-dir folder so you can rollback if needed.
• Copy the following SQL commands into a file called update_foreign_keys.sql

-- Drop and recreate foreign key constraint fk_apiKey_user
ALTER TABLE api_keys DROP CONSTRAINT fk_apiKey_user;
ALTER TABLE api_keys
ADD CONSTRAINT fk_apiKey_user
FOREIGN KEY (user_id)
REFERENCES application_users(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_botanicalInfo_user
-- ALTER TABLE botanical_infos DROP CONSTRAINT fk_botanicalInfo_user;
ALTER TABLE botanical_infos
ADD CONSTRAINT fk_botanicalInfo_user
FOREIGN KEY (user_creator_id)
REFERENCES application_users(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_plant_user
ALTER TABLE plants DROP CONSTRAINT fk_plant_user;
ALTER TABLE plants
ADD CONSTRAINT fk_plant_user
FOREIGN KEY (owner_id)
REFERENCES application_users(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_plant_botanicalName
ALTER TABLE plants DROP CONSTRAINT fk_plant_botanicalName;
ALTER TABLE plants
ADD CONSTRAINT fk_plant_botanicalName
FOREIGN KEY (botanical_name_id)
REFERENCES botanical_infos(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_entityImage_botanicalInfoId
ALTER TABLE entity_images DROP CONSTRAINT fk_entityImage_botanicalInfoId;
ALTER TABLE entity_images
ADD CONSTRAINT fk_entityImage_botanicalInfoId
FOREIGN KEY (botanical_info_entity_id)
REFERENCES botanical_infos(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_entityImage_plantId
ALTER TABLE entity_images DROP CONSTRAINT fk_entityImage_plantId;
ALTER TABLE entity_images
ADD CONSTRAINT fk_entityImage_plantId
FOREIGN KEY (plant_entity_id)
REFERENCES plants(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_avatar_plantId
ALTER TABLE entity_images DROP CONSTRAINT fk_avatar_plantId;
ALTER TABLE entity_images
ADD CONSTRAINT fk_avatar_plantId
FOREIGN KEY (avatar_of)
REFERENCES plants(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_diary_user
ALTER TABLE diaries DROP CONSTRAINT fk_diary_user;
ALTER TABLE diaries
ADD CONSTRAINT fk_diary_user
FOREIGN KEY (user_id)
REFERENCES application_users(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_diary_trackedEntity
ALTER TABLE diaries DROP CONSTRAINT fk_diary_trackedEntity;
ALTER TABLE diaries
ADD CONSTRAINT fk_diary_trackedEntity
FOREIGN KEY (target_id)
REFERENCES plants(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_diaryEntry_diary
ALTER TABLE diary_entries DROP CONSTRAINT fk_diaryEntry_diary;
ALTER TABLE diary_entries
ADD CONSTRAINT fk_diaryEntry_diary
FOREIGN KEY (diary_id)
REFERENCES diaries(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_synonyms_botanicalInfo
ALTER TABLE synonyms DROP CONSTRAINT fk_synonyms_botanicalInfo;
ALTER TABLE synonyms
ADD CONSTRAINT fk_synonyms_botanicalInfo
FOREIGN KEY (botanical_info_id)
REFERENCES botanical_infos(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_reminder_plant
ALTER TABLE reminders DROP CONSTRAINT fk_reminder_plant;
ALTER TABLE reminders
ADD CONSTRAINT fk_reminder_plant
FOREIGN KEY (target_id)
REFERENCES plants(id)
ON DELETE CASCADE;

-- Drop and recreate foreign key constraint fk_dispatcher_user
ALTER TABLE notification_dispatchers DROP CONSTRAINT fk_dispatcher_user;
ALTER TABLE notification_dispatchers
ADD CONSTRAINT fk_dispatcher_user
FOREIGN KEY (user_id)
REFERENCES application_users(id)
ON DELETE CASCADE;

-- Add missing row to the DATABASECHANGELOG table
INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, EXECTYPE, MD5SUM, DESCRIPTION, COMMENTS, TAG, LIQUIBASE, CONTEXTS, LABELS, DEPLOYMENT_ID)
VALUES ('addDeleteCascadeToForeignKeys', 'MDeLuise', 'dblogs/changelog/changes/v1-0-0.xml', '2024-05-04 07:47:13', 15, 'EXECUTED', '9:c634128019dacc068d1e76dcd2f952ec', 'dropForeignKeyConstraint baseTableName=api_keys, constraintName=fk_apiKey_user; addForeignKeyConstraint baseTableName=api_keys, constraintName=fk_apiKey_user, referencedTableName=application_users; dropForeignKeyConstraint baseTableName=botanical_...', NULL, '4.26.0', NULL, NULL, 4808833170); 

• Run docker exec <db_container_name> mysql -u<db_user> -p<db_password> < update_foreign_keys.sql (this will executes the script above).
• Run docker exec <db_container_name> mysql -u<db_user> -p<db_password> -e "USE bootdb; DELETE FROM DATABASECHANGELOG;" (this is needed to upgrade the project version in the next step).
• Re-deploy the project using the 0.5.0 version of the image.

---

This could solve the issue, but there can be other issues about missing foreign keys or similar, so let me know if it's ok or if the error in the log change so we can solve step by step.

[cbarond]

So, I tried rolling back to the previous version I was using, but I couldn't find one that worked properly. On several different versions, the logs indicate that the backend was working fine, but the api wasn't available. Maybe I need to wait longer than 5 minutes? I think I also found an indicator of the last version I was using, or at least the last time I set up the .env files. In backend.env, the Trefle key is still labeled as TRAFLE_KEY. I also found a previous docker ps which lists plant-it-cache-1 as being created 6 months ago and the other containers as 2 months ago. So, I honestly don't know what version I was running

After trying that, I decided to just load v0.4.3 and run the SQL file you gave me. I then updated to v0.5.0 by using the latest image. I am no longer getting a foreign key error. The final error is now Caused by: java.lang.NullPointerException: Cannot invoke "java.lang.Throwable.getMessage()" because the return value of "java.lang.Exception.getCause()" is null. I have attached the logs.

plant-it-server-logs-2.txt

[cbarond]

Update on this issue:

After remembering that we didn't have much in the database, I decided to restart. After making sure I had backups of the old database, I deleted the db and certs folders, pulled the latest image, and ran the compose file. It is now running on 0.7.3 and seems to be working fine (other than the issues with Trefle). In the future, I will make sure to check for breaking changes and update accordingly.