pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt
4+ messages / 3 participants
[nested] [flat]
* PostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt
@ 2020-08-08 09:59 Hotmail <crajac66@hotmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Hotmail @ 2020-08-08 09:59 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi All,
We migrated data from Oracle to postgres using oracle_fdw without seeing any invalid byte sequence for encoding "UTF8": 0x00 errors. The data transfer from Oracle to Postgres using oracle_fdw completed successfully without any errors.
After we started our application using Postgres as the new data source we are consistently seeing "invalid byte sequence for encoding "UTF8": 0x00 errors" once or twice a minute on a basic select statement like this:
Select * from <table> where <col1>=‘foo’ and <col2>=‘foo2’;
Not all queries fail just some. We can select * from the table in question from psql without any invalid byte sequence errors (A problem with the jdbc client maybe?)
We went back to the source Oracle database and re-ran queries to try to find any invalid byte sequences and we could not find any. Again the data was copied with oracle_fdw with no errors.
We would like to know if there are any queries we can run on the postgres table generating this error to identify the source of the invalid byte sequence errors. The query we used in the Oracle db to identify invalid byte sequences does not translate directly because it uses the instr oracle function. Here’s the oracle query:
select count(*) from <table_name> where instr(<table_cole>, unistr('\0000')) > 0;
Is there an equivalent query we could run in Postgres?
Regards,
Craig Jackson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt
@ 2020-08-08 11:58 Samed YILDIRIM <samed@reddoc.net>
parent: Hotmail <crajac66@hotmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Samed YILDIRIM @ 2020-08-08 11:58 UTC (permalink / raw)
To: Hotmail <crajac66@hotmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
<div>Hi,</div><div> </div><div>First of all, 0x00 character is not a valid UTF-8 character. And, PostgreSQL is more strict compare to Oracle about character encodings.</div><blockquote><div>select count(*) from <table_name> where instr(<table_cole>, unistr('\0000')) > 0;</div></blockquote><div>If your character encoding of your Postgres database is UTF-8 and data type of the column is a type for storing character, such as varchar, text etc., answer is no. There is no equivalent in Postgres of the query above. Reason is simple, you can't store character 0x00 by the reason that it is not a valid character string :)</div><div> </div><div>Most probably, while copying of data from Oracle to Postgres, oracle_fdw or oracle driver it used skipped character 0x00. So, your data in PostgreSQL is clean in the aspect of character 0x00.</div><div> </div><div>The question is what is the source of the errors. Answer is that the application itself is the source of the errors. Your application sends invalid UTF-8 strings to database. Also the application is responsible to invalid UTF-8 strings stored in Oracle database. You should check and fix your application. </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div>08.08.2020, 12:59, "Hotmail" <crajac66@hotmail.com>:</div><blockquote><div><div>Hi All, <br /><br />We migrated data from Oracle to postgres using oracle_fdw without seeing any invalid byte sequence for encoding "UTF8": 0x00 errors. The data transfer from Oracle to Postgres using oracle_fdw completed successfully without any errors.<br /><br />After we started our application using Postgres as the new data source we are consistently seeing "invalid byte sequence for encoding "UTF8": 0x00 errors" once or twice a minute on a basic select statement like this:<br /><br />Select * from <table> where <col1>=‘foo’ and <col2>=‘foo2’;<br /><br />Not all queries fail just some. We can select * from the table in question from psql without any invalid byte sequence errors (A problem with the jdbc client maybe?)<br /><br /><br />We went back to the source Oracle database and re-ran queries to try to find any invalid byte sequences and we could not find any. Again the data was copied with oracle_fdw with no errors. <br /><br />We would like to know if there are any queries we can run on the postgres table generating this error to identify the source of the invalid byte sequence errors. The query we used in the Oracle db to identify invalid byte sequences does not translate directly because it uses the instr oracle function. Here’s the oracle query:<br /><br />select count(*) from <table_name> where instr(<table_cole>, unistr('\0000')) > 0;<br /><br />Is there an equivalent query we could run in Postgres?<br /><br />Regards,<br /><br />Craig Jackson</div></div></blockquote>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt
@ 2020-08-08 14:57 Hotmail <crajac66@hotmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 4+ messages in thread
From: Hotmail @ 2020-08-08 14:57 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Samed YILDIRIM <samed@reddoc.net>
Hi Samed,
Thanks for the detailed response. I still have few questions that I would like to check my understanding on.
First let me provide some additional background information.
• We moved a 10TB oracle database to postgres prior to the current one we are working with and oracle_fdw consistently terminated our insert jobs when encountering an invalid byte sequence. We would identify and then fix the invalid data on the oracle side and then resume the inserts successfully until completion. It seems unlikely the oracle_fdw would consistently identify the invalid 0x00 character in the 10TB database and ignore that same string in this much smaller database (100GB).
• We tested loading the current data set with oracle_fdw several times. No invalid byte sequence errors in testing. This makes us feel that the loaded data from Oracle does not have any invalid byte sequences.
• After the initial load of the data, the cord is cut to Oracle. The application only talks to the postgres database and does not rely on oracle_fdw for retrieving data.
Turning to the application and the errors on the select statements.
• My first question is shouldn’t we see the invalid byte sequence errors on insert statements if the client application is attempting to insert incompatible data? (We aren’t seeing any errors on inserts)
• Is our select statement failing because there is an invalid byte sequence in the actual select statement? It would seem the most likely candidate if this the case would be in the variables being passed in by the where clause predicates.
e.g. select * from table where <col1>=“string w invalid byte sequence”
• Is it a valid assumption that if we can "select * from table” with psql that there aren’t any existing invalid byte sequences in the data stored in postgres on disk?
• Is there a way to identify the actual variable values that are causing the failure with postgres tracing utilities? In the server log we only see the sql statement with the where clause variables replaced with “?”. (We are working with our developers to get the application logs to reveal this information when the exception occurs.)
Thanks again for any additional insight provided.
Regards,
Craig Jackson
On Aug 8, 2020, 5:58 AM -0600, Samed YILDIRIM <samed@reddoc.net>, wrote:
> Hi,
>
> First of all, 0x00 character is not a valid UTF-8 character. And, PostgreSQL is more strict compare to Oracle about character encodings.
> > select count(*) from <table_name> where instr(<table_cole>, unistr('\0000')) > 0;
> If your character encoding of your Postgres database is UTF-8 and data type of the column is a type for storing character, such as varchar, text etc., answer is no. There is no equivalent in Postgres of the query above. Reason is simple, you can't store character 0x00 by the reason that it is not a valid character string :)
>
> Most probably, while copying of data from Oracle to Postgres, oracle_fdw or oracle driver it used skipped character 0x00. So, your data in PostgreSQL is clean in the aspect of character 0x00.
>
> The question is what is the source of the errors. Answer is that the application itself is the source of the errors. Your application sends invalid UTF-8 strings to database. Also the application is responsible to invalid UTF-8 strings stored in Oracle database. You should check and fix your application.
> Best regards.
> Samed YILDIRIM
>
> 08.08.2020, 12:59, "Hotmail" <crajac66@hotmail.com>:
> > Hi All,
> >
> > We migrated data from Oracle to postgres using oracle_fdw without seeing any invalid byte sequence for encoding "UTF8": 0x00 errors. The data transfer from Oracle to Postgres using oracle_fdw completed successfully without any errors.
> >
> > After we started our application using Postgres as the new data source we are consistently seeing "invalid byte sequence for encoding "UTF8": 0x00 errors" once or twice a minute on a basic select statement like this:
> >
> > Select * from <table> where <col1>=‘foo’ and <col2>=‘foo2’;
> >
> > Not all queries fail just some. We can select * from the table in question from psql without any invalid byte sequence errors (A problem with the jdbc client maybe?)
> >
> >
> > We went back to the source Oracle database and re-ran queries to try to find any invalid byte sequences and we could not find any. Again the data was copied with oracle_fdw with no errors.
> >
> > We would like to know if there are any queries we can run on the postgres table generating this error to identify the source of the invalid byte sequence errors. The query we used in the Oracle db to identify invalid byte sequences does not translate directly because it uses the instr oracle function. Here’s the oracle query:
> >
> > select count(*) from <table_name> where instr(<table_cole>, unistr('\0000')) > 0;
> >
> > Is there an equivalent query we could run in Postgres?
> >
> > Regards,
> >
> > Craig Jackson
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt
@ 2020-08-11 08:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Hotmail <crajac66@hotmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2020-08-11 08:33 UTC (permalink / raw)
To: Hotmail <crajac66@hotmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sat, 2020-08-08 at 03:59 -0600, Hotmail wrote:
> We migrated data from Oracle to postgres using oracle_fdw without seeing any invalid byte
> sequence for encoding "UTF8": 0x00 errors. The data transfer from Oracle to Postgres using
> oracle_fdw completed successfully without any errors.
>
> After we started our application using Postgres as the new data source we are consistently
> seeing "invalid byte sequence for encoding "UTF8": 0x00 errors" once or twice a minute on
> a basic select statement like this:
>
> Select * from <table> where <col1>=‘foo’ and <col2>=‘foo2’;
>
> Not all queries fail just some. We can select * from the table in question from psql without
> any invalid byte sequence errors (A problem with the jdbc client maybe?)
oracle_fdw will complain about zero bytes, so odds are that your PostgreSQL database is fine.
This is confirmed by the face that "SELECT * FROM atable" works in PostgreSQL.
I can see two options:
1. The statement that causes the errors is actually a different statement, for
example an INSERT.
2. 'foo' and 'foo2' contain zero bytes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-08-11 08:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-08 09:59 PostgreSQL JDBC Driver ERROR: invalid byte sequence for encoding "UTF8": 0x00 on Select stmt Hotmail <crajac66@hotmail.com>
2020-08-08 11:58 ` Samed YILDIRIM <samed@reddoc.net>
2020-08-08 14:57 ` Hotmail <crajac66@hotmail.com>
2020-08-11 08:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox