Systemd fails to run service but running service in foreground will work #4893
Description
I cannot start the proxysql service using the default systemd configuration file systemctl start proxysql. The service appears to fully terminate after a minute or so of trying to start. During that time the service is not operational (as in I cannot connect to a database through the proxy).
However:
If I run the service in the foreground: proxysql -f then the service appears to run correctly, and I can connect to a database through the proxy (using another terminal window).
For now I have altered the /etc/systemd/system/proxysql.servce file, it now reads as follows:
[Unit]
Description=High Performance Advanced Proxy for MySQL
After=network.target
[Service]
ExecStart=/usr/bin/proxysql -f
[Install]
WantedBy=multi-user.target
which means the following options have been commented out or changed:
[Service]
Type=forking
RuntimeDirectory=proxysql
ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS
PIDFile=/var/lib/proxysql/proxysql.pid
SyslogIdentifier=proxysql
Restart=no
User=proxysql
Group=proxysql
PermissionsStartOnly=true
UMask=0007
LimitNOFILE=102400
LimitCORE=1073741824
ProtectHome=yes
NoNewPrivileges=true
CapabilityBoundingSet=CAP_SETGID CAP_SETUID CAP_SYS_RESOURCE
RestrictAddressFamilies=AF_INET AF_INET6 AF_UNIX AF_ALG
ProtectSystem=full
PrivateDevices=yes
Are there serious implications for these changes? My use case is that I want my AWS lambda functions to be able to connect to the proxysql service so that I avoid reaching the database's maximum number of connections (which can happen if i connect to the database directly from the lambda functions)
Below i include the log outputs:
output from:
systemctl status proxysql
× proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; preset: disabled)
Active: failed (Result: exit-code) since Sun 2025-03-30 00:38:58 UTC; 2min 57s ago
Duration: 1min 18.108s
Process: 2356 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 2358 (code=exited, status=1/FAILURE)
CPU: 595ms
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal systemd[1]: Starting proxysql.service - High Performance Advanced Proxy for MySQL...
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal proxysql[2356]: 2025-03-30 00:37:39 [INFO] Using config file /etc/proxysql.cnf
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal proxysql[2356]: 2025-03-30 00:37:39 [INFO] Using jemalloc with MALLOC_CONF: config.xmalloc:1, lg_tcache_max:16, opt.prof_accum:1, opt.prof_leak:1, opt.lg_prof_sample:20, opt.lg_prof_interval:30, rc:0
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal proxysql[2356]: 2025-03-30 00:37:39 [INFO] Current RLIMIT_NOFILE: 102400
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal proxysql[2356]: 2025-03-30 00:37:39 [INFO] Using OpenSSL version: OpenSSL 3.3.1 4 Jun 2024
Mar 30 00:37:39 ip-10-0-10-72.eu-west-1.compute.internal proxysql[2356]: 2025-03-30 00:37:39 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
Mar 30 00:37:40 ip-10-0-10-72.eu-west-1.compute.internal systemd[1]: Started proxysql.service - High Performance Advanced Proxy for MySQL.
Mar 30 00:38:58 ip-10-0-10-72.eu-west-1.compute.internal systemd[1]: proxysql.service: Main process exited, code=exited, status=1/FAILURE
Mar 30 00:38:58 ip-10-0-10-72.eu-west-1.compute.internal systemd[1]: proxysql.service: Failed with result 'exit-code'.
contents of /var/lib/proxysql/proxysql.log
(TL;DR important lines seem to be):
2025-03-30 00:38:55 sqlite3db.cpp:218:execute(): [ERROR] SQLITE error: attempt to write a readonly database --- DROP TABLE IF EXISTS history_mysql_status_variables_lookup
2025-03-30 00:38:55 ProxySQL_Admin.cpp:8319:flush_mysql_variables___runtime_to_database(): [ERROR] SQLite3 error with return code 8. Error message: attempt to write a readonly database. Shutting down.
Full Output:
2025-03-30 00:38:23 [INFO] ProxySQL version 2.7.3-12-g50b7f85
2025-03-30 00:38:23 [INFO] ProxySQL SHA1 checksum: 89575fdd7e81119abef537f89d7c1c8380bce1f4
2025-03-30 00:38:23 [INFO] Trying to call external script after exit failure: (null)
2025-03-30 00:38:23 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2025-03-30 00:38:23 [INFO] ProxySQL exited after only 2 seconds , below the 10 seconds threshold. Restarting attempt 5
2025-03-30 00:38:23 [INFO] Angel process is waiting 32 seconds before starting a new ProxySQL process
2025-03-30 00:38:55 [INFO] Angel process started ProxySQL process 2482
2025-03-30 00:38:55 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2025-03-30 00:38:55 [INFO] Loaded built-in SQLite3
Standard ProxySQL MySQL Logger rev. 2.5.0421 -- MySQL_Logger.cpp -- Sun Mar 9 11:38:52 2025
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Sun Mar 9 11:38:52 2025
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Sun Mar 9 11:38:52 2025
2025-03-30 00:38:55 sqlite3db.cpp:218:execute(): [ERROR] SQLITE error: attempt to write a readonly database --- DROP TABLE IF EXISTS history_mysql_status_variables_lookup
2025-03-30 00:38:55 ProxySQL_Admin.cpp:8319:flush_mysql_variables___runtime_to_database(): [ERROR] SQLite3 error with return code 8. Error message: attempt to write a readonly database. Shutting down.
proxysql: ProxySQL_Admin.cpp:8319: void ProxySQL_Admin::flush_mysql_variables___runtime_to_database(SQLite3DB*, bool, bool, bool, bool, bool): Assertion `0' failed.
Error: signal 6:
/usr/bin/proxysql(_Z13crash_handleri+0x2a)[0x68f6da]
/lib64/libc.so.6(+0x54de0)[0x7f5a29054de0]
/lib64/libc.so.6(+0xa157c)[0x7f5a290a157c]
/lib64/libc.so.6(raise+0x16)[0x7f5a29054d36]
/lib64/libc.so.6(abort+0xd3)[0x7f5a290287f3]
/lib64/libc.so.6(+0x2871b)[0x7f5a2902871b]
/lib64/libc.so.6(+0x4dcd6)[0x7f5a2904dcd6]
/usr/bin/proxysql(_ZN14ProxySQL_Admin43flush_mysql_variables___runtime_to_databaseEP9SQLite3DBbbbbb+0xa07)[0x736b97]
/usr/bin/proxysql(_ZN14ProxySQL_Admin4initERK16bootstrap_info_t+0xc77)[0x785b37]
/usr/bin/proxysql(_Z31ProxySQL_Main_init_Admin_moduleRK16bootstrap_info_t+0xa6)[0x666b37]
/usr/bin/proxysql(_Z39ProxySQL_Main_init_phase2___not_startedRK16bootstrap_info_t+0x40)[0x667c03]
/usr/bin/proxysql(main+0x27af)[0x66dc56]
/lib64/libc.so.6(+0x3fee0)[0x7f5a2903fee0]
/lib64/libc.so.6(__libc_start_main+0x80)[0x7f5a2903ff90]
/usr/bin/proxysql(_start+0x2e)[0x6649be]
---- /usr/bin/proxysql(_Z13crash_handleri+0x2a) [0x68f6da] : crash_handler(int)
---- /usr/bin/proxysql(_ZN14ProxySQL_Admin43flush_mysql_variables___runtime_to_databaseEP9SQLite3DBbbbbb+0xa07) [0x736b97] : ProxySQL_Admin::flush_mysql_variables___runtime_to_database(SQLite3DB*, bool, bool, bool, bool, bool)
---- /usr/bin/proxysql(_ZN14ProxySQL_Admin4initERK16bootstrap_info_t+0xc77) [0x785b37] : ProxySQL_Admin::init(bootstrap_info_t const&)
---- /usr/bin/proxysql(_Z31ProxySQL_Main_init_Admin_moduleRK16bootstrap_info_t+0xa6) [0x666b37] : ProxySQL_Main_init_Admin_module(bootstrap_info_t const&)
---- /usr/bin/proxysql(_Z39ProxySQL_Main_init_phase2___not_startedRK16bootstrap_info_t+0x40) [0x667c03] : ProxySQL_Main_init_phase2___not_started(bootstrap_info_t const&)
To report a crashing bug visit: https://github.com/sysown/proxysql/issues
For support visit: https://proxysql.com/services/support/
2025-03-30 00:38:58 main.cpp:1471:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2025-03-30 00:38:58 [INFO] ProxySQL version 2.7.3-12-g50b7f85
2025-03-30 00:38:58 [INFO] ProxySQL SHA1 checksum: 89575fdd7e81119abef537f89d7c1c8380bce1f4
2025-03-30 00:38:58 [INFO] Trying to call external script after exit failure: (null)
2025-03-30 00:38:58 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
2025-03-30 00:38:58 main.cpp:1881:handleProcessRestart(): [ERROR] Angel process already attempted to restart ProxySQL 5 times and has no retries left. exit() with failure.
Further context:
I have configured proxysql using the /etc/proxysql.cnf as follows below,
TL:DR
- I added my database username name and password to the monitor_username and monitor_password fields,
- uncommented one database and filled in the details,
- uncommented one database user and filled in the username and password
I then ran: proxysql --reload once, enabled the proxysql service systemctl enable proxysql and restarted the server reboot.
I have tried these steps on amazon's EC2 with a mixture of using ubuntu and amazon linux and various instance types, the results are always the same:
- tg4.nano (arm achitecture)
- t3.nano (x86 architecture)
- t3.micro
- t3.small
Proxysql version: 2.7.x (I think - at least that is the version number from the install steps)
#file proxysql.cfg
########################################################################################
# This config file is parsed using libconfig , and its grammar is described in:
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file
########################################################################################
########################################################################################
# IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:
########################################################################################
# On startup, ProxySQL reads its config file (if present) to determine its datadir.
# What happens next depends on if the database file (disk) is present in the defined
# datadir (i.e. "/var/lib/proxysql/proxysql.db").
#
# If the database file is found, ProxySQL initializes its in-memory configuration from
# the persisted on-disk database. So, disk configuration gets loaded into memory and
# then propagated towards the runtime configuration.
#
# If the database file is not found and a config file exists, the config file is parsed
# and its content is loaded into the in-memory database, to then be both saved on-disk
# database and loaded at runtime.
#
# IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
# ProxySQL initializes its in-memory configuration from the persisted on-disk
# database ONLY. In other words, the configuration found in the proxysql.cnf
# file is only used to initial the on-disk database read on the first startup.
#
# In order to FORCE a re-initialise of the on-disk database from the configuration file
# the ProxySQL service should be started with "systemctl start proxysql-initial".
#
########################################################################################
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="bgmtDbUser"
monitor_password="<password>"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
# defines all the MySQL servers
mysql_servers =
(
# {
# address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# hostgroup = 0 # no default, required
# status = "ONLINE" # default: ONLINE
# weight = 1 # default: 1
# compression = 0 # default: 0
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
# },
# {
# address = "/var/lib/mysql/mysql.sock"
# port = 0
# hostgroup = 0
# },
{
address="<database hostname>"
port=3306
hostgroup=0
max_connections=20
},
# { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
# { address="127.0.0.1" , port=21892 , hostgroup=1 },
# { address="127.0.0.1" , port=21893 , hostgroup=1 }
# { address="127.0.0.2" , port=3306 , hostgroup=1 },
# { address="127.0.0.3" , port=3306 , hostgroup=1 },
# { address="127.0.0.4" , port=3306 , hostgroup=1 },
# { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
)
# defines all the MySQL users
mysql_users:
(
{
username = "bgmtDbUser" # no default , required
password = "<password>" # default: ''
default_hostgroup = 0 # default: 0
active = 1 # default: 1
},
# {
# username = "root"
# password = ""
# default_hostgroup = 0
# max_connections=1000
# default_schema="test"
# active = 1
# },
# { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
# {
# rule_id=1
# active=1
# match_pattern="^SELECT .* FOR UPDATE$"
# destination_hostgroup=0
# apply=1
# },
# {
# rule_id=2
# active=1
# match_pattern="^SELECT"
# destination_hostgroup=1
# apply=1
# }
)
scheduler=
(
# {
# id=1
# active=0
# interval_ms=10000
# filename="/var/lib/proxysql/proxysql_galera_checker.sh"
# arg1="0"
# arg2="0"
# arg3="0"
# arg4="1"
# arg5="/var/lib/proxysql/proxysql_galera_checker.log"
# }
)
mysql_replication_hostgroups=
(
# {
# writer_hostgroup=30
# reader_hostgroup=40
# comment="test repl 1"
# },
# {
# writer_hostgroup=50
# reader_hostgroup=60
# comment="test repl 2"
# }
)
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#
# setting = name (":" | "=") value (";" | "," | empty)
#
# value = scalar-value | array | list | group
#
# value-list = value | value-list "," value
#
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
# | string
#
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#
# array = "[" (scalar-value-list | empty) "]"
#
# list = "(" (value-list | empty) ")"
#
# group = "{" (setting-list | empty) "}"
#
# empty =
Metadata
Metadata
Assignees
Labels
No labels