Foreign key relationship column value got lost in MySQL when using Budibase API to POST or PUT its relationship parent row

[likke812]

Checklist

• [x] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: k8s
  • Budibase Version: 2.13.48
  • App Version: 2.13.48

Describe the bug Budibase is connecting to MySQL DB with several tables. One table called "bridge_domain" and another table called "epg". There has relationship defined one to many from "bridge_domain" table("id" column as primary key) to "epg" table("fk_bridge_domain_epg" column as foreign key). This works fine with correct relationship on Budibase screen.

But when I tried to use Budibase API to POST(create) or PUT(update) the row at "bridge_domain" table, the foreign key column value - "fk_bridge_domain_epg" will be deleted while in the API request I don't have any change to "bridge_domain" "id" column...

To Reproduce Steps to reproduce the behavior:

1. Create MySQL tables with columns and foreign keys
2. Budibase connects to MySQL database
3. Define relationship at Budibase
4. Use Budibase API(https://docs.budibase.com/reference/rowupdate) to update or overwrite parent's table row, then the foreign key relationship column value will be lost

Expected behavior Update or overwrite parent's table row will not impact foreign key relationship column value

Screenshots One-to-many relation:

App Export If possible - please attach an export of your budibase application for debugging/reproduction purposes.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context Add any other context about the problem here.

[linear[bot]]

BUDI-8339 Foreign key relationship column value got lost in MySQL when using Budibase API to POST or PUT its relationship parent row

[ConorWebb96]

Hey @likke812,

You are a quite a few versions behind of the current release. Can you update to the latest and see if this issue still persists?

[likke812]

Hey @likke812,

You are a quite a few versions behind of the current release. Can you update to the latest and see if this issue still persists?

Hi @ConorWebb96,

I have tried to update my Budibase platform and APP to the latest version (v2.28.4) last Friday and tried the same POST or PUT request. There still has issue but a bit different. I can not POST or PUT the same request to the parent's table row and it returns with 400 error:

Error message during task - "Add/Update Bridge Domains (BD's)": "json": { "message": "Cannot convert undefined or null to object", "status": 400 }

I have to revert back to my previous Budibase version(v2.13.48) so that the API works but the foreign key column value will be lost...

I can send my API request and MySQL DB schema to support if necessary.

[ConorWebb96]

Hey @likke812,

I haven't been able to replicate this behaviour on the latest version of Budibase. Heres the request being sent showing it working and that it doesn't override the primary/foreign keys of the row when it does.

[likke812]

Hi @ConorWebb96 ,

I exported the MySQL DB schema I am using and attached here.

MySQL-dump.zip

I am not sure the different of Budibase API dealing with MySQL and PostgreSQL you are using but I guess the may have difference.

Let me take an example of my scenario.

In the "bridge_domain" table, I have one row as below:

And this row's "id"(719) has relationship as foreign key to "epg" table row as "fk_bridge_domain_epg" like below:

I am using Postman or Ansible to send a PUT request to Budibase API to update id 719 of bridge_domain row like below:

curl --location --request PUT 'https://XXX/api/public/v1/tables/datasource_plus_4fa6dba7b9d14b388ef2bb5bd23df409__bridge_domain/rows/719' \ --header 'x-budibase-api-key: XXX' \ --header 'x-budibase-app-id: app_05a2b84bf2724886b5c5ff7fd28363c6' \ --header 'Content-Type: application/json' \ --header 'Cookie: XXX' \ --data '{ "bd_name": "bd_test_1", "alias": "bd_test_1_alias", "description": "BD Test 1", "routing": 1, "state": "Installed" }'

Then the foreign key column - "fk_bridge_domain_epg" value(719) at epg table is lost. I will have to input it by manual.

If I upgrade Budibase to the newer version (v2.28.4), then there will be error response when I send out the PUT request as I have described in the previous comment.

[ConorWebb96]

Hey @likke812,

I've used SQL this time. Plus your schema and I'm still unable to replicate the issue you are experiencing, below is a gif showing that no relationships are being wiped. I'm now on 2.29.2.

[likke812]

Hi @ConorWebb96,

Thank you for your updating for this and tried to emulate this scenario.

I am on vacation without company's laptop on hand. I will try to upgrade budibase to v2.29.2 and then redo the test when I am back to office in the middle of July.