Modifying MySQL table may break relationships

[ksstms]

Checklist

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

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 3.2.1 (also saw the same on 2.33.2)
  • App Version: 3.2.1

Describe the bug

I'm using MariaDB and have two tables in a one-to-many relationship. If I change a column's value on the "one" side, the "many" side table also gets updated, and some relationships break. This happens both in the data view and using a form or table in the application.

See screenshots for a minimal example. bar.foo_id is a foreign key referencing foo.id. In the data view I changed two values in the name column of foo, which broke most relationships between the tables.

In the MariaDB log you can clearly see that after updating the desired value in foo, there are additional queries for each corresponding row in bar. One sets the foreign key to NULL, the other sets it to the original value. Depending on the order, the relationship may or may not get lost.

See unedited log here: db.log

363 Query   with `paginated` as (select `a`.* from `foo` as `a` order by `a`.`name` asc, `a`.`id` asc limit 101) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`name` asc, `a`.`id` asc
364 Query   with `paginated` as (select `a`.* from `bar` as `a` order by `a`.`name` asc, `a`.`id` asc limit 101) select `a`.*, (select json_arrayagg(json_object('id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `foo` as `b` where `b`.`id` = `a`.`foo_id` order by `b`.`id` asc) as `foo_bb` from `paginated` as `a` order by `a`.`name` asc, `a`.`id` asc
365 Query   with `paginated` as (select `a`.* from `foo` as `a` order by `a`.`name` asc, `a`.`id` asc limit 101) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`name` asc, `a`.`id` asc
366 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = '3', FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

--- This is expected
367 Query   update `foo` set `name` = 'ccc' where COALESCE(`foo`.`id` = '3', FALSE)

367 Query   select * from `foo` where COALESCE(`foo`.`id` = '3', FALSE) order by `foo`.`id` asc limit 1
368 Query   select * from `bar` where COALESCE(`bar`.`foo_id` = '3', FALSE) order by `bar`.`id` asc limit 5000

--- These are unexpected
369 Query   update `bar` set `foo_id` = '3' where COALESCE(`bar`.`id` = 7, FALSE)
370 Query   update `bar` set `foo_id` = NULL where COALESCE(`bar`.`id` = '7', FALSE)

371 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = '3', FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc
372 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = '2', FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

--- This is expected
373 Query   update `foo` set `name` = 'bbb' where COALESCE(`foo`.`id` = '2', FALSE)

373 Query   select * from `foo` where COALESCE(`foo`.`id` = '2', FALSE) order by `foo`.`id` asc limit 1
374 Query   select * from `bar` where COALESCE(`bar`.`foo_id` = '2', FALSE) order by `bar`.`id` asc limit 5000

--- These are unexpected
375 Query   update `bar` set `foo_id` = '2' where COALESCE(`bar`.`id` = 4, FALSE)
376 Query   update `bar` set `foo_id` = NULL where COALESCE(`bar`.`id` = '4', FALSE)
377 Query   update `bar` set `foo_id` = '2' where COALESCE(`bar`.`id` = 6, FALSE)
378 Query   update `bar` set `foo_id` = NULL where COALESCE(`bar`.`id` = '6', FALSE)
379 Query   update `bar` set `foo_id` = NULL where COALESCE(`bar`.`id` = '5', FALSE)
380 Query   update `bar` set `foo_id` = '2' where COALESCE(`bar`.`id` = 5, FALSE)

381 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = '2', FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('foo_id',`b`.`foo_id`,'id',`b`.`id`,'name',`b`.`name`) LIMIT 500) from `bar` as `b` where `b`.`foo_id` = `a`.`id` order by `b`.`foo_id` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

To Reproduce Steps to reproduce the behavior:

1. Create two tables with a one-to-many relationship in MariaDB
2. Fetch them into Budibase
3. Change a value in the name column of foo
4. See if relationships disappear.

Expected behavior Only the one table is updated, and there's no change to the other one at all.

Screenshots

Before:

After:

Desktop (please complete the following information):

• OS: Ubuntu 22.04
• Browser Chrome
• Version 129.0.6668.70

[linear[bot]]

BUDI-8836 Modifying MySQL table may break relationships

[ksstms]

I tried creating the same two tables through Budibase. That case works fine, and the SQL log shows that Budibase is not updating the bar table at all:

276 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = 3, FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('id',`b`.`id`,'name',`b`.`name`,'fk_foo_bar_bb',`b`.`fk_foo_bar_bb`) LIMIT 500) from `bar` as `b` where `b`.`fk_foo_bar_bb` = `a`.`id` order by `b`.`fk_foo_bar_bb` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

277 Query   update `foo` set `name` = 'ccc' where COALESCE(`foo`.`id` = 3, FALSE)

277 Query   select * from `foo` where COALESCE(`foo`.`id` = 3, FALSE) order by `foo`.`id` asc limit 1
278 Query   select * from `bar` where COALESCE(`bar`.`fk_foo_bar_bb` = 3, FALSE) order by `bar`.`id` asc limit 5000
279 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = 3, FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('id',`b`.`id`,'name',`b`.`name`,'fk_foo_bar_bb',`b`.`fk_foo_bar_bb`) LIMIT 500) from `bar` as `b` where `b`.`fk_foo_bar_bb` = `a`.`id` order by `b`.`fk_foo_bar_bb` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc
280 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = 2, FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('id',`b`.`id`,'name',`b`.`name`,'fk_foo_bar_bb',`b`.`fk_foo_bar_bb`) LIMIT 500) from `bar` as `b` where `b`.`fk_foo_bar_bb` = `a`.`id` order by `b`.`fk_foo_bar_bb` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

