Connecting to database through HAproxy

MySQL & MariaDB
,
1

Following a directive to configure the proxy/percona setup to correctly identify client operations by source address, I reconfigured percona backends with send-proxy. I now have:

source-host - proxy-node - percona node (8.4.7)

I installed mysql client (apt install mysql-client) on the source and this installs mysql-client-core-8.0 (8.0.46-ubuntu). If I connect source to percona I get successful results. If I connect proxy node to percona I get successful results. If I connect to source to proxy-node:3306 I get:-

ERROR 2026 (HY000): SSL connection error: error:0A00010B:SSL routines::wrong version number

I did try (from source) openssl s_client and gave path to the directory where our certificates are. It is the same path on all installed hosts companywide and contains the same base certificates, including our own self-signed one(s). I do see

verify error:num=19:self-signed certificate in certificate chain

from the output but I am reluctant to admit this is the cause. I did try removing (commenting out) the ssl bind options fro HAproxy config and restarting but the same error (ERROR 2026) appears. I can find no 8.4.7 client from percona.

HAPROXY for the front/back

defaults pxc_d2
log global
mode tcp
option tcplog
option dontlognull
retries 3
timeout connect 10s
timeout client 1h
timeout server 1h

frontend pxc-d2-frontend from pxc_d2
bind *:3306
use_backend pxc-d2-backend

backend pxc-d2-backend from pxc_d2
default-server inter 1001 rise 3 fall 3 on-marked-down shutdown-sessions
default-server port 9200 check send-proxy
balance roundrobin

server d2-db-node01 xx.xx.140.45:3306
server d2-db-node02 xx.xx.140.46:3306 backup
server d2-db-node03 xx.xx.140.47:3306 backup

Any suggestions?

2

We suggest you get the latest Percona client from our Software download page and give it a try.

Screenshot 2026-06-15 at 2.50.15 PM
Screenshot 2026-06-15 at 2.50.15 PM927×405 34.1 KB

Screenshot 2026-06-15 at 2.50.04 PM
Screenshot 2026-06-15 at 2.50.04 PM925×411 34.5 KB

3

Hi,

I downloaded and installed (for noble) 8.4.7 client and server-common. No change. Still same error. I understand that there is a more DB-centric proxy out there but HAP is what is in use here.

4

Hi @MarcH ,
Welcome to Percona community.

When you say “I reconfigured percona backends with send-proxy” => Does it mean the configuration worked OK without this earlier?

Do you have proxy_protocol_network configured: Support for PROXY protocol - Percona Server for MySQL

Thanks,

K

6

Here is a thing, we have 2 proxy nodes with a ‘floating’ ip address. So the primary node has two net devices until a switch then the other will have two. Thus two addresses. I get two different error messages depending on which proxy node I attempt to use.

(prod) marc.hoppins.ipa@d2-hmaster01:~ $ mysql -h d2-haproxy01 -P 3306 -u march -p -e “select customerNumber,customerName,city,country,creditLimit from classicmodels.customers limit 20;”
Enter password:
ERROR 2026 (HY000): SSL connection error: error:00000000:lib(0)::reason(0)

(prod) marc.hoppins.ipa@d2-hmaster01:~ $ mysql -h d2-haproxy02 -P 3306 -u march -p -e “select customerNumber,customerName,city,country,creditLimit from classicmodels.customers limit 20;”
Enter password:
ERROR 2026 (HY000): SSL connection error: error:0A00010B:SSL routines::wrong version number

A final point, we are using Haproxy 2.8.16

7

Hi,

“wrong version number” appears to be between mysql and haproxy. Do we have ssl enabled on MySQL?

mysql -h... -P 3306 -umarch -p -e "\s"

mysql -h... -P 3306 -umarch -p -e "SHOW STATUS LIKE 'Ssl_cipher'; SHOW STATUS LIKE 'Ssl_version';"

mysql -h... -P 3306 -umarch -p -e "SHOW VARIABLES LIKE '%ssl%'; SHOW VARIABLES LIKE 'tls_version';"

Does this work?

mysql -hproxynode -P 3306 -umarch -p --ssl-mode=DISABLED -e "SELECT 1;"

Thanks,
K

8

Going from client to server (not via proxy).

mysql  Ver 8.0.46-0ubuntu0.24.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:          22174
Current database:
Current user:           march@xx.xx.140.10
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.4.7-7.1 Percona XtraDB Cluster (GPL), Release rel7, Revision a1001c9, WSREP version 26.1.4.3
Protocol version:       10
Connection:             d2-db-node01 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 3 hours 31 min 4 sec

Threads: 14  Questions: 118107  Slow queries: 0  Opens: 596  Flush tables: 4  Open tables: 487  Queries per second avg: 9.326



+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+


