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?
With version 1.3.171 and older: when using local temporary tables and not
dropping them manually before closing the session, and then killing the
process could result in a database that couldn't be opened.
With version 1.3.162 and older: on out of disk space, the database can get
corrupt sometimes, if later write operations succeed. The same problem
happens on other kinds of I/O exceptions (where one or some of the writes
fail, but subsequent writes succeed). Now the file is closed on the first
unsuccessful write operation, so that later requests fail consistently.
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:
- What is your database URL?
- Did you use LOG=0 or LOG=1? Did you read the FAQ about it?
- Did the system ever run out of disk space?
- Could you send the full stack trace of the exception including message
text?
- Did you use SHUTDOWN DEFRAG or the database setting DEFRAG_ALWAYS with H2
version 1.3.159 or older?
- How many connections does your application use concurrently?
- Do you use temporary tables?
- 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
On Sat, Mar 30, 2013 at 5:00 PM, TrendTimer.com <stephengow...@gmail.com>wrote:
> I recently switched from Derby to H2. Amazing speed increase! However, I
> have some concerns about the stability of this database.
>
> This works fine:
> SELECT * FROM TRE_XIGNITE where localsymbol in ('TQQQ') and
> LOCALDATE2>'2010-12-24'
>
> And this works fine:
> SELECT * FROM TRE_XIGNITE where localsymbol in (''SSO') and
> LOCALDATE2>'2010-12-24'
>
> But the following gives the exception below. Any thoughts? It will be a
> nightmare if lots of users of my application have corrupted databases like
> this. Thanks!
>
> SELECT * FROM TRE_XIGNITE where localsymbol in ('TQQQ','SSO') and
> LOCALDATE2>'2010-12-24'
>
> File corrupted while reading record: "page[44172] data leaf table:14
> TRE_XIGNITE entries:12 parent:44161 keys:[440345, 440346, 440347, 440348,
> 440349, 440350, 440351, 440352, 440353, 440354, 440355, 440356]
> offsets:[1939, 1836, 1739, 1630, 1522, 1421, 1318, 1213, 1105, 998, 890,
> 781]". Possible solution: use the recovery tool; SQL statement:
> SELECT * FROM TRE_XIGNITE where localsymbol in ('TQQQ','SSO') and
> LOCALDATE2>'2010-12-24'
> [90030-171]<http://192.168.1.9:8082/query.do?jsessionid=02dc34894bba4fa5218509497147dffb#>
> 90030/90030
> (Help)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90030>
> org.h2.jdbc.JdbcSQLException: File corrupted while reading record:
> "page[44172] data leaf table:14 TRE_XIGNITE entries:12 parent:44161
> keys:[440345, 440346, 440347, 440348, 440349, 440350, 440351, 440352,
> 440353, 440354, 440355, 440356] offsets:[1939, 1836, 1739, 1630, 1522,
> 1421, 1318, 1213, 1105, 998, 890, 781]". Possible solution: use the
> recovery tool; SQL statement:
> SELECT * FROM TRE_XIGNITE where localsymbol in ('TQQQ','SSO') and
> LOCALDATE2>'2010-12-24' [90030-171]
> at
> org.h2.message.DbException.getJdbcSQLException(DbException.java:329<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=329&build=171>
> )
> at
> org.h2.message.DbException.get(DbException.java:169<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=169&build=171>
> )
> at
> org.h2.message.DbException.get(DbException.java:146<http://h2database.com/html/source.html?file=org/h2/message/DbException.java&line=146&build=171>
> )
> at
> org.h2.index.PageBtreeIndex.getPage(PageBtreeIndex.java:161<http://h2database.com/html/source.html?file=org/h2/index/PageBtreeIndex.java&line=161&build=171>
> )
> at
> org.h2.index.PageBtreeNode.nextPage(PageBtreeNode.java:495<http://h2database.com/html/source.html?file=org/h2/index/PageBtreeNode.java&line=495&build=171>
> )
> at
> org.h2.index.PageBtreeLeaf.nextPage(PageBtreeLeaf.java:323<http://h2database.com/html/source.html?file=org/h2/index/PageBtreeLeaf.java&line=323&build=171>
> )
> at
> org.h2.index.PageBtreeCursor.next(PageBtreeCursor.java:59<http://h2database.com/html/source.html?file=org/h2/index/PageBtreeCursor.java&line=59&build=171>
> )
> at
> org.h2.index.IndexCursor.next(IndexCursor.java:238<http://h2database.com/html/source.html?file=org/h2/index/IndexCursor.java&line=238&build=171>
> )
> at
> org.h2.table.TableFilter.next(TableFilter.java:353<http://h2database.com/html/source.html?file=org/h2/table/TableFilter.java&line=353&build=171>
> )
> at
> org.h2.command.dml.Select.queryFlat(Select.java:517<http://h2database.com/html/source.html?file=org/h2/command/dml/Select.java&line=517&build=171>
> )
> at
> org.h2.command.dml.Select.queryWithoutCache(Select.java:622<http://h2database.com/html/source.html?file=org/h2/command/dml/Select.java&line=622&build=171>
> )
> at
> org.h2.command.dml.Query.query(Query.java:311<http://h2database.com/html/source.html?file=org/h2/command/dml/Query.java&line=311&build=171>
> )
> at
> org.h2.command.dml.Query.query(Query.java:281<http://h2database.com/html/source.html?file=org/h2/command/dml/Query.java&line=281&build=171>
> )
> at
> org.h2.command.dml.Query.query(Query.java:36<http://h2database.com/html/source.html?file=org/h2/command/dml/Query.java&line=36&build=171>
> )
> at
> org.h2.command.CommandContainer.query(CommandContainer.java:86<http://h2database.com/html/source.html?file=org/h2/command/CommandContainer.java&line=86&build=171>
> )
> at
> org.h2.command.Command.executeQuery(Command.java:191<http://h2database.com/html/source.html?file=org/h2/command/Command.java&line=191&build=171>
> )
> at
> org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:173<http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcStatement.java&line=173&build=171>
> )
> at
> org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152<http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcStatement.java&line=152&build=171>
> )
> at
> org.h2.server.web.WebApp.getResult(WebApp.java:1312<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1312&build=171>
> )
> at
> org.h2.server.web.WebApp.query(WebApp.java:1002<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=1002&build=171>
> )
> at
> org.h2.server.web.WebApp$1.next(WebApp.java:965<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=965&build=171>
> )
> at
> org.h2.server.web.WebApp$1.next(WebApp.java:954<http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.java&line=954&build=171>
> )
> at
> org.h2.server.web.WebThread.process(WebThread.java:166<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=166&build=171>
> )
> at
> org.h2.server.web.WebThread.run(WebThread.java:93<http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.java&line=93&build=171>
> )
> at java.lang.Thread.run(Thread.java:722)
>
> --
> 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
> email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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 email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
