Driver exception:The object is already closed [90007-56]
Volk
I got the following problem "org.h2.jdbc.JdbcSQLException: The object
is already closed [90007-56]" during executing several SQL
Statements :
ALTER TABLE Action ALTER COLUMN Default_Value BOOLEAN NULL;
DELETE FROM Action WHERE Action_ID=51 OR Action_ID=52;
The TRACE records in trace.db file are :
08-15 17:07:20 jdbc[0]:
/**/stat6.executeUpdate("\nDELETE FROM Action_Dependence WHERE
Action_ID=51 OR Action_ID=52 OR Required_Action_ID=51 OR
Required_Action_ID=52");
08-15 17:07:20 jdbc[0]:
/*SQL*/DELETE FROM Action_Dependence WHERE Action_ID=51 OR
Action_ID=52 OR Required_Action_ID=51 OR Required_Action_ID=52;
08-15 17:07:20 lock: 0 xlock ok ACTION_DEPENDENCE
08-15 17:07:20 index: Index PUBLIC.PRIMARY_KEY_3 head consistent=false
08-15 17:07:20 jdbc[0]:
/**/conn0.getWarnings();
08-15 17:07:20 jdbc[0]:
/**/Statement stat7 =
08-15 17:07:20 jdbc[0]:
/**/conn0.createStatement();
08-15 17:07:20 jdbc[0]:
/**/stat7.executeUpdate("\nDELETE FROM Action WHERE Action_ID=51 OR
Action_ID=52");
08-15 17:07:20 jdbc[0]:
/*SQL*/DELETE FROM Action WHERE Action_ID=51 OR Action_ID=52;
08-15 17:07:20 lock: 0 xlock ok ACTION
08-15 17:07:20 command: long query: 125
08-15 17:07:20 jdbc[0]: SQLException
org.h2.jdbc.JdbcSQLException: The object is already closed [90007-56]
at org.h2.message.Message.getSQLException(Message.java:84)
at org.h2.message.Message.getSQLException(Message.java:66)
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.index.BtreeIndex.find(BtreeIndex.java:177)
at
org.h2.constraint.ConstraintReferential.found(ConstraintReferential.java:
286)
at
org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:
344)
at
org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:
361)
at
org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:
234)
at org.h2.table.Table.fireConstraints(Table.java:412)
at org.h2.table.Table.fireAfterRow(Table.java:420)
at org.h2.command.dml.Delete.update(Delete.java:76)
at org.h2.command.CommandContainer.update(CommandContainer.java:64)
at org.h2.command.Command.executeUpdate(Command.java:120)
at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:109)
If I change the order of statements to :
DELETE FROM Action WHERE Action_ID=51 OR Action_ID=52;
ALTER TABLE Action ALTER COLUMN Default_Value BOOLEAN NULL;
the script executes fine.
The problem is that I can't change the order in the production script.
Maybe you have any suggestions what the cause of the problem?
Best regards,
Kirill Volgin.
Thomas Mueller
I can't reproduce it so far, but I think I know the problem: when
altering a table, the database rebuilds it. But in your case, for some
reason, it does not drop and re-create all constraints. Then, when
deleting, it tries to check a constraint that uses an index that is
closed.
Did you alter other tables as well (maybe Action_Dependence)? What are
the constraints of ACTION?
I'm not sure yet what the workaround could be if you can't change the
order in the script, but maybe I have a solution if I fully understand
the problem.
Thomas
Volk
The cause of the problem is my schema :
CREATE TABLE Action (
Action_ID INTEGER NOT NULL,
Action_Code CHAR(20) NOT NULL,
Action_Name CHAR(80) NULL,
IsVisible BOOLEAN NULL,
Default_Value CHAR(1) NULL,
Action_Group_ID INTEGER NULL,
Action_Index INTEGER NULL,
PRIMARY KEY (Action_ID)
);
CREATE TABLE Action_Dependence (
Action_ID INTEGER NOT NULL,
Required_Action_ID INTEGER NOT NULL,
PRIMARY KEY (Action_ID, Required_Action_ID)
);
ALTER TABLE Action_Dependence
ADD FOREIGN KEY (Action_ID)
REFERENCES Action (Action_ID);
ALTER TABLE Action_Dependence
ADD FOREIGN KEY (Required_Action_ID)
REFERENCES Action (Action_ID);
And here it is sample filling :
INSERT INTO Action (ACTION_ID, ACTION_CODE, ACTION_NAME, ISVISIBLE,
DEFAULT_VALUE, ACTION_GROUP_ID) VALUES (1, '1000', 'View
Object', '1', '1', 1);
INSERT INTO Action (ACTION_ID, ACTION_CODE, ACTION_NAME, ISVISIBLE,
DEFAULT_VALUE, ACTION_GROUP_ID) VALUES (2, '1010', 'Add
Object', '1', '0', 1);
INSERT INTO Action (ACTION_ID, ACTION_CODE, ACTION_NAME, ISVISIBLE,
DEFAULT_VALUE, ACTION_GROUP_ID) VALUES (3, '1020', Delete
Object', '1', '0', 1);
INSERT INTO Action_Dependence VALUES (2, 1);
After executing this statements
ALTER TABLE Action ALTER COLUMN Default_Value BOOLEAN NULL;
DELETE FROM Action WHERE Action_ID=3;
I get
ALTER TABLE Action ALTER COLUMN Default_Value BOOLEAN NULL;
Update count: 0
(32 ms)
DELETE FROM Action WHERE Action_ID=3;
The object is already closed [90007-56]
org.h2.jdbc.JdbcSQLException: The object is already closed [90007-56]
at org.h2.message.Message.getSQLException(Message.java:84)
at org.h2.message.Message.getSQLException(Message.java:66)
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.index.BtreeIndex.find(BtreeIndex.java:177)
at
org.h2.constraint.ConstraintReferential.found(ConstraintReferential.java:
286)
at
org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:
344)
at
org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:
361)
at
org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:
234)
at org.h2.table.Table.fireConstraints(Table.java:412)
at org.h2.table.Table.fireAfterRow(Table.java:420)
at org.h2.command.dml.Delete.update(Delete.java:76)
at org.h2.command.CommandContainer.update(CommandContainer.java:
64)
at org.h2.command.Command.executeUpdate(Command.java:120)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:155)
at org.h2.server.web.WebThread.getResult(WebThread.java:1218)
at org.h2.server.web.WebThread.query(WebThread.java:882)
at org.h2.server.web.WebThread.process(WebThread.java:345)
at org.h2.server.web.WebThread.processRequest(WebThread.java:118)
at org.h2.server.web.WebThread.run(WebThread.java:145)
Is it a wrong db schema or bug in H2?
Best regards,
Kirill Volgin.
Volk
On Aug 16, 12:40 pm, Volk <k.vol...@gmail.com> wrote:
> INSERT INTO Action (ACTION_ID, ACTION_CODE, ACTION_NAME, ISVISIBLE,
> DEFAULT_VALUE, ACTION_GROUP_ID) VALUES (3, '1020', Delete
> Object', '1', '0', 1);
I missed quote, right insert is :
INSERT INTO Action (ACTION_ID, ACTION_CODE, ACTION_NAME, ISVISIBLE,
DEFAULT_VALUE, ACTION_GROUP_ID) VALUES (3, '1020', 'Delete
Object', '1', '0', 1);
Kirill
Thomas Mueller
It is definetly a bug in H2. It will be fixed in the next release (in
a week or so). I will also add a test case. Thanks for reporting this
issue!
Thomas