Configuring MySQL server to avoid "MySQL Server has gone away" error

mysql mysqldump mysql-management
siva636 picture siva636 · Sep 13, 2011 · Viewed 13.7k times · Source

When I tried to establish a MySQL database using a dump, I got the following error message

ERROR 2006 (HY000) at line 312: MySQL server has gone away

After extensive Googling (and searching in this forum), I found that I have to increase the max_allowed_packet in the my.ini file. I have tried various values (including very large values) for this parameter, but I cannot get rid of this error.

Even if I specify the --max_allowed-packet option in the command line (as follows) there is no use

C:\Users\Panora>mysql --user=root --password=root --max-allowed-packet=600M db < dump.sql
ERROR 2006 (HY000) at line 50973: MySQL server has gone away

I tried creating the dump file using the following command, still no use

mysqldump --opt --skip-extended-insert --max_allowed_packet=10M --user=root --password=root db > dump.sql

Please advice me what I am really missing.

Answer

siva636 picture siva636 · Sep 21, 2011

After extensive analysis, I have found that the old technique simply works, yes, editing the my.ini configuration file.

But the actual problem is to locate the my.ini file (MySQL V5.5 on Windows Vista), which is in the following location and this folder is HIDDEN (not like earlier versions) by default:

C:\ProgramData\MySQL\MySQL Server 5.5\my.ini

(Note the name carefully, this is NOT Program Files but ProgramData)

Related questions

Is there a faster way to load mysqldumps?

Possible Duplicate: Speeding up mysql dumps and imports mysqldump is reasonably fast, but dumps of a medium-sized database (20-30 megs) take several minutes to load using mysql my_database < my_dump_file.sql Are there some mysql settings I …

Read More
Importing 1GO SQL File => ERROR 2013 (HY000) at line 23: Lost connection to MySQL server during query

Ive got to import 1go of sql data, i raised up the max_allowed_packet to 1100M to be sure. So i use : My query mysql -u root -p -D mainbase < GeoPC_WO.sql But 1 minute later it stops …

Read More
Can MySQL reliably restore backups that contain views or not?

Environment: Ubuntu 11.10, MySQL 5.1.58 I have a small database with views. When I try to dump and restore, I get ERROR 1356 (HY000) at line 1693: View 'curation2.condition_reference_qrm_v' references invalid table(s) or column(s) or function(s) or …

Read More