Thread

Commits

Fix COPY FROM when database encoding is SQL_ASCII.
- f82595ac903e 14.4 landed
- fc36ac52ebd3 15.0 landed
Do COPY FROM encoding conversion/verification in larger chunks.
- f82de5c46bdf 14.0 cited

BUG #17501: COPY is failing with "ERROR: invalid byte sequence for encoding "UTF8": 0xe5"

The Post Office <noreply@postgresql.org> — 2022-05-28T20:52:19Z

The following bug has been logged on the website:

Bug reference:      17501
Logged by:          Vitaly Voronov
Email address:      wizard_1024@tut.by
PostgreSQL version: 14.3
Operating system:   CentOS Linux release 7.9.2009 (Core)
Description:        

Hello,

We've seen a such bug: COPY command shows error "ERROR:  invalid byte
sequence for encoding "UTF8": 0xe5" on file.
The same file with small amount of lines is imported without any errors.

How to reproduce bug:
# create database
# create database with
# SQL_ASCII, C, C
createdb --encoding=SQL_ASCII --lc-collate=C --lc-ctype=C
--template=template0 test

# connect to the database
psql test

# Create table
CREATE TABLE test_data (
    test_data text
);

# Import without error
truncate table test_data;
COPY test_data (test_data) FROM '/tmp/test_pass.csv' WITH DELIMITER AS ','
CSV QUOTE AS '"';

COPY 207

# Import with error
truncate table test_data;
COPY test_data (test_data) FROM '/tmp/test_fail.csv' WITH DELIMITER AS ','
CSV QUOTE AS '"';

ERROR:  invalid byte sequence for encoding "UTF8": 0xe5
CONTEXT:  COPY test_data, line 627

# both files contains the same rows, but test_fail contains more rows
# seems that the file more than 65K size cannot be imported
# if create DB with UTF8 encoding instead of SQL_ASCII - both tests will be
passed

# How to generate files:
# Imported without errors
for i in $(seq 1 207); do echo
"NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】https://www.test.jp/1234/5678.html&id=12211"
>> /tmp/test_pass.csv; done;
# Imported with errors
for i in $(seq 1 5722); do echo
"NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】https://www.test.jp/1234/5678.html&id=12211"
>> /tmp/test_fail.csv; done;

# Both files can be imported without any problem to PostgreSQL 11.

Re: BUG #17501: COPY is failing with "ERROR: invalid byte sequence for encoding "UTF8": 0xe5"

Vitaly V. Voronov <wizard_1024@tut.by> — 2022-05-28T20:57:25Z

