hibernate/hibernate-orm · error · StaleStateException

Unexpected row count (the expected row count for an ON DUPLI

Error message

Unexpected row count (the expected row count for an ON DUPLICATE KEY UPDATE statement should be either 0, 1 or 2 ) [{}]

What it means

For dialects whose translators extend SqlAstTranslatorWithOnDuplicateKeyUpdate (MySQL, MariaDB), Hibernate implements optional-table upserts with INSERT ... ON DUPLICATE KEY UPDATE. MySQL's affected-rows contract for such a statement is 0 (existing row updated to same values), 1 (row inserted) or 2 (existing row updated); MySQLRowCountExpectation.verifyOutcome enforces exactly that and throws StaleStateException('Unexpected row count ...') when the JDBC driver reports more than 2 affected rows. A larger count means one statement touched several rows -- typically several unique indexes conflicted at once, or triggers inflated the count -- which Hibernate reports like a lost optimistic-lock race.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SqlAstTranslatorWithOnDuplicateKeyUpdate.java:59

				optionalTableUpdate.getMutationTarget(),
				getSql(),
				new MySQLRowCountExpectation(),
				getParameterBinders()
		);

		return new DeleteOrUpsertOperation(
				optionalTableUpdate.getMutationTarget(),
				optionalTableUpdate.getMutatingTable().getTableMapping(),
				upsertOperation,
				optionalTableUpdate
		);
	}

	private static class MySQLRowCountExpectation implements Expectation {
		@Override
		public final void verifyOutcome(int rowCount, PreparedStatement statement, int batchPosition, String sql) {
			if ( rowCount > 2 ) {
				throw new StaleStateException(
						"Unexpected row count"
						+ " (the expected row count for an ON DUPLICATE KEY UPDATE statement should be either 0, 1 or 2 )"
						+ " [" + sql + "]"
				);
			}
		}
	}

	@Override
	protected void renderUpsertStatement(OptionalTableUpdate optionalTableUpdate) {
		renderInsertInto( optionalTableUpdate );
		appendSql( " " );
		renderOnDuplicateKeyUpdate( optionalTableUpdate );
	}

	protected void renderInsertInto(OptionalTableUpdate optionalTableUpdate) {
		if ( optionalTableUpdate.getValueBindings().isEmpty() ) {
			appendSql( "insert ignore into " );

View on GitHub (pinned to fad1729dce)

Solutions

Inspect the target table with SHOW CREATE TABLE and drop every unique index except the primary key so at most one row can conflict per ODKU
Remove or adjust triggers on the upsert target table that alter affected rows
If extra unique constraints must stay, bypass ODKU for that entity: pre-select then insert/update, or catch SQLIntegrityConstraintViolationException on plain insert
Catch StaleStateException around flush/commit, refresh the entity, and re-apply the change at business level

Example fix

// before: secondary table has an extra unique key -> ODKU can affect >2 rows
@Entity @Table(name = "user_detail")
@UniqueConstraint(name = "uk_user_detail_email", columnNames = "email"); // remove this

// after: rely on the primary key only
@Entity @Table(name = "user_detail"); // PK on user_id is the sole conflict target

Defensive patterns

Strategy: try-catch

Validate before calling

// Verify the upsert target has exactly one unique index (the PK) before relying on ODKU
static boolean singleConflictTarget(EntityManager em, String table) {
    Long cnt = (Long) em.createNativeQuery("""
        select count(*) from information_schema.statistics
        where table_schema = database() and table_name = :t
          and non_unique = 0 group by table_name""")
        .setParameter("t", table).getSingleResult();
    return cnt != null && cnt <= 1;
}

Try / catch

try {
    em.flush();
} catch (StaleStateException e) {
    if (e.getMessage().startsWith("Unexpected row count")
        && e.getMessage().contains("ON DUPLICATE KEY UPDATE")) {
        // >2 affected rows: multiple unique keys or triggers -- reconcile and retry
        em.clear();
        reconcileUpsertTarget(entity); // re-select, then explicit update/insert
        return;
    }
    throw e;
}

Prevention

Keep exactly one unique index (the PK) on tables Hibernate upserts via ON DUPLICATE KEY UPDATE
Avoid triggers on those tables; they distort affected-row counts
Add a schema-consistency test that fails when a new unique constraint is added to an upsert target

When it happens

Trigger: Flushing an entity whose optional/secondary table row is upserted via ODKU while the target table has more than one unique key and the new values conflict with multiple existing rows simultaneously (MySQL then reports >2 affected rows); BEFORE/AFTER INSERT-UPDATE triggers on the table changing the count; middleware (ProxySQL, Galera, sharding proxies) altering affected-rows semantics. verifyOutcome runs during flush/statement execution, so the StaleStateException surfaces from EntityManager.flush()/commit.

Common situations: Secondary/optional tables carrying an extra unique business-key index besides the primary key; schema migrations that added unique constraints to a table Hibernate upserts; MySQL 8 replication/proxy layers returning modified counts.

Related errors

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/f74bafec69b285e8. Report an issue: GitHub.