[Bug]: syncCommit runs after target connection authentication, leaving a new-account multi-CN visibility race

[aptend]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Environment

• MO-Tester commit: b95f5c09930c42bdbe77828b6ae72d39f2be48ed
• MatrixOne branch: 4.2-dev
• MatrixOne PR/commit: [4.2-dev] fix(txn): preserve snapshot state on cancellation matrixone#26894 / 1b0068a92f91bf6a3e205740db2c1bc41e7c0a6b
• Deployment: Docker Compose, Proxy + 2 CNs
• Failed CI: https://github.com/matrixorigin/matrixone/actions/runs/31354056843/job/93350269778

Actual Behavior

When a test creates an account on one connection and immediately switches to a new connection authenticated as that account, MO-Tester calls getConnection(command) before syncCommit().

Authentication is part of getConnection(). If Proxy routes the new connection to another CN which has not observed the account metadata yet, authentication fails with:

internal error: there is no tenant acc_idx

The configured JDBC options amplify the short visibility race:

initialTimeout: 60
autoReconnect: "true"
maxReconnects: 4

In the observed failure:

04:27:14.624  new acc_idx connection reaches Proxy
04:27:14.629  CN0 authentication: there is no tenant acc_idx
04:27:14.634  second authentication attempt fails
04:27:24.624  original sys connection is closed after wait_timeout=10
04:28:14.635  JDBC retries after initialTimeout=60
04:28:14.662  acc_idx authentication succeeds
04:28:14       next SQL on the dead sys connection is reported as a timeout

The reported failing SQL was DROP ACCOUNT, but it never reached a CN at that time. Executor classified the dead connection generically as:

MO does not return result in 300000 ms

The 300000 value was the configured socket timeout, not the observed 60-second wait.

Current execution order:

connection = getConnection(command); // target-account authentication happens here

if (last_commit_id != command.getConn_id()) {
    syncCommit();                    // too late for first authentication
}

This creates a bootstrap dependency: the new-account connection must authenticate before the visibility barrier intended to make that authentication reliable can run.

Expected Behavior

When switching connections, MO-Tester should execute the cluster visibility barrier through its independent sys connection before creating or authenticating the target connection:

if (last_commit_id != command.getConn_id()) {
    syncCommit();
}
connection = getConnection(command);

A syncCommit failure should also be returned to the caller and fail the affected command instead of being silently ignored.

This should preserve the existing barrier semantics: MatrixOne mo_ctl('cn', 'synccommit', '') obtains the maximum commit timestamp from all CNs, broadcasts it to every CN, and waits for local logtail application.

Steps to Reproduce

Run against Proxy with at least two CNs:

set wait_timeout = 10;
create account acc_idx ADMIN_NAME 'root' IDENTIFIED BY '123456';

-- switch to a new MO-Tester session immediately
-- @session:id=1&user=acc_idx:root&password=123456
select 1;
-- @session

drop account acc_idx;

The failure is routing/timing dependent: account creation executes on CN-A and the new tenant login must be routed to CN-B before its catalog/logtail state has caught up.

Additional information

• MO-Tester call site:

  mo-tester/src/main/java/io/mo/db/Executor.java

  Lines 150 to 180 in b95f5c0

  	String sqlCmd = transformFlushSql(cmd, transformed);
  	if (transformed[0]) {
  	connection = connectionManager.getConnectionForSys();
  	} else {
  	connection = getConnection(command);
  	}
  	// if can not get valid connection,put the command to the abnormal commands
  	// array
  	if (connection == null) {
  	logger.error("[" + script.getFileName() + "][row:" + command.getPosition() + "]["
  	+ command.getCommand().trim() + "] can not get invalid connection,con[id="
  	+ command.getConn_id() + ", user=" + getConnUser(command) + ", pwd=" + getConnPswd(command)
  	+ "].");
  	script.addAbnoramlCmd(command);
  	command.getTestResult().setResult(RESULT.RESULT_TYPE_ABNORMAL);
  	command.getTestResult().setActResult(RESULT.ERROR_CAN_NOT_GET_CONNECTION_DESC);
  	command.getTestResult().setExpResult(command.getExpResult().toString());
  	logger.error("[" + script.getFileName() + "][row:" + command.getPosition() + "]["
  	+ command.getCommand().trim() + "] was executed failed");
  	logger.error("[EXPECT RESULT]:\n" + command.getTestResult().getExpResult());
  	logger.error("[ACTUAL RESULT]:\n" + command.getTestResult().getActResult());
  	continue;
  	}
  	if (last_commit_id != command.getConn_id()) {
  	logger.debug(String.format("[%s][row:%d][%s]Connection id had been turned from %d to %d",
  	command.getScriptFile(), command.getPosition(), command.getCommand(),
  	last_commit_id, command.getConn_id()));
  	syncCommit();
  	}

• syncCommit() implementation:

  mo-tester/src/main/java/io/mo/db/Executor.java

  Lines 644 to 650 in b95f5c0

  	public void syncCommit() {
  	executeSqlInternal(null, "select mo_ctl('cn','synccommit','')",
  	(stmt, updateCount, rs) -> {
  	logger.debug("select mo_ctl('cn','synccommit','') with sys user["
  	+ MoConfUtil.getSysUserName() + "] successfully.");
  	});
  	}

• MatrixOne cross-CN account visibility is documented as session consistency in CREATE ACCOUNT returns before tenant is visible across all CNs (race condition in multi-CN cluster) matrixone#25520. This issue is specifically about MO-Tester's barrier ordering, not changing MatrixOne's consistency contract.