<div>Hello,</div><div> </div><div>Right commands:</div><div><div><span style="background-color:#ffffff;color:#000000;float:none;font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"># Imported without errors</span><br style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px" /><span style="background-color:#ffffff;color:#000000;float:none;font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">for i in $(seq 1 207); do echo</span><br style="background-color:rgb( 255 , 255 , 255 );color:rgb( 0 , 0 , 0 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px" /><span style="background-color:#ffffff;color:#000000;float:none;font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">"NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】</span><a href="https://www.test.jp/1234/5678.html&amp;id=12211" rel="noopener noreferrer" target="_blank" style="background-color:rgb( 255 , 255 , 255 );font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">https://www.test.jp/1234/5678.html&amp;id=12211</a><span style="background-color:#ffffff;color:#000000;float:none;font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">" </span>&gt;&gt; /tmp/test_pass.csv; done;</div></div><div><div><span style="background-color:#ffffff;color:#000000;float:none;font-family:'ys text' , 'arial' , sans-serif;font-size:16px;font-style:normal;font-weight:400;text-decoration-style:initial;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"># Imported with errors</span></div></div><div><div>for i in $(seq 1 5722); do echo "NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】https://www.test.jp/1234/5678.html&amp;id=12211" &gt;&gt; /tmp/test_fail.csv; done;</div></div><div><br /></div><div><br /></div><div>28.05.2022, 23:53, "PG Bug reporting form" &lt;noreply@postgresql.org&gt;:</div><blockquote><p>The following bug has been logged on the website:<br /><br />Bug reference:      17501<br />Logged by:          Vitaly Voronov<br />Email address:      <a href="mailto:wizard_1024@tut.by">wizard_1024@tut.by</a><br />PostgreSQL version: 14.3<br />Operating system:   CentOS Linux release 7.9.2009 (Core)<br />Description:        <br /><br />Hello,<br /><br />We've seen a such bug: COPY command shows error "ERROR:  invalid byte<br />sequence for encoding "UTF8": 0xe5" on file.<br />The same file with small amount of lines is imported without any errors.<br /><br />How to reproduce bug:<br /># create database<br /># create database with<br /># SQL_ASCII, C, C<br />createdb --encoding=SQL_ASCII --lc-collate=C --lc-ctype=C<br />--template=template0 test<br /><br /># connect to the database<br />psql test<br /><br /># Create table<br />CREATE TABLE test_data (<br />    test_data text<br />);<br /><br /># Import without error<br />truncate table test_data;<br />COPY test_data (test_data) FROM '/tmp/test_pass.csv' WITH DELIMITER AS ','<br />CSV QUOTE AS '"';<br /><br />COPY 207<br /><br /># Import with error<br />truncate table test_data;<br />COPY test_data (test_data) FROM '/tmp/test_fail.csv' WITH DELIMITER AS ','<br />CSV QUOTE AS '"';<br /><br />ERROR:  invalid byte sequence for encoding "UTF8": 0xe5<br />CONTEXT:  COPY test_data, line 627<br /><br /># both files contains the same rows, but test_fail contains more rows<br /># seems that the file more than 65K size cannot be imported<br /># if create DB with UTF8 encoding instead of SQL_ASCII - both tests will be<br />passed<br /><br /># How to generate files:<br /># Imported without errors<br />for i in $(seq 1 207); do echo<br />"NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】<a href="https://www.test.jp/1234/5678.html&amp;id=12211">https://www.test.jp/1234/5678.html&amp;id=12211</a>"<br /></p><blockquote class="210e7a848e8fcb45wmi-quote"><blockquote class="210e7a848e8fcb45wmi-quote"> /tmp/test_pass.csv; done;<br /></blockquote></blockquote><p># Imported with errors<br />for i in $(seq 1 5722); do echo<br />"NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】<a href="https://www.test.jp/1234/5678.html&amp;id=12211">https://www.test.jp/1234/5678.html&amp;id=12211</a>"<br /></p><blockquote class="210e7a848e8fcb45wmi-quote"><blockquote class="210e7a848e8fcb45wmi-quote"> /tmp/test_fail.csv; done;<br /></blockquote></blockquote><p><br /># Both files can be imported without any problem to PostgreSQL 11.<br /></p></blockquote>

Re: BUG #17501: COPY is failing with "ERROR: invalid byte sequence for encoding "UTF8": 0xe5"

Heikki Linnakangas <hlinnaka@iki.fi> — 2022-05-29T09:19:10Z

Sounds a lot like a bug in commit 
f82de5c46bdf8cd65812a7b04c9509c218e1545d. Thanks for the report, I'll 
investigate!

- Heikki

