Thanks! I will try this later tonight or tomorrow (when the kids are sleeping or otherwise settled :-) )
I first performed your query and indeed, nothing was returned
sqlite> SELECT `id`, `extra_data` FROM metadata_item_settings where length(extra_data) = 0;
Now, I don’t follow how that update would change anything. As confirmed above, no rows match length(extra_data) = 0, so the update consequently didn’t update any rows
sqlite> UPDATE metadata_item_settings SET `extra_data` = NULL WHERE length(extra_data) = 0;
I don’t usually work with SQLite, so I’m not sure exactly what was expected here, but it seems to me that length(extra_data) is not the correct way to determine if extra_data is null or empty:
sqlite> SELECT `id`, `extra_data`, length(extra_data) FROM metadata_item_settings LIMIT 10;
1||
2||
3||
4||
5||
6||
7||
8||
9||
10||
Notice how the length(extra_data) column is empty, instead of containing a number. Again, I don’t know if this is expected, but it seems to me that perhaps length() should not be used on a column of extra_data’s type.
Now, if length(extra_data) = 0 is what was (incorrectly?) used in the preceding migration, that would explain how we ended up in this situation.
I was discussing this with @OttoKerner internally
SQLite fails on JSON path queries with the empty string but not with NULL:
sqlite> select '' -> 'pv:SyncedViewAt';
Runtime error: malformed JSON
sqlite> select NULL -> 'pv:SyncedViewAt';
sqlite> select NULL -> 'pv:SyncedViewAt' IS NULL;
1
Since the CLI for SQLite displays NULLs and empty strings the same, I had guessed from your earlier query,
that these rows had an empty string for the JSON. However your queries indicate these are not empty strings.
BTW, on length(extra_data) being used to differentiate between an empty string and NULL, this does work:
sqlite> select typeof('');
text
sqlite> select length('');
0
sqlite> select length(NULL) is NULL;
1
sqlite> select length('') == 0;
1
sqlite> select length(NULL) == 0;
sqlite>
(basically, length(NULL) results in a NULL and the test = 0 still results in NULL which is not a 1 so the where clause filters these out)
So, something else is going on here and I think we need to identify the type of the value in these cases. Since SELECT id, extra_data FROM metadata_item_settings where NOT json_valid(extra_data); returned rows, you have invalid json in some rows. So perhaps a SELECT id, extra_data, typeof(extra_data) FROM metadata_item_settings where NOT json_valid(extra_data); could identify what’s going on here.
Hi,
Sorry for the absence, life got in the way.
When I run the query, I do get some interesting results.
Most look like this:
sqlite> SELECT id, extra_data, typeof(extra_data) FROM metadata_item_settings where NOT json_valid(extra_data);
1||null
2||null
3||null
4||null
...
98533||null
98534||null
197275|{"ma:container":"avi","ma:deepAnalysisVersion":"424?lang=en2019-10-11 22:40:452019-10-11 22:02:332019-10-11 22:40:45d]Ty��
","url":"ma%3Acontainer=avi&ma%3AdeepAnalysisVersion=424%3Flang%3Den2019-10-11%2022%3A40%3A452019-10-11%2022%3A02%3A332019-10-11%2022%3A40%3A45%01d%5DTy%86%81%0B%0E"}|text
197276||null
197277||null
Looking back, I see that I did actually get that row in some of the queries above too, I just missed it when scrolling through all the rows. Sorry for that 
Can I just set that cell to null too, and get on with it, or is that particular cell valuable for some reason I don’t understand (my guess is No, since it can’t be read now anyways).
I still find it disturbing that you are getting rows where the extra_data is null because json_valid(null) does not return true.
That aside, the extra_data in this row is clearly corrupt and the data that is there doesn’t below in a metadata_item_settings row. So you likely should null out the extra_data on this row.
I don’t know all ins and outs of SQLite, but explicitly filtering out null values does render the expected result:
sqlite> SELECT id, extra_data, typeof(extra_data) FROM metadata_item_settings where NOT json_valid(extra_data) AND extra
_data IS NOT NULL;
197275|{"ma:container":"avi","ma:deepAnalysisVersion":"424?lang=en2019-10-11 22:40:452019-10-11 22:02:332019-10-11 22:40:45d]Ty��
","url":"ma%3Acontainer=avi&ma%3AdeepAnalysisVersion=424%3Flang%3Den2019-10-11%2022%3A40%3A452019-10-11%2022%3A02%3A332019-10-11%2022%3A40%3A45%01d%5DTy%86%81%0B%0E"}|text
And to be explicit, I use the Plex SQLite tool when connecting to the com.plexapp.plugins.library.db database. I extracted this tool from Plex’s Docker image. I read somewhere that this tool is not identical (though very similar) to the official SQLite tool. Maybe this is one difference…?
stolpe@Flitwick:~/Plex/config/Library/Application Support/Plex Media Server/Plug-in Support/Databases$ ~/apa/plexmediaserver/Plex\ SQLite com.plexapp.plugins.library.db
Hi,
I can answer this myself ¯\_(ツ)_/¯. json_valid() behaves as you describe:
sqlite> select json_valid('{}');
1
sqlite> select json_valid('');
0
sqlite> select json_valid(null);
0
Here is the schema of the metadata_item_settings table:
sqlite> pragma table_info(metadata_item_settings);
0|id|INTEGER|1||1
1|account_id|INTEGER|0||0
2|guid|varchar(255)|0||0
3|rating|float|0||0
4|view_offset|INTEGER|0||0
5|view_count|INTEGER|0||0
6|last_viewed_at|dt_integer(8)|0||0
7|created_at|dt_integer(8)|0||0
8|updated_at|dt_integer(8)|0||0
9|skip_count|INTEGER|0|0|0
10|last_skipped_at|dt_integer(8)|0|NULL|0
11|changed_at|integer(8)|0|'0'|0
12|extra_data|varchar(255)|0||0
13|last_rated_at|dt_integer(8)|0||0
Now, SQLite describes the column as a varchar(255), but in a query above its contents was described as having the type text. Maybe this is just how SQLite works…?
I was just going to remove the malformed data, but I see that my Plex server is in use at the moment, so that will have to wait a bit more 
Column type in SQLite merely a suggestion and doesn’t constrain the actual data type of items in that column.
You should be good once you’ve removed the malformed data.
I can confirm that removing the malformed data indeed solved that problem. I’m now on the latest version again \^o^/
Thanks a lot for all your help @OttoKerner and @gbooker02!
Still stuck with the same problem. Updating the values to NULL doesn’t seem to have fixed the issue for me. Upgrades are really struggling lately. At least the last one worked, after letting it sit for over 24h.
I think the left over errors (if the empty even were any) are “extra_data” rows which simply have errors/are in the wrong format.
Correct Entry A: {“pv:unplayedAt”:“1715262437”,“url”:“pv%3AunplayedAt=1715262437”}
Wrong Entry B: pv%3AunplayedAt=1686866117
Analyzing the existing data I assume the corrected entry B would be
{“pv:unplayedAt”:“1686866117”,“url”:“pv%3AunplayedAt=1686866117”}
Can anyone confirm? Otherwise I’ll just try my luck
EDIT
As expected that fixed my issues. If anyone has the same issues, follow along.
Side node. As my SQL skills are rather bad I used Excel to make the necessary corrections and generate the queries needed. Someone capable of creating proper queries should easily be able to generate an UPDATE query with all the necessary replacements in a single command.
- Download the DB (check for your system where this file is located)
- Get all faulty rows with the following query, provided by @ gbooker02 (thanks!)
SELECT
id,extra_dataFROM metadata_item_settings where NOT json_valid(extra_data)
- Export those rows into a CSV and open it Excel.
- Generate corrected values by inserting “Formula A” into Column “P”.
- Generate update queries by isnerting “Formula B” into Column “Q”.
- Update all the rows with the generate queries.
# Formula A (Column P)
English
=“{”“”&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(O2,“%3A”,“:”),“=”,“”“:”“”),“&”,“”“,”“”)&“”“,”“url”“:”“”&O2&“”“}”
(Not tested as my Office is setup in German)
German
= “=”{“”“&WECHSELN(WECHSELN(WECHSELN(O2;”%3A";“:”);“=”;“”“:”“”);“&”;“”“,”“”)&“”“,”“url”“:”“”&O2&“”“}”
# Formula B (Column Q)
English
=“UPDATE metadata_item_settings SET extra_data='”&P2&“’ WHERE id=”&A2&“;”
(Not tested as my Office is setup in German)
German
=“UPDATE metadata_item_settings SET extra_data='”&RP&“’ WHERE id=”&A2&“;”
1 Like
Had the exact same issue as what is described. Received malformed JSON starting with 40.4.
Fixed by following the steps outlined in this topic.
- Explicitly null out extra_data = 0
UPDATE metadata_item_settings SET `extra_data` = NULL WHERE length(extra_data) = 0;
- Fix remaining syntax with YouhBi’s commands:
= "{""" & SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2, "%3A", ":"), "=", """:"""), "&", """,""") & """,""url"":""" & B2 & """}"
= "UPDATE metadata_item_settings SET extra_data='" & C2 & "' WHERE id=" & A2 & ";"
1 Like
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
