Documentation · MariaDB
Sessions and pooling on MariaDB
A generated MariaDB server is always pooled. Each tool call is one transaction, and after it the session is reset, so nothing one caller leaves behind reaches the next. This page is the MariaDB counterpart of Session management and Connection pooling, which are written for Oracle.
Finding the connections
A generated server’s connections do not carry a name. MariaDB has nothing a session can name
itself with and change later: Oracle’s DBMS_APPLICATION_INFO has no counterpart, and a connection
attribute is fixed when the connection opens. So a server’s connections are known by their login,
their host and their database:
SELECT id, user, host, db, command, time, state, left(info, 80) AS statement
FROM information_schema.PROCESSLIST
WHERE user = 'your_login'
ORDER BY time DESC;
Give each generated server a login of its own if you need to tell them apart, or to tell them from other applications. It is also the better security arrangement, since the login’s grants are what the server can reach.
The generator’s own connection, which reads your catalog, does send a connection attribute,
program_name = MCPDBWizard. It is visible in performance_schema.session_connect_attrs only
when performance_schema is on, and it is off by default.
statement is the statement running now, with ? where values are bound: a generated server never
puts a value into SQL text. An idle pooled connection shows command = 'Sleep'.
To stop a call, KILL QUERY <id> stops its statement and the call fails; KILL <id> ends the
connection, and the pool replaces it.
Pooling is always on
The console’s pooling switch is fixed on for MariaDB. Each concurrent call borrows its own DAO factory, with its own connection and its own prepared statements. The pool settings are the same as on Oracle, and each can be overridden by an environment variable of the same name without regenerating:
| Key | Meaning |
|---|---|
DAO_POOL_MAX_SIZE | Ceiling. Also your effective concurrency limit. |
DAO_POOL_MIN_IDLE | How many stay connected when idle. |
DAO_POOL_MAX_WAIT_MS | How long a call waits for a free factory before “Server busy”. |
DAO_POOL_IDLE_TIMEOUT_MS | How long an idle factory survives before it is closed. |
DAO_POOL_ON_RETURN | COMMIT or ROLLBACK after a call that succeeded. |
Sizing
Size the maximum against the server’s max_connections, not against how many CPUs you have.
Each factory in the pool is one MariaDB connection, and so one server thread. max_connections is
151 by default. If you run several generated servers against one database, it is the total
across them, plus everything else that connects, that has to fit.
MariaDB has no counterpart of Oracle’s per-session OPEN_CURSORS limit to run into. Its nearest is
max_prepared_stmt_count (16,382 by default), which counts server-side prepared statements across
every connection to the server. A generated server uses none by default: the driver prepares its
statements on the client. If you add useServerPrepStmts=true to the connection URL, each statement
in use takes one, but the reset after every call releases them and the driver prepares them again
when they are next used, so a server holds only those of the calls in progress.
One call, one transaction
When a call succeeds, its transaction is committed (or rolled back, with
DAO_POOL_ON_RETURN=ROLLBACK). When it fails, it is rolled back. A COMMIT inside a procedure
commits the call’s work up to that point, and DDL commits implicitly, so a later failure cannot undo
what came before it.
Session state lasts one call
A MariaDB session carries state that a commit or rollback does not clear: a package’s variables, user
variables (@x) and temporary tables. On a pooled connection that state would pass from one caller
to the next. So after every call the server resets the session (COM_RESET_CONNECTION), which clears
all three, and the next caller starts from nothing. The reset costs about half a millisecond.
The consequence to know about: package state lasts one tool call, not the life of a connection. A package’s initialisation section runs again in each call that uses the package, and a package variable set in one call is not there in the next. A routine that relies on state carried between calls has to keep it in a table instead. (Before 2.0.39 the state did carry over, to whichever caller next got the same connection.)
Context Pinning values are user variables (@mcp_...), set at the start of
each call, so they too last only for that call.
Runaway calls
DAO_QUERY_TIMEOUT_SECONDS caps how long one statement may run. Past it, MariaDB stops the statement
with error 1969 (the wording differs: 11.x says “max_statement_time exceeded”, 12.3 and later
“execution time limit … exceeded”), the call fails, and the connection goes back to the pool. A rate
limit bounds how often calls start, not how long one runs, so this is the setting that protects the
database.
One exception, measured on 13.0: a SLEEP() or BENCHMARK() inside a routine is cut short at the
time limit without an error. The routine carries on from there and the call can succeed. A query
the routine runs is still stopped with 1969.
Watching the pool
A pooled server logs a POOL-STATS line every 15 seconds, which the Runtime page reads. The same
numbers are Prometheus gauges under mcpdbwizard_mcp_pool_* if you have
metrics on. Server busy: all database connections are in use means
the pool is at its ceiling: load, not a fault.