Error invalid byte sequence for encoding utf8 0x00

I’ve spent the last 8 hours trying to import the output of ‘mysqldump —compatible=postgresql’ into PostgreSQL 8.4.9, and I’ve read at least 20 different threads here and elesewhere already about this specific problem, but found no real usable answer that works.

MySQL 5.1.52 data dumped:

mysqldump -u root -p --compatible=postgresql --no-create-info --no-create-db --default-character-set=utf8 --skip-lock-tables rt3 > foo

PostgreSQL 8.4.9 server as destination

Loading the data with ‘psql -U rt_user -f foo’ is reporting (many of these, here’s one example):

psql:foo:29: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

According the following, there are no NULL (0x00) characters in the input file.

database-dumps:rcf-temp1# sed 's/x0/ /g' < foo > nonulls
database-dumps:rcf-temp1# sum foo nonulls
04730 2545610 foo
04730 2545610 nonulls
database-dumps:rcf-temp1# rm nonulls

Likewise, another check with Perl shows no NULLs:

database-dumps:rcf-temp1# perl -ne '/00/ and print;' foo
database-dumps:rcf-temp1#

As the «HINT» in the error mentions, I have tried every possible way to set ‘client_encoding’ to ‘UTF8’, and I succeed but it has no effect toward solving my problem.

database-dumps:rcf-temp1# psql -U rt_user --variable=client_encoding=utf-8 -c "SHOW client_encoding;" rt3
 client_encoding
-----------------
 UTF8
(1 row)

database-dumps:rcf-temp1#

Perfect, yet:

database-dumps:rcf-temp1# psql -U rt_user -f foo --variable=client_encoding=utf-8 rt3
...
psql:foo:29: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
...

Barring the «According to Hoyle» correct answer, which would be fantastic to hear, and knowing that I really don’t care about preserving any non-ASCII characters for this seldom-referenced data, what suggestions do you have?

Update: I get the same error with an ASCII-only version of the same dump file at import time. Truly mind-boggling:

database-dumps:rcf-temp1# # convert any non-ASCII character to a space
database-dumps:rcf-temp1# perl -i.bk -pe 's/[^[:ascii:]]/ /g;' mysql5-dump.sql
database-dumps:rcf-temp1# sum mysql5-dump.sql mysql5-dump.sql.bk
41053 2545611 mysql5-dump.sql
50145 2545611 mysql5-dump.sql.bk
database-dumps:rcf-temp1# cmp mysql5-dump.sql mysql5-dump.sql.bk
mysql5-dump.sql mysql5-dump.sql.bk differ: byte 1304850, line 30
database-dumps:rcf-temp1# # GOOD!
database-dumps:rcf-temp1# psql -U postgres -f mysql5-dump.sql --variable=client_encoding=utf-8 rt3
...
INSERT 0 416
psql:mysql5-dump.sql:30: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encod.
INSERT 0 455
INSERT 0 424
INSERT 0 483
INSERT 0 447
INSERT 0 503
psql:mysql5-dump.sql:36: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encod.
INSERT 0 502
INSERT 0 507
INSERT 0 318
INSERT 0 284
psql:mysql5-dump.sql:41: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encod.
INSERT 0 382
INSERT 0 419
INSERT 0 247
psql:mysql5-dump.sql:45: ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encod.
INSERT 0 267
INSERT 0 348
^C

One of the tables in question is defined as:

                                        Table "public.attachments"
     Column      |            Type             |                        Modifie
