MySQL Server has gone away when importing large sql file

Question

I tried to import a large sql file through phpMyAdmin   But it kept showing error       MySql server has gone away    What to do

User · Answer

I had this error and other related ones  when I imported at 16 GB SQL file  For me  editing my ini and setting the following  based on several different posts  in the  mysqld  section   max allowed packet        110M innodb buffer pool size 511M innodb log file size 500M innodb log buffer size   800M net read timeout          600 net write timeout         600   If you are running under Windows  go to the control panel  services  and look at the details for MySQL and you will see where my ini is  Then after you edit and save my ini  restart the mysql service  or restart the computer    If you are using HeidiSQL  you can also set some or all of these using that

User · Answer

For GoDaddy shared hosting  On GoDaddy shared hosting accounts  it is tricky to tweak the PHP ini etc files  However  there is another way and it just worked perfectly for me   I just successfully uploaded a 3 8Mb  sql text file  containing 3100 rows and 145 cols  Using the IMPORT command in phpMyAdmin  I was getting the dreaded MySQL server has gone away error  and no further information    I found that Matt Butcher had the right answer  Like Matt  I had tried all kinds of tricks  from exporting MySQL databases in bite-sized chunks  to writing scripts that break large imports into smaller ones  But here is what worked    1  CPANEL ---  FILES  group  ---  BACKUP   2a  Under  Partial Backups  heading     2b  Under  Download a MySQL Database Backup   2c  Choose your database and download a backup  this step optional  but wise    3a  Directly to the right of 2b  under heading  Restore a MySQL Database Backup   3b  Choose the  SQL import file from your local drive  3c  True happiness will be yours  shortly       Mine took about 5 seconds     I was able to use this method to import a single table  Nothing else in my database was affected -- but that is what step  2  above is intended to protect against    Notes  a  If you are unsure how to create a  SQL import file  use phpMyAdmin to export a table and modify that file structure   SOURCE  Matt Butcher 2010 Article

User · Answer

If your data includes BLOB data   Note that an import of data from the command line seems to choke on BLOB data  resulting in the  MySQL server has gone away  error   To avoid this  re-create the mysqldump but with the --hex-blob flag   http   dev mysql com doc refman 5 7 en mysqldump html option mysqldump hex-blob  which will write out the data file with hex values rather than binary amongst other text   PhpMyAdmin also has the option  Dump binary columns in hexadecimal notation  for example   abc  becomes 0x616263   which works nicely   Note that there is a long-standing bug  as of December 2015  which means that GEOM columns are not converted  Back up a table with a GEOMETRY column using mysqldump  so using a program like PhpMyAdmin seems to be the only workaround  the option noted above does correctly convert GEOM columns

User · Answer

i got a similar error   to solve this just open my ini file  here at line no 36 change the value of maximum allowed packet size ie  max allowed packet   20M

User · Answer

For me this solution didn t work out so I executed  SET GLOBAL max allowed packet 1073741824    in my SQL client   If not able to change this with MYSql service running  you should stop the service and change the variable in  my ini  file   For example    max allowed packet 20M

User · Answer

Make sure mysqld process does not restart because of service managers like systemd   I had this problem in vagrant with centos 7  Configuration tweaks didn t help  Turned out it was systemd which killed mysqld service every time when it took too much memory

User · Answer

If you are using MAMP on OS X  you will need to change the max allowed packet value in the template for MySQL    You can find it at  File   Edit template   MySQL my cnf Then just search for max allowed packet  change the value and save

User · Answer

If you are running with default values then you have a lot of room to optimize your mysql configuration   The first step I recommend is to increase the max allowed packet to 128M   Then download the MySQL Tuning Primer script and run it  It will provide recommendations to several facets of your config for better performance   Also look into adjusting your timeout values both in MySQL and PHP   How big  file size  is the file you are importing and are you able to import the file using the mysql command line client instead of PHPMyAdmin

User · Answer

I updated  max allowed packet  to 1024M  but it still wasn t working  It turns out my deployment script was running    mysql --max allowed packet 512M --database mydb -u root  lt    db db sql   Be sure to explicitly specify a bigger number from the command line if you are donig it this way

User · Answer

I solved my issue with this short  etc mysql my cnf file      mysqld  wait timeout   600 max allowed packet   100M

User · Answer

