Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MySQL Server Has Gone Away or "Elvis Has Left the Building!"

Posted on 2015-02-13
10
101 Views
Last Modified: 2015-02-23
I've got sql file I'm trying to import into my database using phpMyAdmin. Cake and Ice Cream, right? The sql file is a little over 6MB. Every time I attempt to import it, I get a message that says "MySQL server has gone away.

I've been able to access my.ini and I changed the read_buffer_size to 8 M and the key_buffer_size to 32M. I chose these settings based on what I able to learn via google. Still getting the same error which tells me that I'm missing something.

What do I need to change?

Thanks!
0
Comment
Question by:brucegust
  • 6
  • 4
10 Comments
 
LVL 13

Expert Comment

by:Phil Phillips
ID: 40608428
Try changing wait_timeout and max_allowed_packet.  For testing, you can go big with:

wait_timeout = 600
max_allowed_packet = 128M

Open in new window

0
 

Author Comment

by:brucegust
ID: 40608585
Phil! Thanks for getting back with me!

It didn't work, but let me ask you this:

First of all, I'm using my.ini as opposed to my.cnf. From what I can gather, it's the same thing, it just depends on your operating system, correct?

Second, on my my.ini, I see a bunch of lines prefaced with "Uncomment the following if you are using InnoDB tables:

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 600

When I look at myPhpAdmin at I'm perusing the "type" of tables that I have, I see "InnoDB." Do I need to uncomment those lines?

BTW: I appreciate your help. I'm trying to get this app live today and it's nothing more than making a couple of tweaks and uploading some tables to a database. I've never encountered this error and it's killin' me!

What do you think?
0
 
LVL 13

Expert Comment

by:Phil Phillips
ID: 40608698
On Windows, my.ini will work.  You can also run 'SHOW VARIABLES' as a query just to make sure that the options are being set.

Yes, you can uncomment those if you are using InnoDB, and go ahead and tweak the memory settings depending on the size of your RAM (refer to the comments within the config file).  Though, I'm not sure that will fix the problem.  For me, it's only really been the network settings that caused that particular issue.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:brucegust
ID: 40608921
OK, Phil! Check this out.

I had an epiphane. I took the same sql file and attempted it upload to another database using phpmyAdmin on another computer. It took a while, but it worked.

Copied the my.ini file from that computer and pasted it into the configuration I've got on this box.

Ran it and got the same message: "MySQL server has gone away."

You mentioned "network settings." Mind you this is my localhost. Can you think of anything else?
0
 

Author Comment

by:brucegust
ID: 40608923
Also, if I uncomment the lines pertaining to innodb..., nothing works. phpmyAdmin, anything related to the database just flat out quits. Add the "#" back in, gold. But I can't change those settings and have the thing work.
0
 
LVL 13

Expert Comment

by:Phil Phillips
ID: 40608966
The network settings I was referring to were 'wait_timeout' and 'max_allowed_packet'.  Check to see through the 'SHOW VARIABLES' query that the settings are actually applied.

An ugly hack would be to try to split up the dump into smaller files (but you shouldn't have to do that...)
0
 

Author Comment

by:brucegust
ID: 40609013
wait_timeout is set to 28800 and max_allowed_packet is 1048576

When I compare those values to the my.ini file that I've been manipulating, the max_allowed_packet is:
 
max_allowed_packet = 16M

...and the wait timeout is:

#innodb_lock_wait_timeout = 50

Now, Phil, something doesn't smell right here. The variables that are listed on the my.ini file don't seem to match what's actually being perceived by system, yes? Is there a discrepancy? BTW: I'm accessing my.ini by highlighting the my.ini option in the WAMP interface.

thoughts?
0
 
LVL 13

Accepted Solution

by:
Phil Phillips earned 500 total points
ID: 40609024
Yes, looks like you're either editing the wrong my.ini file, or there's an override somewhere.  Also, make sure you are restarting MySQL after making changes ;).

Just to get things working, you can try executing these commands in MySQL:

SET GLOBAL max_allowed_packet=134217728;
SET GLOBAL wait_timeout=600;

Open in new window


That should at least force those settings, and you can try the import.  If that works, then you can at least continue on and take your time in figuring out which conf file to edit :).
0
 

Author Comment

by:brucegust
ID: 40612033
Changed the settings, Phil, and I was stoked because I figured this was the ultimate way in which to establish some settings that would pave the way for what needed to be done.

Nothing.

Same error.

What do you think?
0
 

Author Comment

by:brucegust
ID: 40625906
I wasn't able to get it to work, but I did find a workaround so I was able to accomplish what I needed to get done. Thanks. Phil, for helping me out with the MySQL code that allowed me to directly affect the max_allowed_packet setting and the max_timeout variable.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question