{"record":{"id":"f74bafec69b285e8","repo":"hibernate/hibernate-orm","slug":"unexpected-row-count-the-expected-row-count-for-a","errorCode":null,"errorMessage":"Unexpected row count (the expected row count for an ON DUPLICATE KEY UPDATE statement should be either 0, 1 or 2 ) [{}]","messagePattern":"Unexpected row count \\(the expected row count for an ON DUPLICATE KEY UPDATE statement should be either 0, 1 or 2 \\) \\[(.+?)\\]","errorType":"exception","errorClass":"StaleStateException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SqlAstTranslatorWithOnDuplicateKeyUpdate.java","lineNumber":59,"sourceCode":"\t\t\t\toptionalTableUpdate.getMutationTarget(),\n\t\t\t\tgetSql(),\n\t\t\t\tnew MySQLRowCountExpectation(),\n\t\t\t\tgetParameterBinders()\n\t\t);\n\n\t\treturn new DeleteOrUpsertOperation(\n\t\t\t\toptionalTableUpdate.getMutationTarget(),\n\t\t\t\toptionalTableUpdate.getMutatingTable().getTableMapping(),\n\t\t\t\tupsertOperation,\n\t\t\t\toptionalTableUpdate\n\t\t);\n\t}\n\n\tprivate static class MySQLRowCountExpectation implements Expectation {\n\t\t@Override\n\t\tpublic final void verifyOutcome(int rowCount, PreparedStatement statement, int batchPosition, String sql) {\n\t\t\tif ( rowCount > 2 ) {\n\t\t\t\tthrow new StaleStateException(\n\t\t\t\t\t\t\"Unexpected row count\"\n\t\t\t\t\t\t+ \" (the expected row count for an ON DUPLICATE KEY UPDATE statement should be either 0, 1 or 2 )\"\n\t\t\t\t\t\t+ \" [\" + sql + \"]\"\n\t\t\t\t);\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderUpsertStatement(OptionalTableUpdate optionalTableUpdate) {\n\t\trenderInsertInto( optionalTableUpdate );\n\t\tappendSql( \" \" );\n\t\trenderOnDuplicateKeyUpdate( optionalTableUpdate );\n\t}\n\n\tprotected void renderInsertInto(OptionalTableUpdate optionalTableUpdate) {\n\t\tif ( optionalTableUpdate.getValueBindings().isEmpty() ) {\n\t\t\tappendSql( \"insert ignore into \" );","sourceCodeStart":41,"sourceCodeEnd":77,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SqlAstTranslatorWithOnDuplicateKeyUpdate.java#L41-L77","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before: secondary table has an extra unique key -> ODKU can affect >2 rows\n@Entity @Table(name = \"user_detail\")\n@UniqueConstraint(name = \"uk_user_detail_email\", columnNames = \"email\"); // remove this\n\n// after: rely on the primary key only\n@Entity @Table(name = \"user_detail\"); // PK on user_id is the sole conflict target","handlingStrategy":"try-catch","validationCode":"// Verify the upsert target has exactly one unique index (the PK) before relying on ODKU\nstatic boolean singleConflictTarget(EntityManager em, String table) {\n    Long cnt = (Long) em.createNativeQuery(\"\"\"\n        select count(*) from information_schema.statistics\n        where table_schema = database() and table_name = :t\n          and non_unique = 0 group by table_name\"\"\")\n        .setParameter(\"t\", table).getSingleResult();\n    return cnt != null && cnt <= 1;\n}","typeGuard":null,"tryCatchPattern":"try {\n    em.flush();\n} catch (StaleStateException e) {\n    if (e.getMessage().startsWith(\"Unexpected row count\")\n        && e.getMessage().contains(\"ON DUPLICATE KEY UPDATE\")) {\n        // >2 affected rows: multiple unique keys or triggers -- reconcile and retry\n        em.clear();\n        reconcileUpsertTarget(entity); // re-select, then explicit update/insert\n        return;\n    }\n    throw e;\n}","preventionTips":["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"],"tags":["hibernate","mysql","mariadb","upsert","on-duplicate-key-update","stale-state","flush"],"backgroundTag":"on-duplicate-key-update-row-count","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}