Brak wartości NULL, ale niepoprawna sekwencja bajtów do kodowania „UTF8”: 0x00

Spędziłem ostatnie 8 godzin, próbując zaimportować dane wyjściowe „mysqldump --compatible = postgresql” do PostgreSQL 8.4.9, i przeczytałem już co najmniej 20 różnych wątków tutaj i gdzie indziej na temat tego konkretnego problemu, ale nie znalazłem prawdziwa użyteczna odpowiedź, która działa.

Zrzucone dane MySQL 5.1.52:

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

Serwer PostgreSQL 8.4.9 jako miejsce docelowe

Ładowanie danych za pomocą polecenia „psql -U rt_user -f foo” zgłasza się (wiele z nich, oto jeden przykład):

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".

Zgodnie z tym, w pliku wejściowym nie ma znaków NULL (0x00).

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

Podobnie, kolejne sprawdzenie za pomocą Perla pokazuje brak wartości NULL:

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

Jak wspomina „WSKAZÓWKA” w błędzie, próbowałem wszystkich możliwych sposobów, aby ustawić „kod_klienta” na „UTF8”, ale mi się to udaje, ale nie ma to wpływu na rozwiązanie mojego problemu.

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#

Idealne, ale:

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".
...

Z wyjątkiem poprawnej odpowiedzi „Według Hoyle'a”, która byłaby fantastyczna do usłyszenia i wiedząc, że tak naprawdę nie dbam o zachowanie znaków spoza ASCII dla tych rzadko odwoływanych danych, jakie masz sugestie?

Aktualizacja: Podczas importu pojawia się ten sam błąd w wersji tego samego pliku zrzutu tylko do ASCII. Naprawdę zadziwiające:

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

Jedna z omawianych tabel jest zdefiniowana jako:

                                        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)

Nie mam możliwości zmiany typu żadnej części schematu DB. Takie postępowanie prawdopodobnie zepsułoby przyszłe aktualizacje oprogramowania itp.

Prawdopodobną kolumną problemu jest „treść” typu „tekst” (być może także inne w innych tabelach). Jak już wiem z poprzednich badań, PostgreSQL nie zezwoli na NULL w wartościach „tekstowych”. Jednak zobacz wyżej, gdzie zarówno sed, jak i Perl nie wyświetlają znaków NULL, a następnie dalej w dół, gdzie usuwam wszystkie znaki spoza ASCII z całego pliku zrzutu, ale nadal pojawia się błąd.

Co najmniej jedno z tych pól znaków / tekstu MOŻE mieć wartość 0x00.

Spróbuj wykonać następujące czynności:

SELECT * FROM rt3 where some_text_field = 0x00 LIMIT 1;

Jeśli to zwróci pojedynczy wiersz, spróbuj zaktualizować te pola znaków / tekstu za pomocą:

UPDATE rt3 SET some_text_field = '' WHERE some_text_field = 0x00;

Następnie wypróbuj inną MYSQLDUMP ... (i metodę importu PostgreSQL).

Miałem ten sam problem z MySQL w wersji 5.0.51 i Postgres w wersji 9.3.4.0. Rozwiązałem problem „nieprawidłowej sekwencji bajtów do kodowania„ UTF8 ”: 0x00” po tym, jak zobaczyłem komentarz Daniela Vérité, że „mysqldump w trybie postgresql zrzuci null bajty jako \ 0 w ciągach, więc prawdopodobnie chcesz wyszukać tę sekwencję znaków”.

Z pewnością grep w końcu ujawnił znaki NULL.

grep \\\\0 dump.sql

Zastąpiłem znaki NULL za pomocą następującego polecenia

sed -i BAK 's/\\0//g' dump.sql

Następnie Postgres mógł pomyślnie załadować plik dump.sql

Ten błąd można uzyskać bez bajtu NULL lub znaku innego niż ascii w pliku. Przykład w bazie danych utf8:

select E'ab\0cd';

da:

BŁĄD: niepoprawna sekwencja bajtów do kodowania „UTF8”: 0x00 WSKAZÓWKA: Ten błąd może również wystąpić, jeśli sekwencja bajtów nie jest zgodna z kodowaniem oczekiwanym przez serwer, który jest kontrolowany przez „kodowanie klienta”.

mysqldump w trybie postgresql zrzuci null bajty jako \ 0 w ciągach, więc prawdopodobnie chcesz wyszukać tę sekwencję znaków.

W połowie pamiętam taki problem. Myślę, że zakończyłem migrację schematu, a następnie zrzut danych jako csv i ładowanie danych z pliku csv. Pamiętam, że musiałem zaktualizować plik csv (przy użyciu narzędzi uniksowych, takich jak sed lub unixtodos) lub użyć otwartego pakietu kalkulacyjnego (excell), aby naprawić niektóre elementy, które były błędami na etapie importowania - może to być tak proste, jak otwarcie i ponowne zapisanie pliku plik.