search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.05k stars 983 forks source link

ProxySQL 2.6.1 - SSL connect error #4487

Closed JihadMotii-REISys closed 8 months ago

JihadMotii-REISys commented 8 months ago

Hello,

I'm running my ProxySQL as a sidecar within a micro-service POD in AWS EKS that connects to an AWS Aurora MySQL database. Here are the details of this stack:

ProxySQL version: 2.6.1 OS version: n/a, Docker image "proxysql/proxysql:2.6.1" Backend MySQL: AWS Aurora MySQL 3.x (MySQL version 8.x) with 1 instance that acts as Writer and Reader. Backend MySQL Database Parameters: require_secure_transport=ON tls_version=TLSv1.2 Micro-Service/ProxySQL connection through JDBC: jdbc:mysql://localhost:/tmp/proxysql.sock/mydatabase?useLegacyDatetimeCode=false&serverTimezone=America/Chicago ProxySQL configuration (proxysql.cnf):

datadir="/var/lib/proxysql"
errorlog="/dev/stderr"

admin_variables={
  admin_credentials="admin:admin"
  mysql_ifaces="0.0.0.0:6032"
  debug=true
}
mysql_variables={
  interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
  monitor_username="root"
  monitor_password="rootpassword"
  ssl_p2s_ca="/etc/aws-rds-certs-secret/rds-ca-2019-root.pem"
  have_ssl=true
  server_version="8.0.33"
  verbose_query_error=true
}
mysql_servers=(
  { hostname="my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=1, use_ssl=1 },
  { hostname="my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=1, use_ssl=1 },
  { hostname="my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=1, use_ssl=1 },
  { hostname="my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=2, use_ssl=1 },
  { hostname="my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=2, use_ssl=1 },
  { hostname="my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com", port=3306, hostgroup=2, use_ssl=1 }
)
mysql_aws_aurora_hostgroups=(
  {writer_hostgroup=1, reader_hostgroup=2, active=1, aurora_port=3306, domain_name=".xyz.us-east-1.rds.amazonaws.com", writer_is_also_reader=1}
)
mysql_users=(
  { username="root", password="rootpassword", default_hostgroup=1, active=1, use_ssl=1 }
)
mysql_replication_hostgroups=(
  { writer_hostgroup=1, reader_hostgroup=2, comment="MySQL Replication 8.0" }
)

ProxySQL logs:

2024-03-28 17:16:31 [INFO] Using config file /etc/proxysql.cnf
2024-03-28 17:16:31 [INFO] Current RLIMIT_NOFILE: 1048576
2024-03-28 17:16:31 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
2024-03-28 17:16:31 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
2024-03-28 17:16:31 [INFO] ProxySQL version 2.6.1-31-g9d788c0
2024-03-28 17:16:31 [INFO] Detected OS: Linux ppms-auctions-api-85c5f6449d-xspnr 5.10.209-198.858.amzn2.x86_64 #1 SMP Tue Feb 13 18:46:41 UTC 2024 x86_64
2024-03-28 17:16:31 [INFO] ProxySQL SHA1 checksum: edcef414bb0f4b1c84dc0b62ee8ad2179fd65d8e
2024-03-28 17:16:31 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2024-03-28 17:16:31 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Wed Mar 20 18:59:32 2024
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Mar 20 18:59:32 2024
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Mar 20 18:59:32 2024
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Mar 20 18:59:32 2024
2024-03-28 17:16:31 [INFO] Using UUID: 920ba8b9-b706-4248-8690-c63a2a2549b0 , randomly generated. Writing it to database
2024-03-28 17:16:31 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0x9903504DC63B315D', with epoch '1711646191'
2024-03-28 17:16:31 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x86BF6E38DC87D08F', with epoch '1711646191'
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Mar 20 18:59:32 2024
2024-03-28 17:16:31 [INFO] ProxySQL SHA1 checksum: edcef414bb0f4b1c84dc0b62ee8ad2179fd65d8e
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Mar 20 18:59:32 2024
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Mar 20 18:59:32 2024
2024-03-28 17:16:31 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0x8CEE4F54D02883BA', with epoch '1711646191'
2024-03-28 17:16:31 [INFO] Generating runtime mysql servers and mysql servers v2 records.
2024-03-28 17:16:31 [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 |
+--------------+-----------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com                     | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com                         | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com                         | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
+--------------+-----------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-03-28 17:16:31 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-03-28 17:16:31 [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 |
+--------------+-----------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com                         | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 1            | my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com                         | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
| 2            | my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com                      | 3306 | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         | 0           | 0         | 1      | 0      | 0           | 1000            | 0                   | 1       | 0              |         |
+--------------+-----------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2024-03-28 17:16:31 [INFO] Creating new server in HG 1 : my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] Creating new server in HG 1 : my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] Creating new server in HG 1 : my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] Creating new server in HG 2 : my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] Creating new server in HG 2 : my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] Creating new server in HG 2 : my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com:3306 , gtid_port=0, weight=1, status=0
2024-03-28 17:16:31 [INFO] New mysql_group_replication_hostgroups table
2024-03-28 17:16:31 [INFO] New mysql_galera_hostgroups table
2024-03-28 17:16:31 [INFO] New mysql_aws_aurora_hostgroups table
2024-03-28 17:16:31 [INFO] Loading AWS Aurora info for (1,2,on,3306,".xyz.us-east-1.rds.amazonaws.com",600000,30,30,1,1000,1000,"")
2024-03-28 17:16:31 [INFO] New mysql_hostgroup_attributes table
2024-03-28 17:16:31 [INFO] New mysql_servers_ssl_params table
2024-03-28 17:16:31 [INFO] Checksum for table mysql_servers_v2 is 0x5E5E2123311C2CD6
2024-03-28 17:16:31 [INFO] Checksum for table mysql_replication_hostgroups is 0xDB29BDDEC591543C
2024-03-28 17:16:31 [INFO] Checksum for table mysql_aws_aurora_hostgroups is 0x74FEEAF227EB1670
2024-03-28 17:16:31 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x77C893146F1E835A'
2024-03-28 17:16:31 [INFO] Checksum for table mysql_servers is 0x5E5E2123311C2CD6
2024-03-28 17:16:31 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0x311C2CD65E5E2123 }, mysql_replication_hostgroups { old:0x0, new:0xDB29BDDEC591543C }
2024-03-28 17:16:31 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-03-28 17:16:31 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1711646191'
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Wed Mar 20 18:59:32 2024
2024-03-28 17:16:31 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0x0000000000000000', with epoch '1711646191'
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Mar 20 18:59:32 2024
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Wed Mar 20 18:59:32 2024
2024-03-28 17:16:31 [INFO] For information about products and services visit: https://proxysql.com/
2024-03-28 17:16:31 [INFO] For online documentation visit: https://proxysql.com/documentation/
2024-03-28 17:16:31 [INFO] For support visit: https://proxysql.com/services/support/
2024-03-28 17:16:31 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
2024-03-28 17:16:32 [INFO] Detected new/changed definition for AWS Aurora monitoring
2024-03-28 17:16:32 [INFO] Activating Monitoring of 1 AWS Aurora clusters
2024-03-28 17:16:32 [INFO] Starting Monitor thread for AWS Aurora writer HG 1
2024-03-28 17:16:32 [INFO] Started Monitor thread for AWS Aurora writer HG 1
2024-03-28 17:16:32 [INFO] read_only_action_v2() detected RO=0 on server my-db-cluster-name-instance-3.xyz.us-east-1.rds.amazonaws.com:3306 for the first time after commit(), but no need to reconfigure
2024-03-28 17:16:32 [INFO] read_only_action_v2() detected RO=0 on server my-db-cluster-name.cluster-ro-xyz.us-east-1.rds.amazonaws.com:3306 for the first time after commit(), but no need to reconfigure
2024-03-28 17:16:32 main.cpp:146:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error
2024-03-28 17:16:32 [INFO] read_only_action_v2() detected RO=0 on server my-db-cluster-name.cluster-xyz.us-east-1.rds.amazonaws.com:3306 for the first time after commit(), but no need to reconfigure

As you can see in the logs, the proxysql is throwing the following error message and nothing else after that: 2024-03-28 17:16:32 main.cpp:146:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error

The ProxySQL container does have the rds-ca-2019-root.pem and rds-combined-ca-bundle.pem that are mounted through EKS Secret with the chmod 0666 and are available through in this location /etc/aws-rds-certs-secret/.

I have tried using both rds-ca-2019-root.pem and rds-combined-ca-bundle.pem at a time and I have also used different ciphers within the mysql_variables section in the proxysql.cnf as such: ssl_p2s_cipher="ECDHE-RSA-AES256-GCM-SHA384"

but no matter what I do, it doesn't help to fix the issue. I couldn't find any related issue to this one except for this ticket: https://github.com/sysown/proxysql/issues/4466

Looking forward for your help on this issue, much appreciated!

renecannao commented 8 months ago

Hi @JihadMotii-REISys . Can I ask to please clarify what is the issue? What is the problem you are experiencing?

JihadMotii-REISys commented 8 months ago

Hi @renecannao Thank you for your prompt response.

I was able to identify the root cause of this error message: 2024-03-28 17:16:32 main.cpp:146:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error

It seems like the ProxySQL is making a call to https://www.proxysql.com/latest to get the latest version as shown here https://github.com/sysown/proxysql/blob/v2.x/src/main.cpp#L133C46-L157

Since our outbound network is restricted, this call was failing and resulting in the error message above. This error confused me as I believed it was related to MySQL SSL connection but instead it was related to proxysql getting the latest version.

I have whitelisted the DNS www.proxysql.com and the error disappeared. For now, things are looking good and we can close this ticket.