apache/beam · error

expected to write: , but written

Error message

expected to write: %v, but written: %v

What it means

After executing the batched INSERT, writer.write compares database/sql's RowsAffected() result against the number of rows it attempted to write (w.rowCount). If the database reports a different number of affected rows, this error is thrown, because silently losing (or double-counting) rows would corrupt the pipeline's exactly-once expectations.

Solutions

1. Inspect the SQL table for constraints, unique keys, triggers, or ON CONFLICT/INSERT IGNORE clauses that suppress row inserts, and remove or account for them
2. Ensure the driver reports RowsAffected accurately for batch inserts (check driver docs/config, e.g. MySQL CLIENT_FOUND_ROWS flag)
3. Deduplicate or pre-validate incoming rows so every bound row is a genuine new insert
4. Retry the batch after clearing duplicates if the failure is transient contention

Example fix

// before
INSERT IGNORE INTO users (id, name) VALUES ...  // duplicate rows silently skipped
// after
INSERT INTO users (id, name) VALUES ... ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

// Before writing, check for rows that would be suppressed:
// e.g. pre-query for existing unique keys
func hasConflicts(ctx context.Context, db *sql.DB, table, keyCol string, keys []any) (bool, error) {
    var n int
    err := db.QueryRowContext(ctx,
        fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE %s IN (?%s)", table, keyCol, strings.Repeat(",?", len(keys)-1)),
        keys...).Scan(&n)
    return n > 0, err
}

if err := w.write(ctx, db); err != nil {
    if strings.Contains(err.Error(), "expected to write") {
        // count mismatch: log rows, dedupe/fix conflicts, retry batch
        return retryBatch(ctx, db, w)
    }
    return err
}

When it happens

Trigger: Executing the multi-row INSERT where fewer (or more) rows are actually inserted than requested — e.g. some rows violate a constraint and the driver/dialect skips them, an INSERT IGNORE / ON DUPLICATE KEY style behavior suppresses rows, triggers alter the affected-row count, or the driver reports affected rows differently (some drivers return 0 or -1 for batch inserts, or rows with identical values are skipped by MySQL).

Common situations: MySQL with the default (non-CLIENT_FOUND_ROWS) behavior skipping duplicate rows in batch inserts; a table with triggers or ON CONFLICT DO NOTHING clauses; a driver whose RowsAffected is unreliable for multi-row statements; concurrent writers to the same table causing unexpected counts.

Related errors

• expected row values, but had
• columns were empty
• failed to matched a field for SQL column
• failed to open database
• failed to prepare query

AI-assisted analysis of apache/beam@12126d8942 (2026-09-13). Data as JSON: /api/errors/eb4f5c72cdccba19. Report an issue: GitHub.

Appendix: source

Thrown at sdks/go/pkg/beam/io/databaseio/writer.go:70

	}
	w.binding = append(w.binding, row...)
	return nil
}

func (w *writer) write(ctx context.Context, db *sql.DB) error {
	values := w.valueTemplateGenerator.generate(w.rowCount, w.columnCount)
	if len(values) == 0 {
		log.Info(ctx, "No value(s) to be written....")
		return nil
	}
	SQL := w.sqlTemplate + values
	resultSet, err := db.ExecContext(ctx, SQL, w.binding...)
	if err != nil {
		return err
	}
	affected, _ := resultSet.RowsAffected()
	if int(affected) != w.rowCount {
		return errors.Errorf("expected to write: %v, but written: %v", w.rowCount, affected)
	}
	w.binding = []any{}
	w.rowCount = 0
	return nil
}

func (w *writer) writeBatchIfNeeded(ctx context.Context, db *sql.DB) error {
	if w.rowCount >= w.batchSize {
		return w.write(ctx, db)
	}
	return nil
}

func (w *writer) writeIfNeeded(ctx context.Context, db *sql.DB) error {
	if w.rowCount >= 0 {
		return w.write(ctx, db)
	}
	return nil

View on GitHub (pinned to 12126d8942)