ProxySQL works breifly, then must be restarted to work again

[Tyler]

Hello,

I have set up ProxySQL as a service in Azure AKS using the guide here and integrated it into an existing application that uses Azure managed MySQL version 8 on the backend. ProxySQL connects correctly for several minutes until all of a sudden the backend servers are shunned permanently and the log reports "[ERROR] Hostgroup # has no servers available! Checking servers shunned for more than 1 second" where # is either 1 or 2 depending the query. 1 has write queries and 2, reads as the example linked shows. I can make this the same server and the same behavior is observed.

I haven't not changed anything other than mysql_user credentials and server host names from the example linked. ProxySQL works for somewhere between 5-10 minutes and then the log is flooded with those messages and logging into the monitor and running `select * from stats.stats_mysql_connection_pool;` shows that the servers' statuses are "SHUNNED". As far as I have tried, restarting is the only thing that works to revive ProxySQL. Looking through the variables and configuration options set, I do not see a reason that ProxySQL work work for a short time, then all of a sudden not work until restarted.

Does anyone have a suggestion for further troubleshooting or know how to remediate my issue?

Thanks!

[René Cannaò]

Hi Tyler,

Error log is normally a good place to start troubleshooting.

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e643a85e-2fe5-4171-84ca-cca149fc4ec5n%40googlegroups.com.

[Tyler]

Thank you for your response Rene.

I included the most prevalent message in the log file that continues to repeat indefinitely until ProxySQL is restarted in the above description of the issue. Here is the start of that, before this, everything is just the beginning of the service and it works fine:

2021-02-11 19:32:41 MySQL_Monitor.cpp:2581:monitor_ping(): [ERROR] Server replica-hostname-for-hostgroup-2.:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
2021-02-11 19:33:23 MySQL_HostGroups_Manager.cpp:2877:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second
2021-02-11 19:33:25 MySQL_HostGroups_Manager.cpp:2877:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second
2021-02-11 19:33:27 MySQL_HostGroups_Manager.cpp:2877:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second
2021-02-11 19:33:29 MySQL_HostGroups_Manager.cpp:2877:get_random_MySrvC(): [ERROR] Hostgroup 2 has no servers available! Checking servers shunned for more than 1 second

The same can be seen elsewhere in the log for the the write hostgroup 1, but because there are less queries that are sent to that server, the messages are more spread out. Likewise, if I do not split the queries and send everything to hostgroup 1 that is the write MySQL server, I only see "Hostgroup 1 has no...". These are the only messages in the log aside from the successful startup of ProxySQL. This repeats until ProxySQL is restarted. I can still access the backend servers during this time so it is not a backend server issue. Please let me know if you need to see this.

Thanks!

[René Cannaò]

Hi Tyler,

This is the relevant information:

MySQL_Monitor.cpp:2581:monitor_ping(): [ERROR] Server replica-hostname-for-hostgroup-2.:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.

ProxySQL seems unable to reach or ping the backend.

It could be a network issue (firewall) or something else.

The fact that a ProxySQL restart fixes the issue is odd, but combining it with "ProxySQL connects correctly for several minutes until all of a sudden the backend servers are shunned permanently" suggests that several minutes is the time for the 3 missed heartbeats related to monitor ping. Sort of: traffic works, but ping doesn't.

Having a look at the complete error log can make debugging easier, especially all messages reported by Monitor.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e5365a24-409a-4c1e-b337-20500c074684n%40googlegroups.com.

[Tyler]

Hello Rene,

The only table in the monitor table is monitor.mysql_server_ping_log and it contains:

+----------------------------------------------------------------+------+------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostname                                                       | port | time_start_us    | ping_success_time_us | ping_error
                                                                                                                                            |
+----------------------------------------------------------------+------+------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| replica-hostname-2 | 3306 | 1613138361263454 | 0                    | The server's domain is not supported by this Gateway: replica-hostname-2. Please check the Username and retry connection. The Username should be in <username@hostname> format. |
| main-hostname-1         | 3306 | 1613138361250238 | 0                    | The server's domain is not supported by this Gateway: main-hostname-1. Please check the Username and retry connection.
The Username should be in <username@hostname> format.         |
| main-hostname-1         | 3306 | 1613138561250303 | 0                    | The server's domain is not supported by this Gateway: main-hostname-1. Please check the Username and retry connection.
The Username should be in <username@hostname> format.         |
| replica-hostname-2 | 3306 | 1613138561268860 | 0                    | The server's domain is not supported by this Gateway: replica-hostname-2. Please check the Username and retry connection. The Username should be in <username@hostname> format. |
| main-hostname-1         | 3306 | 1613138761250463 | 0                    | The server's domain is not supported by this Gateway: main-hostname-1. Please check the Username and retry connection.
The Username should be in <username@hostname> format.         |
| replica-hostname-2 | 3306 | 1613138761268193 | 0                    | The server's domain is not supported by this Gateway: replica-hostname-2. Please check the Username and retry connection. The Username should be in <username@hostname> format. |
| main-hostname-1         | 3306 | 1613138961250489 | 0                    | The server's domain is not supported by this Gateway: main-hostname-1. Please check the Username and retry connection.
The Username should be in <username@hostname> format.         |
| replica-hostname-2 | 3306 | 1613138961265550 | 0                    | The server's domain is not supported by this Gateway: replica-hostname-2. Please check the Username and retry connection. The Username should be in <username@hostname> format. |
+----------------------------------------------------------------+------+------------------+----------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Which does not make sense because the only username in mysql_users is `proxysql@mysql-server-name`. I can log into both the master and the replica using the username and password as it exists in mysql_users. Access to this MySQL server is done through a private endpoint so there should not be a firewall issue there. I have not had any issue accessing the MySQL server from where ProxySQL is located when I manually connect or when I take ProxySQL out, the application has no problems connecting to the MySQL server.

Is it possible to disable the health pings and always connect or is there a better way to address the issue?

Thanks!

[Tyler]

The only table in the monitor database that contains data* I should clarify.

[Prasann Jha]

Hi ﹰTaylor,

Recently i have similar issue and finally got that monitoring user name has additional @ at the end. Please verify.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/93ffce21-55ec-4769-bd7d-9acf1d747e58n%40googlegroups.com.

[Jaco Theunissen]

Hi, I have the exact same setup, the Azure DB for MySQL needs the username to be in the format user@db, so my monitoring user is timing out on all DBs except hostgroup one, with the error "Client from Interface Endpoint is not allowed to access the server. Please make sure your Virtual Network is correctly configured." the reason is just the username for hostgroup 2 should be user@db2 instead of user@db, I've disabled monitoring for now and everything is running smoothly, but how would you get around this while still having monitoring enabled?