ProxySQL 2.0.3 - MySQL 8 node is SHUNNED

[BhuviTheDataGuy]

Im running the ProxySQL v2.0.3 on Ubuntu 18.04. Its a fresh setup. MySQL version 8.0

It was able to access MySQL server for an hour. But later I got it was showing the MySQL server is SHUNNED.

I did a restart of ProxySQL, for next 3mins it was fine, then again its showing SHUNNED.

Error log:

2019-03-29 12:53:39 ProxySQL_GloVars.cpp:9:term_handler(): [WARNING] Received TERM signal: shutdown in progress...
2019-03-29 12:53:39 [INFO] Starting shutdown...
2019-03-29 12:53:39 [INFO] Shutdown completed!
2019-03-29 12:53:39 [INFO] Exiting...
2019-03-29 12:53:39 [INFO] Shutdown angel process
2019-03-29 12:53:42 [INFO] ProxySQL version 2.0.3-28-g39a0c9c5
2019-03-29 12:53:42 [INFO] Detected OS: Linux proxysql-server-01 4.18.0-1007-gcp #8-Ubuntu SMP Thu Feb 14 08:55:26 UTC 2019 x86_64
2019-03-29 12:53:42 [INFO] ProxySQL SHA1 checksum: 6caf4f5b903be8b42d87408d3db406e8919be27c
2019-03-29 12:53:42 [INFO] Starting ProxySQL
2019-03-29 12:53:42 [INFO] Sucessfully started
2019-03-29 12:53:42 [INFO] Angel process started ProxySQL process 3165
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Mar 20 14:08:00 2019
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Mar 20 14:08:00 2019
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Mar 20 14:08:00 2019
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Wed Mar 20 14:08:00 2019
2019-03-29 12:53:42 [INFO] ProxySQL SHA1 checksum: 6caf4f5b903be8b42d87408d3db406e8919be27c
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Mar 20 14:08:00 2019
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Mar 20 14:08:00 2019
2019-03-29 12:53:42 [INFO] Dumping mysql_servers_incoming
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
| hostgroup_id | hostname   | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 10.10.4.40 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2019-03-29 12:53:42 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2019-03-29 12:53:42 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 10.40.4.50 | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2019-03-29 12:53:42 [INFO] Creating new server in HG 10 : 10.40.4.50:3306 , gtid_port=0, weight=1, status=0
2019-03-29 12:53:42 [INFO] New mysql_group_replication_hostgroups table
2019-03-29 12:53:42 [INFO] New mysql_galera_hostgroups table
Standard Query Processor rev. 0.4.1031 -- Query_Processor.cpp -- Wed Mar 20 14:08:00 2019
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Mar 20 14:08:00 2019
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Mar 20 14:08:00 2019
2019-03-29 12:54:03 MySQL_Monitor.cpp:1824:monitor_ping(): [ERROR] Server 10.40.4.50:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.

2019-03-29 12:55:11 MySQL_Session.cpp:3967:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'' (using password: YES)
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM stats_mysql_global
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- INSERT INTO mysql_query_cache_hour SELECT timestamp/3600*3600 , MAX(count_GET), MAX(count_GET_OK), MAX(count_SET), MAX(bytes_IN), MAX(bytes_OUT), MAX(Entries_Purged), AVG(Entries_In_Cache), AVG(Memory_bytes) FROM mysql_query_cache WHERE timestamp >= 1553842800 AND timestamp < 1553842800 GROUP BY timestamp/3600
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache WHERE timestamp < 1553239543
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM mysql_query_cache_hour WHERE timestamp < 1522308343
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Active_Transactions","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Client_Connections_aborted","1")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Client_Connections_connected","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Client_Connections_created","36")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Server_Connections_aborted","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Server_Connections_connected","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Server_Connections_created","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Server_Connections_delayed","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Client_Connections_non_idle","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Queries_backends_bytes_recv","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Queries_backends_bytes_sent","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Queries_frontends_bytes_recv","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Queries_frontends_bytes_sent","1872")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Query_Processor_time_nsec","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("Backend_query_time_nsec","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("mysql_backend_buffers_bytes","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("mysql_frontend_buffers_bytes","0")
2019-03-29 12:55:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: UNIQUE constraint failed: stats_mysql_global.Variable_Name --- INSERT INTO stats_mysql_global VALUES ("mysql_session_internal_bytes","11296")
*3600 , MAX(MyHGM_myconnpoll_destroy), MAX(MyHGM_myconnpoll_get), MAX(MyHGM_myconnpoll_get_ok), MAX(MyHGM_myconnpoll_push), MAX(MyHGM_myconnpoll_reset) FROM myhgm_connections WHERE timestamp >= 1553842800 AND timestamp < 1553842800 GROUP BY timestamp/3600
2019-03-29 12:56:43 sqlite3db.cpp:61:execute(): [ERROR] SQLITE error: database is locked --- DELETE FROM myhgm_connections WHERE timestamp < 15532396039

I don't know all of my recent ProxySQL deployments are having some issues :)

[BhuviTheDataGuy]

Reproduced the Issue with ProxySQL 2.0.2 with Ubuntu 18.10 and MySQL 8.0.15. Im getting the same error.

Error log:

2019-03-29 13:14:24 [INFO] Received SAVE MYSQL SERVERS TO DISK command
2019-03-29 13:14:24 [INFO] Received LOAD MYSQL USERS TO RUNTIME command
2019-03-29 13:14:24 [INFO] Received SAVE MYSQL USERS TO DISK command
2019-03-29 13:14:53 MySQL_Monitor.cpp:1824:monitor_ping(): [ERROR] Server 10.10.4.40:3306 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.

No data from monitor.mysql_server_connect_log table and stats_mysql_errors table

[BhuviTheDataGuy]

One more Update: Reproduced the same on Proxysql 2.0.3 which is installed on Cent Os 7

[BhuviTheDataGuy]

Finally I have fixed this.

Its because of caching_sha2_password Plugin.

From Ping error:

Plugin caching_sha2_password could not be loaded: lib/mariadb/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

FIx:

add this under [mysqld]

collation-server               = utf8mb4_general_ci
default-authentication-plugin  = mysql_native_password

[renecannao]

Reopening. Your fix in reality is a workaround. It must work no matter what default authentication plugin is used. If this is not the case, it must be considered a bug (even if a workaround exists)

[BhuviTheDataGuy]

Thanks, @renecannao Wait for the fix :)

