Broken linked row leads to DtableDb Server Error / Tutorial on how to fix

[AkDk7]

Hi @ll.

Installation: Self-hosted seatable-enterprise, version 3.5.10

The problem:
I’m having a strange issue after adjusting some columns in a big base with many formulas and python scripts.

So, from one second to the other the sql queries plugin and python script which uses sql queries didn’t work anymore. I’ve enabled debug for dtable-db because I’ve seen a suspicious line in the dtable-db.log file. It seems that the backend cannot parse it’s own json for this base anymore. In any other base I am still able to run sql queries with the sql queries plugin or python script.

Here are the logs:

==> seatable-data/seatable/logs/dtable-db.log <==
[2023-05-09 13:56:51] [debug] [116] fetching dc base 6b822169-3779-4809-a048-9316b4661d27

==> seatable-data/seatable/logs/dtable-access.log <==
[2023-05-09 13:56:51] [INFO] requestTime - GET /dtables/6b822169-3779-4809-a048-9316b4661d27?from=dtable-db 200 18.0ms

==> seatable-data/seatable/logs/dtable-db.log <==
[2023-05-09 13:56:51] [debug] can't unmarshal base 6b822169-3779-4809-a048-9316b4661d27 from dtable server. json string around the error position is:
[2023-05-09 13:56:51] [debug] Tua2M9IIl85AOA"],"CheYJouDS5uuXii9jKw4BA":["XY3fF6-KTua2M9IIl85AOA"],"TTZdTql1QFObboT3OaDlKw":["XY3fF6-KTua2M9IIl85AOA"],"d1KcEy9ISCWvwPNB8EX11w":["XY3fF6-KTua2M9IIl85AOA"],"cz8_19tATQaaANyHtu3azw":[{"0000":"2023-04-09","Mpj4":25200,"L90h":50400,"v0pl":0,"_id":"Xc5Z9N9iSXaUOlT42GDd0g","_ctime":"2023-05-08T11:10:11.322+00:00","_mtime":"2023-05-08T11:10:11.322+00:00"}],"V9TAqP2QT3W_Br5pX8jm-w":["cz8

==> seatable-data/seatable/logs/dtable-db-access.log <==
[2023-05-09 13:56:51] - "e5ad4d695d36416f964257b0915fdb0e@auth.local" - "6b822169-3779-4809-a048-9316b4661d27" - "SQL:[select * from Logbuch]" - "failed" - 0.061000

==> seatable-data/seatable/logs/dtable-db.log <==
[2023-05-09 13:56:51] [error] POST /api/v1/query/6b822169-3779-4809-a048-9316b4661d27: json: cannot unmarshal object into Go struct field Link.links.table1_table2_map of type string

==> seatable-data/seatable/logs/dtable-server.log <==
[2023-05-09 13:56:51] [ERROR] dtable-db-utils.js[19] - dtable-db error: AxiosError: Request failed with status code 500

==> seatable-data/seatable/logs/dtable-access.log <==
[2023-05-09 13:56:51] [INFO] requestTime - POST /api/v1/dtables/6b822169-3779-4809-a048-9316b4661d27/query/ 500 66.3ms

The analyzis:
If you ever get this error and wonder why you don’t get any rows back from the query (sql plugin or python scripts) this could be very painful to figure out which record is broken. I had to go through every table.

There was one row that was connected to a deleted linked record which represents like this:

With the help of the DEBUG logging flag and this json line I could figure out which row id I had to find.

[2023-05-09 13:56:51] [debug] Tua2M9IIl85AOA"],"CheYJouDS5uuXii9jKw4BA":["XY3fF6-KTua2M9IIl85AOA"],"TTZdTql1QFObboT3OaDlKw":["XY3fF6-KTua2M9IIl85AOA"],"d1KcEy9ISCWvwPNB8EX11w":["XY3fF6-KTua2M9IIl85AOA"],"cz8_19tATQaaANyHtu3azw":[{"0000":"2023-04-09","Mpj4":25200,"L90h":50400,"v0pl":0,"_id":"Xc5Z9N9iSXaUOlT42GDd0g","_ctime":"2023-05-08T11:10:11.322+00:00","_mtime":"2023-05-08T11:10:11.322+00:00"}],"V9TAqP2QT3W_Br5pX8jm-w":["cz8

The row id I had to find was: cz8_19tATQaaANyHtu3azw

So I wrote a small python script to find the correct row.

from seatable_api import Base, context

server_url = context.server_url or ''
api_token = context.api_token or ''
base = Base(api_token, server_url)
base.auth()

rows = base.list_rows('TABLE_NAME', view_name=None, limit=None)

# cz8_19tATQaaANyHtu3azw
for row in rows:
    if row['_id'] == 'cz8_19tATQaaANyHtu3azw':
        print(row)

The solution:

1. I removed the link with the message “Die verknüpfe Zeile wurde gelöscht” which should be translated to “The related row was deleted” or something like that.
2. Unfortunately this didn’t resolve the problem and I duplicated the row and removed the old one. By this way I was able to save all the linked rows and values and now it’s working again.

To the SeaTable team:
It seems like if you run a python script there is a small possibility that somehow an invalid link appears. I don’t know how that could happen.
My appeal to the SeaTable team is: Could you eventually think about adding a validation algorithm for linked columns? Thank you very much!

Regards!
AkDk7