After Upgrade 6.2.5 -> 6.2.6, Piggy Banks (and therefore also creating new transactions) fails with a database error

[werdck]

After the upgrade, I tried using firefly-iii as normal, but encountered an error when trying to create a transaction:

SQLSTATE[42S02]: Base table or view not found: 1932 Table 'firefly.account_piggy_bank' doesn't exist in engine (Connection: mysql, SQL: select `piggy_banks`.* from `piggy_banks` left join `account_piggy_bank` on `account_piggy_bank`.`piggy_bank_id` = `piggy_banks`.`id` left join `accounts` on `accounts`.`id` = `account_piggy_bank`.`account_id` where `accounts`.`user_id` = 1 and `piggy_banks`.`deleted_at` is null order by `piggy_banks`.`order` asc)

The log (docker compose logs) tells me the following:

[2025-02-15 17:01:24] production.ERROR: Exception is: {"class":"Illuminate\\Database\\QueryException","errorMessage":"SQLSTATE[42S02]: Base table or view not found: 1932 Table 'firefly.account_piggy_bank' doesn't exist in engine (Connection: mysql, SQL: select `piggy_banks`.* from `piggy_banks` left join `account_piggy_bank` on `account_piggy_bank`.`piggy_bank_id` = `piggy_banks`.`id` left join `accounts` on `accounts`.`id` = `account_piggy_bank`.`account_id` where `accounts`.`user_id` = 1 and `piggy_banks`.`deleted_at` is null order by `piggy_banks`.`order` asc)","time":"Sat, 15 Feb 2025 17:01:24 +0100","file":"\/var\/www\/html\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php","line":825,"code":"42S02","version":"6.2.6","url":"http:\/\/localhost:8080\/piggy-banks","userAgent":"Mozilla\/5.0 (X11; Linux x86_64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/133.0.0.0 Safari\/537.36","json":true,"method":"GET","post":""}

Can I just recreate the view "firefly.account_piggy_bank", or should I expect more errors?
If I can just recreate the view: How do I do that? I am adept in SQL but never worked with Views so far.

Edit:
Some analysis in the DB itself shows me, that the table indeed DOES exist. What does Firefly-III miss then?

MariaDB [firefly]> SHOW TABLES;
+---------------------------------+
| Tables_in_firefly               |
+---------------------------------+
| 2fa_tokens                      |
| account_balances                |
| account_meta                    |
| account_piggy_bank              |
| account_types                   |
| accounts                        |
| attachments                     |
| audit_log_entries               |
| auto_budgets                    |
| available_budgets               |
| bills                           |
| budget_limits                   |
| budget_transaction              |
| budget_transaction_journal      |
| budgets                         |
| categories                      |
| category_transaction            |
| category_transaction_journal    |
| configuration                   |
| currency_exchange_rates         |
| export_jobs                     |
| failed_jobs                     |
| group_journals                  |
| group_memberships               |
| import_jobs                     |
| invited_users                   |
| jobs                            |
| journal_links                   |
| journal_meta                    |
| limit_repetitions               |
| link_types                      |
| locations                       |
| migrations                      |
| notes                           |
| notifications                   |
| oauth_access_tokens             |
| oauth_auth_codes                |
| oauth_clients                   |
| oauth_personal_access_clients   |
| oauth_refresh_tokens            |
| object_groupables               |
| object_groups                   |
| password_resets                 |
| permission_role                 |
| permissions                     |
| personal_access_tokens          |
| piggy_bank_events               |
| piggy_bank_repetitions          |
| piggy_banks                     |
| preferences                     |
| recurrences                     |
| recurrences_meta                |
| recurrences_repetitions         |
| recurrences_transactions        |
| role_user                       |
| roles                           |
| rt_meta                         |
| rule_actions                    |
| rule_groups                     |
| rule_triggers                   |
| rules                           |
| sessions                        |
| tag_transaction_journal         |
| tags                            |
| transaction_currencies          |
| transaction_currency_user       |
| transaction_currency_user_group |
| transaction_groups              |
| transaction_journals            |
| transaction_types               |
| transactions                    |
| user_groups                     |
| user_roles                      |
| users                           |
| webhook_attempts                |
| webhook_messages                |
| webhooks                        |
+---------------------------------+
77 rows in set (0.000 sec)

[werdck]

Restoring from a backup helped, with extra steps:

1. docker compose down
2. docker run --rm -v "firefly-iii_firefly_iii_db-res:/temp" -v "/opt/docker/firefly-iii:/backup" ubuntu sleep infinity
3. [new Shell] docker exec -it bash
4. rm -rf /temp/* /temp/.*
5. cd /temp && tar xvf /backup/2025-02-14_firefly-iii-db-1.bakz
6. exit
7. docker stop
8. docker compose up -d

Doing the restore without deleting first did not work, failing with an "Read only filesystem" error

[JC5]

Thanks for sharing the solution, I would not have thought of that!