-----------------+-----------------------------+--------------------------------
 id              | integer                     | not null default nextval('atta)
 transactionid   | integer                     | not null
 parent          | integer                     | not null default 0
 messageid       | character varying(160)      |
 subject         | character varying(255)      |
 filename        | character varying(255)      |
 contenttype     | character varying(80)       |
 contentencoding | character varying(80)       |
 content         | text                        |
 headers         | text                        |
 creator         | integer                     | not null default 0
 created         | timestamp without time zone |
Indexes:
    "attachments_pkey" PRIMARY KEY, btree (id)
    "attachments1" btree (parent)
    "attachments2" btree (transactionid)
    "attachments3" btree (parent, transactionid)

I do not have the liberty to change the type for any part of the DB schema. Doing so would likely break future upgrades of the software, etc.

The likely problem column is ‘content’ of type ‘text’ (perhaps others in other tables as well). As I already know from previous research, PostgreSQL will not allow NULL in ‘text’ values. However, please see above where both sed and Perl show no NULL characters, and then further down where I strip all non-ASCII characters from the entire dump file but it still barfs.

Describe what is not working as expected.
while inserting a data from a file there we are getting the error msg of this.

Description of the Issue
we have many columns in a table and like that their many tables we cannot add on each and every table

Is there any other way we can do it As if when we do it in MSSQL it automatically removes the space. But in Postgres, we have the issue I think.

Workaround
we have to replace the space in the column

Exception message:
Stack trace:
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.ValueTask`1.get_Result()
at Npgsql.NpgsqlDataReader.d__46.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.d__100.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

C# .NET
Npgsql version:4.0.4
PostgreSQL version:11.1
Operating system:Windows R2 2012

In this article, we will see how you can fix error ‘invalid byte sequence for encoding UTF8’ while restoring a PostgreSQL database. At work, I got a task to move DBs which has ASCII encoding to UTF8 encoding. Let me first confess that the ASCII DBs was not created by intention, someone accidentally created it!!! Having a DB ASCII encoded is very dangerous, it should be moved to UTF8 encoding as soon as possible. So the initial plan was to create archive dump of the DB with  pg_dump , create a new DB with UTF8 encoding and restore the dump to the new DB using  pg_restore . The plan worked for most of the DBs, but failed for one DB with below error.

DETAIL: Proceeding with relation creation anyway.
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 35091; 0 2527787452 TABLE DATA my_table release
pg_restore: [archiver (db)] COPY failed for table "my_table": ERROR: invalid byte sequence for encoding "UTF8": 0xa5
CONTEXT: COPY my_table, line 41653
WARNING: errors ignored on res

As the error says, there are some invalid UTF8 characters in table “my_table” which prevents pg_restore from restoring the particular table. I did a lot of research and googling to see what to do. I will list out what all steps I did.

Assume ‘my_db’ and ‘my_table’ is the database name and table name respectively.

Step 1:

Dump the Database excluding particular table ‘my_table’. I would suggest dumping the database in archive format for saving time and disk space.

pg_dump -Fc -T 'my_table' -p 1111  -f dbdump.pgd my_db

Step 2:

Create the new database with UTF8 encoding and restore the dump.

pg_restore -p 2222 -j 8 -d my_new_db dbdump.pgd

The restoration should be successful as we didn’t restore the offending table.

Step 3:

Dump the offending table ‘my_table’ in plain text format.

pg_dump -Fp -t 'my_table' -p 1111 my_db >  my_db_table_only.sql

Step 4:

Now we have table data in plain text. Let’s find invalid UTF8 characters in the file by running below command(make sure locale is set to UTF-8,).

# grep -naxv '.*'   my_db_table_only.sql
102:2010-03-23 ��ԥ�	data1 data2

� represents an invalid UTF8 character and it is present in 102th line of the file.

Step 5:

Find which charset the invalid UTF8 characters belongs to.

#grep -naxv '.*' my_db_table_only.sql > test.txt 
#file -i test.txt
test.txt: text/plain; charset=iso-8859-1

As per the output, those characters belongs to iso-8859-1. The charset may be different in your case.

Step 6:

Let’s convert  iso-8859-1  to  UTF8  using  iconv  command.

#grep -naxv '.*' my_db_table_only.sql |  iconv --from-code=ISO-8859-1 --to-code=UTF-8
102:2010-03-23 ¥Êԥ¡ data1 data2

Now you got the characters in UTF8 encoding. So you can just replace  ��ԥ� with  ¥Êԥ¡   in 102th line of dump file(I used  nano  editor to do this, faced issues with  Vim .)

I know that replacing characters manually could be a pain in the ass if there are lot of invalid UTF8 characters. We can run  iconv  on the whole file as shown below.

iconv --from-code=ISO-8859-1 --to-code=UTF-8 my_db_table_only.sql  > my_db_table_only_utf8.sql

But I won’t recommend this as it may change valid characters(eg: Chinese characters ) to some other characters. If you plan to run iconv on the file, just make sure only invalid UTF8 characters are converted by taking  diff  of both files.

Step7.

Once the characters are replaced. Restore the table to the database.

psql -p 2222 -d my_new_db -f my_db_table_only.sql

No more “Invalid byte sequence for encoding UTF8” error. Thanks for the time taken to read my blog. Subscribe to this blog so that you don’t miss out anything useful   (Checkout Right Sidebar for the Subscription Form and Facebook follow button)  . Please also put your thoughts as comments .

By Anders Cornell, Jr. DBA

PostgreSQL is a great piece of software. Its features are well-designed, and they compose elegantly. It’s among the most versatile and reliable software I’ve ever used and its comprehensive superiority over other relational database products leads me to think of PostgreSQL as the data-store that can do anything. But today I’m here to discuss something that PostgreSQL can’t do: handle null characters (also known as zero bytes) in text values.

Conventionally, a zero byte is reserved to mark the end of a text string, so a zero byte inside a string is a contradiction. If a text string were to contain a zero byte, then that string would be truncated by any software that relies on C’s null-terminator convention. Text encodings have evolved since this convention was established, but no widely-used encoding has introduced a new meaning for the zero byte. So, regardless of encoding, if a zero byte is encountered within a string, it is probably safe to assume that it is there by mistake.

In contrast, modern systems treat zero bytes with much more passivity. Strings are no longer null-terminated in the software of this century; instead, every string is stored with an explicit length. This describes almost all software written in C++, Java, Python, Ruby, Go, JavaScript, Clojure, or Rust, for example, as well as most newer C code that does serious text handling. Application software that treats a zero byte as a string terminator is now the exception.

As a result, mistake or not, zero bytes occur in text nowadays. Cosmic rays, buggy UI code, and meddlesome users are all capable of producing a text string containing them.

Ideally, these zero bytes and other splashes of definite meaninglessness in text could be summarily rejected as errors, but human language is horrifically complicated, and in practice text validation must be conservative. Absent higher-level, application-specific validation, the most you can do without stepping on someone’s toes is to verify that the bytes of a string decode to a sequence of valid characters in your chosen character set.

Since it’s 2020, your chosen character set is Unicode, encoded with UTF-8. In UTF-8, a zero byte represents the code point U+0000 (NULL), just as a 0x61 byte represents U+0061 (LATIN SMALL LETTER A). The Unicode Standard does designate some code points “noncharacters” that “should never be interchanged,” but U+0000 is not one of them. In other words, there is no basis for rejecting null characters in the standard. Accordingly, UTF-8 decoders and Unicode normalization routines, as found in modern library code, do not reject null characters.

But PostgreSQL, whose backend codebase is over 30 years old and written in C, does. Try, for example:

postgres=# SELECT e'string with a  byte';

ERROR: invalid byte sequence for encoding "UTF8": 0x00

PostgreSQL’s UTF-8 text type does not allow zero bytes. In other words, there are valid Unicode text strings that PostgreSQL cannot store as text.

The implications are far-reaching due to the foundational nature of the text type. For example, jsonb represents JSON string values internally as text, which means that, counterintuitively, it is possible to give PostgreSQL some valid JSON and get back an error:

postgres=# SELECT '{"a_json_object": "with_a_u0000_byte"}'::jsonb;

ERROR: unsupported Unicode escape sequence

LINE 1: SELECT '{"a_json_object": "with_a_u0000_byte"}'::jsonb;

^

DETAIL: u0000 cannot be converted to text.

CONTEXT: JSON data, line 1: {"a_json_object":...

To give another example, it is similarly impossible to use PostgreSQL’s text-search features on a document containing null characters, since such a document is not representable as text.

To be fair, for a system that cannot accept text with null characters, PostgreSQL handles text with null characters commendably. It’s careful not to let a null character slip in through DML, and throws a descriptive error rather than silently truncating the string. Furthermore, the frontend-backend protocol does not use null-termination, sparing database driver code the responsibility of catching zero bytes. It’s hard to imagine how a system that uses null-termination internally could behave better in the face of null characters. Most software written in C does much worse, and PostgreSQL’s diligence does a lot to head off potential null-byte injection vulnerabilities.

However, PostgreSQL comes in last among relational databases in null character support. Oracle, MS, and even MariaDB, for all their faults, treat U+0000 like any other character.

If one accepts that text can contain null characters, but still wants to store such text in PostgreSQL, there are two workarounds:

1. Strip null characters out, or replace them with a different character (I suggest U+FFFD REPLACEMENT CHARACTER) before passing text values to the database. Otherwise, PostgreSQL will abort the transaction and throw an error. The possibility of null characters must be considered at every occasion where your application hands a string off to the database. Even though null characters are rare and unmeaningful, the need to remove them will present an unexpected burden.
2. Abandon text and store the UTF-8 bytes of the string in PostgreSQL as bytea instead. Zero bytes are, of course, permitted in bytea values, so a column of type bytea can store any valid UTF-8 string. (Not to mention any invalid one.) With bytea, INSERTing a valid string will never cause an “invalid byte sequence” error, and when later SELECTed, a string will return from the database with all its characters intact. This transparency comes at the expense of all of PostgreSQL’s text-processing features. JSON, full-text search, locale-aware comparison, regular expressions and more are unavailable when using bytea.

One of these approaches must be identified and implemented on an application-by-application basis. As long as PostgreSQL rejects null characters, individual engineering teams will continue to spend time working around the problem. Some teams have been lucky, their systems never encountering a null character, and haven’t had to spend time implementing a workaround. This will become less common as UTF8-encoded Unicode becomes the standard approach to text representation and the null terminator convention dies off—both of these processes are well underway.

There is only one solution. It will be difficult, but it is necessary. PostgreSQL must learn to accept null characters.

Supporting null characters will be a breaking change, but on the bright side, it need not break applications or database drivers—the frontend-backend protocol could be respecified to allow embedded nulls, without changes to client-side code, because text values in the protocol are already length-prefixed, not null-terminated. Text values are also length-prefixed in tuples on disk, so no change is needed in the disk data format either.

Here’s my four-step plan for fixing PostgreSQL to support null characters:

1. Switch to a length-prefixed string representation for all internal text-processing code: Datum (already length-prefixed) instead of char *. Null characters are still disallowed, but at this point, absent extensions, the database could handle them correctly.
2. Deprecate public functions (functions in both the C and fmgr sense) that use the cstring data type. This includes supporting and preferring Datum-based I/O functions for new base types defined in extensions, instead of the cstring-based ones that are currently required.
3. Introduce a cluster-wide, off-by-default configuration option for allowing null characters. Turning the option on will break extensions that use the deprecated functions, and applications that assume null characters are illegal.
4. Far in the future, when the null-termination convention is but a distant memory, allow null characters by default.

Null-termination is a relic, and beginning to show its age. A previous generation of developers may protest, but in software that handles UTF-8 text, erroring on zero bytes should be considered a bug. To keep its status as a reliable, enterprise-grade, production-ready relational database and worthy core component in modern software stacks, PostgreSQL must make this difficult transition.

How has the null-character bug affected your company? Did you discover this blog post after a single zero byte crashed your entire application? What do you think of my four-step plan? Let’s make PostgreSQL better together. Leave a comment below.

Если вам нужно хранить данные UTF8 в своей базе данных, вам нужна база данных, которая принимает UTF8. Вы можете проверить кодировку своей базы данных в pgAdmin. Просто щелкните правой кнопкой мыши базу данных и выберите «Свойства».

Но эта ошибка, похоже, говорит вам о некоторых недопустимых данных UTF8 в исходном файле. Это означает, что утилита copy обнаружила или предположила, что вы загружаете файл UTF8.

Если вы работаете под некоторым вариантом Unix, вы можете проверить кодировку (более или менее) с помощью file.

$ file yourfilename
yourfilename: UTF-8 Unicode English text

(Я думаю, что это будет работать и на Mac в терминале.) Не уверен, как это сделать в Windows.

Если вы используете ту же самую утилиту для файла, который поступает из систем Windows (то есть файла, который не закодирован в UTF8), он, вероятно, будет показывать что-то вроде этого:

$ file yourfilename
yourfilename: ASCII text, with CRLF line terminators

Если ситуация остается странной, вы можете попытаться преобразовать свои входные данные в известную кодировку, изменить свою клиентскую кодировку или и то, и другое. (Мы действительно растягиваем пределы моих знаний о кодировках.)

Вы можете использовать утилиту iconv для изменения кодировки входных данных.

iconv -f original_charset -t utf-8 originalfile > newfile

Вы можете изменить кодировку psql (клиент), следуя инструкциям Поддержка набора символов. На этой странице найдите фразу «Включить автоматическое преобразование набора символов».