Why Does SET SQL_SELECT_LIMIT=1 Cause Missing Rows in MySQL Connections?

Developers reported that after executing certain SQL statements, subsequent queries sometimes returned only a single record, causing data loss. Investigation revealed that a preceding SET SQL_SELECT_LIMIT=1 statement was automatically issued by the MySQL driver when Statement.setMaxRows was called.

Root Cause in MySQL Driver

The driver’s executeInternal method eventually calls locallyScopedConn.setSessionMaxRows, which executes SET SQL_SELECT_LIMIT=... on the session. Because the connection pool reuses the same physical connection, the limit persists for the next client, explaining the observed one‑row results.

Reproducing the Issue

Using HikariCP with a pool size of 1, the following steps demonstrate the problem:

private boolean executeInternal(String sql, boolean returnGeneratedKeys) throws SQLException {
    JdbcConnection locallyScopedConn = this.checkClosed();
    ...
    locallyScopedConn.setSessionMaxRows(maybeSelect ? this.maxRows : -1);
    ...
}

public void setSessionMaxRows(int max) throws SQLException {
    synchronized(this.getConnectionMutex()) {
        if (this.session.getSessionMaxRows() != max) {
            this.session.setSessionMaxRows(max);
            this.session.execSQL((Query)null, "SET SQL_SELECT_LIMIT=" + (this.session.getSessionMaxRows() == -1 ? "DEFAULT" : this.session.getSessionMaxRows()), -1, null, false, null, null, false);
        }
    }
}

Running a test that explicitly executes SET SQL_SELECT_LIMIT=1 shows that both the first and second queries return only the first row.

Why setMaxRows Does Not Replicate the Problem

When a new Statement is created, the driver compares the current maxRows with the session’s setting. If they differ, it automatically sends SET SQL_SELECT_LIMIT=DEFAULT to reset the variable, so using statement.setMaxRows(1) does not cause the same row‑loss issue.

Mitigation Strategies

Prefer using LIMIT clauses or top‑N queries instead of manipulating SQL_SELECT_LIMIT directly.

Avoid issuing raw SET statements through JDBC; let the driver manage session variables.

When using proxies or connection pools, detect and reset session‑level settings before returning a connection to the pool.

Connection Pool Pollution and Solutions

Because pooled connections retain session state, improper settings can “pollute” the connection, leading to hard‑to‑debug issues. Options include:

Connect directly to MySQL without a proxy, letting the driver handle resets.

Use a DB proxy that can detect and clean up harmful SET statements.

Implement connection‑management logic that parses incoming SQL, identifies risky SET commands, and resets or isolates the connection accordingly.

These approaches help maintain connection hygiene, improve proxy performance, and prevent unexpected query behavior caused by lingering session variables such as SQL_SELECT_LIMIT, autocommit, or transaction isolation levels.