Hi,
I have an issue with H2 on tables that has self references columns. An
update statement on such table never ends.
I've attached a test case that create a sample database.
Once the database is filled with sample data (150000 rows), open the
database (jdbc:h2:~/testUpdate;MVCC=TRUE) with the console and key in that
request :
UPDATE DOCUMENT SET STATE=4;
It should never end.
Regards,
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/qTuF_BCT5skJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
package com.tessi.tests.h2;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
public class H2Tester {
/**
* @param args
* @throws SQLException
* @throws ClassNotFoundException
*/
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName(org.h2.Driver.class.getName());
Connection cnx = DriverManager.getConnection("jdbc:h2:~/testUpdate;MVCC=TRUE", "root", "r00t");
try {
Statement st = cnx.createStatement();
st.executeUpdate("DROP TABLE IMAGE");
st.executeUpdate("DROP TABLE DOCUMENT");
st.executeUpdate("CREATE TABLE IF NOT EXISTS DOCUMENT (" +
"id int primary key auto_increment," +
"cmc7 varchar(50)," +
"date1 datetime," +
"state int," +
"parentDocumentId int " +
")"
);
st.executeUpdate("CREATE TABLE IF NOT EXISTS IMAGE (" +
"id int primary key auto_increment," +
"name varchar(50)," +
"date1 datetime," +
"parentDocumentId int " +
")"
);
st.executeUpdate("ALTER TABLE IMAGE " +
"ADD FOREIGN KEY(parentDocumentId) " +
"REFERENCES DOCUMENT(ID)");
st.executeUpdate("ALTER TABLE DOCUMENT " +
"ADD FOREIGN KEY(parentDocumentId) " +
"REFERENCES DOCUMENT(ID)");
st.close();
PreparedStatement psDocument = cnx.prepareStatement("INSERT INTO DOCUMENT values (null, ?, ?, ?, ?)");
PreparedStatement psImage = cnx.prepareStatement("INSERT INTO IMAGE values (null, ?, ?, ?)");
Random rnd = new Random();
byte[] buffer = new byte[50];
cnx.setAutoCommit(false);
int rootId=-1;
for(int i=0; i<150000; ++i) {
int pos = 0;
rnd.nextBytes(buffer);
psDocument.setString(++pos, new String(buffer));
psDocument.setDate(++pos, new Date(System.currentTimeMillis()));
psDocument.setInt(++pos, /*(char)(rnd.nextInt(26) + 'A') + ""*/0);
if(i == 0) {
psDocument.setNull(++pos, java.sql.Types.INTEGER);
} else {
psDocument.setInt(++pos, rootId);
}
psDocument.executeUpdate();
ResultSet rs = psDocument.getGeneratedKeys();
Integer id = null;
while(rs.next()) {
id = rs.getInt(1);
}
pos = 0;
if(rootId == -1) rootId = id;
psImage.setString(++pos, new String(buffer));
psImage.setDate(++pos, new Date(System.currentTimeMillis()));
psImage.setInt(++pos, id);
psImage.executeUpdate();
if(i % 100 == 0) {
System.out.println(String.format("Inserted %d documents", i));
if(i % 10000 == 0) {
System.out.println("Commit");
cnx.commit();
}
}
}
psDocument.close();
psImage.close();
cnx.commit();
} finally {
cnx.rollback();
cnx.close();
}
}
}
