Is there a bug in mysql 5.7 while processing json data types?

Question

I am working on laravel project using mysql. Recently I updated my ubuntu and after that I am getting error while fetching data from mysql db using json_extract() method in where clause.

Until today I was able to fetch data using below code in laravel model:

return self::where([
            'transaction_details->compensation_status' => Compensation::GIVEN,
            'transaction_type' => self::COMPENSATION,
            'transaction_details->compensation_type' => Compensation::MANUAL
        ])->orderBy('updated_at')->get();

Here transaction_details column holds the json data, while transaction_type is an integer column.

So above code when dumped, it gives me the SQL query:

select * from `transactions` where (json_unquote(json_extract(`transaction_details`, '$."compensation_status"')) = 1 and `transaction_type` = 7 and json_unquote(json_extract(`transaction_details`, '$."compensation_type"')) = 2) order by `updated_at` asc

And when I run this query I am getting some unexpected error:

#3141 - Invalid JSON text in argument 1 to function json_extract: "Missing a comma or '}' after an object member." at position 196.

I also tried make this query shorter, keeping only one condition so that I can get rid of any error at position 196.

select * from `transactions` where json_unquote(json_extract(`transaction_details`, '$."compensation_status"')) = 1 order by `updated_at` asc

Now this query do not have anything at position 196 but still the same error.

I guess this the bug in latest mysql.

Please do help me in this regards.

EDIT: This is the json data the transaction_details holds

{"message":"fgdfgdfg","referral_compensation_date":"2019-11-30","compensation_status":1,"compensation_type":2,"allocated_count":"-NA-","daily_job_count":"-NA-","allocation_date":"2019-11-30"}

have to checked this ? https://stackoverflow.com/questions/33513625/invalid-json-text-in-argument-2-json-contains-in-mysql-5-7-8 — Kamlesh Paul, Jan 29 '20 at 12:37
Have you checked if the data itself isn't corrupted/malformed? — Florian Humblot, Jan 29 '20 at 12:40
json_extract() need json object or array and your are giving `(transaction_details, '$."compensation_status"')` this that's why https://database.guide/json_extract-return-data-from-a-json-document-in-mysql/ — Kamlesh Paul, Jan 29 '20 at 12:41
I have updated my question, added the json data which is I am concerned — Dinesh Suthar, Jan 29 '20 at 12:43
have you checked near `Missing a comma or '}' after an object member` .? — Kamlesh Paul, Jan 29 '20 at 12:48
Yes I checked, even JSON_VALID() returns 1 when checking the data. — Dinesh Suthar, Jan 29 '20 at 12:53
Does this answer your question? [Invalid JSON text in argument 2 - json\_contains in MySQL 5.7.8](https://stackoverflow.com/questions/33513625/invalid-json-text-in-argument-2-json-contains-in-mysql-5-7-8) — Déjà vu, Jan 29 '20 at 13:04
I tested your SQL query with the JSON value you show, but I get no error. Tested on MySQL 5.7.27. — Bill Karwin, Jan 29 '20 at 15:15

Akina · Accepted Answer · 2020-01-29T13:06:58.363

1

Your query is fully correct and is executed without problems with shown sample data on MySQL 5.7.

fiddle

The problem is NOT in a query. Check the data you're processing. Look for a comma in a value, non-printed char, special chars, errorneously truncated values, etc.

Try to find one problematic record (add a condition by ID range, process a half of records, a quarter, ... until one record caused error found). Copy this record to separate table, ensure it is still problematic, backup this table, and share backup script.

edited Jan 29 '20 at 13:06

answered Jan 29 '20 at 12:59

Akina

39,301
5
14
25

Thank you, it has brought some relief for me. I shall check it again in my system as per the steps you mentioned. I shall revert you back tomorrow morning. – Dinesh Suthar Jan 29 '20 at 17:19
This is completely strange phenomena. Yesterday that query was not at all running but today it is running as expected.May be my system was needed to be booted after ubuntu software updation. Thank you again for providing the dbfiddle. – Dinesh Suthar Jan 30 '20 at 05:25

Is there a bug in mysql 5.7 while processing json data types?

1 Answers1