What causes "insert failed to generate an id" in vitessio/vitess?

Ensure the inserted-into table has an AUTO_INCREMENT primary key as the vreplication schema expects

insert failed to generate an id

Error message

insert failed to generate an id

What it means

The vreplication engine's exec, when executing an INSERT plan against the local _vt.vreplication-style tables, requires MySQL to report the generated auto-increment id (qr.InsertID). If the fetch succeeds but InsertID is 0, the insert did not generate an id and the engine aborts, since subsequent rows rely on computed ids (maxInsert = InsertID + numInserts).

Source

Thrown at go/vt/vttablet/tabletmanager/vreplication/engine.go:423

		return nil, err
	}
	defer dbClient.Close()

	// Change the database to ensure that these events don't get
	// replicated by another vreplication. This can happen when
	// we reverse replication.
	if _, err := dbClient.ExecuteFetch("use "+sidecar.GetIdentifier(), 1); err != nil {
		return nil, err
	}

	switch plan.opcode {
	case insertQuery:
		qr, err := dbClient.ExecuteFetch(plan.query, 1)
		if err != nil {
			return nil, err
		}
		if qr.InsertID == 0 {
			return nil, errors.New("insert failed to generate an id")
		}
		maxInsert := qr.InsertID + uint64(plan.numInserts)
		if maxInsert > math.MaxInt32 {
			return nil, fmt.Errorf("insert id %v out of range", qr.InsertID)
		}

		// If we are creating multiple streams, for example in a
		// merge workflow going from 2 shards to 1 shard, we
		// will be inserting multiple rows. To get the ids of
		// subsequent streams we need to know what the
		// auto_increment_increment step is. In a multi-primary
		// environment, like a Galera cluster, for example,
		// we will often encounter auto_increment steps > 1.
		autoIncrementStep, err := vre.getAutoIncrementStep(dbClient)
		if err != nil {
			return nil, err
		}
		firstID := int32(qr.InsertID)

View on GitHub (pinned to 01a25a7d17)

Solutions

Ensure the inserted-into table has an AUTO_INCREMENT primary key as the vreplication schema expects
Use the standard binlogplayer DBClient implementation that returns a correct InsertID from ExecuteFetch
Re-apply the default Vitess _vt table schema (verify with the schema in go/vt/mysqlctl) if it was customized
Inspect any proxy between vttablet and MySQL for LAST_INSERT_ID handling

Example fix

// before
CREATE TABLE _vt.vreplication (id INT PRIMARY KEY, ...) // no auto-increment
// after
CREATE TABLE _vt.vreplication (id INT AUTO_INCREMENT PRIMARY KEY, ...)

Defensive patterns

Strategy: validation

Validate before calling

// ensure target _vt tables keep AUTO_INCREMENT ids before exec
qr, err := dbClient.ExecuteFetch("show columns from _vt.vreplication like 'id'", 10)

Prevention

Never remove AUTO_INCREMENT from _vt.vreplication-style tables
Use the stock binlogplayer DBClient implementation
Avoid proxies between vttablet and MySQL that strip insert ids

When it happens

Trigger: Executing an INSERT through vrengine exec where the target table lacks an AUTO_INCREMENT column or MySQL fails to report an insert id — e.g. inserts into tables not declared with auto-increment, or drivers/proxies that strip the last-insert-id info.

Common situations: Custom or patched binlogplayer DBClient implementations that don't populate InsertID; altering _vt tables to remove AUTO_INCREMENT; running against a backend (proxy/firewall) that doesn't return LAST_INSERT_ID properly.

Related errors

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