After much searching, I have finally found a solution.
I am not much of a writer, so I will do my best to make this as concise as possible.
So as far as I could find, there are 2 possible solutions:
SQL Relay
http://sqlrelay.sourceforge.net/
This does exactly what the question asked for, and a bunch more. I wont go into too much detail on what I was able to find out about this but will mention that it was not a viable solution as it is not transparent. Meaning that the flow is as follows:
PHP -> Queries -> SQL Relay Extension -> SQL Relay -> Externally hosted MySQL
So this would have involved rewriting all of our code from mysql to sql relay. Not an option in our case.
All that being said, if someone is planning a fresh large scale project that requires any of the numerous features that SQL Relay has, it sounds beautiful.
Mysql Proxy
http://forge.mysql.com/wiki/MySQL_Proxy
This is the solution we ended up using.
The key to making this do what we want it to do is the pooling LUA script for mysql proxy.
This LUA extension can be found at:
https://github.com/cwarden/mysql-proxy/blob/315ab806bb95b8223f5afd3d238eff2a40af03d8/lib/ro-pooling.lua
Without going into too much detail, here are some basic stats... Bare in mind, this is tested at LOW usage time:
[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
6433 38598 572537
After switching to mysql-proxy, and letting things settle:
[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
32 192 2848
As you can clearly see, the TIME_WAIT ports to mysql have dropped to almost none.
The connections are now in fact persistent WITHOUT using mysql_pconnect / mysqli_connect( ... p:hostname ... ).
Worth mentioning there appear to be a few configurable settings near the top of the pooler lua script.
local min_idle_connections
and
local max_idle_connections
These appear to be pretty self explanatory. Except that: It would appear that each username (and password? untested... most likely not tho.) combination creates its own set of persistent connections.
So multiply max_idle_connections by the number of unique mysql users that will be connecting to the database. And that should give you an idea of how many idle connections you will end up having.
So, let me reiterate for so this little blurb hits some keywords for those searching via google:
When using PHP is it possible to have persistent mysql connections WITHOUT mysql_pconnect?
Yes, this can be done via SQL Relay if you dont mind rebuilding most of your code to pipe your queries thru their extension OR transparently using mysql-proxy with the ro-pooling.lua script.
We have been wanting something like this for about a year now.
ENJOY!
You state that "Connections, ports, user accounts and credentials are all fine.", but I have my doubts.
Mentioning the exact error from the documentation:
[The error] occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host
Look in your mysql.user table on the Linux MySQL server for a user entry with the user/host combination of your windows machine:
SELECT User, Host FROM mysql.user WHERE User='my_user' AND Host='windows_ip';
If that doesn't return a result, you'll need to add that user. If it does return a result, can you add that to your question?
Best Answer
Well, on the mysqli constructor page for the php manual you have several examples on how to deal with connection errors, depending on the PHP version. For example, on the first code snippet, change the die() to
However, doing everything programmatically has its disadvantages, and as you are on DBA.stackexchange, and not on stackoverflow, I could suggest an alternative setup:
Obviously, this may cost more than a couple of shared hosting but avoids manual intervention (imports, failover management) and there is better management and control or certain type of errors (network timouts, etc.).