MyCATApache/Mycat-Server · error · SQLNonTransientException

create table from other table not supported :

Error message

create table from other table not supported :

What it means

Mycat's DruidCreateTableParser rejects CREATE TABLE ... AS SELECT / LIKE (statements carrying a query). Routing DDL that copies another table's data cannot be planned across sharded nodes, so the parser logs a warning and throws this SQLNonTransientException before any route is computed.

Solutions

1. Rewrite as two statements: CREATE TABLE t2 (...) then INSERT INTO t2 SELECT ... .
2. Run the CTAS statement directly against the backend MySQL node, bypassing Mycat routing.
3. Create the target table explicitly with the same DDL as the source table instead of AS SELECT.

Example fix

// before
CREATE TABLE t2 AS SELECT * FROM t1;
// after
CREATE TABLE t2 (id INT, name VARCHAR(64));
INSERT INTO t2 SELECT * FROM t1;

// Reject CTAS before routing through Mycat
if (/^\s*CREATE\s+TABLE\s+.+\s+AS\s+SELECT|CREATE\s+TABLE\s+.+\s+LIKE\s+/i.test(sql)) {
  throw new Error("CREATE TABLE AS/LIKE is not supported through Mycat routing");
}

When it happens

Trigger: Sending 'CREATE TABLE t2 AS SELECT ...' or any create-table-from-query statement through Mycat; detected in statementParse when MySqlCreateTableStatement.getQuery() != null.

Common situations: Schema migration scripts or ORM auto-DDL that use CTAS running against Mycat; SQL that works on standalone MySQL failing through the Mycat proxy.

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

• batch not supported
• CallableStatement not supported
• Can't identify the operation of of where
• ChildTable multi insert not provided
• "create table from other table not supported :" + stmt

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/d5d3f6e1c6205860. 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)