🐛 Bug: Can't sync metadata after deleting a column or row

[modularizer]

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Steps to reproduce ?

I did the following four things to the postgres database using PgAdmin:

1. added a new table named "apples"
2. modified the table "bananas" to "cats"
3. created a view named bananas which selects from the table cats CREATE VIEW FROM "bananas" AS SELECT * from "cats";
4. renamed the column "exact_age" in the table "dogs" from "exact_age" to "finite_age"
5. added a new table "grapes"

Then, I reloaded NocoDB and wen to Team & Settings > Data Sources > Sync Metadata. This correctly showed in red the new table "apples", the deleted column "exact age" and showed "bananas" as a deleted table. When I clicked Sync Now, I got the error: "Expected espression after * at character 14".

The change # 1 (new table) worked. For change # 2 and # 3 , "cats" showed up as a new table, but the table "bananas" still exists and the view "bananas" does not. For change # 4, "finite_age" showed up as a new column but "exact_age" was not removed correctly. Change # 5 worked.

The reload menu still shows in the that table "bananas" was deleted and column "exact age" was deleted, but Sync Now fails every time. I tried restarting my noco process and also tried uninstalling and reinstalling noco, but neither fixed the problem.

On the backend in postgres all changes worked correctly.

Desired Behavior

Columns and tables should delete correctly, and no matter what changes have been made to the database schema, you should always be able to sync the changes in NocoDB without creating a new project.

Project Details

Node: v18.11.0 Arch: x64 Platform: linux Docker: false RootDB: pg PackageVersion: 0.101.0-beta.0

Attachements

[wingkwong]

1. Updated the issue description. Please add a space after # next time as this would create the reference to the issue with that issue number after #.
2. In step 2, modified the table "bananas" to "cats", do you mean renaming?
3. Schema changes done outside UI is expected not to show as you need to sync it manually first.
4. Does your table includes formula fields?

[modularizer]

Yes I meant I renamed the table. And yeah, I'm aware schema changes done outside of the UI don't automatically update the UI, the issue is I get an error which I try to resync metadata through Table & Settings > Data Sources > Sync Metadata > Sync Now.

No, I don't have any formula fields on the tables in question.

[pranavxc]

@modularizer Can you share the column list and its type? a screenshot of the fields menu will be okay.

[modularizer]

I don't want to share the exact columns or setup of my data, but I did want to let you know that I was wrong and I did actually have formula fields which had been broken and hidden while back in the table in which I was unable to rename a column successfully.

I was unable to delete these formula fields or edit their formulas from noco ui, so I had to go into the public.nc_col_formula_v2 to modify the formula and formula_raw columns of records which contained broken formulas to something simple ('CONCAT("a", "b")'). Once I saved this and restarted noco, I was able to delete the formula columns and then the metadata sync was able to successfully rename the other column. It would be nice if there was a more straightforward way of resolving broken formulas via the UI without having to dive into noco's internal database storage.

Overall, I was able to resolve all metadata sync issues by diving into noco's internal postgres storage and manually deleting references to the tables and columns which were broken. I can't really give any more details of my exact data structure or the exact steps I followed to fix the issues, so at this point I don't expect to be able to pinpoint the exact bug.

[wingkwong]

It would be nice if there was a more straightforward way of resolving broken formulas via the UI without having to dive into noco's internal database storage.

I'm working on this part and hopefully it will be available in the next release.

[wingkwong]

Handled in #4776. Please try on the latest version.