Mysql ERROR 1064 while importing database via SSH

General
1

I just take a backup from my webmin server and want to restore it on cyberpanel since the database is large I was restoring it via command line. but I am getting an error

ERROR 1064
ERROR 1064 (42000) at line 76929: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘

The dynamic response body size is over the limit, the response will be tru…’ at line 129
2

The error you’re encountering during the database restoration indicates a syntax issue in the SQL file, specifically around line 76929. Here are some steps to troubleshoot and resolve the problem:

1. Inspect the SQL Dump File

Open the SQL dump file in a text editor (like nano, vim, or any text editor of your choice) and navigate to line 76929 to see what the SQL statement looks like. The error message suggests there might be a problem with a dynamic response body size or an incomplete statement.

2. Look for Common Issues

Common SQL syntax issues include:

  • Mismatched Quotes: Check for unmatched single or double quotes.
  • Comment Syntax: Ensure comments (like -- or /* ... */) are correctly formatted.
  • Incomplete Statements: Look for incomplete SQL commands that might need closing brackets or semicolons.

3. Check for Unsupported SQL Features

If the SQL dump includes features not supported by MariaDB or the version you are using, it could lead to errors. Look for any MySQL-specific commands or features that might not be compatible.

4. Split the SQL File

If the SQL dump is large, consider splitting it into smaller files. This can help isolate the problematic section. You can do this using a text editor or a script. Here’s a simple command to split it:

split -l 1000 your_dump_file.sql part_

This will create smaller files (with 1000 lines each) that you can restore individually.

5. Use mysql Command-Line Options

When restoring, you can use the --force option with the mysql command to skip errors and continue restoring:

mysql -u username -p --force your_database < your_dump_file.sql

Note: Using --force can result in data loss if the errors are critical.

6. Validate the SQL File

Before restoring, you can validate the SQL file with:

mysqlcheck -u username -p --databases your_database

7. Fixing Specific Errors

If you identify a specific line causing the error, you can manually fix it. Look for the specific syntax issue, and correct it according to SQL standards.

8. Check MariaDB Version Compatibility

Ensure that the SQL dump is compatible with the version of MariaDB you’re using. Check for any deprecated features or syntax differences between the versions.

9. Error Logging

You can enable error logging in MariaDB to capture more detailed information about the issue:

  1. Open the MariaDB configuration file (usually located at /etc/my.cnf or /etc/mysql/my.cnf).
  2. Add or uncomment the following lines:
[mysqld]
log_error = /var/log/mariadb/mariadb.log
  1. Restart the MariaDB service:
systemctl restart mariadb

After that, you can check the log file for more detailed error messages.

Conclusion

By following these steps, you should be able to troubleshoot the SQL syntax error and restore your database successfully. If you continue to encounter issues, please share any specific lines or errors, and I can help further!