search

sysown / proxysql

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

New 'mysql_servers_ssl_params' settings not working (TLS/SSL error: invalid directory) #4466

Closed brogon closed 8 months ago

brogon commented 8 months ago

ProxySQL version: 2.6.0 OS version: n/a, Docker image "proxysql/proxysql:2.6.0"|

When using "mysql_servers_ssl_params" instead of the global variables, ProxySQL can't use the given files.

If only given "ssl_ca", the "file open" error is just ignored, resulting in certificate validation issues if using a custom CA. If you use "ssl_cert" and "ssl_key" for cert-based authentication, it fails with "TLS/SSL error: invalid directory".

I've created a docker-compose based demonstration environment; you can find it attached to this report. It contains the needed configuration (proxysql.cnf and certificate/key-files) to reproduce the issue.

proxysql_ssl_params_test.zip proxysql_ssl_params_test.tar.bz2.zip (zipped .tar.bz2 to re-create the correct permissions on Linux)

The complete log of a failure from the aforementioned environment:

proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Using config file /etc/proxysql.cnf
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Current RLIMIT_NOFILE: 1048576
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
db-1                   | 2024-03-08 17:56:21+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.7+maria~ubu2204 started.
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Using config file /etc/proxysql.cnf
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Current RLIMIT_NOFILE: 1048576
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Using OpenSSL version: OpenSSL 3.2.1 30 Jan 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] ProxySQL version 2.6.0-590-g9878ed3
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Detected OS: Linux c8aeeb5bdb9d 5.15.0-94-generic #104~20.04.1-Ubuntu SMP Tue Jan 16 13:34:09 UTC 2024 x86_64
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] ProxySQL SHA1 checksum: e7e19b20285e13cfbceb88a6a2331afdfcbad8da
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Loaded built-in SQLite3
proxysql-global-1      | Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Using UUID: be05a49c-5751-4f0f-85f0-fc030b148ec4 , randomly generated. Writing it to database
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0xECFC7190D0FB69B9', with epoch '1709920581'
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x40AE3CA1873ABB26', with epoch '1709920581'
proxysql-global-1      | Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] ProxySQL SHA1 checksum: e7e19b20285e13cfbceb88a6a2331afdfcbad8da
proxysql-global-1      | Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0xD6F3FDE2B2DC1787', with epoch '1709920581'
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Generating runtime mysql servers and mysql servers v2 records.
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers_incoming
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | | 10           | db       | 3306 | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         |
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
proxysql-global-1      | +-------------+--------------+----------+------+
proxysql-global-1      | | mem_pointer | hostgroup_id | hostname | port |
proxysql-global-1      | +-------------+--------------+----------+------+
proxysql-global-1      | +-------------+--------------+----------+------+
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | | 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 |
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | | 10           | db       | 3306 | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         | 0           | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         |
proxysql-global-1      | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Creating new server in HG 10 : db:3306 , gtid_port=0, weight=10, status=0
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New mysql_group_replication_hostgroups table
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New mysql_galera_hostgroups table
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New mysql_aws_aurora_hostgroups table
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New mysql_hostgroup_attributes table
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New mysql_servers_ssl_params table
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Checksum for table mysql_servers_v2 is 0x8DA754A126B9F18B
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x528311152709B15E'
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Checksum for table mysql_servers is 0x8DA754A126B9F18B
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0x26B9F18B8DA754A1 }, mysql_replication_hostgroups { old:0x0, new:0x0 }
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1709920581'
proxysql-global-1      | Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0xABC239DAF6C84BC1', with epoch '1709920581'
proxysql-global-1      | In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Fri Mar  1 15:22:35 2024
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] For information about products and services visit: https://proxysql.com/
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] For online documentation visit: https://proxysql.com/documentation/
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] For support visit: https://proxysql.com/services/support/
proxysql-global-1      | 2024-03-08 17:56:21 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] ProxySQL version 2.6.0-590-g9878ed3
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Detected OS: Linux aa16b632fb71 5.15.0-94-generic #104~20.04.1-Ubuntu SMP Tue Jan 16 13:34:09 UTC 2024 x86_64
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] ProxySQL SHA1 checksum: e7e19b20285e13cfbceb88a6a2331afdfcbad8da
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Loaded built-in SQLite3
proxysql-ssl-params-1  | Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Using UUID: 006becf6-9254-4ee6-8885-1bd9813eec55 , randomly generated. Writing it to database
db-1                   | 2024-03-08 17:56:21+00:00 [Warn] [Entrypoint]: /sys/fs/cgroup/rdma:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 12:memory:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 11:perf_event:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 10:freezer:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 9:devices:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 8:net_cls,net_prio:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 7:misc:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 6:pids:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 5:cpu,cpuacct:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 4:cpuset:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 3:hugetlb:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 2:blkio:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 1:name=systemd:/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2
db-1                   | 0::/docker/5f667d9e254b66e4f5f1d7e822d67a845792dd67a9dd97e9d8d8a56cbd320ca2/memory.pressure not writable, functionality unavailable to MariaDB
db-1                   | 2024-03-08 17:56:21+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD ADMIN VARIABLES TO RUNTIME' was '0xECFC7190D0FB69B9', with epoch '1709920581'
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL VARIABLES TO RUNTIME' was '0x8B82E9183FA1E428', with epoch '1709920581'
proxysql-ssl-params-1  | Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] ProxySQL SHA1 checksum: e7e19b20285e13cfbceb88a6a2331afdfcbad8da
proxysql-ssl-params-1  | Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL USERS TO RUNTIME' was '0xD6F3FDE2B2DC1787', with epoch '1709920581'
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Generating runtime mysql servers and mysql servers v2 records.
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers_incoming
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | 10           | db       | 3306 | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | | mem_pointer | hostgroup_id | hostname | port |
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | 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 |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | 10           | db       | 3306 | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         | 0           | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Creating new server in HG 10 : db:3306 , gtid_port=0, weight=10, status=0
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New mysql_group_replication_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New mysql_galera_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New mysql_aws_aurora_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New mysql_hostgroup_attributes table
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New mysql_servers_ssl_params table
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Checksum for table mysql_servers_v2 is 0x8DA754A126B9F18B
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] New computed global checksum for 'mysql_servers_v2' is '0x528311152709B15E'
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Checksum for table mysql_servers is 0x8DA754A126B9F18B
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { old: 0x0, new: 0x26B9F18B8DA754A1 }, mysql_replication_hostgroups { old:0x0, new:0x0 }
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD PROXYSQL SERVERS TO RUNTIME' was '0x0000000000000000', with epoch '1709920581'
proxysql-ssl-params-1  | Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0xABC239DAF6C84BC1', with epoch '1709920581'
proxysql-ssl-params-1  | In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Fri Mar  1 15:22:35 2024
db-1                   | 2024-03-08 17:56:21+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.7+maria~ubu2204 started.
proxysql-ssl-params-1  | Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Fri Mar  1 15:22:35 2024
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] For information about products and services visit: https://proxysql.com/
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] For online documentation visit: https://proxysql.com/documentation/
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] For support visit: https://proxysql.com/services/support/
proxysql-ssl-params-1  | 2024-03-08 17:56:21 [INFO] For consultancy visit: https://proxysql.com/services/consulting/
db-1                   | 2024-03-08 17:56:22+00:00 [Note] [Entrypoint]: Initializing database files
proxysql-global-1      | 2024-03-08 17:56:22 main.cpp:146:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error
proxysql-ssl-params-1  | 2024-03-08 17:56:22 main.cpp:146:main_check_latest_version(): [ERROR] curl_easy_perform() failed: SSL connect error
db-1                   | 
db-1                   | 
db-1                   | PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
db-1                   | To do so, start the server, then issue the following command:
db-1                   | 
db-1                   | '/usr/bin/mariadb-secure-installation'
db-1                   | 
db-1                   | which will also give you the option of removing the test
db-1                   | databases and anonymous user created by default.  This is
db-1                   | strongly recommended for production servers.
db-1                   | 
db-1                   | See the MariaDB Knowledgebase at https://mariadb.com/kb
db-1                   | 
db-1                   | Please report any problems at https://mariadb.org/jira
db-1                   | 
db-1                   | The latest information about MariaDB is available at https://mariadb.org/.
db-1                   | 
db-1                   | Consider joining MariaDB's strong and vibrant community:
db-1                   | https://mariadb.org/get-involved/
db-1                   | 
db-1                   | 2024-03-08 17:56:22+00:00 [Note] [Entrypoint]: Database files initialized
db-1                   | 2024-03-08 17:56:22+00:00 [Note] [Entrypoint]: Starting temporary server
db-1                   | 2024-03-08 17:56:22+00:00 [Note] [Entrypoint]: Waiting for server startup
db-1                   | 2024-03-08 17:56:22 0 [Note] Starting MariaDB 10.11.7-MariaDB-1:10.11.7+maria~ubu2204 source revision 87e13722a95af5d9378d990caf48cb6874439347 as process 100
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Number of transaction pools: 1
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
db-1                   | 2024-03-08 17:56:22 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Completed initialization of buffer pool
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: End of log at LSN=45518
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: 128 rollback segments are active.
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
db-1                   | 2024-03-08 17:56:22 0 [Note] InnoDB: log sequence number 45518; transaction id 14
db-1                   | 2024-03-08 17:56:22 0 [Note] Plugin 'FEEDBACK' is disabled.
db-1                   | 2024-03-08 17:56:22 0 [Warning] 'user' entry 'root@5f667d9e254b' ignored in --skip-name-resolve mode.
db-1                   | 2024-03-08 17:56:22 0 [Warning] 'proxies_priv' entry '@% root@5f667d9e254b' ignored in --skip-name-resolve mode.
db-1                   | 2024-03-08 17:56:22 0 [Note] mariadbd: ready for connections.
db-1                   | Version: '10.11.7-MariaDB-1:10.11.7+maria~ubu2204'  socket: '/run/mysqld/mysqld.sock'  port: 0  mariadb.org binary distribution
db-1                   | 2024-03-08 17:56:23+00:00 [Note] [Entrypoint]: Temporary server started.
db-1                   | 2024-03-08 17:56:25+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
db-1                   | 
db-1                   | 2024-03-08 17:56:25+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/user_setup.sql
db-1                   | 
db-1                   | 
db-1                   | 2024-03-08 17:56:25+00:00 [Note] [Entrypoint]: Stopping temporary server
db-1                   | 2024-03-08 17:56:25 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: FTS optimize thread exiting.
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Starting shutdown...
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Buffer pool(s) dump completed at 240308 17:56:25
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Shutdown completed; log sequence number 47108; transaction id 15
db-1                   | 2024-03-08 17:56:25 0 [Note] mariadbd: Shutdown complete
db-1                   | 
db-1                   | 2024-03-08 17:56:25+00:00 [Note] [Entrypoint]: Temporary server stopped
db-1                   | 
db-1                   | 2024-03-08 17:56:25+00:00 [Note] [Entrypoint]: MariaDB init process done. Ready for start up.
db-1                   | 
db-1                   | 2024-03-08 17:56:25 0 [Note] Starting MariaDB 10.11.7-MariaDB-1:10.11.7+maria~ubu2204 source revision 87e13722a95af5d9378d990caf48cb6874439347 as process 1
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Number of transaction pools: 1
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
db-1                   | 2024-03-08 17:56:25 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Completed initialization of buffer pool
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: End of log at LSN=47108
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: 128 rollback segments are active.
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: log sequence number 47108; transaction id 16
db-1                   | 2024-03-08 17:56:25 0 [Note] Plugin 'FEEDBACK' is disabled.
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
db-1                   | 2024-03-08 17:56:25 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
db-1                   | 2024-03-08 17:56:25 0 [Note] InnoDB: Buffer pool(s) load completed at 240308 17:56:25
db-1                   | 2024-03-08 17:56:25 0 [Note] Server socket created on IP: '0.0.0.0'.
db-1                   | 2024-03-08 17:56:25 0 [Note] Server socket created on IP: '::'.
db-1                   | 2024-03-08 17:56:25 0 [Note] mariadbd: ready for connections.
db-1                   | Version: '10.11.7-MariaDB-1:10.11.7+maria~ubu2204'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Generating runtime mysql servers and mysql servers v2 records.
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
proxysql-ssl-params-1  | HID: 10 , address: db , port: 3306 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 2000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping mysql_servers: ALL
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | | 10  | db       | 3306 | 0    | 10     | 0      | 0   | 2000      | 0       | 1   | 0       |         | 140242012450368 |
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping mysql_servers_incoming
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | 10           | db       | 3306 | 0         | 10     | 0      | 0           | 2000            | 0                   | 1       | 0              |         |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | | mem_pointer | hostgroup_id | hostname | port |
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | +-------------+--------------+----------+------+
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | | 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 |
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | +--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_replication_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_group_replication_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_galera_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_aws_aurora_hostgroups table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_hostgroup_attributes table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New mysql_servers_ssl_params table
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Loading MySQL Server SSL Params for (db,3306,)
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Checksum for table mysql_servers_v2 is 0x8DA754A126B9F18B
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Checksum for table mysql_servers_ssl_params is 0x41039902D46F80AA
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] New computed global checksum for 'mysql_servers_v2' is '0xEB18B5B6E7B68F7D'
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
proxysql-ssl-params-1  | HID: 10 , address: db , port: 3306 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 2000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Dumping mysql_servers: ALL
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | | hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | | 10  | db       | 3306 | 0    | 10     | 0      | 0   | 2000      | 0       | 1   | 0       |         | 140242012450368 |
proxysql-ssl-params-1  | +-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] Checksum for table mysql_servers is 0x8DA754A126B9F18B
proxysql-ssl-params-1  | 2024-03-08 17:56:30 [INFO] MySQL_HostGroups_Manager::commit() locked for 4ms
proxysql-ssl-params-1  | 2024-03-08 17:56:46 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:46 3 [Warning] Aborted connection 3 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
db-1                   | 2024-03-08 17:56:46 4 [Warning] Aborted connection 4 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:46 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:46 5 [Warning] Aborted connection 5 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:46 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
proxysql-ssl-params-1  | 2024-03-08 17:56:46 MySQL_HostGroups_Manager.cpp:853:connect_error(): [ERROR] Shunning server db:3306 with 5 errors/sec. Shunning for 10 seconds
proxysql-ssl-params-1  | 2024-03-08 17:56:46 MySQL_HostGroups_Manager.cpp:3103:get_random_MySrvC(): [ERROR] Hostgroup 10 has no servers available! Checking servers shunned for more than 1 second
proxysql-ssl-params-1  | 2024-03-08 17:56:48 MySQL_HostGroups_Manager.cpp:3103:get_random_MySrvC(): [ERROR] Hostgroup 10 has no servers available! Checking servers shunned for more than 1 second
proxysql-ssl-params-1  | 2024-03-08 17:56:48 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:48 6 [Warning] Aborted connection 6 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
db-1                   | 2024-03-08 17:56:48 7 [Warning] Aborted connection 7 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:48 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:48 8 [Warning] Aborted connection 8 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:48 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
proxysql-ssl-params-1  | 2024-03-08 17:56:48 MySQL_HostGroups_Manager.cpp:853:connect_error(): [ERROR] Shunning server db:3306 with 5 errors/sec. Shunning for 10 seconds
proxysql-ssl-params-1  | 2024-03-08 17:56:50 MySQL_HostGroups_Manager.cpp:3103:get_random_MySrvC(): [ERROR] Hostgroup 10 has no servers available! Checking servers shunned for more than 1 second
proxysql-ssl-params-1  | 2024-03-08 17:56:50 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:50 9 [Warning] Aborted connection 9 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:50 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:50 10 [Warning] Aborted connection 10 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:50 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
proxysql-ssl-params-1  | 2024-03-08 17:56:50 MySQL_HostGroups_Manager.cpp:853:connect_error(): [ERROR] Shunning server db:3306 with 5 errors/sec. Shunning for 10 seconds
db-1                   | 2024-03-08 17:56:50 11 [Warning] Aborted connection 11 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:52 MySQL_HostGroups_Manager.cpp:3103:get_random_MySrvC(): [ERROR] Hostgroup 10 has no servers available! Checking servers shunned for more than 1 second
db-1                   | 2024-03-08 17:56:52 12 [Warning] Aborted connection 12 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
proxysql-ssl-params-1  | 2024-03-08 17:56:52 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
proxysql-ssl-params-1  | 2024-03-08 17:56:52 mysql_connection.cpp:1164:handler(): [ERROR] Failed to mysql_real_connect() on 10:db:3306 , FD (Conn:40 , MyDS:40) , 2026: TLS/SSL error: invalid directory. SSL Params: /ssl/ca.crt , /ssl/client.crt , /ssl/client.key ,  ,  ,  ,  , 
db-1                   | 2024-03-08 17:56:52 13 [Warning] Aborted connection 13 to db: 'unconnected' user: 'unauthenticated' host: '172.29.0.4' (This connection closed normally without authentication)
renecannao commented 8 months ago

Hi @brogon . Thank you for the report. I confirm I can reproduce it with the details provided. I am looking into it.

brogon commented 8 months ago

Thanks for looking into it!

In my search of the issue, I've found three other places besides the central "MySQLConnection" class, which seem to create backend connections - the main() function line 1802, lib/MySQL_Monitor.cpp MySQL_Monitor_State_Data::create_new_connection() line 1529 and lib/MySQL_Session.cpp kill_query_thread() line 230.

As I'm not sure in which context these are used, but you might want look into these, too, and decide if they also need the "ssl_params" logic. I'd think the "main" function is OK, as it only "bootstraps" a given database, but killing queries and monitoring might "benefit" from it...

renecannao commented 8 months ago

@brogon , PR #4467 adds:

Thank you for the report