Problem importing a Dump on MariaDB (Ubuntu 20.04.4 LTS)



  • I am writing this post because I cannot import a dump on MariaDB (Version 15.1) using Ubuntu 20.04.4 LTS. I did this dump a few weeks ago and now trying to import it using the command

    mysql -u root -p database < dumpfile.sql
    

    gives me the following error:

    Error at line 46: ASCII ' 0' appeared in the statement but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode.

    Error 1

    So, I tried with the following command:

    mysql -u root -p --binary-mode = 1 database < dumpfile.sql
    

    But it gives me the following error:

    Error at line 47: Unknown command '\▒'.
    

    Error 2

    I tried to open the database using Notepad ++ and going to line 47 I noticed that it has strange characters:

    Error 3

    I don't understand if it's a problem with the dump getting corrupted on export. I want to clarify that some parts are clearly visible, while others are seen with strange characters as in the figure.

    I have tried various programs to check the integrity of the database (without success), I have tried to change the encoding of Notepad ++ to UTF-8 but nothing has changed.

    I also tried to put --default-character-set=utf8 in the command to import the database but it always gives me the above error.

    I specify that the .gz file appears to be corrupt (I cannot open it) and I do not have the possibility to do the dump again as I have the database only in .sql format.

    EDIT:

    I tried with the command:

    mysql --default-character-set=ucs2 -u root -p database < dumpfile.sql
    

    But the result is: "Variable 'character_set_client' can't be set to the value of 'ucs2'".

    As for the HEX, some points are legible, while others are "00 00 00 [...]".

    HEX00

    EDIT 2.0:

    In my database I find references to "GIT" and code in C #.

    1. git_ref

    2. c#_ref



  • That is probably CHARACTER SET ucs2.

    mysql --default-character-set=ucs2 -u root -p database < dumpfile.sql
    

    That does not impact what charset is used in the tables, only for reading the file.

    History

    MySQL 5.0.67 (Aug, 2008): "ucs2 does not work as a client character set, but attempts to use it as such were not rejected. Now character_set_client cannot be set to ucs2. This also affects statements such as SET NAMES and SET CHARACTER SET. (Bug #31615)".

    MySQL 8.0.28 (Jan, 2022): ucs2, macroman, macce, dec, hp8 and "their collations in SQL statements or elsewhere in the MySQL server now produces a deprecation warning." (That is, they are still supported, but not for very long.)

    Conclusion: It may not be possible to import that data.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2