Error decoding JSON data: Syntax error after update to 3.6.3

[Mzlaki]

Ok, mbabker, Joomla reports database ok. I'd have to search it manually and find the error. I, for one, wouldn't know what to look for. That's why I'm using CMS, because I'm not a programmer and this is way beyond my knowledge.
It would be easier for me to manually copy my K2 items into Joomla core articles, for example.
Do you know exactly what I should look for and where?

[mbabker]

Unfortunately I can't say what specifically to look for. There are a lot of columns that may contain JSON formatted data just within the core database tables, and it's hard to say what extensions that add database tables may be doing too. The tool posted earlier in this thread will help with a couple of common patterns, but it doesn't catch every possible error. As I hinted at the only way to really fix it would be to script something that can check every field known to store JSON data in every database table, attempt to decode it, and log some kind of message so you could manually fix it (i.e. I saw one example where the user parameters field had become corrupted with three levels of recursive data; clearing the field would fix it but you'd also clear the user's parameters).

Yes I get the whole concept of the CMS being a tool to help people. Unfortunately it can't do everything. The core APIs are pretty good at not inserting invalid data, but it's also pretty easy to mess things up based on local environment settings or transferring data between platforms. Hence the reason there's a need to check for bad data. Blissfully ignoring it keeps things working, but can have unintended side effects (going back to that user parameter example, if your profile had set a different timezone or editor to use, you'd expect those to be respected, but when the JSON structure is invalid they can't be, which means times are displaying incorrectly for your account as one possible side effect).

[robwent]

The original script just replaced the bad empty params by pattern matching.

There is a second test which will try and decode any data saved in any column named 'params' or 'rules' and report the ones that are invalid.

If there are issues in other columns then it just needs to be added to the script but my original thoughts were about memory and the initial checks seem to be fixing things for most people.

It's pretty harmless to try. You can see some screenshots here: https://robertwent.com/blog/joomla/102- ... omla-3-3-6

Better than nothing...

[mbabker]

Looking over that script, it's going to fix the majority of issues. But because of the scope it goes into it couldn't be plugged into core's update process because of resource concerns. I'd definitely suggest using it if you need to, but just don't walk into the situation thinking it'll fix everything for you.

[robwent]

I just made it to try and point out where the issue might be.

If it's not Joomla getting the blame, it's the template

[strix6]

The cleanup tool fixed the problem for me.. it's worth trying..

[mojito]

Thanks mbabker for your efforts on this and helping to explain what it is about. I am appreciative.
Urm ...you make some valid points but surely there is some kind of try catch for php ? Ok if this doesn't run in the update we could use a script that can detect what fields contain invalid JSON.
@joomlateam
I agree its better to weed that stuff out. But taking the backend down for a lot of beginner webmasters is bad. They are also your users/customers and they need more training wheels - for them going into a database is difficult.

[naghris]

robwent script fixed 2 affected sites of mine. Thanks!

[mojito]

Ok and also thank you to Brent Laminack your solution is the best for me. I found the culprit was also {""} so any modules with this you need simply to delete the params content = {""} then I got back in to my backend.
Look back to Brent Laminack's solution.

[robwent]

If you run the script it will fix the {""} fields automatically.

Brent's post was where I got the idea from to write it from (Thanks Brent!).

You shouldn't just delete the fields as it put's Joomla into legacy mode (J1.5), you should replace them with either {} or {"":""}

[mojito]

ok thanks for that clarification. Not sure how it can send joomla into some kind of legacy mode ? But ok...curious is all..
I guess I will go into them and resave should create them correctly. Thanks everyone.

[robwent]

I'm not totally sure but it was mentioned by a lead developer at rockettheme - https://issues.joomla.org/tracker/jooml ... ent-211096

I assume if the parameters field is empty it thinks an ini file should exist with the parameters inside, but if it contains {} then the field isn't empty but the param contents are.
ACL Manager will also replace empty fields with {} when it checks the asset table.

[mbabker]

Urm ...you make some valid points but surely there is some kind of try catch for php ?

There is, but realize there are a lot of uses of the Registry class and the formatter API within it so even if we were to try and throw a patch together it wouldn't be a quick process. It also wasn't expected that there would be such damaged database records.

On the whole "legacy" mode thing, the JSON formatter makes a check of the string it's about to try and decode. The first character must be { and the last character must be } and if that check isn't passed then the JSON formatter will hand the data over to the INI formatter and try to handle it that way since at that point it has already determined it cannot be a valid JSON string. So from that regard there is one sanity check in place but that's really a bare minimum thing.

[robwent]

That makes sense. So basically, things will only break if the field contents start and end with {} but the contents aren't valid.
Which would be why serialized arrays don't cause issues?
If that's the case I can update my checks to only look for fields that start and end with the curlies.

Thanks for the clarification.

[mojito]

Thanks guys for the extra info. Here is an image of what was wrong in my db for others ref. It may be these that are an issue for others. http://cambs.eu/blog/entry/this-week-in ... web-design

[robwent]

I've just updated the script to use the same checks as Joomla for json data.

So now, when you run the full check it should only report rows that will definitely cause Joomla to throw an error.

