Mysql – Rare error lasts only a second “Can’t connect to local MySQL server through socket ‘/tmp/thesql.sock’ (146)” 146=ECONNREFUSED

connectivityerrorsMySQL

This is a rare error that occurs on Solaris 10, have seen it from Perl, where this line
DBI->connect(...) or die "Whoops! $DBI::errstr"

outputs Whoops! Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146) at something.pl line ...

I determined that on my system, 146=ECONNREFUSED

It is very rare for this error to occur, but it happens on an ongoing basis.
It always a temporary problem on this setup—lasting only a second or two.

The global variable max_connections has been ruled out as a problem.

This does not occur on TCP/IP based connections.

I am using the [mysqld_safe] log-error option in my.cnf.
The log outputs the following at startup:
mysqld started
InnoDB: Started; log sequence number...
[Note] .../libexec/mysqld: ready for connections.
Version: '5.0.27-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
There are no other log entries, so there's nothing here to indicate the problem.

Please let me know if you can suggest a setting for more detailed logging.

Can you enumerate the possible or likely causes of this error happening only occasionally and only for a second or two?

Best Answer

Life has many great mysteries. For the MySQL DBA, one of life's greatest mysteries is your question. It's been this way going on 12 years. In fact, I found a bug report that was resolved when the disappearance of mysql.sock was caused by trying to start mysqld with mysqld already running (a.k.a. Shooting mysqld in the Foot).

I have personally addressed workarounds to this situation many times in the StackExchange:

Jul 02, 2013 : MySQL crash. Unknown cause. Signal 11 (ServerFault)
Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?
Mar 06, 2013 : How to properly kill MySQL?
Feb 28, 2013 : mysql restart won't kill child processes on CentOS
Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)
May 08, 2012 : How to properly stop MySQL server on Mac OS X?

The mystery has to do with mysql.sock (the MySQL Socket file) disappearing. Once this happens, you cannot connect to mysqld using root@localhost. In fact, no user defined with host='localhost' in the mysql.user table can connect. TCP/IP connections can still be used. The workaround is to connect to mysqld using TCP/IP.

Try create the user root@'127.0.0.1'. From the mysql client you then do this:

mysql -uroot -p -h127.0.0.1 --protocol=tcp

The only way to bring back mysql.sock is to restart mysqld WHEN mysqld IS NOT RUNNING.

You should make sure you explicitly define the mysql.sock file in /etc/my.cnf

[mysqld]
socket=/var/run/mysqld/mysql.sock

Of course, go create the folder

mkdir /var/run/mysqld
chmod 777 /var/run/mysqld

Give it a Try !!!

UPDATE 2013-12-06 10:17 EST

Based on your comments...

I included the log, please let me know if you know how to increase the detail

You are using MySQL 5.0.27, which means you are using a very old mysqld (with a truck load of bugs) and an old InnoDB Storage Engine. There may not be additional detail available. I also noted you are using a Source Distribution rather than RPM installed binaries.

bring back mysql.sock is to restart mysqld. This does not happen for me. I never have to do anything. The problem lasts a second and goes away.

If the problem remains for only a second or two, I can see what is happening. When you run service mysql start, you are actually running mysqld_safe. At the bottom of mysqld_safe, there is an infinite loop. In the infinite loop, it calls mysqld and waits for a return value. Certain return values from mysqld will make mysqld_safe terminate (normal shutdown,condition where mysqld can't start again). Other return values will cause mysqld_safe to loop and try mysqld again.

SUGGESTION

You should upgrade to MySQL 5.6 to get the latest version of mysqld and the InnoDB Storage Engine. If the problem still persists, at least there will be less baggage due to better quality MySQL binaries. That way, you can rule out mysqld and look for other external factors. Also, you should download RPM binaries rather than compiling source because RPM version usually have more optimizations in place.

UPDATE 2013-12-06 10:39 EST

If you look back at the two links at the top of my answer

you will learn that the first link references MySQL 4.0.20, and the second link was from a bug report thread that said the version in question was MySQL 3.22.32 (the final release of MySQL 3 was 3.23.58)

Issues with mysql.sock has been around 13 years now due to shooting yourself in the foot (starting mysqld even though it is running), internal factors (very old version or clunky version of MySQL) or some external condition (lack of RAM or other OS resources).

Then, it looks like you found an internal factor. To verify that mysqld is correcting itself, run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number continues increasing and never resets, then you can rightly blame the clunky MySQL binary for not being more elaborate. MySQL AB was probably solving some internal issue and may have left some debug statements or trace points in the binary

UPDATE 2013-12-06 11:24 EST

Just looked inside mysqld_safe. There is code to print mysqld ended and mysqld restarted. If mysqld did indeed terminate, mysqld_safe would have logged it. If mysqld was restarted by mysqld_safe, the fresh/new instance of mysqld would print out the log information including version number. Since the version number was printed only once, it is clear that mysqld was never ended and never restarted. – George Bailey 13 mins ago

Just FYI, I found out that 146 is ECONNREFUSED, I edited it in to my question. – George Bailey 11 mins ago

Then, it looks like you found an internals issue. The source-compiled MySQL 5.0.27 mysqld may be just printing debug messages after an internal recovery. To verify mysqld is OK in terms of being up, just run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number does not reset and just keeps increasing, then mysqld is correcting itself from within. Given the old build you are using and the way it was compiled, I would not expect mysqld to be more forthcoming on its own.

You should see if mysqld was started with debug enabled. If not, try setting debug in my.cnfand see if it can get more verbose about its internals. I hope this reveals what the problem may be.

Related Solutions

Fixing ‘Can’t Connect to Local MySQL Server’ Error

First: Make sure that the owner of /var/lib/mysql and its files and subdirectories is mysql user of mysql group

to do that run the following command:

chown mysql:mysql -R /var/lib/mysql/*

Second: change write permissions to rwxr-xr-x (755) for mysql directory, and all its files and subdirectories, by running the following command:

chmod 755 -R /var/lib/mysql/*

now start mysql service

service mysql start

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.