1

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

13
  • Please share more details, like the error message in text form and your attempts to resolve the problem. Also, how is this related to programming?
    – Nico Haase
    Mar 25, 2022 at 9:00
  • @NicoHaase I modified the post by inserting the requested data.
    – TurboProgram
    Mar 25, 2022 at 9:06
  • Show us lines 45-47 of the source file. And show it in hex. If those lines don't show the "INSERT" command (or whatever), then show us the line with that. Probably the dump needed to be taken with a different set of parameters.
    – Rick James
    Mar 25, 2022 at 15:46
  • @Rick James Here are the required screens: -Hex: ibb.co/hZzvWb2 -Lines of Notepad++: ibb.co/Sy2tp9z Thanks :) Mar 25, 2022 at 20:31
  • How was the dump taken? Perhaps something changed the encoding before the attempt to 'load'?
    – Rick James
    Mar 28, 2022 at 16:43

2 Answers 2

0

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.

3
  • Hi @Rick James, thanks for replying. I tried the command you told me and the result is: "Variable 'character_set_client' can't be set to the value of 'ucs2'". Going deeper into the database in HEX, I noticed that in some places the code is "00 00 00 [...]".Could you tell me what this is due to? Thanks :) Mar 28, 2022 at 11:33
  • @TurboProgram - Hmmm... I added to my Answer.
    – Rick James
    Mar 28, 2022 at 16:40
  • In my database I find references to "GIT" and code in C #, do you know why? Thank you :) @Rick James I added some screens to my post. Mar 28, 2022 at 16:52
0

Try this commend line

mysql -u root -p -h localhost -D database --binary-mode -o < dumpfile.sql

Note: make sure your sql file is not zipped.

Reffernce - https://stackoverflow.com/questions/38383975/mysql-error-ascii-0-while-importing-sql-file-on-linux-server

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.