Problem

Applications that send a COM_INIT_DB or USE <db> at the start and then proceed to generate a large amount of session commands will eventually end up pushing the DB change off of the history. This is the currently documented behavior and with the history compaction added in 23.08, is less of an issue than it was in older versions where cyclical patterns easily overflowed the history.

Solution

Whenever a COM_INIT_DB or USE command would be pruned from the history, replace the default database of the connection with that one. Since no history exists before it and all statements after it are executed with it, it'll be a safe default.

Summary

A client can connect with no default database and select one afterwards (COM_INIT_DB / USE).
If that client then runs more than max_sescmd_history session commands with prune_sescmd_history=true (the default), the COM_INIT_DB entry is pruned from the his
tory.
When the primary backend connection is later lost, readwritesplit reconnects transparently (master_reconnection, which transaction_replay and lazy_connect force on).
The new backend connection is opened with the database from the client's original handshake (empty), and the pruned COM_INIT_DB is not replayed.
From then on the session has no default database. Every unqualified query fails with:

ERROR 1046 (3D000): No database selected

MaxScale logs nothing above info level. COM_PING still succeeds, so connection pools (e.g. Connector/J isValid()) keep the broken connection and reuse it indefinitel
y.

This was found on a production ServiceNow deployment. One pooled connection returned "No database selected" more than 700 times per minute for over 1.5 hours after a net
work stall killed its primary backend connection. At the time, 701 of about 1,325 live sessions were at the 50-entry history cap.

Expected behaviour

After a transparent reconnection, the session's default database should be the same as before: MaxScale already tracks it in MYSQL_session::current_db.
If MaxScale cannot restore the session state faithfully, it should close the session rather than hand back a connection in a different state.

Actual behaviour

The reconnected session has DATABASE() = NULL. Unqualified queries fail with 1046. Ping and isValid() succeed. The client-side catalog (Connector/J {{getCatalog(
)}}) still reports the old database.

How to reproduce

1. MariaDB primary (any 10.6+). Create a schema and a user:

CREATE DATABASE db1;

CREATE TABLE db1.t (id INT PRIMARY KEY, v VARCHAR(20));

INSERT INTO db1.t VALUES (1,'one'),(2,'two'),(3,'three');

CREATE USER 'app'@'%' IDENTIFIED BY '...';

GRANT ALL ON db1.* TO 'app'@'%';

2. MaxScale configuration used. The service settings mirror the production system. The CCR filter is only there so that every statement is routed to the primary.

[server1]

type=server

address=<primary>

port=3306

[server2]

type=server

address=<replica>

port=3306

[Monitor]

type=monitor

module=mariadbmon

servers=server1,server2

user=maxuser

password=...

monitor_interval=2000ms

[CCRFilter]

type=filter

module=ccrfilter

global=true

time=65535s

options=ignorecase

[RW-Service]

type=service

router=readwritesplit

servers=server1,server2

user=maxuser

password=...

filters=CCRFilter

transaction_replay=true

lazy_connect=true

master_reconnection=true

master_failure_mode=fail_on_write

prune_sescmd_history=true

max_sescmd_history=50

connection_keepalive=300s

master_accept_reads=true

retry_failed_reads=true

delayed_retry=true

[RW-Listener]

type=listener

service=RW-Service

port=4407

3. Do one write through MaxScale so the CCR filter pins reads to the primary:

mysql -h <maxscale> -P 4407 -u app -p -e "UPDATE db1.t SET v=v WHERE id=1"

4. Open a session through MaxScale without a default database and push the database selection out of the history:

-- mysql -h <maxscale> -P 4407 -u app -p --skip-reconnect     (no database argument)

use db1                     -- CLI sends COM_INIT_DB

SET @v1=1; SET @v2=2; ...   -- 60 distinct session commands in total

                            -- (lab mix: SET @vN=N, SET SESSION sql_mode=..., SET NAMES utf8mb4)

SELECT CONNECTION_ID(), DATABASE();   -- e.g. 25, db1

SELECT * FROM t;                      -- 3 rows

maxctrl show session <id> now reports sescmd_history_len=50.

5. On the primary, kill that backend connection while the client is idle:

KILL 25;

6. In the same client session:

SELECT 1;                              -- OK

SELECT CONNECTION_ID(), DATABASE();    -- 28, NULL

SELECT * FROM t;                       -- ERROR 1046 (3D000): No database selected

Lab output (MaxScale 23.02.10, log_info=true)

