Обсуждение: Strange error using postgres 8.2 + JDBC 8.2 driver

I get the following error:

org.postgresql.util.PSQLException: ERROR: invalid byte sequence for
encoding "UTF8": 0x00
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:344)
[sni, goes into our code]

The statement is an insert into a table with various columns, one of
them being a bytea column.

The data base has locale C, encoding UTF-8, the "file.encoding" java
property is set to UTF-8 too, I think it should be OK if I'm not missing
something else...

The biggest problem is that I cant reproduce it in a self contained test
case, although in our system is consistently happening with the same
data.

I tried to set all the parameters with the same data as in our system,
including the byte array used to populate the bytea field, but in the
independent test the insert succeeds...

I tried to set the JDBC driver/server debug level to the highest
possible values, but I seem unable to get a more detailed error message
(in particular about which parameter is wrong, and what was it's value),
so I'm not completely sure I have the right values in the independent
test case (I got them from debugging our system and copy paste the
values).

Any ideas how could I further debug this ? It's show-stopper for us in
adopting 8.2.

Thanks,
Csaba.

On Wed, 17 Jan 2007, Csaba Nagy wrote:

> I get the following error:
>
> org.postgresql.util.PSQLException: ERROR: invalid byte sequence for
> encoding "UTF8": 0x00
> at
>
> Any ideas how could I further debug this ? It's show-stopper for us in
> adopting 8.2.
>

This will actually happen in 8.1 servers if you are running the latest
releases.  Previously, inserting something like "a\0b" would silently
truncate at the null byte, but now it's an error.  So, check your data for
\0.

Kris Jurka

> This will actually happen in 8.1 servers if you are running the latest
> releases.  Previously, inserting something like "a\0b" would silently
> truncate at the null byte, but now it's an error.  So, check your data for
> \0.

Well, if the 0 bytes are in the byte array, I would expect that to be
OK, the driver should escape them, right ? (I actually tested this, it
works).

Other than that, the parameters are plain strings and primitives, so I
would also be surprised to be able to insert invalid 0 bytes in those...
Could it be that we have some strange characters not properly encoded by
the driver ?

Thanks,
Csaba.

On Wed, 17 Jan 2007, Csaba Nagy wrote:

>> This will actually happen in 8.1 servers if you are running the latest
>> releases.  Previously, inserting something like "a\0b" would silently
>> truncate at the null byte, but now it's an error.  So, check your data for
>> \0.
>
> Well, if the 0 bytes are in the byte array, I would expect that to be
> OK, the driver should escape them, right ? (I actually tested this, it
> works).

Right, bytea data can contain 0.

> Other than that, the parameters are plain strings and primitives, so I
> would also be surprised to be able to insert invalid 0 bytes in those...
> Could it be that we have some strange characters not properly encoded by
> the driver ?
>

Java is not C where 0 is a string terminator.  Java String objects may
contain embedded 0 characters.  The driver encodes these correctly and
sends them to the server which rejects them.  The server being written in
C has code everywhere that treats 0 as a terminator and it cannot
correctly handle embedded 0s.  You have 0s somewhere in your string data.

Kris Jurka

On Wed, 2007-01-17 at 18:13, Csaba Nagy wrote:
> > This will actually happen in 8.1 servers if you are running the latest
> > releases.  Previously, inserting something like "a\0b" would silently
> > truncate at the null byte, but now it's an error.  So, check your data for
> > \0.

OK, I was able to reproduce it using:

Connection connection = getConnection();
Statement statement = connection.createStatement();
statement.execute("create table test_null_in_string (data text)");
PreparedStatement prepStatement =
  connection.prepareStatement("insert into test_null_in_string values
(?)");
String test = new String(new byte[] {1, 2, 3, 0, 1, 2, 3}, "US-ASCII");
prepStatement.setString(1, test);
// will throw exception
prepStatement.executeUpdate();


So it is easily possible... now I have to check how it happens in our
system, but I'm afraid I won't like the implications :-/

Cheers,
Csaba.