What causes "INSERT with a target destination is not allowed" in vitessio/vitess?

Remove the target destination before executing the INSERT and let vindex-based routing pick the shard

VT09017

VT09017

Error message

INSERT with a target destination is not allowed

What it means

VT09017 indicates an INSERT statement with an explicitly targeted destination, which Vitess does not allow. Unlike UPDATE/DELETE, INSERTs cannot be pinned to a destination target at all — routing must be derived from the vindex.

Source

Thrown at go/vt/vtgate/planbuilder/operators/route.go:418

	)
}

func createTargetedRouting(ctx *plancontext.PlanningContext, target key.ShardDestination, tabletType topodatapb.TabletType, vschemaTable *vindexes.BaseTable) Routing {
	switch ctx.Statement.(type) {
	case *sqlparser.Update:
		if tabletType != topodatapb.TabletType_PRIMARY {
			panic(vterrors.VT09002("update"))
		}
	case *sqlparser.Delete:
		if tabletType != topodatapb.TabletType_PRIMARY {
			panic(vterrors.VT09002("delete"))
		}
	case *sqlparser.Insert:
		if tabletType != topodatapb.TabletType_PRIMARY {
			panic(vterrors.VT09002("insert"))
		}
		if target != nil {
			panic(vterrors.VT09017("INSERT with a target destination is not allowed"))
		}
	case sqlparser.SelectStatement:
		if target != nil {
			panic(vterrors.VT09017("SELECT with a target destination is not allowed"))
		}
	}

	if target != nil {
		return &TargetedRouting{
			keyspace:          vschemaTable.Keyspace,
			TargetDestination: target,
		}
	}
	return nil
}

// createRouteFromTable creates a route from the given VSchema table.
func createRouteFromVSchemaTable(

View on GitHub (pinned to 01a25a7d17)

Solutions

Remove the target destination before executing the INSERT and let vindex-based routing pick the shard
Split the workflow: do reads/targeted queries with the target, then reset the target for INSERTs
Use `USE ks` (keyspace only) instead of a shard destination

Example fix

// before
USE ks:-80;
INSERT INTO t (id) VALUES (5); -- VT09017
// after
USE ks;
INSERT INTO t (id) VALUES (5); -- routed by primary vindex

Defensive patterns

Strategy: validation

Validate before calling

// ensure no shard/destination qualifier is active before INSERT
if strings.Contains(currentTarget, ":") || strings.Contains(currentTarget, "@") {
    conn.Execute("USE ks", nil) // keyspace only, no destination
}
conn.Execute("INSERT INTO t (id) VALUES (5)", nil)

Try / catch

_, err := conn.Execute("INSERT INTO t (id) VALUES (5)", nil)
if err != nil && strings.Contains(err.Error(), "VT09017") {
    conn.Execute("USE ks", nil)
    // retry — vindex routing picks the shard
}

Prevention

Never set shard-level targets for sessions that perform INSERTs
Rely on primary vindex routing instead of manual shard pinning for writes
Reset the target (`USE ks`) after targeted maintenance work

When it happens

Trigger: Running an INSERT (e.g. `INSERT INTO ks.t ...`) while a target destination is set (USE `ks:shard-0` / `@shard` style target), so target != nil in the Insert case of createTargetedRouting.

Common situations: Application sets a shard-specific target to help routing and then performs an INSERT; migration scripts that set explicit shard targets before loading data.

Related errors

AI-assisted analysis of vitessio/vitess@01a25a7d17 (2026-09-01). Data as JSON: /api/errors/576234a3321644ff. Report an issue: GitHub.