It also fixes an issue with servers that have php short tags disabled and an issue where the reported row id was totally wrong.

Please open an issue on github if you notice any problems.

[habehako]

It would be much easier to find the error in the database in "params", if not only a message but also the incorrect data are visible in output.

Program: ./libraries/vendor/joomla/registry/src/Format/Json.php
Function: stringToObject
actual Code: throw new \RuntimeException(sprintf('Error decoding JSON data: %s', json_last_error_msg()));
better: throw new \RuntimeException(sprintf('Error decoding JSON data: %s message: %s', $data, json_last_error_msg()));

In my case the error was in table xxx_template_styles the missing "} at unused(!) template stil "rt_modulus - Tutorials"
wrong params: {"master":"8","current_id":"11","mainbodyPosition":"a:1:{i:12;a:1:{i:2;a:2:{s:2:\"mb\";i:6;s:2:\"sa\";i:6;}}}
Right params: {"master":"8","current_id":"11","mainbodyPosition":"a:1:{i:12;a:1:{i:2;a:2:{s:2:\"mb\";i:6;s:2:\"sa\";i:6;}}}"}

[CritiKill]

At the end of the day this code update was bad. They can defend it at a hard limit but there should have been a cleanup or warning or auto resetting of broken fields. I am going to have to take drastic action like this as well. Did you try robert wents script ? I would like to know if anyone did.
https://github.com/robwent/joomla-json-db-check

I tried the script and it worked perfectly. Thanks!

[tat2d]

I'm really lost, mainly because I don't know code or sql. What do we have to change to fix this issue?

Recap of my issue: I am getting the error "Error displaying the error page: Error decoding JSON data: Syntax error: Error decoding JSON data: Syntax error" on the front end of 2 (out of 37) sites, 2 separate hosts, after upgrading from 3.6.2 to 3.6.4. I do not have JBLibrary installed on either site. I can access the administrator for both sites, but the error "Warning Error decoding JSON data: Syntax error" is in yellow on the control panel. 2 factor plugins are already disabled.

[mojito]

If you have 37 sites this is quite a bit of work - or you replace the file to the older version to HACK IT - but this is not ideal but not very harmful either IMO.
You may need to hire a joomla guy to get this fixed for you.

[tat2d]

If you have 37 sites this is quite a bit of work - or you replace the file to the older version to HACK IT - but this is not ideal but not very harmful either IMO.
You may need to hire a joomla guy to get this fixed for you.

Luckily only 2 sites are broken, so it can be manageable. I just need to know what code needs to be replaced and where.

[mojito]

The code is fine. The issue is legacy or for whatever reason database json strings are not validating ok.
Do you have phpmyadmin setup for the 2 sites ?
OR have you uploaded the github file mentioned above ? Often running that fixes the database strings - try that first it is safe.

[tat2d]

The code is fine. The issue is legacy or for whatever reason database json strings are not validating ok.
Do you have phpmyadmin setup for the 2 sites ?
OR have you uploaded the github file mentioned above ? Often running that fixes the database strings - try that first it is safe.

Oh ok. I do have PHPMyAdmin. I ran the file on both sites, then clicked the button because I still had the error. This was the only error in the output for both:

Checking table: xxxxx_template_styles, column params
Row 82 is not valid JSON. Error: (Syntax error)
Content: {"configuration":"82","preset":"rows_and_columns "}

I had a feeling the problem was with the template, since they both have the same one. This is where I am confused. Do I have to edit code somewhere to fix the issue? Or do I have to edit the database somewhere to fix it? Do I need to go to my template provider?

Thank you!

[tat2d]

Actually, my template provider has addressed the issue. Thank you for helping me narrow it down. If anyone is using RocketTheme's templates and you get the "Error decoding JSON data..." error, visit here: http://www.rockettheme.com/forum/gantry ... =0#1320376

If you have updated to J3.6.3 and are receiving a "Error decoding JSON data: Syntax error" error then please read the following.

There was a little error in a JSON string for the presets in the template - we have now corrected this but for existing sites you will need to use PHPMYADMIN to edit the database to correct his...

Use this SQL command in PHPMYADMIN:

UPDATE `#_template_styles` SET `params` = '{"configuration":"82","preset":"rows_and_columns"}' WHERE `id` = 82


Please replace the "#" above by your table prefix.

The database will then be corrected and your site should then be ok.

Regards, Mark.

[koyan]

Please ignore this post. Edited since a better solution has been suggested.

[robwent]

just use the script

[robwent]

UPDATE `MYTABLES_users` SET params = '{}' WHERE length(params) > 65000

You are going to wipe out any parameters which are less than 65000 chars.

This is totally stupid advice. People should not do this.

I suggest you delete your post.

[koyan]

UPDATE `MYTABLES_users` SET params = '{}' WHERE length(params) > 65000

You are going to wipe out any parameters which are less than 65000 chars.

">" is more, not less

[robwent]

so I miss read it but it's still stupid

[koyan]

The maximum size of the params field is 65535 chars.
The only way for the params to reach > 65000 chars is if enough / have been recursively added. Which is the case in all the params fields that I found (in several websites).