apache/shardingsphere · error · TableExistsException

1050

1050

Error message

Table '%s' already exists

What it means

When a DDL statement routed by the single-rule engine targets a table that already exists, and the statement neither contains IF NOT EXISTS nor sets the skip-metadata-validation hint, SingleRouteEngine throws TableExistsException (error code 1050) with the table name. The engine refuses to route a CREATE TABLE that would collide with an existing single table.

Source

Thrown at kernel/single/core/src/main/java/org/apache/shardingsphere/single/route/engine/SingleRouteEngine.java:118

            boolean containsIfNotExists = ((CreateTableStatement) sqlStatement).isIfNotExists();
            if (dataNode.isPresent()) {
                routeDDLStatementWithExistTable(routeContext, containsIfNotExists, dataNode.get(), table);
            } else {
                String dataSourceName = rule.assignNewDataSourceName();
                routeContext.getRouteUnits().add(new RouteUnit(new RouteMapper(dataSourceName, dataSourceName), Collections.singleton(new RouteMapper(table.getTableName(), table.getTableName()))));
            }
        } else {
            fillRouteContext(rule, routeContext, singleTables, database);
        }
    }
    
    private void routeDDLStatementWithExistTable(final RouteContext routeContext, final boolean containsIfNotExists, final DataNode dataNode, final QualifiedTable table) {
        if (containsIfNotExists || hintValueContext.isSkipMetadataValidate()) {
            String dataSourceName = dataNode.getDataSourceName();
            routeContext.getRouteUnits()
                    .add(new RouteUnit(new RouteMapper(dataSourceName, dataSourceName), Collections.singleton(new RouteMapper(table.getTableName(), table.getTableName()))));
        } else {
            throw new TableExistsException(table.getTableName());
        }
    }
    
    private void fillRouteContext(final SingleRule singleRule, final RouteContext routeContext, final Collection<QualifiedTable> logicTables, final ShardingSphereDatabase database) {
        for (QualifiedTable each : logicTables) {
            String tableName = each.getTableName();
            DataNode dataNode = singleRule.findTableDataNode(database, each).orElseThrow(() -> new SingleTableNotFoundException(tableName));
            String dataSource = dataNode.getDataSourceName();
            routeContext.putRouteUnit(new RouteMapper(dataSource, dataSource), Collections.singletonList(new RouteMapper(tableName, tableName)));
        }
    }
    
    private void combineRouteContext(final RouteContext routeContext, final RouteContext newRouteContext) {
        Map<String, RouteUnit> dataSourceRouteUnits = getDataSourceRouteUnits(newRouteContext);
        routeContext.getRouteUnits().removeIf(each -> !dataSourceRouteUnits.containsKey(each.getDataSourceMapper().getLogicName()));
        for (Entry<String, RouteUnit> entry : dataSourceRouteUnits.entrySet()) {
            routeContext.putRouteUnit(entry.getValue().getDataSourceMapper(), entry.getValue().getTableMappers());
        }

View on GitHub (pinned to e952770a21)

Solutions

1. Add IF NOT EXISTS to the CREATE TABLE statement when the dialect supports it.
2. Drop or rename the existing table first if the intent is a clean re-create (DROP TABLE IF EXISTS t;).
3. For one-off schema sync where validation should be bypassed, set the SQL hint to skip metadata validation (/* SHARDINGSPHERE_HINT: ... */ per current hint syntax) — use sparingly.
4. Check SHOW SINGLE TABLES / metadata to see where the table is already registered.

Example fix

-- before
CREATE TABLE t_order (id INT, user_id INT);
-- after
CREATE TABLE IF NOT EXISTS t_order (id INT, user_id INT);

-- before creating, check existing single tables
SHOW SINGLE TABLES;
-- or test existence on storage
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 't_order';

try (Statement st = conn.createStatement()) {
    st.execute("CREATE TABLE t_order (...) /* IF NOT EXISTS variant */");
} catch (final TableExistsException ex) {
    // treat as success when the table already has the expected schema
}

When it happens

Trigger: Executing CREATE TABLE t (...) through ShardingSphere when t is already a loaded single table in the target database; executing CREATE TABLE without IF NOT EXISTS while neither the SQL nor the hint context authorizes skipping validation.

Common situations: Re-running an init script that already executed; deploying schema migrations twice; legacy tables already present in the storage unit; ORACLE/MySQL dialect differences where IF NOT EXISTS is unsupported so users omit it.

Related errors

• 40
• 41
• 33
• 36
• 57

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/64ed8850deb5cd39. Report an issue: GitHub.