Error ASCII '\0' when importing 29GB .sql file into mysql 5.7 database on Ubuntu 22.04

Question

I have 29GB magento db .sql file that need to be imported into mysql database, I am using Ubuntu 22.04 operating system

I import into database using command line as below

mysql -u root -p

mysql > USE magento_project1 mysql > source db_dump.sql

The import seems to be working fine at first, but after a few hours of importing error as below comes out

ERROR at line 44259: ASCII '\0' appeard in the statement, but this is not alllowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: '

Attached the image of the error

I noticed the query stopped when at the part it need to insert data into sales_order_payment table

I have try

1. rename the db_dump.sql file to db_dump.sql.gz

• based on solution given here

2. import using command mysql -u root -p -f -D database_name < db_dump.sql.gz

3. when create database, create charset as utf8mb4, collation utf8mb4_general_ci

4. import using command mysql -u root -p -h localhost -D database_name --binary-mode -o < db_dump.sql

• based on solution given here

When i write command file db_dump.sql.gz I get the result as below

db_dump.sql.gz: ASCII text, with very long lines (37268)

My ubuntu is already sudo apt-update and sudo apt-upgrade with mysql, php, nginx and elasticsearch installed and enabled

But none of the solution above fix the issue I am having

Answer 1

Able to fix the issue by dumping new database from magento cloud using command below

magento-cloud db:dump

0 -> Staging -> 0 ( database )

then import the db into local using command below

mysql -u root -p -f -D local_db < dumped_db.sql

Not sure what cause the error above on the first place, might be the db corrupted somewhere during transfer between pendrive ?