Strings are no longer null-terminated in the software of this century instead, every string is stored with an explicit length. In contrast, modern systems treat zero bytes with much more passivity. 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.
Text encodings have evolved since this convention was established, but no widely-used encoding has introduced a new meaning for the zero byte. 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. But today I'm here to discuss something that PostgreSQL can't do: handle null characters (also known as zero bytes) in text values.Ĭonventionally, a zero byte is reserved to mark the end of a text string, so a zero byte inside a string is a contradiction. 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. Its features are well-designed, and they compose elegantly. 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.PostgreSQL is a great piece of software. As I already know from previous research, PostgreSQL will not allow NULL in 'text' values. The likely problem column is 'content' of type 'text' (perhaps others in other tables as well). Doing so would likely break future upgrades of the software, etc. I do not have the liberty to change the type for any part of the DB schema. "attachments3" btree (parent, transactionid) "attachments_pkey" PRIMARY KEY, btree (id) Id | integer | not null default nextval('atta)Ĭontentencoding | character varying(80) | One of the tables in question is defined as: Table "public.attachments" Psql:mysql5-dump.sql:45: ERROR: invalid byte sequence for encoding "UTF8": 0x00 Psql:mysql5-dump.sql:41: ERROR: invalid byte sequence for encoding "UTF8": 0x00 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. Psql:mysql5-dump.sql:30: ERROR: invalid byte sequence for encoding "UTF8": 0x00 Mysql5-dump.sql differ: byte 1304850, line 30ĭatabase-dumps:rcf-temp1# psql -U postgres -f mysql5-dump.sql -variable=client_encoding=utf-8 rt3 Truly mind-boggling: database-dumps:rcf-temp1# convert any non-ASCII character to a spaceĭatabase-dumps:rcf-temp1# perl -i.bk -pe 's/]/ /g ' mysql5-dump.sqlĭatabase-dumps:rcf-temp1# sum mysql5-dump.sql ĭatabase-dumps:rcf-temp1# cmp mysql5-dump.sql Update: I get the same error with an ASCII-only version of the same dump file at import time. Psql:foo:29: ERROR: invalid byte sequence for encoding "UTF8": 0x00īarring 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? Perfect, yet: database-dumps:rcf-temp1# psql -U rt_user -f foo -variable=client_encoding=utf-8 rt3 database-dumps:rcf-temp1# psql -U rt_user -variable=client_encoding=utf-8 -c "SHOW client_encoding " rt3 Likewise, another check with Perl shows no NULLs: database-dumps:rcf-temp1# perl -ne '/\000/ and print ' fooĪs 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# sed 's/\x0/ /g' nonullsĭatabase-dumps:rcf-temp1# sum foo nonulls 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".Īccording the following, there are no NULL (0x00) characters in the input file. 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
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 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.
0 kommentar(er)