281 Query   update `foo` set `name` = 'bbb' where COALESCE(`foo`.`id` = 2, FALSE)

281 Query   select * from `foo` where COALESCE(`foo`.`id` = 2, FALSE) order by `foo`.`id` asc limit 1
282 Query   select * from `bar` where COALESCE(`bar`.`fk_foo_bar_bb` = 2, FALSE) order by `bar`.`id` asc limit 5000
283 Query   with `paginated` as (select `a`.* from `foo` as `a` where COALESCE(`a`.`id` = 2, FALSE) order by `a`.`id` asc limit 5000) select `a`.*, (select json_arrayagg(json_object('id',`b`.`id`,'name',`b`.`name`,'fk_foo_bar_bb',`b`.`fk_foo_bar_bb`) LIMIT 500) from `bar` as `b` where `b`.`fk_foo_bar_bb` = `a`.`id` order by `b`.`fk_foo_bar_bb` asc) as `bar_bb` from `paginated` as `a` order by `a`.`id` asc

[adrinr]

Hello @ksstms. Trying to replicate your error, but not being able to reproduce. Could you please:

1. Share the SQL schema you are using (column types and constrains included) https://docs.budibase.com/docs/troubleshooting-sql#sql-schema-export
2. Share how you define the relationship once both tables are fetched in Budibase. Providing a data export of the app with the issue would be really helpful

[ksstms]

@adrinr Thanks for looking into this. I struggled a lot reproducing it again, because I experimented with creating the tables through BB and overwrote the original one.

Looks like it works fine if the id column types are int. I can't remember what I used originally, but for example if I set them to be of type bigint unsigned the issue is present.

App export

Schema export:

CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci */
CREATE TABLE `bar` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `foo_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bar_foo_FK` (`foo_id`),
  CONSTRAINT `bar_foo_FK` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci

Let me know if you need more info.

EDIT: I got suspicious of the column type because in the original log I saw things like:

where COALESCE(`foo`.`id` = '3', FALSE)

But when I created the tables with int, I got queries like:

where COALESCE(`foo`.`id` = 3, FALSE)

[mike12345567]

Hi @ksstms closing this issue as we believe it is related to one central problem we are tracking, we have created this issue which you can use to track progress on the fix for this issue.

[ksstms]

Thanks @mike12345567 Btw for the meantime is there any recommended, known-good type for ids?