[h2] Data Inconsistencies between H2 DB and the generated SQL

[Anerudh Balaji]

Hey all,

While using Hibernate w/ H2, we ran into this weird case today where the H2
primary Id for an entity and the generated SQL Id for the same entity had a
mismatch. Been struggling with this one for a while and any help would be
appreciated !


Here's the Java class that we have (Cut out irrelevant portions of the
code) :

////////////////////////////////////////////////////////////////////////////////////////////////

@Entity(name="some_entity")
@Table(name = "table_name")
@EqualsAndHashCode

public class ProblemClass implements Serializable {


private long identifier;


private ProblemClass parent;


@Override
@Id
@GeneratedValue
@Column(name = "identifier")
public long getIdentifier() {
return id;
}

public void setIdentifier(long identifier) {
this.id = id;


}

@Nullable
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "parent", referencedColumnName = "identifier")
public ProblemClass getParent() {
return parent;
}

public void setParent(@Nullable ProblemClass parent) {
this.parent = parent;
}


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Here's the H2 creation code that we have :

CREATE TABLE table_name (
`identifier` bigint(10) NOT NULL auto_increment,

`parent_id` bigint(10) DEFAULT NULL,


.......

}


///////////



The data generation used to work absolutely fine till today when the
generated sql and the SQL when I use the H2 to inspect didn't match the
parentId that was being referred to.

For example, In the SQL file we found this statement : INSERT INTO
PUBLIC.TABLE_NAME(identifier, parent_id) VALUES
(1, 1)
(2, 1)
(3, 6)
...

But in the H2 database, we found this (when I do select * from TABLE_NAME)

(1, 1)
(2, 1)
(3, 2)


Eventually, this led to a *org.hibernate.ObjectNotFoundException: No row
with the given identifier exists* issue while retrieving a row, whose Id
number doesn't exist in the DB.



*Questions* :

1) Am I doing something incorrect/invalid ?

2) How could the generated SQL (using Hibernate's SessionImpl) and the H2
DB values be different ? (Previously, it used to be the exact same -
whatever was observed on the generated SQL files was what got inserted into
the H2 db)

3) Are there any known issues with the auto_increment feature on H2 ?


Thanks a ton!

Cheers,
Anerudh

[Kerry Sainsbury]

The column with the 'wrong' value is not an auto-increment column, so
auto-increment isn't likely to be the problem.
Most likely explanation is that something else has changed parent_id from 6
to 2, after the original insert.

Post by Anerudh Balaji
Hey all,
While using Hibernate w/ H2, we ran into this weird case today where the
H2 primary Id for an entity and the generated SQL Id for the same entity
had a mismatch. Been struggling with this one for a while and any help
would be appreciated !
Here's the Java class that we have (Cut out irrelevant portions of the
////////////////////////////////////////////////////////////
////////////////////////////////////
@Entity(name="some_entity")
@Table(name = "table_name")
@EqualsAndHashCode
public class ProblemClass implements Serializable {
private long identifier;
private ProblemClass parent;
@Override
@Id
@GeneratedValue
@Column(name = "identifier")
public long getIdentifier() {
return id;
}
public void setIdentifier(long identifier) {
this.id = id;
}
@Nullable
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "parent", referencedColumnName = "identifier")
public ProblemClass getParent() {
return parent;
}
this.parent = parent;
}
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////
CREATE TABLE table_name (
`identifier` bigint(10) NOT NULL auto_increment,
`parent_id` bigint(10) DEFAULT NULL,
.......
}
///////////
The data generation used to work absolutely fine till today when the
generated sql and the SQL when I use the H2 to inspect didn't match the
parentId that was being referred to.
For example, In the SQL file we found this statement : INSERT INTO
PUBLIC.TABLE_NAME(identifier, parent_id) VALUES
(1, 1)
(2, 1)
(3, 6)
...
But in the H2 database, we found this (when I do select * from TABLE_NAME)
(1, 1)
(2, 1)
(3, 2)
Eventually, this led to a *org.hibernate.ObjectNotFoundException: No row
with the given identifier exists* issue while retrieving a row, whose Id
number doesn't exist in the DB.
1) Am I doing something incorrect/invalid ?
2) How could the generated SQL (using Hibernate's SessionImpl) and the H2
DB values be different ? (Previously, it used to be the exact same -
whatever was observed on the generated SQL files was what got inserted into
the H2 db)
3) Are there any known issues with the auto_increment feature on H2 ?
Thanks a ton!
Cheers,
Anerudh
--
You received this message because you are subscribed to the Google Groups
"H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.