Cannot update column value with composite primary key and JSON column

[davidcv91]

Describe the bug
I cannot edit any column value when table has a composite key and a JSON column

To Reproduce
Steps to reproduce the behavior:

1. Create the following table:

CREATE TABLE `tmp_bug_antares` (
  `product_id` char(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `category_id` char(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `status` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  `images` json NOT NULL,
  PRIMARY KEY (`product_id`,`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci

2. Insert a record

INSERT INTO `tmp_bug_antares` (`product_id`, `category_id`, `status`, `images`) VALUES ("18ff3cc2-4ee9-4fc0-ade2-d2b9b5ee6120","8b3431fd-8663-4180-9f1d-91553484ec09","test","[]");

3. Try to edit any field
4. Error is displayed and changes are not applied ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 1")

Expected behavior
Field is updated with value typed

Screenshots
https://github.com/user-attachments/assets/d688d6d7-6229-4c5b-91a3-145a9b3feca8

Application:

• App client: MySQL
• App version: 0.7.30-beta.1
• Installation source: Snap

Environment:

• OS name: Ubuntu
• OS version: 24.04.1 LTS
• DB name: MySQL Community Server
• DB version: 8.0.37

Additional context
Works fin if JSON column is removed or if there is only a single primary key column

[dyaskur]

This might be a bit related to my PR #919 . We need to encode to json first. @Fabio286, are you currently working on it? If not, would it be okay for me to take a shot at fixing it?

[Fabio286]

This might be a bit related to my PR #919 . We need to encode to json first. @Fabio286, are you currently working on it? If not, would it be okay for me to take a shot at fixing it?

Hi @dyaskur, I haven't started working on it yet.
If it's not a problem for you to try to fix I would be very grateful!

[dyaskur]

I just made an update in PR #920

I've tested the update on MySQL and PostgreSQL, as these are the databases I currently have available.

For MariaDB, JSON data is stored as LONGTEXT, so the update should work as long as the data is saved with the same formatting.

As for SQLite and Firebird, it appears they do not natively support JSON columns. So I believe it should be fine.