Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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:

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.

share|improve this answer
    
On Linux I get error 2 ('No such file or directory') if I try to connect the mysql client to a UNIX domain socket that doesn't exist. The OP is running Solaris, though, where the error 146 he's getting appears to mean 'connection refused'. A useful experiment for the OP to do would be to run 'mysql -S /tmp/doesnotexist.sock' and see if the same error 146 occurs. If so, it's likely that the problem is indeed something deleting the socket file. My guess would be a script or sysadmin cleaning /tmp. –  James Jul 11 '13 at 20:11
    
@Rolando, Thanks for your regularly detailed answers. Please see my edit. 1. I included the log, please let me know if you know how to increase the detail. 2. 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. Have you ever heard of it only lasting a second or two and then going a way by itself? –  George Bailey Dec 6 '13 at 14:53
    
@James, script or sysadmin cleaning /tmp, ruled out. mysql -S /tmp/missing.sock outputs Can't connect...(2). mysql -S /dev/null outputs Can't connect...(95). The error message I am getting above is Can't connect...(146). The only difference is the number at the end. –  George Bailey Dec 6 '13 at 14:56
    
@GeorgeBailey This is still a mystery today. 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. I noticed you are using MySQL 5.0.27. You should upgrade to 5.6. –  RolandoMySQLDBA Dec 6 '13 at 15:05
1  
Included reason to upgrade in my answer... –  RolandoMySQLDBA Dec 6 '13 at 15:23
show 8 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.