MyCATApache/Mycat-Server · error · SQLNonTransientException

"create table from other table not supported :" + stmt

Error message

"create table from other table not supported :" + stmt

What it means

Mycat's Druid CREATE TABLE parser rejects CREATE TABLE ... AS SELECT / CREATE TABLE ... LIKE statements. When the parsed MySqlCreateTableStatement has a non-null query (i.e. the table is created from another table's result set), routing cannot proceed because Mycat does not support distributing a CTAS statement, so it throws SQLNonTransientException.

Solutions

  1. Rewrite as two statements: `CREATE TABLE new_table (...)` then `INSERT INTO new_table SELECT ... FROM old_table`
  2. Execute the CTAS statement directly on each backend MySQL node, bypassing Mycat routing
  3. Upgrade Mycat to a version that supports CTAS, if available
  4. Create the table structure explicitly (showing CREATE from the source table) and copy data separately

Example fix

// before
CREATE TABLE t2 AS SELECT * FROM t1;
// after
CREATE TABLE t2 LIKE t1_structure; -- or explicit DDL
INSERT INTO t2 SELECT * FROM t1;
Defensive patterns

Strategy: try-catch

Validate before calling

// reject CTAS before sending through Mycat
String normalized = sql.replaceAll("\\s+", " ").toUpperCase();
if (normalized.startsWith("CREATE TABLE") && (normalized.contains(" AS SELECT") || normalized.contains(" LIKE "))) {
    throw new IllegalArgumentException("CTAS not supported via Mycat: split DDL and DML");
}

Type guard

// Java: check before routing
static boolean isCreateTableAsSelect(SQLStatement stmt) {
    return stmt instanceof MySqlCreateTableStatement
        && ((MySqlCreateTableStatement) stmt).getQuery() != null;
}

Try / catch

try {
    router.route(schema, rrs, stmt);
} catch (SQLNonTransientException e) {
    if (e.getMessage().startsWith("create table from other table not supported")) {
        throw new IllegalArgumentException("Split CREATE TABLE ... AS SELECT into DDL + INSERT ... SELECT", e);
    }
    throw e;
}

Prevention

When it happens

Trigger: Executing `CREATE TABLE new_table AS SELECT ... FROM old_table` (or any CREATE TABLE with a trailing query) against a Mycat-managed schema; statementParse detects createStmt.getQuery() != null and throws.

Common situations: Migrating DDL scripts written for a plain MySQL database straight to Mycat; schema-copy/idempotent migration tools (e.g. Flyway/Liquibase output) that emit CTAS; developers cloning tables during test setup.

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/6802250ccc45e258. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/DruidCreateTableParser.java:35

import io.mycat.route.function.SlotFunction;
import io.mycat.route.parser.druid.MycatSchemaStatVisitor;
import io.mycat.util.StringUtil;


public class DruidCreateTableParser extends DefaultDruidParser {

	@Override
	public void visitorParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt,
			MycatSchemaStatVisitor visitor) {
	}
	
	@Override
	public void statementParse(SchemaConfig schema, RouteResultset rrs, SQLStatement stmt) throws SQLNonTransientException {
		MySqlCreateTableStatement createStmt = (MySqlCreateTableStatement)stmt;
		if(createStmt.getQuery() != null) {
			String msg = "create table from other table not supported :" + stmt;
			LOGGER.warn(msg);
			throw new SQLNonTransientException(msg);
		}
		String tableName = StringUtil.removeBackquote(createStmt.getTableSource().toString().toUpperCase());
		if(schema.getTables().containsKey(tableName)) {
			TableConfig tableConfig = schema.getTables().get(tableName);
			AbstractPartitionAlgorithm algorithm = tableConfig.getRule().getRuleAlgorithm();
			if(algorithm instanceof SlotFunction){
				SQLColumnDefinition column = new SQLColumnDefinition();
				column.setDataType(new SQLCharacterDataType("int"));
				column.setName(new SQLIdentifierExpr("_slot"));
				column.setComment(new SQLCharExpr("自动迁移算法slot,禁止修改"));
				((SQLCreateTableStatement)stmt).getTableElementList().add(column);
				String sql = createStmt.toString();
				rrs.setStatement(sql);
				ctx.setSql(sql);
			}
		}
		ctx.addTable(tableName);

View on GitHub (pinned to 65f8d8beb7)