On 28/05/2022 23:57, Vitaly V. Voronov wrote:
> Hello,
> Right commands:
> # Imported without errors
> for i in $(seq 1 207); do echo
> "NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋外工 
> 事につきましては具体的な工事日案内の準備が整い次第、こちらからご連絡いた 
> します。※詳細はこちら【工事について】https://www.test.jp/1234 
> /5678.html&id=12211 <https://www.test.jp/1234/5678.html&id=12211>" >> 
> /tmp/test_pass.csv; done;
> # Imported with errors
> for i in $(seq 1 5722); do echo "NURO光です。明日の宅内工事お立合いよろ 
> しくお願い致します。2回目の屋外工事につきましては具体的な工事日案内の準 
> 備が整い次第、こちらからご連絡いたします。※詳細はこちら【工事について】 
> https://www.test.jp/1234/5678.html&id=12211" >> /tmp/test_fail.csv; done;
> 
> 
> 28.05.2022, 23:53, "PG Bug reporting form" <noreply@postgresql.org>:
> 
>     The following bug has been logged on the website:
> 
>     Bug reference: 17501
>     Logged by: Vitaly Voronov
>     Email address: wizard_1024@tut.by <mailto:wizard_1024@tut.by>
>     PostgreSQL version: 14.3
>     Operating system: CentOS Linux release 7.9.2009 (Core)
>     Description:
> 
>     Hello,
> 
>     We've seen a such bug: COPY command shows error "ERROR: invalid byte
>     sequence for encoding "UTF8": 0xe5" on file.
>     The same file with small amount of lines is imported without any errors.
> 
>     How to reproduce bug:
>     # create database
>     # create database with
>     # SQL_ASCII, C, C
>     createdb --encoding=SQL_ASCII --lc-collate=C --lc-ctype=C
>     --template=template0 test
> 
>     # connect to the database
>     psql test
> 
>     # Create table
>     CREATE TABLE test_data (
>          test_data text
>     );
> 
>     # Import without error
>     truncate table test_data;
>     COPY test_data (test_data) FROM '/tmp/test_pass.csv' WITH DELIMITER
>     AS ','
>     CSV QUOTE AS '"';
> 
>     COPY 207
> 
>     # Import with error
>     truncate table test_data;
>     COPY test_data (test_data) FROM '/tmp/test_fail.csv' WITH DELIMITER
>     AS ','
>     CSV QUOTE AS '"';
> 
>     ERROR: invalid byte sequence for encoding "UTF8": 0xe5
>     CONTEXT: COPY test_data, line 627
> 
>     # both files contains the same rows, but test_fail contains more rows
>     # seems that the file more than 65K size cannot be imported
>     # if create DB with UTF8 encoding instead of SQL_ASCII - both tests
>     will be
>     passed
> 
>     # How to generate files:
>     # Imported without errors
>     for i in $(seq 1 207); do echo
>     "NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋
>     外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご
>     連絡いたします。※詳細はこちら【工事について】https://www.test.jp
>     /1234/5678.html&id=12211 <https://www.test.jp/1234/5678.html&id=12211>"
> 
>               /tmp/test_pass.csv; done;
> 
>     # Imported with errors
>     for i in $(seq 1 5722); do echo
>     "NURO光です。明日の宅内工事お立合いよろしくお願い致します。2回目の屋
>     外工事につきましては具体的な工事日案内の準備が整い次第、こちらからご
>     連絡いたします。※詳細はこちら【工事について】https://www.test.jp
>     /1234/5678.html&id=12211 <https://www.test.jp/1234/5678.html&id=12211>"
> 
>               /tmp/test_fail.csv; done;
> 
> 
>     # Both files can be imported without any problem to PostgreSQL 11.
>

Re: BUG #17501: COPY is failing with "ERROR: invalid byte sequence for encoding "UTF8": 0xe5"

Heikki Linnakangas <hlinnaka@iki.fi> — 2022-05-29T10:39:52Z

On 28/05/2022 23:52, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      17501
> Logged by:          Vitaly Voronov
> Email address:      wizard_1024@tut.by
> PostgreSQL version: 14.3
> Operating system:   CentOS Linux release 7.9.2009 (Core)
> Description:
> 
> Hello,
> 
> We've seen a such bug: COPY command shows error "ERROR:  invalid byte
> sequence for encoding "UTF8": 0xe5" on file.
> The same file with small amount of lines is imported without any errors.

I was able to reproduce this with your instructions. Thank you!

The bug was indeed introduced by commit f82de5c46b, in v14. Attached 
patch fixes it. Barring any objections, I'll commit and backpatch this.

I also noticed that there are some outdated comments in copyfromparse.c 
that still talk about the old protocol version 2 and COPY_OLD_FE. I'll 
clean that up as a separate commit.

- Heikki

Re: BUG #17501: COPY is failing with "ERROR: invalid byte sequence for encoding "UTF8": 0xe5"

Heikki Linnakangas <hlinnaka@iki.fi> — 2022-05-29T21:09:38Z

On 29/05/2022 13:39, Heikki Linnakangas wrote:
> On 28/05/2022 23:52, PG Bug reporting form wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      17501
>> Logged by:          Vitaly Voronov
>> Email address:      wizard_1024@tut.by
>> PostgreSQL version: 14.3
>> Operating system:   CentOS Linux release 7.9.2009 (Core)
>> Description:
>>
>> Hello,
>>
>> We've seen a such bug: COPY command shows error "ERROR:  invalid byte
>> sequence for encoding "UTF8": 0xe5" on file.
>> The same file with small amount of lines is imported without any errors.
> 
> I was able to reproduce this with your instructions. Thank you!
> 
> The bug was indeed introduced by commit f82de5c46b, in v14. Attached
> patch fixes it. Barring any objections, I'll commit and backpatch this.
> 
> I also noticed that there are some outdated comments in copyfromparse.c
> that still talk about the old protocol version 2 and COPY_OLD_FE. I'll
> clean that up as a separate commit.

Committed. And I included those comment changes in the same commit, 
after all.

Thanks for the report!

- Heikki