Bug #114524 | INSERT with JSON column error | ||
---|---|---|---|
Submitted: | 1 Apr 2024 12:36 | Modified: | 2 Apr 2024 9:17 |
Reporter: | Evgeniy Devyatykh | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.3.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[1 Apr 2024 12:36]
Evgeniy Devyatykh
[2 Apr 2024 8:50]
MySQL Verification Team
Hello Evgeniy Devyatykh, Thank you for the report and feedback. regards, Umesh
[2 Apr 2024 9:17]
Evgeniy Devyatykh
I forget to say that this issue reproduced for Connector/J 8.2.0, 8.1.0, 8.0.33 and for server 8.0.36 (but not for 8.0.35) that is why I have choose "Server" category but not "Connector/J".
[10 Jul 2024 13:28]
MySQL Verification Team
Bug #114833 marked as duplicate of this one.
[10 Sep 2024 15:22]
Morgan Cook
Adding some additional context around this bug, I was also seeing the above error for the use-case mentioned above, but I was using the node packages sequelize & mysql2 to connect to our mysql8 database. *Details:* MySQL Server - 8.0.36 sequelize - 6.4.0 mysql2 - 3.10.1 Additionally, I found another use-case to reproduce this issue: 1. Insert a new row into a table with a JSON column. 2. Update the JSON column to a null value. 3. Update a JSON column to a JSON value. This second update will fail. *Code to Reproduce:* const sequelize = new Sequelize('test', 'username', 'pw', { host: 'host', dialect: 'mysql' }); const BugTest = sequelize.define('bugtest', { s: { type: Sequelize.STRING }, j: { type: Sequelize.JSON } }); const newRow = await BugTest.create({ s: '1', j: {} }); await BugTest.update({ j: null }, { where: { id: newRow.id } }); // this 2nd update will fail await BugTest.update({ j: {} }, { where: { id: newRow.id } });
[25 Sep 2024 22:29]
Max Menchaca
Thanks for updating this bug Morgan Cook. We also use sequelize for this, here's our package versions using Node: *Details:* MySQL Server - 8.0.36 sequelize - 6.35.1 mysql2 - 3.11.3 This bug looks to pop up when trying to update an entry that has its JSON column set to 'null' to a non-null JSON object. This is even simpler than Morgan's case: Additionally, I found another use-case to reproduce this issue: 1. Insert a new row into a table with a JSON column. The JSON column is set to NULL. 2. Update the JSON column to a non-null value. Posting here on this issue, but it looks like this case actually works when posting raw SQL.
[7 Nov 2024 23:36]
Jeff Bocarsly
This error is also triggered when you insert a json string in binary format into a json column, e.g. This insert produces the error: INSERT INTO `mydb`.`mytbl` (`id`,`MyJson`) VALUES (1, x'7b22726f77223a207b2274797065223a202250222c202276616c7565223a20357d7d'); This insert runs: INSERT INTO `mydb`.`mytbl` (`id`,`MyJson`) VALUES (1, '{"row": {"type": "P", "value": 5}}');
[18 Nov 2024 9:52]
Giacomo Arru
Hello, this bug also affects me. Database version: 8.0.36 As a workaround, I had to disable these parameters from my JDBC connection string: cachePrepStmts=true useServerPrepStmts=true prepStmtCacheSize=256 prepStmtCacheSqlLimit=2048 reWriteBatchedInserts=true
[21 Nov 2024 17:28]
Max Menchaca
It looks like this issue is fixed by upgrading to MySQL 8.0.39.
[27 Mar 7:34]
Xiaocong Ding
8.0.41 and 8.4.2 still have similar problem. It can be repeated by followsing SQL: set names utf8mb4; create table t1 (id int auto_increment primary key, c1 varchar(255), c2 json null); prepare it from 'insert into t1(c2, c1) values(?, ?)'; set @a=0; set @b=2; execute it using @b, @a; set @b='{}'; # Unexpectedly fails with ERROR 3144 execute it using @b, @a;
[16 Jun 11:58]
Filipe Silva
If any, this is an issue within the MySQL Server, not Connector/J. Re-classified as MySQL Server: Optimizer.