Closed cbarond closed 4 days ago
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.
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?
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.
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;"
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
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).
0.4.3
).db
and upload-dir
folder so you can rollback if needed.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=applicationusers; 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.
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.
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.
Avoid duplicated bug reports
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
anddocker 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:
server.env:
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 ofCaused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name ...