+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| admin_ssl_ca                        |                 |
| admin_ssl_capath                    |                 |
| admin_ssl_cert                      |                 |
| admin_ssl_cipher                    |                 |
| admin_ssl_crl                       |                 |
| admin_ssl_crlpath                   |                 |
| admin_ssl_key                       |                 |
| mysqlx_ssl_ca                       |                 |
| mysqlx_ssl_capath                   |                 |
| mysqlx_ssl_cert                     |                 |
| mysqlx_ssl_cipher                   |                 |
| mysqlx_ssl_crl                      |                 |
| mysqlx_ssl_crlpath                  |                 |
| mysqlx_ssl_key                      |                 |
| performance_schema_show_processlist | OFF             |
| ssl_ca                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
| ssl_session_cache_mode              | ON              |
| ssl_session_cache_timeout           | 300             |
+-------------------------------------+-----------------+
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tls_version   | TLSv1.2,TLSv1.3 |
+---------------+-----------------+
9

One thing was missed

Does this work?

mysql -hproxynode -P 3306 -umarch -p --ssl-mode=DISABLED -e "SELECT 1;"

If this succeeds, it confirms that the failure is in the TLS PROXY-protocol layer.
Also instead of hinting “send-proxy” usage at “default-server” level, would you try explicitly mentioning them on per server basis

From

backend pxc-d2-backend from pxc_d2
default-server inter 1001 rise 3 fall 3 on-marked-down shutdown-sessions
default-server port 9200 check send-proxy
balance roundrobin

server d2-db-node01 xx.xx.140.45:3306
server d2-db-node02 xx.xx.140.46:3306 backup
server d2-db-node03 xx.xx.140.47:3306 backup

To:

backend pxc-d2-backend from pxc_d2
default-server inter 1001 rise 3 fall 3 on-marked-down shutdown-sessions
balance roundrobin
server d2-db-node01 xx.xx.140.45:3306 check port 9200 send-proxy
server d2-db-node02 xx.xx.140.46:3306 check port 9200 send-proxy backup
server d2-db-node03 xx.xx.140.47:3306 check port 9200 send-proxy backup

Thanks,
K

10

Before and after setting send-proxy per server.

d2-hmaster01:~ $ mysql -h d2-haproxy01 -P 3306 -umarch  -p --ssl-mode=DISABLED -e "SELECT 1;"
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server during query
d2-hmaster01:~ $ mysql -h d2-haproxy02 -P 3306 -umarch  -p --ssl-mode=DISABLED -e "SELECT 1;"
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server during query
11

Could it be that we should be using send-proxy-v2? If that would make a difference.

12

I though of send-proxy-v2-ssl but I couldn’t be sure of that before ensuring if dedicated send-proxy configuration works for us.

13

Does it only need to be configured on the proxy?

14

Also, why am I seeing nothing in logs, either proxy log or on the target server? Wouldn’t there at least be some failure messages somewhere?

15

OK. I tried also using the other two master (client) nodes and both proxies. The second proxy had the previous default server options. The errors from proxy01 and proxy 02:

mysql -h d2-haproxy02 -P 3306 -u march -p -e “select customerNumber,customerName,city,country,creditLimit from classicmodels.stomers limit 20;”

proxy01

ERROR 2026 (HY000): SSL connection error: error:0A00010B:SSL routines::wrong version number

proxy02

ERROR 2026 (HY000): SSL connection error: error:00000000:lib(0)::reason(0)

from all 3 client nodes.

When I changed proxy2 config to match proxy01 both error messages are now the same (0A00010B). If I run:

mysql -h d2-haproxy01 -P 3306 -u march -p --ssl-mode=DISABLED -e “SELECT 1;”

The error is the same from all 3 clients:

ERROR 2013 (HY000): Lost connection to MySQL server during query

So I now post the entire HAP config as that might help.

userlist stats-auth
  group admin users xxxx (username redacted due to pansy insecurity)
  user xxxx (username redacted due to pansy insecurity) insecure-password xxxxxx
  group readonly users haproxy
  user haproxy insecure-password xxxxxx

global
  log /dev/log    local0
#  log /dev/log    local1 notice
  chroot /var/lib/haproxy
  stats socket /var/lib/haproxy/stats
  stats timeout 30s
  user haproxy
  group haproxy
  daemon
  stats socket ipv4@127.0.0.1:9999 level admin
  stats socket /var/run/haproxy.sock mode 600 level admin

# Default SSL material locations
  ca-base /etc/ssl/certs
  crt-base /etc/ssl/private

# Default ciphers to use on SSL-enabled listening sockets.
# See: https://ssl-config.mozilla.org/#server=haproxy&server-version=2.0.3&config=intermediate
  ssl-default-bind-ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:D-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384
  ssl-default-bind-ciphersuites TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256
  ssl-default-bind-options ssl-min-ver TLSv1.2 no-tls-tickets

