Error decoding JSON data: FIXING Syntax error

[tekno_boy]

Hi, a request for a little help because I'm out of my depth.

I've been using Joomla for several years but in a recent update one of my sites broke with the "Error decoding JSON data: Syntax error"

For those who find this post because they too have this error, I have at least discovered that the only solution is to fix the data that's causing it (it's not the fault of Joomla, more a fault of your data and the fact that Joomla now has a check for it).

I found a script to help you see what data is at fault here (instructions on how to use it are on the page):
https://github.com/robwent/joomla-json-db-check

So I got as far as seeing hundreds of errors. I imagine some simple SQL will fix it, but, I don't know SQL and more importantly, I don't know what is wrong.

I have 99 lines which the script says:
Row 99 is not valid JSON. Error: (Syntax error)
Content: {"category_layout":"","image":""}

But I have no idea what the correct syntax is.

I also have hundreds of lines like this:
Row 3769 is not valid JSON. Error: (Syntax error)
Content: {"show_title":"","link_titles":"","show_intro":"","show_category":"","link_category":"","show_parent_category":"","link_parent_category":"","show_author":"","link_author":"","show_create_date":"","show_modify_date":"","show_publish_date":"","show_item_navigation":"","show_icons":"","show_print_icon":"","show_email_icon":"","show_vote":"","show_hits":"","show_noauth":"","alternative_readmore":"","article_layout":""}

If I could recognize the syntax (I tried online tools, but nothing helped me identify it, some even told me there was no error in the syntax) I think I could (with my limited knowledge) be able to write the SQL to fix them, but I just can't figure it out.

Is there anyone here who might be able to offer me the SQL to fix these JSON syntax errors? (they exist in multiple lines each).

Much Appreciated.

[deleted user]

The two lines you pasted in are valid JSON and correctly decoded.

[tekno_boy]

Thanks mbabker!

Any suggestion on how to get around the Error decoding JSON data error then (which appears when the menus are cliecked)?

At the moment the only solution I have found is to use the Joomla 3.2 libraries but then the Administrator backend won't work.

All I need is to stop that error from displaying, but in the last six months, I haven't found a solution so I'm left with either menus which don't work or no administrator backend.

[Per Yngve Berg]

Content: should be "Content":

And there should be an outer set of brackets {}

[tekno_boy]

Ok, Cheers, Per Yngve Berg. Then it's the same issue for all of them.

Is it too much to ask if you would know what SQL could correctly update each one throughout the DB?

[Per Yngve Berg]

Use Replace in an update query

https://dev.mysql.com/doc/refman/5.6/en ... on_replace

Code: Select all

replace(params, 'Content:', '"Content":')

Presume 'params' is the column with the broken JSON data.

[tekno_boy]

That seems straightforward, but not knowing SQL, I've spent an hour reading tutorials and visiting sites (which all showed the same instructions about how to use replace) and I can't work out exactly what to put.

The tables in question are:
xxxxx_content, column attribs
xxxxx_categories, column params

I could write the dozens of queries I attempted (which didn't work),

I think the closest I got was:
SELECT `xxxxx_categories` REPLACE(params, 'Content:', '"Content":')
I also tried:
SELECT xxxxx_categories REPLACE(params, 'Content:', '"Content":')
and I tired with a trailing semicolon i.e. ....);

I really did try to work it out for myself, but all I got was errors.

Sorry, can you see where I'm going wrong?

[Per Yngve Berg]

Code: Select all

UPDATE `xxxxx_categories` SET params =REPLACE(params, 'Content:', '"Content":')

[tekno_boy]

Thank you very much. I changed the xxxxx to the table prefix of my database (after backing up first of course).

I ran the query and it worked (it ran correctly), but no rows were affected (I tired both tables), so I still have the error.

Any more suggestions?

[deleted user]

The string "Content:" won't be part of what's stored in the database, that's part of the output from the tool.

[tekno_boy]

Ok. Yes, that makes sense, it's just output from the script.

OK, so... you pointed out the json is valid and correctly decoded,

So how do I get rid of the "Error decoding JSON data"?

I'm at my wits end!

[deleted user]

Unfortunately I can't guide you without having access to your database. The two lines you posted originally are indeed valid JSON, so it's not those entries causing your issues.

Also, though less likely, it is possible the JSON source is NOT your database. The Registry API where this error is most commonly thrown from is a general use key/value storage and it's possible an extension developer uses it to load/parse data from an external resource such as a third party API. Just something to keep in mind in case you do find that there is no broken data in your database.

[tekno_boy]

Is there any way of turning off or simply removing this check? (I know, I know,... the check has been added for a purpose so I should seek to circumvent it... but I simply don't have the knowledge or resources to fix it).

The site is working without the error if I replace the libraries folder from the previous version (which I can't imagine is a great solution), but then the administrator backend doesn't work.

So either a working site or no admin access.

Actually, the biggest issue I face: Not knowing what to do next.

[SigridGR]

Hi,

I know there has been some months now, but I ran into the same problem.

At first I thought drag'n'drop helped, but it did not. I mixed up the menu items.
sorry, no solution yet.

[leolam]

Quoted from our website "Tips & Tricks"

Joomla 3.6.3 improved validation of JSON data stored in the database (Usually as params for extensions). Unfortunately, this means that after updating, sites with invalid data can can become inaccessible.

The usual error message shown is:

0 - Error decoding JSON data: Syntax error

So a nice script is developed by Rob Went to repair the syntax errors and you can review the description and download the script here here on Github.

Leo