[breisig]

caching_sha2_password should be a priority to get implemented.

[fuyar]

Any news on caching_sha2_password support @renecannao ?

[debashismallick]

I need one help .. Here is my issue for mysql 8 DB user through proxy sql.

Frequent Access denied coming for database user connecting through proxysql .

Here is my environment : Azure ubunti 18.4 instancce with three mysql 8.018 version of mtaster slave xxx.xxx.xxx.73 (mysql slave) xxx.xxx.xxx.75 (mysql master ) xxx.xxx.xxx.79 (mysql slave) Proxy sql version is proxysql --version ProxySQL version 2.0.6-73-gc746bf7f, codename Truls mysql client is mysql Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL).

I also change password authintication value in all mysql node config parameter to collation-server = utf8mb4_general_ci default-authentication-plugin = mysql_native_password.

Still I get access issue from proxy sql connecting to DB server node for same user id and password sometime it get access to mysql some time it giving access denined . mysql -h 10.5.0.80 -u -p -P 6033 -e " SELECT @@hostname ; ". this script some time gets connceted and sometime it won't access and getting message acess denied " ERROR 1045 (28000): ProxySQL Error: Access denied for user 'app_user'@'10.5.0.80' (using password: YES) " I am breaking my head for last three days .. need help urgently I print the beckend hostname , by using this

My proxy connection some time get connected and some time getting access denied

[barlowzhou]

我需要一个帮助..这是通过代理sql对于mysql 8 DB用户的问题。

对于通过proxysql连接的数据库用户，拒绝频繁访问。

这是我的环境： 具有三个mysql 8.018版本的mtaster从站 xxx.xxx.xxx.73（mysql从站） xxx.xxx.xxx.75（mysql主站） xxx.xxx.xxx.79（mysql从站）的Azure ubunti 18.4实例 代理sql版本是 proxysql --version ProxySQL版本2.0.6-73-gc746bf7f，代号Truls mysql客户端是mysql Ver 8.0.19（适用于x86_64上的Linux）（MySQL Community Server-GPL）。

我还将所有mysql节点配置参数中的密码身份验证值更改 为 collat​​ion-server = utf8mb4_general_ci default-authentication-plugin = mysql_native_password。

我仍然从代理sql连接到数据库服务器节点以相同的用户ID和 密码获得访问问题，有时它会访问mysql，从而导致访问被拒绝。 mysql -h 10.5.0.80 -u -p -P 6033 -e“ SELECT @ @hostname ;”。 此脚本有时被压缩，有时将无法访问，并且消息访问被拒绝“错误1045（28000）：ProxySQL错误：用户'app_user'@'10.5.0.80'（使用密码：是）的访问被拒绝' 我最近三天 我都 伤了脑筋..我迫切需要帮助，打印出beckend主机名

我的代理服务器连接有时会连接，有些时候会拒绝访问

有解决这个问题吗？我也遇到同样情况

[oceanapplications]

I'm having this same issue. I restart ProxySQL and everything is fine for a few minutes but then the MySQL server gets shunned and does not reconnect.

I'm able to connect directly to the MySQL server without using ProxySQL.

Is there a setting that'll get ProxySQL to reconnect?

[GabrieleCalarota]

Same issue ... any news?

[renecannao]

Please make sure to have read https://proxysql.com/documentation/mysql-8-0/

[GabrieleCalarota]

@renecannao I'm sorry man, put I've read it and this is the line from the documentation

ProxySQL doesn’t yet support caching_sha2_password.

[breisig]

That's a feature it will need to support.

[renecannao]

Are you not able to connect to proxysql?