add identity an primary key after filling

[Yaroslav Kavenchuk]

In H2 Console:

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT , NAME VARCHAR(255));
INSERT INTO TEST VALUES(1, 'Hello');
INSERT INTO TEST VALUES(2, 'World');
ALTER TABLE TEST ALTER COLUMN ID INT IDENTITY;
ALTER TABLE test ALTER COLUMN ID RESTART WITH 3;
INSERT INTO TEST (name) VALUES('Other World');

result:

ALTER TABLE TEST ALTER COLUMN ID INT IDENTITY;
Timeout trying to lock table SYS [50200-69]
<http://localhost:8182/query.do?jsessionid=b8fc17bf6da1fa71e7adc0ffe9c070d3#>
HYT00/50200
org.h2.jdbc.JdbcSQLException: Timeout trying to lock table SYS [50200-69]
at org.h2.message.Message.getSQLException(Message.java:91
<http://h2database.com/html/source.html?file=org/h2/message/Message.java&line=91&build=69>)

at org.h2.message.Message.getSQLException(Message.java:95
<http://h2database.com/html/source.html?file=org/h2/message/Message.java&line=95&build=69>)

at org.h2.message.Message.getSQLException(Message.java:73
<http://h2database.com/html/source.html?file=org/h2/message/Message.java&line=73&build=69>)

at org.h2.table.TableData.lock(TableData.java:377
<http://h2database.com/html/source.html?file=org/h2/table/TableData.java&line=377&build=69>)

at org.h2.engine.Database.removeMeta(Database.java:698
<http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=698&build=69>)

at org.h2.engine.Database.update(Database.java:1112
<http://h2database.com/html/source.html?file=org/h2/engine/Database.java&line=1112&build=69>)

at org.h2.schema.Sequence.flush(Sequence.java:104
<http://h2database.com/html/source.html?file=org/h2/schema/Sequence.java&line=104&build=69>)

at org.h2.table.Column.updateSequenceIfRequired(Column.java:269
<http://h2database.com/html/source.html?file=org/h2/table/Column.java&line=269&build=69>)

at org.h2.table.Column.validateConvertUpdateSequence(Column.java:251
<http://h2database.com/html/source.html?file=org/h2/table/Column.java&line=251&build=69>)

at org.h2.table.Table.validateConvertUpdateSequence(Table.java:469
<http://h2database.com/html/source.html?file=org/h2/table/Table.java&line=469&build=69>)

at org.h2.command.dml.Insert.update(Insert.java:117
<http://h2database.com/html/source.html?file=org/h2/command/dml/Insert.java&line=117&build=69>)

at
org.h2.command.ddl.AlterTableAlterColumn.execute(AlterTableAlterColumn.java:368
<http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAlterColumn.java&line=368&build=69>)

at
org.h2.command.ddl.AlterTableAlterColumn.copyData(AlterTableAlterColumn.java:316
<http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAlterColumn.java&line=316&build=69>)

at
org.h2.command.ddl.AlterTableAlterColumn.update(AlterTableAlterColumn.java:152
<http://h2database.com/html/source.html?file=org/h2/command/ddl/AlterTableAlterColumn.java&line=152&build=69>)

at org.h2.command.CommandContainer.update(CommandContainer.java:69
<http://h2database.com/html/source.html?file=org/h2/command/CommandContainer.java&line=69&build=69>)

at org.h2.command.Command.executeUpdate(Command.java:197
<http://h2database.com/html/source.html?file=org/h2/command/Command.java&line=197&build=69>)

at org.h2.server.TcpServerThread.process(TcpServerThread.java:248
<http://h2database.com/html/source.html?file=org/h2/server/TcpServerThread.java&line=248&build=69>)

at org.h2.server.TcpServerThread.run(TcpServerThread.java:104
<http://h2database.com/html/source.html?file=org/h2/server/TcpServerThread.java&line=104&build=69>)

at java.lang.Thread.run(Thread.java:619)

at org.h2.engine.SessionRemote.done(SessionRemote.java:319
<http://h2database.com/html/source.html?file=org/h2/engine/SessionRemote.java&line=319&build=69>)

at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:172
<http://h2database.com/html/source.html?file=org/h2/command/CommandRemote.java&line=172&build=69>)

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:154
<http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcStatement.java&line=154&build=69>)

at org.h2.server.web.WebThread.getResult(WebThread.java:1663
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=1663&build=69>)

at org.h2.server.web.WebThread.query(WebThread.java:1211
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=1211&build=69>)

at org.h2.server.web.WebThread.process(WebThread.java:413
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=413&build=69>)

at org.h2.server.web.WebThread.processRequest(WebThread.java:160
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=160&build=69>)

at org.h2.server.web.WebThread.process(WebThread.java:210
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=210&build=69>)

at org.h2.server.web.WebThread.run(WebThread.java:170
<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=170&build=69>)

ALTER TABLE test ALTER COLUMN ID RESTART WITH 3;
Sequence ID not found; SQL statement:
ALTER TABLE test ALTER COLUMN ID RESTART WITH 3 [90036-69]
<http://localhost:8182/query.do?jsessionid=b8fc17bf6da1fa71e7adc0ffe9c070d3#>
90036/90036

How do it?

--
WBR, Yaroslav Kavenchuk.

[Yaroslav Kavenchuk]

Solved.

How get list of current connections to H2 server and kill some from it?

Thanks!

--
WBR, Yaroslav Kavenchuk.

[Thomas Mueller]

Hi,

> list of current connections to H2 server

SELECT * FROM INFORMATION_SCHEMA.SESSIONS

> kill some from it?
http://www.h2database.com/html/functions.html#cancelsession
This will only kill the current statement of a session (the example is
wrong, I will fix it).
Killing a specific session is not possible, but killing all sessions
is by killing the TCP server:
http://www.h2database.com/javadoc/org/h2/tools/Server.html#r13

Regards,
Thomas

[Thomas Mueller]

Hi,

This is a bug of course. Adding a IDENTITY column to a table with data
throws a lock timeout. The only workaround is to alter the table while
it is empty. I will fix this for the next release and add a test case.

Thanks for your help!
Thomas

[Endre Stølsvik]

> org.h2.jdbc.JdbcSQLException: Timeout trying to lock table SYS [50200-69]
> at org.h2.message.Message.getSQLException(Message.java:91
> <http://h2database.com/html/source.html?file=org/h2/message/Message.java&line=91&build=69>)

How did these amazing file URL references come about?!

Endre.

[Thomas Mueller]

Hi,

> > <http://h2database.com/html/source.html?file=org/h2/message/Message.java&line=91&build=69>)
>
> How did these amazing file URL references come about?!

The H2 Console converts stack traces to links to the subversion copy
of the source code (of the correct build). See also:
http://www.h2database.com/html/sourceError.html

Regards,
Thomas