Backend MySQL Server Connect Error ([ERROR] Failed to mysql_real_connect())

[JabirBaig9395]

Hi Team ,

I am testing proxysql on two test environments

1. Hosted on Centos7 and MySQL 5.7 (Host1)
2. Hosted on Oracle Linux 8 and MySQL 8.0.33 (Host2)

I am using sysbench to test the load and connections on both my test servers, when i tested on Host1 sysbench ran without errors but when i am testing on Host2 I am getting the following errors
`2024-06-18 03:36:33 mysql_connection.cpp:1177:handler(): [ERROR] Failed to mysql_real_connect() on 2:10.5.130.203:3306 , FD (Conn:0 , MyDS:2879) , 2002: Can't connect to server on '10.5.130.203' (111).

2024-06-18 03:36:33 MySQL_Session.cpp:4881:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,10.5.130.203,3306,169237): 1295, This command is not supported in the prepared statement protocol yet

2024-06-18 03:36:35 MySQL_HostGroups_Manager.cpp:3309:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second

2024-06-18 03:36:39 MySQL_Session.cpp:3309:handler_again___status_CONNECTING_SERVER(): [ERROR] Max connect timeout reached while reaching hostgroup 2 after 10000ms . HG status: [{"Bytes_recv":"896439","Bytes_sent":"960913406","ConnERR":"6787","ConnFree":"0","ConnOK":"3274","ConnUsed":"0","Latency_us":"224","MaxConnUsed":"843","Queries":"4575649","Queries_GTID_sync":"0","hostgroup":"2","srv_host":"10.5.130.203","srv_port":"3306","status":"SHUNNED"}]`

can you look into this

my test hosts are not loaded with any connections and I am testing with sysbench and MySQLslap both the test are giving the above errors

I checked removing the host from mysql_server and re-inserting and refreshing but nothing worked.

[JavierJF]

Hi @JabirBaig9395,

this is an issue of the same kind as the closed yesterday. It's not a real issue, the only issue I see in those log is that you might be trying to use non-supported commands on prepared statements (not by ProxySQL) but by the protocol itself, for example USE foodb.

ProxySQL error log is being clear here:

2024-06-18 03:36:35 MySQL_HostGroups_Manager.cpp:3309:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second

There are no servers available in that hostgroup. Hence the failure when trying to get connections from that hostgroup.

If you want assistance with setting up ProxySQL, please check the documentation and later resort to the channels specified in the issue template. This is no the place for setup questions if they don't relate to potential real issues.

Thanks, Javier.