导入SQL至MySQL遇ERROR 2006 (HY000)错误，求助排查与修复

Hey, I’ve run into this exact "MySQL server has gone away" error countless times when importing SQL dumps, so let’s walk through how to diagnose and fix it, starting with your line 1248 issue.

First, let’s pinpoint why line 1248 is causing the disconnect:

• Check the content of line 1248: This line almost always contains either a massive INSERT statement (think bulk inserts with large text/Blob data, like images or long JSON strings) or a syntax error that crashes the connection. To view it quickly:
  • On Linux/macOS: Run sed -n '1248p' your_dump_file.sql in the terminal
  • On Windows: Open the SQL file in a text editor (like VS Code or Notepad++) and jump directly to line 1248
• If it’s a syntax error: Look for unclosed quotes, missing semicolons, or MySQL reserved words used without backticks (e.g., using user as a table name without `user`)
• If it’s a large chunk of data: That’s almost certainly a max_allowed_packet configuration problem—let’s dive into that next.

max_allowed_packet controls the maximum size of a single data packet MySQL can receive. Default values are often tiny (16MB or less), so if your line 1248 has data larger than this limit, MySQL will drop the connection immediately.

How to check your current setting: #

• Log into MySQL and run:

  SHOW VARIABLES LIKE 'max_allowed_packet';
  

  The result will show the value in bytes (e.g., 16777216 = 16MB)
• Or check directly from the terminal without logging in:

  mysql -u your_username -p -e "SHOW VARIABLES LIKE 'max_allowed_packet';"
  

If your current max_allowed_packet is smaller than the data in line 1248, you have two fix options:

Temporary Fix (Resets after MySQL restart) #

This is great for quick testing:

1. Log into MySQL and run:

   SET GLOBAL max_allowed_packet = 1073741824; -- Sets to 1GB, adjust as needed
   

   (Use 536870912 for 512MB, 268435456 for 256MB—pick a size larger than your biggest data packet)
2. Log out and log back into MySQL for the change to take effect, then try importing your SQL file again.

Permanent Fix (Survives MySQL restarts) #

To make the change stick:

1. Locate your MySQL configuration file:
   • Linux: Typically /etc/my.cnf or /etc/mysql/my.cnf
   • Windows: my.ini (usually in your MySQL installation folder or C:\ProgramData\MySQL\MySQL Server X.X)
2. Open the file and find the [mysqld] section. Add or modify this line:

   max_allowed_packet = 1G
   

   (Again, adjust the size based on your needs—don’t go overboard, as it can consume unnecessary memory)
3. Save the file and restart MySQL:
   • Linux: sudo systemctl restart mysql
   • Windows: Open Services, find MySQL, right-click and select "Restart"

If adjusting max_allowed_packet doesn’t fix it, double-check line 1248:

• Verify encoding consistency: Ensure your SQL file’s encoding matches your MySQL database’s encoding (e.g., UTF-8 vs. Latin1)
• Split large statements: If line 1248 is a huge bulk INSERT, split it into smaller INSERT statements (most dump tools let you configure this when creating the dump)
• Check for corrupted data: Your SQL file might have a corrupted chunk at line 1248—try re-creating the dump from the source database if possible.

内容的提问来源于stack exchange，提问作者Pradeepkumar H S