If increasing max allowed packet doesn t help   I was getting the same error as you when importing a  sql file into my database via Sequel Pro    The error still persisted after upping the max allowed packet to 512M so I ran the import in the command line instead with   mysql --verbose -u root -p DatabaseName  lt  MySQL sql   It gave the following error    ASCII   0  appeared in the statement  but this is not allowed unless option --binary-mode is enabled   I found a couple helpful StackOverflow questions     Enable binary mode while restoring a Database from an SQL dump Mysql ERROR  ASCII   39  0  39  while importing sql file on linux server   In my case  my  sql file was a little corrupt or something  The MySQL dump we get comes in two zip files that need to be concatenated together and then unzipped  I think the unzipping was interrupted initially  leaving the file with some odd characters and encodings  Getting a fresh MySQL dump and unzipping it properly worked for me   Just wanted to add this here in case others find that increasing the max allowed packet variable was not helping

User · Answer

As stated here      Two most common reasons  and fixes  for the MySQL server has gone away    error 2006  are       Server timed out and closed the connection  How to fix           check that wait timeout variable in your mysqld   s my cnf configuration file is large enough  On Debian  sudo nano    etc mysql my cnf  set wait timeout   600 seconds  you can   tweak decrease this value when error 2006 is gone   then sudo    etc init d mysql restart  I didn t check  but the default value for   wait timeout might be around 28800 seconds  8 hours     Server dropped an incorrect or too large packet  If mysqld gets a packet that is too large or incorrect  it assumes that something has   gone wrong with the client and closes the connection  You can increase   the maximal packet size limit by increasing the value of   max allowed packet in my cnf file  On Debian  sudo nano    etc mysql my cnf  set max allowed packet   64M  you can   tweak decrease this value when error 2006 is gone   then sudo    etc init d mysql restart       Edit   Notice that MySQL option files do not have their commands already available as comments  like in php ini for instance   So you must type any change tweak in my cnf or my ini and place them in mysql data directory or in any of the other paths  under the proper group of options such as  client    myslqd   etc  For example       mysqld  wait timeout   600 max allowed packet   64M   Then restart the server  To get their values  type in the mysql client                       gt  select   wait timeout   gt  select   max allowed packet

User · Answer

The other reason this can happen is running out of memory   Check  var log messages and make sure that your my cnf is not set up to cause mysqld to allocate more memory than your machine has   Your mysqld process can actually be killed by the kernel and then re-started by the  safe mysqld  process without you realizing it   Use top and watch the memory allocation while it s running to see what your headroom is   make a backup of my cnf before changing it

User · Answer

I am doing some large calculations which involves the mysql connection to stay long time and with heavy data  i was facing this  Mysql go away issue   So i tried t optimize the queries but that doen t helped me then i increased the mysql variables limit which is set to a lower value by default    wait timeout max allowed packet  To the limit what ever suits to you it should be the Any Number   1024 Bytes   you can login to terminal using  mysql -u username - p  command and can check and change for these variable limits

User · Answer

I had similar error today when duplicating database  MySQL server has gone away      but when I tried to restart mysql server restart I got error  ERROR  The server quit without updating PID       This is how I solved it  I opened up Applications Utilities  and ran Activity Monitor   quit mysqld   then was able to solve the error problem with  mysql server restart

User · Answer

None of the solutions regarding packet size or timeouts made any difference for me  I needed to disable ssl  mysql -u -p -hmyhost com --disable-ssl db  lt  file sql   https   dev mysql com doc refman 5 7 en encrypted-connections html

User · Answer

If you are working on XAMPP then you can fix the MySQL Server has gone away issue with following changes    open your my ini file my ini location is  D  xampp mysql bin my ini   change the following variable values   max allowed packet   64M innodb lock wait timeout   500

User · Answer

I got same issue with   image base64   base64 encode file get contents   FILES  file    tmp name        image    data image jpeg base64    image base64   query    insert into images image  values     image       mysqli query  con  query     In  xampp mysql bin my ini file of phpmyadmin we get only   mysqldump  max allowed packet 110M   which is just for mysqldump -u root -p dbname   I resolved my issue by replacing above code with  max allowed packet 110M  mysqldump  max allowed packet 110M

User · Answer

If it takes a long time to fail  then enlarge the wait timeout variable    If it fails right away  enlarge the max allowed packet variable  it it still doesn t work  make sure the command is valid SQL  Mine had unescaped quotes which screwed everything up   Also  if feasible  consider limiting the number of inserts of a single SQL command to  say  1000  You can create a script that creates multiple statements out of a single one by reintroducing the INSERT    part every n inserts

[mysql] MySQL Server has gone away when importing large sql file

Examples related to mysql

Examples related to phpmyadmin