MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

ReplaceStatement can't be supported,use insert into ...on…

Error message

 ReplaceStatement can't be supported,use insert into ...on duplicate key update... instead

What it means

Mycat's Druid router explicitly rejects REPLACE statements via checkUnSupportedStatement. REPLACE INTO performs delete+insert semantics that conflict with Mycat's sharding routing and replication handling, so users are told to use INSERT ... ON DUPLICATE KEY UPDATE instead.

Solutions

Rewrite the statement as INSERT ... ON DUPLICATE KEY UPDATE with the desired column values.
Pre-check existence in the application and issue UPDATE or INSERT accordingly.
If REPLACE semantics are truly needed, execute directly against the backend MySQL, bypassing Mycat for that connection.
Wrap the upsert in a stored procedure on the backend and call it through Mycat if supported.

Example fix

// before
REPLACE INTO users (id, name) VALUES (1, 'alice');
// after
INSERT INTO users (id, name) VALUES (1, 'alice') ON DUPLICATE KEY UPDATE name = VALUES(name);

Defensive patterns

Strategy: validation

Validate before calling

if (sql.trim().toLowerCase().startsWith("replace ")) {
    throw new IllegalArgumentException("REPLACE not supported by Mycat; use INSERT ... ON DUPLICATE KEY UPDATE");
}

Try / catch

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("ReplaceStatement can't be supported")) {
        // convert to INSERT ... ON DUPLICATE KEY UPDATE and retry once
    } else throw e;
}

Prevention

Ban REPLACE INTO in code review/SQL lint rules for Mycat targets.
Use INSERT ... ON DUPLICATE KEY UPDATE as the standard upsert.
Configure ORMs/sync tools to emit upserts instead of REPLACE.
Route REPLACE-needing jobs directly to backend MySQL if unavoidable.

When it happens

Trigger: Executing 'REPLACE INTO table ...' (any SQLReplaceStatement, including REPLACE ... SELECT) through Mycat routing; the check runs in routeNormalSqlWithAST0 via checkUnSupportedStatement.

Common situations: Porting MySQL scripts that rely on REPLACE; ORMs or sync tools (e.g. data import jobs) generating REPLACE INTO; developers using REPLACE as an upsert idiom.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/640887722656195f. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java:605

	/**
	 * SELECT 语句
	 */
	private boolean isSelect(SQLStatement statement) {
		if(statement instanceof SQLSelectStatement) {
			return true;
		}
		return false;
	}

	/**
	 * 检验不支持的SQLStatement类型 ：不支持的类型直接抛SQLSyntaxErrorException异常
	 * @param statement
	 * @throws SQLSyntaxErrorException
	 */
	private void checkUnSupportedStatement(SQLStatement statement) throws SQLSyntaxErrorException {
		//不支持replace语句
		if (statement instanceof SQLReplaceStatement) {
			throw new SQLSyntaxErrorException(" ReplaceStatement can't be supported,use insert into ...on duplicate key update... instead ");
		}
	}

	/**
	 * 分析 SHOW SQL
	 */
	@Override
	public RouteResultset analyseShowSQL(SchemaConfig schema,
			RouteResultset rrs, String stmt) throws SQLSyntaxErrorException {

		String[] fields = SchemaUtil.parseShowTable(stmt);
        if ("1".equals(fields[0])) {// show tables
			String relSchema = fields[3];
			String tableName = fields[8];
            //
            if (relSchema != null && !relSchema.equalsIgnoreCase(schema.getName())) {
                schema = MycatServer.getInstance().getConfig().getSchemas().get(relSchema);
                if (schema == null) {

View on GitHub (pinned to 65f8d8beb7)