Link to home
Create AccountLog in
Avatar of BlakeISS
BlakeISS

asked on

Database: MySQL server has gone away

We have an inventory server that has been quiely running for years.  I had to go in and delete some large log files because the drive became filled up.  After rebooting it has run now for months but today, I started seeing an error  "Database: MySQL server has gone away" at the top of the website.  I have gone in and stopped and restarted the mysql service
/etc/init.d/mysql stop
/etc/init.d/mysql start
and have also tried rebooting the server but still get the error
"Database: MySQL server has gone away"
Avatar of Loganathan Natarajan
Loganathan Natarajan
Flag of India image

May be "The database connection has timed out and thus the SQL has failed" ..

ref. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
What all file types did u deleted, Does that contain any of the .dat file or .frm files. If so then u need to move back those file and do a Back up of Mysql, rather then deleting the files manually,

Hope this helps
Avatar of BlakeISS
BlakeISS

ASKER


OS Information
intranet ~ # uname -r
2.6.15-gentoo-r1

MYSQL Version
intranet ~ # mysql --version
mysql  Ver 11.18 Distrib 3.23.58, for pc-linux-gnu (i686)

Running top  I see two services for mysql running
 9517 root      23   0  2324 1172  992 S  0.0  0.1   0:00.00 safe_mysqld
 9605 mysql     16   0 37088 5360 1988 S  0.0  0.5   0:00.01 mysqld
ID:34446464Author:shinuq

"What all file types did u deleted, Does that contain any of the .dat file or .frm files. If so then u need to move back those file and do a Back up of Mysql, rather then deleting the files manually,

Hope this helps "

I only deleted two log files nothing else.  
ID:34446460Author:logudotcomDate:12/29/10 10:17 PM
logudotcom:
May be "The database connection has timed out and thus the SQL has failed" ..

ref. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html 

How would i know if this is the cause? I found the mysql log files
/var/log/mysql/ mysql.err
and
mysql.log
there are no timeout errors located in these logs.
SOLUTION
Avatar of Shinesh Premrajan
Shinesh Premrajan
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

ID:34446491Author:shinuqDate:12/29/10 10:24 PM
If there are two services running then try connecting to the mysql from command prompt

mysql -u username -p password -P 3306

if this connects then u should see all the data bases are available using

show databases
Ok, well now it appears as though none of the passwords that the previous admin left me are working ..

ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
 one question.. if my password has characters in it such as  "&" do i need to put brackets into the password field?
I tried quotes and no quotes
mysql -u root -p"s&mplep&ssword"
Yes .. this is the part where I realize I do not have the root password for the MYSQL instance.. awesome..


ID:34446494Author:logudotcomDate:12/29/10 10:25 PMExpert Comment
There may be other common reasons described here,

.. read from " Some other common reasons for the MySQL server has gone away error are:"
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

Unfortunately I am a beginner in MYSQL so the information in this link is not really helpful.. I have gone through and looked and can verify that the --skip networking option commented out 9 not in use)
You may also see the MySQL server has gone away error if MySQL is started with the --skip-networking option.



intranet ~ # mysqld --log-warnings=2
mysqld: unrecognized option `--log-warnings=2'
mysqld  Ver 3.23.58 for pc-linux-gnu on i686


basedir:     /usr/
datadir:     /var/lib/mysql/
tmpdir:      /tmp/
language:    /usr/share/mysql/english/
pid file:    /var/run/mysqld/mysqld.pid
logfile:     /var/log/mysql/mysql.log
TCP port:    3306
Unix socket: /var/run/mysqld/mysqld.sock
 current-values.txt
OK one thing is clear that your mysql is up and its running in the default port. tyr accessing it using the mysqladmin.
open this file to see wt is actually went missing

/var/log/mysql/mysql.log
Default pass for mysql root is blank...did you try just doing
mysql
from the command line to see if your client connects locally?
If there is a php app of any kind under the web root that was using the db, there will be a connection string with at least one user / pass.  
There are ways to reset the root pass also.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
In looking at the log file, I see that now, mysql is reporting:
/usr/sbin/mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
101229 22:27:12            1 Connect     Access denied for user: 'root@localhost' (Using password: YES)


Prior to when the issue started, it was logging connections like this:

/usr/sbin/mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
prior to the issue beginning,
mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

Do I need to reset the root password to be blank? I am on vacation and to the best of my knowlege, don't believe anyone has changed the root password.  How do I reset the root password for mysql to be blank?
mysql-log.txt
I did manage to reset the root password to be blank and have restarted the server.  In looking at the log it looks like it doesn't like the authentication of using a password.

Below is copy of the session showing that the password is blank:
login as: root
Using keyboard-interactive authentication.
Password:
Last login: Thu Dec 30 05:08:57 2010 from 192.168.1.65
intranet ~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 3.23.58-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+-----------+
| Database  |
+-----------+
| blakemis  |
| mysql     |
| test      |
| usernames |
+-----------+
4 rows in set (0.03 sec)

mysql>

Here is a snippet of the log file since the reboot:
/usr/sbin/mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
101230  5:22:13            1 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:32:47            2 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:33:40            3 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:33:54            4 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:34:14            5 Connect     root@localhost on
101230  5:34:38            5 Query       mysql
101230  5:35:13            5 Query       mysql
101230  5:35:29            5 Query       update user set password= where User='root'
101230  5:35:42            5 Query       update user set password= where User=root
101230  5:37:28            5 Query       SET PASSWORD FOR root@localhost=PASSWORD('')
101230  5:37:39            5 Quit      
/usr/sbin/mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
101230  5:40:26            1 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:40:41            2 Connect     Access denied for user: 'root@localhost' (Using password: YES)
101230  5:40:44            3 Connect     Access denied for user: 'root@localhost' (Using password: YES)

Where can I look to see how it is trying to authenticate using password?
(Using password: YES)

This string means that you are providing a password to connect  to the server

If not password is given during the connect u will get  something like below

(Using password: NO)

mysql -u root -p <enter>

Hope this helps





ID:34446880Author:mugojavaDate:12/30/10 12:52 AMExpert Comment
Reclaim your mysqld root!

http://forums.gentoo.org/viewtopic.php?t=153453 

Thanks for the tip Mugojava,  I am fortunate enough to have been able to reset the password and now need to figure out where the authentication is broken.


ID:34446915Author:shinuqDate:12/30/10 01:09 AMExpert Comment
(Using password: YES)

This string means that you are providing a password to connect  to the server

If not password is given during the connect u will get  something like below

(Using password: NO)

mysql -u root -p <enter>

Hope this helps

Where do I begin to look for the file that holds this authentication method please.
To get a list of users, you can, from mysql client..

use mysql;
select * from user\G;

To see the grants for each,
show grants for user@host;

You can also set or change grants for any user /host  you need.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Look in the apache log and the mysql log time stamps match to see wt files are accessing the MySql server.

There must be some config file in the Inventroy server that hold the DB connection details.

hope this helps
Ok,  I deeply thank all that responded to my question.  Without your expert advice, I would not have fixed what was broken.
The expert comments were all insightful and my points handed out were merited by efforts and relevence.  
The issue was due to the mysql root password being changed  but not within the scripts in the www directory.

Thank you!
Glad to help. Thanks for the points.