Select with bigint type field restriction

[stolyarchukav]

Hi,
I have a problem with select data.

Select:
SELECT ID, GUID FROM CG_BARCODE where guid = 941088;

Result:
ID GUID
21801 941068

As you see, GUID in select different with GUID in result

Type of GUID filed is BIGINT(19) NOT NULL

Thanks

[stolyarchukav]

Using recovery tools doesn't take any effect.
Column GUID isn't PK. I have index on this column. After droping this
index, my select work correctly, but i cannot create index on field
GUID again, because h2 throws error:
General error: "java.lang.RuntimeException: rowcount remaining=79
CG_BARCODE"; SQL statement:
CREATE INDEX INDEX_BARCODE_GUID ON CG_BARCODE (GUID) [50000-141]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convert(DbException.java:279)
at org.h2.command.Command.executeUpdate(Command.java:219)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:
176)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.server.web.WebApp.getResult(WebApp.java:1316)
at org.h2.server.web.WebApp.query(WebApp.java:1004)
at org.h2.server.web.WebApp$1.next(WebApp.java:967)
at org.h2.server.web.WebApp$1.next(WebApp.java:970)
at org.h2.server.web.WebThread.process(WebThread.java:161)
at org.h2.server.web.WebThread.run(WebThread.java:88)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: rowcount remaining=79
CG_BARCODE
at org.h2.message.DbException.throwInternalError(DbException.java:
226)
at org.h2.table.RegularTable.addIndex(RegularTable.java:243)
at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:89)
at org.h2.command.CommandContainer.update(CommandContainer.java:
70)
at org.h2.command.Command.executeUpdate(Command.java:199)
... 9 more

[Thomas Mueller]

Hi,

This looks like a corrupt database. To recover the data, use the tool
org.h2.tools.Recover to create the SQL script file, and then re-create
the database using this script. Does it work when you do this?

Important corruption problems were fixed in version 1.2.135 and
version 1.2.140 (see the change log). Known causes for corrupt
databases are: if the database was created or used with a version
older than 1.2.135, and the process was killed while the database was
closing or writing a checkpoint. Using the transaction isolation level
READ_UNCOMMITTED (LOCK_MODE 0) while at the same time using multiple
connections. Disabling database file protection using (setting
FILE_LOCK to NO in the database URL). Some other areas that are not
fully tested are: Platforms other than Windows XP, Linux, Mac OS X, or
JVMs other than Sun 1.5 or 1.6; the feature MULTI_THREADED; the
features AUTO_SERVER and AUTO_RECONNECT; the file locking method
'Serialized'.

I am very interested in analyzing and solving this problem. Corruption
problems have top priority for me. I have a few questions:

- Could you send the full stack trace of the exception including message text?
- What is your database URL?
- How many connections does your application use concurrently?
- Do you use temporary tables?
- Did you use LOG=0 or LOG=1?
- With which version of H2 was this database created?
You can find it out using:
select * from information_schema.settings where name='CREATE_BUILD'
or have a look in the SQL script created by the recover tool.
- Did the application run out of memory (once, or multiple times)?
- Do you use any settings or special features (for example cache settings,
two phase commit, linked tables)?
- Do you use any H2-specific system properties?
- Is the application multi-threaded?
- What operating system, file system, and virtual machine
(java -version) do you use?
- How did you start the Java process (java -Xmx... and so on)?
- Is it (or was it at some point) a networked file system?
- How big is the database (file sizes)?
- How much heap memory does the Java process have?
- Is the database usually closed normally, or is process terminated
forcefully or the computer switched off?
- Is it possible to reproduce this problem using a fresh database
(sometimes, or always)?
- Are there any other exceptions (maybe in the .trace.db file)?
Could you send them please?
- Do you still have any .trace.db files, and if yes could you send them?
- Could you send the .h2.db file where this exception occurs?

Regards,
Thomas