PG::UntranslatableCharacter: ERROR

[jonathan-wondereur]

Tell us about your environment

Ruby Version: ruby 2.5.0p0 (2017-12-25 revision 61468) [x86_64-linux]

Rails Version: 5.0.6

PostgreSQL Version: 9.6.12

Logidze Version: 0.12.0

What did you do?

Ran a bulk update SQL.

What did you expect to happen?

Records to update.

What actually happened?

PG::UntranslatableCharacter: ERROR:  unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.
CONTEXT:  JSON data, line 1: ..."relative_time_description":"2 years ago","text":...
PL/pgSQL function logidze_logger() line 31 at assignment
: UPDATE institutions
      SET l0=subquery.l0,
      updated_at=current_timestamp
      FROM (
          ...) AS subquery
      WHERE institutions.id=subquery.i1id;

[Envek]

Hi! \u0000 cannot be converted to text looks like you're trying to insert binary data into a bytea column. Is this your case?

Can you please provide your table schema (which columns of which types) and example of data you're trying to insert? This may be Ruby script with migration class, or raw SQL CREATE TABLE and UPDATE queries.

[jonathan-wondereur]

It is in a JSON field, I think it is an issue with the data in the JSON field (that I got from Google Places API). I think user reviews are the issue.

I tried to update to ignore the column but it is still failing on the column.

[Envek]

Null bytes are not supported and will not be supported in PostgreSQL JSONB datatype. See this thread for details: https://www.postgresql.org/message-id/8239.1500489054%40sss.pgh.pa.us

You can strip it from the input strings with SQL spells like this: https://dba.stackexchange.com/a/120718/61198

regexp_replace(stringWithNull, '\\u0000', '', 'g')

[palkan]

I tried to update to ignore the column but it is still failing on the column.

I think, this line is failing: https://github.com/palkan/logidze/blob/master/lib/generators/logidze/install/templates/migration.rb.erb#L142

We try to convert to JSONB first and remove the ignored columns after that. I think, that could be solved by filtering before conversion.

That would make ignoring the column work.

[palkan]

@Envek

You can strip it from the input strings with spells like this:

I guess, we need to do that in Ruby before hitting the DB (since the actual failure is happening within the Logidze trigger function).

[jonathan-wondereur]

Okay, as a work around I disabled versioning for this update, because I am doing raw SQL in this case, not using Active Record I used this to do so: https://github.com/palkan/logidze/blob/4b17b00e59c0a04f23663f77512e5f94890fa0e3/lib/logidze.rb#L40

[palkan]

I am doing raw SQL

Curious, how do you do that? You are not using Active Record?

I've tried to reproduce this exception with AR, and found that it catches the problem itself:

User.where(id: user.id).update_all("name = 'Hip-\u0000Hop'")
=> ArgumentError: string contains null byte