defaults base_http
  log     global
  mode    http
  option httpchk
  retries 3
  option  dontlognull
  timeout http-request 10s
  timeout connect 15s
  timeout check 5s
  timeout client 50s
  timeout server 50s
  errorfile 400 /etc/haproxy/errors/400.http
  errorfile 403 /etc/haproxy/errors/403.http
  errorfile 408 /etc/haproxy/errors/408.http
  errorfile 500 /etc/haproxy/errors/500.http
  errorfile 502 /etc/haproxy/errors/502.http
  errorfile 503 /etc/haproxy/errors/503.http
  errorfile 504 /etc/haproxy/errors/504.http

defaults pxc_d2 from base_http
# defaults pxc_d2
  log global
  mode tcp
  option tcplog
  option dontlognull
  retries 3
  timeout connect 10s
  timeout client 1h
  timeout server 1h

listen stats from base_http
  option redispatch
  bind    xx.xx.140.28:62626
  stats   uri /
  stats   refresh 60s
  stats   auth xxxx (username redacted due to pansy insecurity):xxxxxx
  stats   show-legends
  stats   admin if TRUE

frontend pxc-d2-frontend from pxc_d2
  bind *:3306
  use_backend pxc-d2-backend

backend pxc-d2-backend from pxc_d2
  default-server inter 1001 rise 3 fall 3 on-marked-down shutdown-sessions
  default-server port 9200 check
  balance roundrobin
#  option mysql-check user haproxy post-41
  server d2-db-node01 xx.xx.140.45:3306 check port 9200 send-proxy
  server d2-db-node02 xx.xx.140.46:3306 check port 9200 send-proxy backup
  server d2-db-node03 xx.xx.140.47:3306 check port 9200 send-proxy backup

and

(prod) marc.hoppins.ipa@d2-hmaster01:~ $ mysql -h d2-haproxy01  -P 3306 -u march  -p --ssl-mode=PREFERRED -e "SELECT 1;"
Enter password:
ERROR 2026 (HY000): SSL connection error: error:0A00010B:SSL routines::wrong version number

I am getting the impression that the problem of querying via a proxy IS at the database end.

16

Also, interestingly

(prod) marc.hoppins.ipa@d2-haproxy01:~ $ mysql -h d2-db-node01  -P 3306 -u march  -p --ssl-mode=PREFERRED -e "SELECT 1;"
Enter password:
+---+
| 1 |
+---+
| 1 |
+---+

but from a proxy to itself

(prod) marc.hoppins.ipa@d2-haproxy01:~ $ mysql -h d2-haproxy01  -P 3306 -u march  -p --ssl-mode=PREFERRED -e "SELECT 1;"
Enter password:
ERROR 2026 (HY000): SSL connection error: error:0A00010B:SSL routines::wrong version number

proxies have percona-server-client and percona-server-common installed.

17

…and I just tried send-proxy-v2 with the same errors returned.

18

These are proxy settings in mysql

+-----------------------------------+----------------+
| Variable_name                     | Value          |
+-----------------------------------+----------------+
| check_proxy_users                 | OFF            |
| mysql_native_password_proxy_users | OFF            |
| proxy_protocol_networks           | xx.xx.140.0/22 |
| proxy_user                        |                |
| sha256_password_proxy_users       | OFF            |
+-----------------------------------+----------------+


| %           | march                      |
| xx.xx.140.% | haproxy                    |


+-----------------------------------------------+
| Grants for haproxy@xx.xx.140.%                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `haproxy`@`xx.xx.140.%` |
+-----------------------------------------------+
19

The Percona server(s) are dropping the session complaining about SSL/TLS but I do not see anything being logged anywhere.

This is all I have from the proxy server

pxc-d2-frontend pxc-d2-backend/d2-db-node01 1/0/0 118 SD 3/1/0/0/0 0/0

The SD indicates that the percona server refused the connection.

Percona general log contained NO information for any failed attempt and only registered a successful query when I tried CLIENT → PERCONA and avoided hte proxy.

20

Finally, but I cannot tell from this if the server didn’t accept or the proxy didn’t send. If I go by the regular error messages the server refuses to accept the connecting session.

