Referencing non-existant tables causing issues

[haithcockce]

After setting the OAuth Key and syncing, accounts, categories, etc do not load. Transactions do however load.

In checking the docker logs for firefly_pico_db, the following is seen often;

2024-09-10 23:40:01.202 UTC [87] ERROR:  relation "tags" does not exist at character 15
2024-09-10 23:40:01.202 UTC [87] STATEMENT:  select * from "tags" where 0 = 1
2024-09-10 23:40:01.248 UTC [88] ERROR:  relation "transaction_templates" does not exist at character 35
2024-09-10 23:40:01.248 UTC [88] STATEMENT:  select count(*) as aggregate from "transaction_templates"
2024-09-10 23:40:01.286 UTC [89] ERROR:  relation "categories" does not exist at character 15
2024-09-10 23:40:01.286 UTC [89] STATEMENT:  select * from "categories" where "id" in ($1, $2, $3, $4, $5, $6, $7, $8)
2024-09-10 23:40:01.703 UTC [90] ERROR:  relation "accounts" does not exist at character 15
2024-09-10 23:40:01.703 UTC [90] STATEMENT:  select * from "accounts" where "id" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50)
2024-09-10 23:40:01.955 UTC [91] ERROR:  relation "profiles" does not exist at character 15

I'm using 1.3.4, via the docker-compose yaml file for just a standalone pico setup (as I already have a firefly setup).

The docker-compose file is largely unmodified;

version: '3.3'
services:

  firefly-pico:
    image: cioraneanu/firefly-pico:latest
    container_name: firefly_pico
    ports:
      - "6976:80"
    environment:
      - FIREFLY_URL=https://firefly.haith.cloud
      - DB_CONNECTION=pgsql
      - DB_HOST=firefly-pico-postgresql
      - DB_PORT=5432
      - DB_DATABASE=firefly-pico
      - DB_USERNAME=firefly-pico
      - DB_PASSWORD=xxxx
      - TZ=Europe/London
    depends_on:
      - firefly-pico-postgresql
    networks:
      - public

  firefly-pico-postgresql:
    image: postgres:latest
    container_name: firefly_pico_db
    environment:
      - POSTGRES_DB=firefly-pico
      - POSTGRES_USER=firefly-pico
      - POSTGRES_PASSWORD=xxxx
    volumes:
      - /media/containers/firefly-iii/pico_db:/var/lib/postgresql/data
    networks:
      - public

networks:
  public:
    external: true

[cioraneanu]

The DB migration didn't run for some reason. Try deleting and recreating the Pico containers. Alternatively manually ssh into the Pico app container and run php artisan migrate

[haithcockce]

Apologies, I've also been wrapping up a move to a new apartment so this had to take a back seat.

The issue is that this happens on a new installation.

When reinstalling as recommended, I did not rebuild the containers all at once and start them all at once (e.g. via docker-compose up -d) but rather I built and started the database service then the built and started the app service (docker-compose up firefly-pico-postgresql -d, wait until it stops producing startup logs, then docker-compose up firefly-pico -d). This time, the logs show migrations occurring;

❯ docker logs -f firefly_pico

   INFO  Preparing database.  

  Creating migration table ........................................ 217ms DONE

   INFO  Running migrations.  

  2014_10_12_000000_create_users_table ............................ 137ms DONE
  2014_10_12_100000_create_password_reset_tokens_table ............ 110ms DONE
  2019_08_19_000000_create_failed_jobs_table ...................... 140ms DONE
  2019_12_14_000001_create_personal_access_tokens_table ........... 230ms DONE
  2023_10_02_124513_create_transaction_templates .................. 401ms DONE
  2024_02_22_131702_create_transaction_template_audio_names_table .. 90ms DONE
  2024_03_10_084308_create_tags_table ............................. 130ms DONE
  2024_03_27_130655_add_icon_to_accounts_and_categories ........... 180ms DONE
  2024_04_01_121800_add_is_todo_to_tags ............................ 10ms DONE
  2024_04_23_155844_rename_transaction_templates_audio_names ....... 10ms DONE
  2024_05_14_054224_add_is_visible_on_dashboard_to_accounts ........ 10ms DONE
  2024_06_25_000000_create_profiles_table ......................... 140ms DONE

   INFO  Configuration cache cleared successfully.  

   INFO  Configuration cached successfully.  

   INFO  Application cache cleared successfully.  

2024-09-18 20:27:24,407 INFO Included extra file "/etc/supervisor.d/cron.ini" during parsing
[...]

In comparison, the logs look like this on a fresh installation for the app container;

❯ docker logs -f firefly_pico

In Connection.php line 801:

  SQLSTATE[08006] [7] connection to server at "firefly-pico-postgresql" (172.  
  20.0.20), port 5432 failed: Connection refused                               
    Is the server running on that host and accepting TCP/IP connections? (Conn   
  ection: pgsql, SQL: select * from information_schema.tables where table_cat  
  alog = firefly-pico and table_schema = public and table_name = migrations a  
  nd table_type = 'BASE TABLE')                                                

In Connector.php line 65:

  SQLSTATE[08006] [7] connection to server at "firefly-pico-postgresql" (172.  
  20.0.20), port 5432 failed: Connection refused                               
    Is the server running on that host and accepting TCP/IP connections?         

   INFO  Configuration cache cleared successfully.  

   INFO  Configuration cached successfully.  

   INFO  Application cache cleared successfully.  

2024-09-18 20:15:26,149 INFO Included extra file "/etc/supervisor.d/cron.ini" during parsing
[...]

I briefly looked over the Dockerfile and entry point and see the app container immediately starts the migration. In my brief experience for similar containerized workloads, the app container normally has a delay built into the app container while the database finishes startup. From the logs of my firefly_iii_core container, for instance;

[...]
  [i] Wait for the database. You may see an error about an 'aborted connection', this is normal.
wait-for-it.sh: waiting 60 seconds for firefly_db:3306
wait-for-it.sh: firefly_db:3306 is available after 0 seconds
  [✓] DB is up.
  [i] Wait another 10 seconds in case the DB needs to boot.
  [✓] Done waiting for the DB to boot.
  [i] Will run database commands.
[...]

Given I can reliably reproduce this issue with a simple docker-compose up -d against the docker-compose.yml file provided, but starting the database container then the app container afterwards seems to fix the issue, maybe adding a pause to the app container before migration may help? Or checking if the container is fully up in some way before attempting the migration?

[cioraneanu]

Awesome. Thanks for pointing this out. Will try to add a retry connection + sleep combo at the Laravel level.

[haithcockce]

Fantastic! I don't mind testing it if you need testers.

[cioraneanu]

Added mechanism to sleep / retry until DB is up (9169a15). You can give the dev docker tag a try.

[BenjaminMichaelis]

Added mechanism to sleep / retry until DB is up (9169a15). You can give the dev docker tag a try.

I had the same issue and updating to dev fixed it for me; thanks!

[jeriox]

I had the same issue and updating to dev fixed it for me; thanks!

same over here!

[cioraneanu]

Awesome! Thank you for the feedback 😊