1

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, also MySQL 5.7).

During the import of the dump I received several error messages, which were not helpful. Therefore I looked at the dump file with the nano editor and found out that the content cannot be displayed correctly:

��-^@-^@ ^@M^@y^@S^@Q^@L^@ ^@d^@u^@m^@p^@ ^@1^@0^@.^@1^@3^@ ^@ ^@D^@i^@s^@t^@r^$
^@
^@-^@-^@
^@
^@-^@-^@ ^@H^@o^@s^@t^@:^@ ^@l^@o^@c^@a^@l^@h^@o^@s^@t^@ ^@ ^@ ^@ ^@D^@a^@t^@a^$
^@
...

But if I open the dump under Windows in e.g. Notepad++, everything can be displayed perfectly.

So I thought about an error during the FTP upload. However, if I transfer a file from Windows to Linux and then transfer it back, the new file will still be readable under Windows, but not under Linux.

Can anyone give me a hint? Thanks!

Little update: This is how I made the dump:

.\mysqldump.exe -u root --databases m_data > D:\m_data_dump.sql

Update 2: I also tried exporting with explicit charset:

.\mysqldump.exe -u root --default-character-set=utf8mb4 --databases m_data > D:\m_data_dump.sql

And imprted with:

mysql -u root -p --default-character-set=utf8mb4 < m_data_dump.sql

But sam result.

Improve this question
3
  • Encodings do not match. Use proper charset/collation on all export-import steps.
    – Akina
    Dec 13, 2018 at 10:11
  • Hi Akina, thank your for your message. I already tried with an explicit given charset (see Update 2). What am I doing wrong?
    – Jonson86
    Dec 13, 2018 at 11:37
  • 1) Specify charset while exporting, not only while importing. 2) Check that system/server/connection/database/tables/fields charsets are synced. 3) Test separate copying: structure via mysqldump, data via SELECT INTO OUTFILE / LOAD DATA INFILE.
    – Akina
    Dec 13, 2018 at 12:31

1 Answer 1

Reset to default
3

The contents of the dump file you show appear to be UTF16 - this may be the result of the redirect operator on Windows (particularly if you're using PowerShell).

To avoid the Windows shell redirection operator interfering with the encoding/formatting of data emitted by the mysqldump program, use the --result-file=filename when dumping.

See the MySQL docs for this command line option at https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_result-file

Improve this answer
1
  • Thank you very much, that sounds very plausible, I will try it out.
    – Jonson86
    Dec 19, 2018 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.