Client:

| srv   | backend_id | db_before_kill |

| 10000 |         25 | db1            |

...

| srv   | backend_id_after | db_after_kill |

| 10000 |               28 | NULL          |

ERROR 1046 (3D000) at line 67: No database selected

maxscale.log. The replay starts at history entry 12, and COM_INIT_DB (entry 1) is not replayed:

info   : (3) [readwritesplit] (RW-Service); Primary 'server1' failed: #HY000: Lost connection to backend server: connection closed by peer (server1, session=3, conn_id=2

5)

info   : (3) [readwritesplit] (RW-Service); Connected to 'server1'

info   : (3) Connected to 'server1' with thread id 28

info   : (3) Authentication to 'server1' succeeded.

info   : (3) Execute COM_QUERY 12 on 'server1': SET @v11=11

info   : (3) Execute COM_QUERY 13 on 'server1': SET @v12=12

...

info   : (3) Execute COM_QUERY 61 on 'server1': SET @v60=60

Nothing is logged at notice, warning or error level.

Connector/J 3.5.1 and 2.7.12, URL jdbc:mariadb://<maxscale>:4407/ (no database), same steps:

isValid(5) = true

getCatalog() = db1

SELECT COUNT(*) AS n FROM t -> SQLException state=3D000 code=1046 msg=(conn=5) No database selected

isValid(5) = true

Variations tested

• Stall instead of KILL: the MaxScale-to-primary flow was black-holed with nftables until TCP keepalive fired (network error (... 110, Connection timed out)). Afte
  r the rule was removed the result was the same: DATABASE() NULL and ERROR 1046. This is the production failure path.
• Control, history below the cap (USE + 10 SETs): the replay starts with Execute COM_INIT_DB 1 and the session keeps db1.
• Database in the handshake (mysql ... db1 / jdbc:mariadb://<maxscale>:4407/db1): db1 is kept after the reconnection. Pruned user variables (@v1) are still los
  t, as expected.
• prune_sescmd_history=false: the session is not recovered. The client gets 1927-08S01 Connection killed by MaxScale and isValid() becomes false. This is the w
  orkaround we are recommending.
• max_sescmd_history=500: works while the session stays under the cap.
• Versions: identical behaviour on 23.02.10, 23.02.17 and 24.02.9. 25.01 and later not tested.
• Not tested: a USE db1 sent as COM_QUERY text instead of COM_INIT_DB.

Analysis (source at maxscale-23.02.10)

• MariaDBClientConnection::clientReply() (server/modules/protocol/MariaDB/mariadb_client.cc:3018-3024) updates m_session_data->current_db after a successful
  database change. MaxScale therefore knows the session's current database.
• New backend connections and COM_CHANGE_USER send auth_data->default_db, the database from the initial client handshake, not current_db ({{server/modules/prot
  ocol/MariaDB/mariadb_backend.cc:1554}}, 1903-1987). The same applies in 24.02.8 (mariadb_backend.cc:1290, 1636-1670).
• MariaDBClientConnection::prune_history() (mariadb_client.cc:1404-1423) drops the oldest history entry whatever its type, including COM_INIT_DB.
• RWSplitSession::can_recover_servers() (server/modules/routing/readwritesplit/rwsplitsession.hh:283-290) allows recovery whenever prune_sescmd_history is en
  abled, even after the history has been pruned. readwritesplit.cc:310-322 forces master_reconnection=true when transaction_replay or lazy_connect is on, so users
  cannot simply opt out.

The Configuration Guide warns that pruned commands are not replayed. The default database is different from other session state, though: MaxScale tracks it explicitly, y
et does not use it when it reconnects.

Suggested fix

One of these (the first looks simplest):

1. When creating a replacement backend connection for an existing session, send current_db in the handshake (and in COM_CHANGE_USER) instead of the initial {{default_
   db}}.
2. Never prune the most recent COM_INIT_DB / USE from the session command history. This is analogous to MXS-5302, which exempted COM_STMT_PREPARE.

Related

• MXS-5302: Prepared statements should never be removed from session command history. Same class of problem (pruned entry lost on reconnection), fixed for COM_STMT_PREPA
  RE only. The 23.02.12 prune_history() still prunes COM_INIT_DB.

Workarounds

• prune_sescmd_history=false: sessions whose history overflowed are closed instead of silently recovered.
• Put the database in the client's connection string/handshake.
• Raise max_sescmd_history. This only delays the problem and costs memory per session.