Kamailio MySQL errors: server has gone away, too many connections

What you're seeing

ERROR: db_mysql [km_dbase.c:...]: db_mysql_submit_query_impl(): driver error on query: MySQL server has gone away (2006)
ERROR: <core> [db_query.c:...]: db_do_query_internal(): error while submitting query
ERROR: db_mysql [km_my_con.c:...]: db_mysql_new_connection(): driver error: Too many connections

Other codes to recognise: 2013 (lost connection during query), 1040 (too many connections), 1045 (access denied), and Authentication plugin 'caching_sha2_password' cannot be loaded.

Likely causes

Most common first.

1. The database really was unreachable, or auto_reconnect is off. Idle connections closed by MySQL's wait_timeout are normally handled silently: db_mysql pings before a query when a connection has been idle longer than ping_interval, and with auto_reconnect on (the default) it retries a failed query up to three times. If the error is logged, every retry failed. MySQL was restarting, failing over, or unreachable (a moved VIP, a proxy, a firewall dropping idle flows), or auto_reconnect has been turned off.
2. More connections than MySQL allows. Roughly one per process per distinct db_url. 32 UDP children, TCP workers, timers and RPC processes, times three different URLs, easily passes MySQL's default max_connections of 151.
3. Queries on the call path are slow. Database calls block the SIP worker that makes them. With usrloc in DB-only mode (db_mode 3), every lookup() hits MySQL. A slow query or a locked table stalls workers until SIP traffic backs up.
4. Authentication plugin mismatch. MySQL 8 defaults to caching_sha2_password. A db_mysql module built against an older or MariaDB client library can't use it. Install client libraries that support it, or create the Kamailio user with an authentication plugin your client supports.
5. Schema doesn't match the Kamailio version. Each module checks its table version in the version table at startup. After an upgrade with a skipped schema change, the module refuses to start or its queries fail.

How to fix

1. Reconnect cleanly and fail fast

#!define DBURL "mysql://kamailio:[email protected]/kamailio"

loadmodule "db_mysql.so"
# ping before a query if the connection has been idle this long (seconds);
# keep it below MySQL's wait_timeout and any firewall/proxy idle timeout
modparam("db_mysql", "ping_interval", 60)
# connect/read/write timeout (seconds), so a dead DB doesn't hang workers
modparam("db_mysql", "timeout_interval", 2)
# default; retries a query that fails with "gone away"/"lost connection"
modparam("db_mysql", "auto_reconnect", 1)

Use the same DBURL string for every module. Connections are shared per process only when the URL is identical.

2. Take the database off the call path

loadmodule "usrloc.so"
modparam("usrloc", "db_url", DBURL)
# 2 = write-back: lookups from memory, changes flushed every timer_interval
modparam("usrloc", "db_mode", 2)
modparam("usrloc", "timer_interval", 60)

For permissions, dispatcher and similar modules, load from the database at startup and use their *.reload RPCs rather than querying per call. Cache per-call lookups (routing, CLI checks) in an htable with an expiry.

3. Size MySQL for Kamailio's processes

# how many processes Kamailio runs
kamcmd core.psx | grep -c IDX

-- on MySQL: current use and the limits
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';

Set max_connections above processes × distinct DB URLs for every Kamailio node combined, plus room for your own tools.