MySQL Bugs: #30304: Proxy loses connection

Bug #30304	Proxy loses connection
Submitted:	8 Aug 2007 13:28	Modified:	18 Dec 2008 17:38
Reporter:	Gerry Reno	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Proxy	Severity:	S2 (Serious)
Version:	0.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Warning: mysql_pconnect() [function.mysql-pconnect]: Lost connection to MySQL server at 'reading authorization packet', system error: 0 in ....../adodb/drivers/adodb-mysql.inc.php on line 366

How to repeat:
This is occurring after the application has been idle for some time and then the user clicks on something. If they continue to click on the icon (about 5 times or more) eventually they will get a connection and the page. This did not happen prior to mysql-proxy. 

Suggested fix:
--

Thank you for the report.

Please confirm or refuse if I understood what you said correctly:

1. You have PHP web application which uses ADODB and connect to MySQL server via MySQL proxy.
2. When user uses web page and stay idle for several minutes, she get "Lost connection" error.
3. After refreshing web page connection restores.

Yes, your summary is correct.

Thank you for the feedback.

I can not repeat described behaviour with plain PHP. Please provide example script which uses ADODB.

We need information about:

1. How you connect to database (connection parameters).
2. How do you issue queries (simple query bug is repatable with).

db connection is via php:

from php.ini:

[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
mysql.max_links = -1

; Default port number for mysql_connect().  If unset, mysql_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
; mysql.default_port =
; mysql.default_port = 3306
mysql.default_port = 4040

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
; mysql.default_host =
mysql.default_host =  127.0.0.1

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file.
; *Any* user with PHP access can run 'echo cfg_get_var("mysql.default_password")
; and reveal this password!  And of course, any users with read access to this
; file will be able to reveal the password as well.
mysql.default_password =

As far as a query that duplicates the bug:

There is no one action that causes this bug.  This behavior is spurious.  It is not possible to just make it happen.  It occurs often enough though, and when it does occur it is usually after the user has been idle for some period of time.  It must have something to do with the persistent connection.

Are you using any lua connect_server or disconnect_client hooks? 

Do you get the same behaviour with persistent connections turned off (pconnect seems to be a pretty bad idea if you're trying to use any of the connection pool based lua)

Same problem here.

How to reproduce:

On /usr/local/share/mysql-proxy/rw-splitting.lua:

                min_idle_connections = 4,
                max_idle_connections = 8,

Starting:

/usr/local/sbin/mysql-proxy \
--proxy-backend-addresses=10.25.0.12:3306 \
--proxy-read-only-backend-addresses=10.25.0.11:3306 \
--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua

PS: 10.25.0.12 its the master and 10.25.0.11 its the slave.

Run this script:

$ n=0; while [ $? -eq 0 ]; do sleep 0.1; let n=$n+1; echo $n; mysql --port=4040 -h10.25.0.12 -u test -ptest123 tiago -e "select * from guestbook"; done

And you will see:

5 idle connections on master;
5 idle connections on slave

On attempt number 11, will will see:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading final connect information', system error: 0

4 idle connections on master;
5 idle connections on slave

Debug messages of problem

Attachment: mysql-proxy-debug.txt (text/plain), 12.50 KiB.

This quick fix should solve this problem:

quick fix:

execute the following on your master mysql server:

mysql> set @@old_passwords=OFF;
mysql> SET PASSWORD FOR 'proxytest'@'%' = PASSWORD('proxytest');

http://bugs.mysql.com/bug.php?id=31574

But I will need to to this in all users/ passwords that I have?

I'm using SVN version, #369

Gerry, just to be sure you are experiencing same problem as Tiago please indicate if you use old_passwords.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Same problem here and I'm using old_passwords = 1

Helmuth,

thank you for the feedback. Seems you experiencing bug #31574 and I need feedback from the original reporter to be sure if these bugs are duplicates.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I can confirm I am having this same problem using the PHP library Propel, version 1.3 (via the PHP framework Symfony, version 1.2).

MySQL Proxy version 0.6.1.

The Propel error spat out in the browser is:

----------------------------------
Unable to open PDO connection [wrapped: SQLSTATE[HY000] [2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 111]
----------------------------------

This is the PHP Propel command that failed:

----------------------------------
Propel::initConnection(array('dsn' => 'mysql:dbname=my_db_name;host=my_mysql_proxy_host;port=4040', 'user' => 'myuser', 'password' => 'mypassword', 'classname' => 'DebugPDO', array('ATTR_PERSISTENT' => '1'), array(array('value' => 'utf8'), array())), 'propel')
----------------------------------

my.cnf:
--------------
old_passwords=1
--------------

mysql-proxy ended up crashing and this is what was printed on the terminal (one line per failed request):

---------------------------------------
ioctl(12, FIONREAD, ...) said there is something to read, oops: 37
ioctl(10, FIONREAD, ...) said there is something to read, oops: 37
ioctl(11, FIONREAD, ...) said there is something to read, oops: 37
ioctl(14, FIONREAD, ...) said there is something to read, oops: 37
ioctl(13, FIONREAD, ...) said there is something to read, oops: 37
network-mysqld.c.995: unhandled state: 16
---------------------------------------

The @@old_passwords workaround described in http://bugs.mysql.com/bug.php?id=31574 seems to fix this problem.