Errors on budget download: Column "long_goal" at table "zero_budgets" not found

sondregronas commented 1 month ago

Checks

[X] I have checked that this issue has not already been reported.
[X] I have confirmed this bug exists on the latest version of actualpy.

Reproducible example

Edit: See below, I'm unfortunately not sure how or why the long_goal column got into the db, but this is the snippet that's causing the issue:

from actual import Actual

with Actual(base_url=ACTUAL_URL, password=ACTUAL_PWD, encryption_password=ACTUAL_ENCRYPTION_PASSWORD,
            file=ACTUAL_FILE) as actual:
    ...

same happens with

...
    actual.set_file(actual.list_user_files().data[0])
    actual.download_budget()

Log output

Traceback (most recent call last):
  File "D:\Userdata\Documents\GitHub\actualbudget-utils-nor\src\main.py", line 71, in <module>
    with Actual(base_url=ACTUAL_URL, password=ACTUAL_PWD, encryption_password=ACTUAL_ENCRYPTION_PASSWORD, file=ACTUAL_FILE) as actual:
  File "D:\Userdata\Documents\GitHub\actualbudget-utils-nor\.venv\Lib\site-packages\actual\__init__.py", line 91, in __enter__
    self.download_budget(self._encryption_password)
  File "D:\Userdata\Documents\GitHub\actualbudget-utils-nor\.venv\Lib\site-packages\actual\__init__.py", line 328, in download_budget
    self.sync()
  File "D:\Userdata\Documents\GitHub\actualbudget-utils-nor\.venv\Lib\site-packages\actual\__init__.py", line 358, in sync
    self.apply_changes(changes.get_messages(self._master_key))
  File "D:\Userdata\Documents\GitHub\actualbudget-utils-nor\.venv\Lib\site-packages\actual\__init__.py", line 275, in apply_changes
    raise ActualError(
actual.exceptions.ActualError: Actual found a column not supported by the library: column 'long_goal' at table 'zero_budgets' not found

Issue description

Hello!

I'm very new to using ActualBudget; I am running the edge (also tested on latest, but didn't clear the database or test with a different file) and I'm getting an error message when trying to get transactions with the API. My instance is pretty much brand new with some bank connections attached. When I try to connect to / download a budget in the API I get the following error:

actual.exceptions.ActualError: Actual found a column not supported by the library: column 'long_goal' at table 'zero_budgets' not found

I am unsure where or how the column is generated or where zero_budgets is from, but I didn't do much digging. I did at one point enable all experimental features in Actual, but I'm only using Monthly Spending, Goal Templates and Iterable topological sort budget

If I do not pass a file it will connect and let me list the budgets, but not download any of them (results in the same error).

Modifying lines 274-278 of init.py from:

if column is None:
    raise ActualError(
        f"Actual found a column not supported by the library: "
        f"column '{message.column}' at table '{message.dataset}' not found"
    )

to

if column is None:
    continue

let's me use the API as expected with seemingly no consequence, but I wouldn't know if an exception is warranted (I've only tried reading and creating transactions).

Just figured I'd share, I'm unfortunately not sure how to properly recreate it or what to even look for 😄

Expected behavior

Being able to connect even with the long_goal column present

Installed versions

actualpy version: 0.2.0
Actual Server version: v24.7.0

bvanelli commented 1 month ago

Hello @sondregronas ,

It seems like you are running edge version of Actual, and those new tables are being used. Here are the new tables https://github.com/actualbudget/actual/blob/653a0ab104ab62ad629ff47c0f894d6601ce3a22/packages/loot-core/migrations/1720665000000_goal_context.sql#L4 , they were added 3 days ago and they are apparently in use and having data.Actualpy then has two choices:

let you use the database, but with incomplete data, possibly adding corrupt entries to the remote if the column is not nullable
fail early when the model does not match

I added a test to prevent new versions on the tests that do not have a column from the remote, but it of course only works with the released versions.

My proposals are:

continue failing with wrong models, but
when starting in non strict mode, there will be a warning stating that the column does not exist, if some is received. The budget will then be started on read only mode

In general I would not support unreleased versions, at least as of now, but I could also introduce a pipeline that tracks those changes.at some point.

sondregronas commented 1 month ago

Perfectly reasonable - maybe it could be an idea to keep track of which table gets affected inside the session variables, log a warning, and only raise the exception whenever that table gets interacted with?

Though I'm sure that's probably not even worth the effort :)

bvanelli commented 1 month ago

I believe this is what I already do, but if any unknown column comes on the sync call then it raises an exception.

@sondregronas one workaround is to actually do a Reset sync on the advanced options. This should make sure the sync does not contain any unknown columns.

I guess I can also implement the unknown columns with the sqlalchemy reflect option at some point too.

I'll take a look when I'm available again on how to solve this best.

sondregronas commented 1 month ago

Can confirm resetting the sync works (for me). Thanks!

bobokun commented 1 month ago

Upgraded to v24.8.0 and was experiencing the same issue. Resetting the sync fixed it! Thanks

bvanelli commented 1 month ago

After working on this issue for almost one hour, I have to think a bit more about what to do...

The main issue is that I intended to support multiple versions on Actual, but sqlalchemy emit statements for all columns when doing an insert. For example, when inserting a payee:

E       [SQL: INSERT INTO payees (id, name, category, transfer_acct, favorite) VALUES (?, ?, ?, ?, ?)]
E       [parameters: ('835983d9-1e5e-4c24-bca5-55acea558f2c', None, None, '99293e48-4f92-4d92-a1f1-cd4f4c126d22', None)]

This code works fine for actual 24.8.0, but not for all other versions that do not contain this column.

I tried using sqlalchemy deferred column but that is only effective on selects, not inserts.

I created https://github.com/bvanelli/actualpy/pull/54 initially just with the model changes and I'll try to find a permanent solution to it.

bvanelli commented 3 weeks ago

let's me use the API as expected with seemingly no consequence, but I wouldn't know if an exception is warranted (I've only tried reading and creating transactions).

I decided to split the issues to make them more manageable, so https://github.com/bvanelli/actualpy/issues/58 will now tackle forward compatibility when small changes are made to the model (i.e. a new column).

https://github.com/bvanelli/actualpy/pull/54 should then solve this issue, but unfortunatelly until I find a solution for the multi-model database the latest version of actualpy can only support 24.8.0 version of Actual server.

bvanelli / actualpy