I have an issue with a java BasicDataSource connection pool.
My ProxySql is correctly configured on all my DataProc Compute engine, if I try to connect to my CloudSql instance with sqlclient it works correctly. When I try to connect to my CloudSql from my connection pool, configured with this connection string: pooljdbc:mysql://127.0.0.1/my_db?serverTimezone=UTC, I get this error:
Cannot create PoolableConnectionFactory (Access denied for user 'myuser'@'localhost' (using password: YES))
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'myuser'@'localhost' (using password: YES))
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
And is correct because the proxySQL tcp connection is on 127.0.0.1 and not on localhost (unix socket).
I don't understand why the BasicDataSource try to connect on localhost and not on 127.0.0.1 as I have configured in the connection string.
Any help is really appreciated.
The string 'myuser'@'localhost' is referring to your login identity, not the server connection string; if you ran your connection attempt from a machine called foo-instance, for example, even if you use a connection string specifying a remote IP address, your login would be 'myuser'@'foo-instance'. While direct connections using your sqlclient will resolve the source hostname differently based on the server hostname specified, this is a quirk that shouldn't be relied on. In particular, in your case the ProxySQL service will be acting on your behalf, so the source hostname will come from the ProxySQL instance, not from your connection string.
You may want to share more details on your ProxySQL configuration, but if you're using defaults, your PROXYSQL_HOSTNAME would be set to localhost; you could try ensuring your PROXYSQL_HOSTNAME is set to 127.0.0.1 instead.
Interestingly, based on cursory testing, it appears creating user grants identified by 127.0.0.1 is more restrictive to login host, while creating the grant for localhost allows both 127.0.0.1 and localhost for connection hostnames:
MariaDB [(none)]> create user 'dhuo'@'127.0.0.1' identified by 'dhuopass';
...
$ mysql -h localhost -pdhuopass
ERROR 1045 (28000): Access denied for user 'dhuo'@'localhost' (using password: YES)
$ mysql -h 127.0.0.1 -pdhuopass
Welcome to the MariaDB monitor. Commands end with ; or \g.
...
MariaDB [(none)]> drop user dhuo@127.0.0.1
MariaDB [(none)]> create user 'dhuo'@'localhost' identified by 'dhuopass';
...
$ mysql -h localhost -pdhuopass
Welcome to the MariaDB monitor. Commands end with ; or \g.
$ mysql -h 127.0.0.1 -pdhuopass
Welcome to the MariaDB monitor. Commands end with ; or \g.
Generally it'd probably work best to configure your user grants to be identified as coming from localhost instead of 127.0.0.1 to have the best chance of supporting either inbound hostname.