Can not connect to ProxySQL: reasons and fixtures

This ProxySQL post is sourced from an error I faced recently

handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user

ProxySQL has become one of the popular choice as the “proxy” for MySQL databases. This post is tipping you off for debugging connectivity issue and fixtures around it for a specific use-case.

ProxySQL Connectivity

Ofcourse while to connect ProxySQL we have two choices for what we want to do: Admin Interface and MySQL.

• Admin interface allows us to configure and manage the ProxySQL.
• MySQL interface is basically the “proxy” connectivity to MySQL databases configured in ProxySQL.

Connecting ProxySQL Admin Interface

mysql -uADMIN_USER -pADMIN_PASSWORD -h127.0.0.1 -P6032

The ADMIN_USER and ADMIN_PASSWORD is taken from admin-admin_credentials configuration. Port 6032 is defined in mysql_ifaces configuration option and it is configurable with multiple ports as well.

Connecting backend MySQL Servers from ProxySQL

mysql -uUSER -pPASSWORD -h127.0.0.1 -P6033

Here, USER and PASSWORD are defined in mysql_users table (and ofcourse loaded to runtime). The Port 6033 is defined in interfaces configuration option and it is configurable.

Now that we have established very basic understanding of connectivity, let’s produce the connectivity issue that we’re going to discuss further along with the resolution.

Start with creating an “admin” user for our application to connect to the database. This user is ofcourse present on MySQL server. Note that even though the password here is kept as plaintext, we can also use the encrypted password from MySQL’s user table.

[root@kedar ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
…
mysql> insert into mysql_users (username, password,default_hostgroup) values ('admin','admin',0);
Query OK, 1 row affected (0.00 sec)

mysql> load mysql users to runtime; save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

Let’s try re-connecting to the admin interface again

[root@kedar ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)
[root@kedar ~]#
# note that you can still connect to MySQL port though (6033)

Looking at error log

[root@kedar ~]# tail -1 /var/lib/proxysql/proxysql.log
2022-10-08 10:41:16 MySQL_Session.cpp:5439:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)
[root@kedar ~]#

There is nothing we can see in the error log that hints us anything about the error. But we know that the access is a mess since we created that user! Let’s get rid of that, can we? Without actually connecting to ProxySQL?
Technically we can. ProxySQL uses SQLite as default datastore to save the data on disk and we can manipulate it with CAUTION.

Edit ProxySQL Database

# Stop ProxySQL
[root@kedar ~]# systemctl stop proxysql

# Backup the db file
[root@kedar ~]# cd /var/lib/proxysql/
[root@kedar ~]# cp proxysql.db proxysql.db.1

# Open db file with sqlite3
[root@kedar proxysql]# sqlite3 proxysql.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
global_variables
mysql_aws_aurora_hostgroups
mysql_collations
mysql_firewall_whitelist_rules
mysql_firewall_whitelist_sqli_fingerprints
mysql_firewall_whitelist_users
mysql_galera_hostgroups
mysql_group_replication_hostgroups
mysql_query_rules
mysql_query_rules_fast_routing
mysql_replication_hostgroups
mysql_servers
mysql_users
proxysql_servers
restapi_routes
scheduler

sqlite> .headers on
sqlite> .schema mysql_users
CREATE TABLE mysql_users (username VARCHAR NOT NULL , password VARCHAR , active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1 , use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0 , default_hostgroup INT NOT NULL DEFAULT 0 , default_schema VARCHAR , schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0 , transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1 , fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0 , backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1 , frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1 , max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000 , attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '' , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (username, backend) , UNIQUE (username, frontend));
sqlite> select * from mysql_users;
username|password|active|use_ssl|default_hostgroup|default_schema|schema_locked|transaction_persistent|fast_forward|backend|frontend|max_connections|attributes|comment
kedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|0|1|10000||
kedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|1|0|10000||
admin|*4ACFE3202A5FF5CF467898FC58AAB1D615029441|1|0|10|test|0|1|0|1|1|10000||

# Delete the user we created and verify it is gone
sqlite> delete from mysql_users where username='admin';
sqlite> select * from mysql_users;
username|password|active|use_ssl|default_hostgroup|default_schema|schema_locked|transaction_persistent|fast_forward|backend|frontend|max_connections|attributes|comment
kedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|0|1|10000||
kedar|*8D39C185A69E3453E27F96FBDBFFC701DE027750|1|0|10|test|0|1|0|1|0|10000||
sqlite> .quit

# Start ProxySQL
[root@kedar proxysql]# systemctl start proxysql

# Attempt connectivity (Voila!)
[root@kedar proxysql]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.36-39, for Linux (x86_64) using  6.2

Connection id:		1
Current database:	admin
Current user:		percona
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.30 (ProxySQL Admin Module)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		6032
Uptime:			2 sec

Threads: 0  Questions: 0  Slow queries: 0
--------------

Again, ensure that you have the backups in place before playing with the ProxySQL’s backend database.

But why were we blocked? We faced this issue as the documentation clearly mentions the limitations for usernames.

The users defined in admin-admin_credentials or admin-stats_credentials cannot be used also in mysql_users table.

Thus this is not only limited to “admin” user but also to any other usernames chosen for those two roles. And if you do so, ProxySQL will not warn you or let you know via error log but just block you from accessing it!

Reset ProxySQL

There’s one more thing we can do here but it is like a hard-reset. We can also reset the ProxySQL setup to initialize the backend database from the configuration.

[root@kedar ~]# proxysql --initial
… [INFO] Using config file /etc/proxysql.cnf
Renaming database file /var/lib/proxysql/proxysql.db
…

The initial flag here will reset the SQLite database file to its original state loading it from configuration and rename the existing SQLite database file in case a rollback is required.

That’s what you see in ProxySQL data dir:

[root@kedar ~]# ls proxysql.db*
proxysql.db proxysql.db.bak

Quick Tip: To prepare mysql_users insert statements you may use following insert query on the database. Here I’m assuming default hostgroup is 10.

select concat('INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (',user,',',authentication_string,',10);') from mysql.user group by user;

Hope this helps! Happy ProxySQLing.