MySQL persistent connections (excerpt)

Copied certain part and formatted from http://www.percona.com/blog/2006/11/12/are-php-persistent-connections-evil/

Originally published: 2006-11-12

…

Lets talk now about why Persistent connections were disabled in mysqli extension. Even though you could misuse persistent connections and get poor performance that was not the reason. The real reason is – you could get much more problems with it.

Persistent connections were added to PHP during times of MySQL 3.22/3.23 when MySQL was simple enough so you could recycle connections easily without any problems. In later versions number of problems however arose – If you recycle connection which has uncommitted transactions you run into trouble. If you happen to recycle connections with custom character set settings you’re in trouble back again, not to mention about possibly changed per session variables.

Many applications are fine – ie if you have read-only page and everything on your site uses same charset and does not use per session variables in some tricky way it would work perfect for you. In some complex applications it however can lead to very hard to track bugs.

What would be proper solution to this ?

MySQL should support command which resets connection and guarantees it will be the same as freshly created connection, so the fact connection is persistent can be completely transparent. May be it even should get fresh connection ID so if you track web requests in MySQL Logs by connection ID it would still work. MySQL has change_user() call which kind of does some of this but it looks like no one knows if it does everything what needs to be done, plus it surely was broken in some MySQL versions.

So persistent connections are not evil but they need some MySQL support to be properly implemented. Now with new mysqlnd driver for PHP effort by MySQL team I hope this issue could be fixed. Internal guys sure have more leverage on server development team than external community :)

…