mysql_change_user() call to proxysql failed with error 1045

[Marco Doets]

We are migrating from a hardware level 4 loadbalancer to proxysql.

We run into some issues and I’m wondering if we did something wrong or if we are trying to do something that can’t be done. 

The situation:

We are running a 3 node galera cluster where we use proxysql as a proxy/loadbalancer. The application using this database is using a odbc connector with pooling to make connection to the proxy sql server.

We are running the proxysql in ha mode, but it think this is not directly relevant for this issues we have. Just one of the two proxysql servers is serving the client. But I wanted just to give as much as possible information.

Everything seems to be fine and the application can use the database. But in the proxysql logging we see a lot of errors like below:

2021-01-19 17:21:43 MySQL_Session.cpp:6059:handler___status_WAITING_CLIENT_DATA___STATE_SLEEP___MYSQL_COM_CHANGE_USER(): [ERROR] ProxySQL Error: Access denied for user 'applicationuser'@'10.10.10.10' (using password: YES)2021-01-19 17:21:43 MySQL_Session.cpp:6872:unable_to_parse_set_statement(): [WARNING] Unable to parse unknown SET query from client 10.10.10.10:62103. Setting lock_hostgroup. Please report a bug for future enhancements:set @@max_execution_time=30000

The application developer showed us that the odbc pooling is using a mysql_change_user() to reset connections in the pool. See also wireshark info.

The change_user() is fails with a error 1045, then a TCP/fin/ack and a new login is succesfull.

I have read that proxysql can used in a cascade configuration, so I suppose connection pooling at application side can be used. But then, maybe proxysql is using the mysql_reset_connection() in stead of mysql_change_user() to reset a connection in the pool.

Does anyone recognise this issue or know what is going wrong?

 

   

[john danilson]

We had a similar issue using wildfly pooled connections.  After many weeks debugging we finally gave up trying to figure this out and Percona with whom we have a support contract similarly could not find a root cause.  We finally abandoned the approach and came up with a totally different architecture to change how we use the BigIP f5 in our infrastructure in favor of proxysql.  So far our new approach is working.  It's off topic for this thread but email me if you want details of how we proceeded. 

[Marco Doets]

Hi, thank you for your reaction. We just moved from our hardware loadbalancer to proxysql to get more control of the proxy/loadbalancer :-). Our hardware loadbalancer was hardly able to do a better/simple health check then a "check port" 

We did not found the real root-cause, but we know why it happens. The customer is using the odbc connector and in the pooling, when a connection is reused, a change_users() is used to reset the connection. For now we solved it (workaround) by disabling the pooling. For the feature we will look to use another connector.