mysql_pconnect(): send of 5 bytes failed with errno=32 Broken pipe

Question

I've been using the same DB abstraction library for years. But today it started writing these Notice (8) messages in my log.

The application is working correctly but every time a script connects to the DB the same notice is logged.

I cannot think what might have changed. This is happening on my local dev machine.

OS X 10.6.2
PHP 5.3.0 (cli) 
mysql Ver 14.12 Distrib 5.0.87
mysqlnd 5.0.5-dev - 081106 - $Revision: 1.3.2.27 $

Glairy

Did you try to connect via command line utility? – Homophile

Is there something similar/related in the mysqld log as well? – Unstrained

i've no problems using the mysql cli client. and as i said, mysqlnd does connect and the connection works but it throws this notice ob every pconnect() now. – Glairy

nothing noted in the mysqld error log. i do have --log-warnings set – Glairy

OH How Annoying! I restarted apache (I use apxs2 sapi) and now the error messages have stopped. It won't be easy to find what was causing them now. – Glairy

If the errors will occur again now that you've restarted the apache my bets are on timeouts and mysqlnd (not so) silently reconnecting. – Unstrained

Answer 1

If someone is struggling with this issue, here is the fix:

Try changing/setting up wait_timeout in your mysql my.cnf config file:

wait_timeout=3600

This config file is located in the /etc/mysql/my.cnf (Ubuntu/Debian) and /usr/local/mysql/my.cnf (OSX).

Restart mysql server and it should work.

Minette

Answer 2

Only solution I've found so far is, changing

// From
PDO::ATTR_PERSISTENT => true
// To
PDO::ATTR_PERSISTENT => false

Not so happy with this, but works in the meantime. I'm using a very old PC for personal project, so I'm guessing the problem could have to do with lack of resources.

Hypogynous

I noticed this started happening when I tried to use persistent connections, too. Maybe I'm missing something. – Irrationality

Answer 3

I am using PHP 5.6.20, PDO (throwing exceptions only), and MySQL 5.6.28 with persistent connections and EVERYTHING is utf8mb4. My entire stack is set up for utf-8 (dsn string settings, connections, database server databases, tables, columns, Apache 2.4.12, PHP, all webpages, CSS ... you name it).

I get the following error message intermittently and it is mystifying and annoying.

Notice: PDO::__construct(): send of 5 bytes failed with errno=32 Broken pipe in file /foo/bar/baz

Assuming a persistent connection is a noninteractive one, the MySQL 5.6 manual (5.1.4 Server System Variables) says the following about the server system variable wait_timeout.

The number of seconds the server waits for activity on a noninteractive connection before closing it.

Default: 28800 sec

(28000 sec / 1) * (1 hour / 3600 sec) = 8 hours

Max: 31536000 sec

((31536000 sec / 1) * (1 hr / 3600 sec) * (1 day / 24 hrs) = 365 days

Therefore, check wait_timeout in your my.cnf and decide if persistent connections are what you need. Also, you'll have to invest in making your application more robust to account for a persistent connection that has been torn down. Clearly, you do not want your client to come back the next day (having gone home for the night) and say "What the heck?!"

Irrationality

Answer 4

It may be because your data contains 'utf-8' characters. I had the similar issue is caused by it.

Exception: mysql_query(): send of 1462592 bytes failed with errno=32 Broken pipe

I used

mysql -u username -p database < dump_file # this is bad 

to import the sql file contains lot of UTF8 characters (Thai language), but I didn't set default-character-set=utf8 for [mysql]. So the wrong coded data in the database caused that issue.

Sandell

This can happen even if you have everything set to utf-8. That includes using utf8mb4. I am using MySQL 5.6 with PHP 5.6.20 and I still got the issue. I remember that it started when I tried to implement persistent connections. – Irrationality

Answer 5

just remove mysqlnd driver and use mysqli Yes mysqlnd more modern, but what about stability? next commands fix your problem

apt-get remove php5-mysqlnd

apt-get install php5-pdo-mysql

Honkytonk

I'm sorry, but you don't know what you're talking about. mysqlnd is a PHP extension that can be used by the mysql_*(), mysqli, and PDO APIs as an alternative to linking in libmysqlclient.so, which allows better memory efficiency when fetching rows and potential speed gains due to tighter coupling. mysqlnd doesn't seem to expose any APIs to PHP code, only other extensions. – Reindeer