898072 - Invalid UTF8 data being inserted into JSON in Postgres, raw_crashes in particular is affected

Status: RESOLVED FIXED

(Socorro :: Backend, task)

Description

[Robert Kaiser]

For my custom report on Android devices and their crashes, I'm trying to get that data out of the raw_crashes table now instead of doing weird app_notes parsing experiments. But on some crashes, I run into issues:

breakpad=> SELECT raw_crash->>'Android_Manufacturer' as manufacturer FROM raw_crashes WHERE uuid='5e8d5e28-f5b0-444d-a619-a1ed32130724';
ERROR:  invalid byte sequence for encoding "UTF8": 0xef


but this one works (giving an awfully huge JSON output with all the logcat):

breakpad=> SELECT raw_crash FROM raw_crashes WHERE uuid='5e8d5e28-f5b0-444d-a619-a1ed32130724';

and I can parse that huge JSON fine in PHP for my custom reports - Would be nicer to have the DB only give me the values I want, though.


Also, if that's not an error in how I'm using this field, then it might even be an error in what we store in the DB and we might run into that when starting to use this table more.

Comment 1

[Selena Deckelmann :selenamarie :selena]

This issue is an upstream JSON library issue. I'm poking the maintainer of the library to fix it.

Comment 2

[Robert Kaiser]

This still happens:

[[e-mail]1 ~]$ psql -h socorro-reporting1.db.phx1.mozilla.com -p 6432 -U crashkill breakpad
Password for user crashkill: 
psql (9.0.14, server 9.2.4)
WARNING: psql version 9.0, server version 9.2.
         Some psql features might not work.
Type "help" for help.

breakpad=> SELECT raw_crash->>'Android_Manufacturer' as manufacturer FROM raw_crashes WHERE uuid='5e8d5e28-f5b0-444d-a619-a1ed32130724';
ERROR:  invalid byte sequence for encoding "UTF8": 0xef

Comment 3

[Selena Deckelmann :selenamarie :selena]

So, that encoding sequence is invalid. To fix the problem we need to ensure that Python does not try to insert invalid UTF8.

Comment 4

[Robert Helmer [:rhelmer]]

BTW, the query from comment 2 seems to WFM right now on stage and production:

$ psql -U $databaseUserName -h $databaseHost $databaseName
psql (9.2.4, server 9.2.6)
Type "help" for help.

breakpad=# SELECT raw_crash->>'Android_Manufacturer' as manufacturer FROM raw_crashes WHERE uuid='5e8d5e28-f5b0-444d-a619-a1ed32130724';
 manufacturer 
--------------
 samsung
(1 row)

breakpad=#

Comment 5

[Robert Kaiser]

OK, we still have invalid crashes in there, though. I just tried this one trying to find GMP crashes in the light of not having bug 1060083 yet:

breakpad=> SELECT uuid FROM raw_crashes WHERE raw_crash->>'GMPPlugin'='1';
ERROR:  invalid byte sequence for encoding "UTF8": 0xe6

I of course have no clue which report it tripped over there.

Comment 6

[Robert Kaiser]

I was told that bug 1060113 would resolve this but it didn't:

[[e-mail]1 ~]$ psql -h socorro-reporting1... -p XXXX -U crashkill breakpad
Password for user crashkill: 
psql (9.2.9, server 9.2.6)
Type "help" for help.

breakpad=> SELECT uuid FROM raw_crashes WHERE raw_crash->>'GMPPlugin'='1';
ERROR:  invalid byte sequence for encoding "UTF8": 0xc3

Comment 7

[Robert Kaiser]

After the recent server migration I get the following:

psql (9.2.9)
Type "help" for help.

breakpad=> SELECT uuid FROM raw_crashes WHERE raw_crash->>'GMPPlugin'='1';
ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

Comment 8

[Selena Deckelmann :selenamarie :selena]

(In reply to Robert Kaiser (:[e-mail]) from comment #7)
> After the recent server migration I get the following:
> 
> psql (9.2.9)
> Type "help" for help.
> 
> breakpad=> SELECT uuid FROM raw_crashes WHERE raw_crash->>'GMPPlugin'='1';
> ERROR:  canceling statement due to conflict with recovery
> DETAIL:  User query might have needed to see row versions that must be
> removed.

Please open a ticket with dbops. Sounds like the config from reporting1 didn't make it over to reporting2.

Comment 9

[Robert Kaiser]

Thanks, filed bug 1124668, I'll re-try once that's resolved.

Comment 10

[Will Kahn-Greene [:willkg] ET needinfo? me]

I think the issues in here are either fixed or not relevant anymore. I'm going to mark it as FIXED. If that's not correct, please re-open.