15:21:20.281473 IP CLIENT.55386 > PROXY.mysql: Flags [S], seq 961108031, win 64240, options [mss 1460,sackOK,TS val 1993410093 ecr 0,nop,wscale 7], length 0
15:21:20.281506 IP PROXY.mysql > CLIENT.55386: Flags [S.], seq 3122485353, ack 961108032, win 65160, options [mss 1460,sackOK,TS val 1385581826 ecr 1993410093,nop,wscale 7], length 0
15:21:20.281575 IP CLIENT.55386 > PROXY.mysql: Flags [.], ack 1, win 502, options [nop,nop,TS val 1993410093 ecr 1385581826], length 0
15:21:20.281695 IP PROXY.32826 > PERCONA.mysql: Flags [S], seq 1728864001, win 64240, options [mss 1460,sackOK,TS val 970964879 ecr 0,nop,wscale 7], length 0
15:21:20.281853 IP PERCONA.mysql > PROXY.32826: Flags [S.], seq 2520953573, ack 1728864002, win 65160, options [mss 1460,sackOK,TS val 1621205428 ecr 970964879,nop,wscale 7], length 0
15:21:20.281865 IP PROXY.32826 > PERCONA.mysql: Flags [.], ack 1, win 502, options [nop,nop,TS val 970964879 ecr 1621205428], length 0
15:21:20.281884 IP PROXY.32826 > PERCONA.mysql: Flags [P.], seq 1:50, ack 1, win 502, options [nop,nop,TS val 970964879 ecr 1621205428], length 49
15:21:20.281968 IP PERCONA.mysql > PROXY.32826: Flags [.], ack 50, win 509, options [nop,nop,TS val 1621205429 ecr 970964879], length 0
15:21:20.282196 IP PERCONA.mysql > PROXY.32826: Flags [P.], seq 1:82, ack 50, win 509, options [nop,nop,TS val 1621205429 ecr 970964879], length 81
15:21:20.282200 IP PROXY.32826 > PERCONA.mysql: Flags [.], ack 82, win 502, options [nop,nop,TS val 970964879 ecr 1621205429], length 0
15:21:20.282214 IP PROXY.mysql > CLIENT.55386: Flags [P.], seq 1:82, ack 1, win 510, options [nop,nop,TS val 1385581826 ecr 1993410093], length 81
15:21:20.282222 IP PERCONA.mysql > PROXY.32826: Flags [P.], seq 82:119, ack 50, win 509, options [nop,nop,TS val 1621205429 ecr 970964879], length 37
15:21:20.282225 IP PROXY.32826 > PERCONA.mysql: Flags [.], ack 119, win 502, options [nop,nop,TS val 970964879 ecr 1621205429], length 0
15:21:20.282230 IP PERCONA.mysql > PROXY.32826: Flags [F.], seq 119, ack 50, win 509, options [nop,nop,TS val 1621205429 ecr 970964879], length 0
15:21:20.282232 IP PERCONA.mysql > PROXY.32826: Flags [R.], seq 120, ack 50, win 509, options [nop,nop,TS val 1621205429 ecr 970964879], length 0
15:21:20.282246 IP PROXY.mysql > CLIENT.55386: Flags [F.], seq 82:119, ack 1, win 510, options [nop,nop,TS val 1385581826 ecr 1993410093], length 37
15:21:20.282254 IP CLIENT.55386 > PROXY.mysql: Flags [.], ack 82, win 502, options [nop,nop,TS val 1993410094 ecr 1385581826], length 0
15:21:20.282261 IP PERCONA.mysql > PROXY.32826: Flags [R], seq 2520953655, win 0, length 0
15:21:20.282273 IP PERCONA.mysql > PROXY.32826: Flags [R], seq 2520953692, win 0, length 0
15:21:20.282277 IP CLIENT.55386 > PROXY.mysql: Flags [P.], seq 1:37, ack 82, win 502, options [nop,nop,TS val 1993410094 ecr 1385581826], length 36
15:21:20.282285 IP PROXY.mysql > CLIENT.55386: Flags [.], ack 37, win 510, options [nop,nop,TS val 1385581826 ecr 1993410094], length 0
15:21:20.301595 IP CLIENT.55386 > PROXY.mysql: Flags [P.], seq 37:359, ack 120, win 502, options [nop,nop,TS val 1993410113 ecr 1385581826], length 322
15:21:20.301605 IP PROXY.mysql > CLIENT.55386: Flags [R], seq 3122485473, win 0, length 0
15:21:20.301614 IP CLIENT.55386 > PROXY.mysql: Flags [F.], seq 359, ack 120, win 502, options [nop,nop,TS val 1993410113 ecr 1385581826], length 0
15:21:20.301619 IP PROXY.mysql > CLIENT.55386: Flags [R], seq 3122485473, win 0, length 0
15:21:20.301623 IP CLIENT.55386 > PROXY.mysql: Flags [R.], seq 360, ack 120, win 502, options [nop,nop,TS val 1993410113 ecr 1385581826], length 0
21

For information, we are using HAPROXY 2.8 and Percona 8.4.7 and the people here REFUSE to use a newer version of proxy or even a proxy that is designed more for databases.

They are also snooping and spying like some insidious 3-letter US spy agency, so I had to obfuscate the host names.