Re: [h2] Out of memory when Ordering results für very large table
Hi,
I didn't run your test case, but just to let you know: ResultDiskTape will
not be used in future versions of H2. Instead, temporary tables are used.
Regards,
Thomas
On Monday, June 2, 2014, wrote:
> Hi,
>
> I have already analyzed a heap dump with the software JProfiler.
>
> Please have a look at the function nextSorted() of class ResultDiskBuffer:
>
> *private* Value[] nextSorted() {
> *int* next = -1;
> *for* (*int* i = 0, size = tapes.size(); i < size; i++) {
> ResultDiskTape tape = tapes.get(i);
> *if* (tape.buffer.size() == 0 && tape.pos < tape.end) {
> file.seek(tape.pos);
> *for* (*int* j = 0; tape.pos < tape.end && j <
> *READ_AHEAD*; j++) {
> readRow(tape);
> }
> }
> *if* (tape.buffer.size() > 0) {
> *if* (next == -1) {
> next = i;
> } *else* *if* (compareTapes(tape, tapes.get(next)) < 0) {
> next = i;
> }
> }
> }
> ResultDiskTape t = tapes.get(next);
> Value[] row = t.buffer.get(0);
> t.buffer.remove(0);
> *return* row;
> }
>
> If I understood this correctly, then readRow() reads a row from the DB
> file into memory (ResultDiskTape.buffer).
> The for loop in the method above is not left until the next row (according
> to sort criteria) is found.
> So if finding takes many iterations, then many rows are held in memory -
> no matter which value is set for MAX_MEMORY_ROWS.
>
> Additionally, if I omit sorting (nextUnsorted() is called then), then
> fetching does NOT lead to out of memory errors.
>
> I wrote a simple java program for reproduction (creates a large table, and
> reads sorted/unsorted -- please run with -Xmx512m):
>
>
> *import* java.sql.Connection;
> *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* *H2OomFetch* {
> /**
> * available synbols
> */
> *private* *static* *char*[] *symbols*;
> /**
> * Random number generator
> */
> *private* *final* Random random = *new* Random();
>
> *static* {
> StringBuilder tmp = *new* StringBuilder();
> *for* (*char* ch = '0'; ch <= '9'; ++ch) {
> tmp.append(ch);
> }
> *for* (*char* ch = 'a'; ch <= 'z'; ++ch) {
> tmp.append(ch);
> }
> *symbols* = tmp.toString().toCharArray();
> }
>
> /**
> * *@param* args Unused
> */
> *public* *static* *void* main(String[] args){
> *try* {
> *new* H2OomFetch().run(5, 30, *true*); // ordered, leads
> to out of memory
> // new H2OomFetch().run(5, 30, false); // unordered, works
> } *catch* (Throwable t) {
> t.printStackTrace();
> }
> }
>
> /**
> * Runs the test
> * *@param* rows Number of rows
> * *@param* cols Number of cols
> * *@param* order true to order results
> * *@throws* *SQLException*
> */
> *private* *void* run(*int* rows, *int* cols, *boolean* order) *throws*
> SQLException {
> Connection connection = connect();
>
> Statement statement = connection.createStatement();
> statement.execute("set MAX_MEMORY_ROWS 10");
> *try* {
> statement.execute("drop table ordertest");
> } *catch* (SQLException ex) {
> // failed to drop, ignore
> }
> statement.execute(getCreateStatement(cols));
> statement.close();
>
> PreparedStatement insertStatement =
> connection.prepareStatement(getInsertStatement(cols));
> *for* (*int* row = 1;row <= rows; row++) {
> insertStatement.setInt(1, row);
> *for* (*int* i = 0; i < cols; i++) {
> insertStatement.setString(2 + i, getRandomString1K());
> }
> insertStatement.addBatch();
> *if* ((row % 20 == 0) || (row == rows)) {
> insertStatement.executeBatch();
> }
>
> *if* (row % 1000 == 0) {
> System.*out*.println("inserted " + row);
> }
> }
> connection.commit();
> statement = connection.createStatement();
> ResultSet rs = statement.executeQuery("select * from ordertest" +
> (order ? " order by ID DESC" : ""));
>
> *int* rowIdx = 0;
> *while* (rs.next()) {
> rowIdx++;
> *if* (rowIdx % 1000 == 0) {
> System.*out*.println("fetched " + rowIdx);
> }
> }
> }
>
> /**
> * *@return* Random string 1000 chars
> */
> *private* String getRandomString1K() {
> *char* [] buf = *new* *char*[1000];
> *for* (*int* idx = 0; idx < buf.len
Re: [h2] Query processing in H2 DB
http://h2database.com/html/architecture.html On 2014-06-03 10:23 PM, krismat.des...@gmail.com wrote: Yes, but where could I begin the analysis? The question is, how do the h2 database processed a sql query? The individual steps of the query processing, parsing and validating for example. -- 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. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Query processing in H2 DB
Yes, but where could I begin the analysis? The question is, how do the h2 database processed a sql query? The individual steps of the query processing, parsing and validating for example. Am Dienstag, 3. Juni 2014 13:24:13 UTC+2 schrieb Noel Grandin: > > It's in lots of files. You'll have to be more specific. > > On 2014-06-03 12:55 PM, krismat...@gmail.com wrote: > > > > I have a question about the H2 Database. Where can I analyzing the code > about query processing and optimization? > > In which data file is it? > -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] understanding preparestatement reuse
Hi, I frequently use PreparedStatements, but I am a bit unsure how to properly "reuse" them. I create a statement String sql = "select * from users where score>?"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, n); ResultSet rs = ps.executeQuery(); // do stuff rs.close(); ps.close(); con.close(); Now lets say that I want to later reuse that prepared statement, which is more accurate: 1) do I need to keep a reference to the actual PreparedStatment object (in this case "ps"). If this is the case, do I not close the prepared statement? 2) can I "reuse" the preapred statment by just sending the same sql String when creating a PresparedStatement from a connection. In other words, does the programmer need to keep around references of prepared statement objects, or does the database (in this case H2) keep a cache based on the parametrized sql String that is sent to the connection. Thanks for any help. -Adam -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] web console Servlet - how to not allow others to create new databases
Hi, We use embedded H2 in a webapp with tomcat. We use the servlet webconsole to manage the database. Our database is password protected, which should allow some security. But I don't see how to prevent an unauthorized user from creating a new database and filling it with junk if they visit the webconsole url. Our current method to prevent this is to simply obscure the web-console url-pattern to something that is hard to guess or know - thus trying to prevent someone from stumbling upon it. Though I do not think this is a full solid security method. Any ideas or tips on how to 1) secure the web-console servlet 2) prevent others from creating new databases if they find the url. Thanks, -Adam -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Recursive CTE don't work when recursion predicate uses a bind variable
In fact, there seems to be a second issue related to bind variables and
recursive CTE. Consider the following alternative program:
>
>
Connection con = getConnection();
System.out.println("Wrong result:");
PreparedStatement stmt = con.prepareStatement(
"WITH recursive t(f) AS ( "+
"SELECT ? "+
"UNION ALL"+
"SELECT t.f + 1 "+
"FROM t "+
"WHERE t.f < 10"+
")"+
"SELECT t.f "+
"FROM t "
);
stmt.setInt(1, 1);
ResultSet rs = stmt.executeQuery();
while (rs.next())
System.out.println(rs.getString(1));
System.out.println("Correct result:");
rs = con.createStatement().executeQuery(
"WITH recursive t(f) AS ( "+
"SELECT 1 "+
"UNION ALL"+
"SELECT t.f + 1 "+
"FROM t "+
"WHERE t.f < 10 "+
")"+
"SELECT t.f "+
"FROM t "
);
while (rs.next())
System.out.println(rs.getString(1));
The output is now:
Wrong result:
*null*
Correct result:
1
2
3
4
5
6
7
8
9
--
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.
For more options, visit https://groups.google.com/d/optout.
[h2] Recursive CTE don't work when recursion predicate uses a bind variable
Java program to reproduce this:
Connection con = getConnection();
System.out.println("Wrong result:");
PreparedStatement stmt = con.prepareStatement(
"WITH recursive t(f) AS ( "+
"SELECT 1 "+
"UNION ALL"+
"SELECT t.f + 1 "+
"FROM t "+
"WHERE t.f < ?"+
")"+
"SELECT t.f "+
"FROM t "
);
stmt.setInt(1, 10);
ResultSet rs = stmt.executeQuery();
while (rs.next())
System.out.println(rs.getInt(1));
System.out.println("Correct result:");
rs = con.createStatement().executeQuery(
"WITH recursive t(f) AS ( "+
"SELECT 1 "+
"UNION ALL"+
"SELECT t.f + 1 "+
"FROM t "+
"WHERE t.f < 10 "+
")"+
"SELECT t.f "+
"FROM t "
);
while (rs.next())
System.out.println(rs.getInt(1));
The produced output is:
Wrong result:
1
Correct result:
1
2
3
4
5
6
7
8
9
10
--
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.
For more options, visit https://groups.google.com/d/optout.
[h2] State of the recursive CTE feature
Hello,
We're currently integrating support for CTE in jOOQ and we're wondering if
we should support CTE for H2 at all. H2 has experimental CTE support, if
I'm not mistaken:
http://www.h2database.com/html/advanced.html?highlight=recursive&search=recursive#recursive_queries
This would be one of our test cases, and it seems to work for H2:
with recursive "t1"("f1", "f2") as (
select
1,
'a'
from dual
union all
select
("t1"."f1" + 1),
("t1"."f2" || 'a')
from "t1"
where "t1"."f1" < 10
)
select
"t1"."f1",
"t1"."f2"
from "t1"
However, in standard SQL, I can declare several tables in the WITH clause,
e.g.:
with recursive "t1"("f1", "f2") as (...),
"t2"("g1", "g2") as (...),
...
select ...
This doesn't seem to work right now for H2.
Am I right in thinking that:
1. Only RECURSIVE CTE are currently supported, although I can tweak a
synthetic UNION ALL clause into the query to make H2 believe that we have
the required syntax (see below)
2. The RECURSIVE keyword seems to be optional - probably to be Oracle
compatible as in Oracle, recursiveness is implicit
3. Only single-table CTE are currently supported
4. This is currently still not a priority for the H2 maintenance team? (as
this question occasionally pops up on the user-group) :-)
[From 1] Non-recursive tweak to comply with H2 syntax requirements:
with "t1"("f1", "f2") as (
select
1,
'a'
from dual
union all
select null, null
where false
)
select
"t1"."f1",
"t1"."f2"
from "t1"
Any feedback is very welcome.
Cheers
Lukas
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] Query processing in H2 DB
It's in lots of files. You'll have to be more specific. On 2014-06-03 12:55 PM, krismat.des...@gmail.com wrote: I have a question about the H2 Database. Where can I analyzing the code about query processing and optimization? In which data file is it? -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Query processing in H2 DB
Hi, I am not sure whether I understand the question but here are few tips to analyse H2 performance http://zvikico.typepad.com/problog/2008/04/h2-performance.html Tono On Tuesday, June 3, 2014 12:55:58 PM UTC+2, krismat...@gmail.com wrote: > > Hello, > > I have a question about the H2 Database. Where can I analyzing the code > about query processing and optimization? > In which data file is it? > > Thanks for each help :-) > -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] Query processing in H2 DB
Hello, I have a question about the H2 Database. Where can I analyzing the code about query processing and optimization? In which data file is it? Thanks for each help :-) -- 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. For more options, visit https://groups.google.com/d/optout.
[h2] ARRAY_AGG() support
Hello,
I just wanted to create an example for a blog post and noticed that there
is (probably?) no way to aggregate data into an array, the way PostgreSQL
allows it through ARRAY_AGG():
http://www.postgresql.org/docs/9.3/interactive/functions-aggregate.html
An example in PostgreSQL:
select
t.table_schema,
t.table_name,
array_agg(c.column_name::varchar order by c.ordinal_position)
from information_schema.tables t
join information_schema.columns c
on (t.table_schema, t.table_name)
= (c.table_schema, c.table_name)
group by t.table_schema, t.table_name
Sample output:
"information_schema";"enabled_roles";"{role_name}"
"information_schema";"foreign_data_wrapper_options";"{foreign_data_wrapper_catalog,foreign_data_wrapper_name,option_name,option_value}"
"information_schema";"foreign_data_wrappers";"{foreign_data_wrapper_catalog,foreign_data_wrapper_name,authorization_identifier,library_name,foreign_data_wrapper_language}"
"information_schema";"foreign_server_options";"{foreign_server_catalog,foreign_server_name,option_name,option_value}"
Do you think this would be a useful addition to the roadmap?
Cheers
Lukas
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] Exception when opening DB after forced shutdown (1.3.176)
Hello, I can test the fix for you easily, all I need is h2 jar with fix. Regarding the test I tried all I could think of. Our app is fairly large (15MB jar of classes with lots of framework-ish stuff happening in the background) so it is difficult to point out the scenario. Tried INFO logging to re-execute the same set of queries but that has not replicated the problem. I am also pretty sure we do not create any temporary tables explicitly (also searched code-base for "create memory table"). Can you point me in some direction as what to look for? I mean what would you expect to be happening to create the table? Does H2 create temp tables implicitly? All we do to trigger the problem are select queries (based on the info log I had). thanks Tono On Monday, June 2, 2014 6:04:03 PM UTC+2, Thomas Mueller wrote: > > Hi, > > I could analyze it now. The problem seems to be that creating a temporary > table is not committed, and then the temporary table is deleted in another > session. I have a fix / workaround for that, but so far no test case. > Please tell me if you have a reproducible test case. > > Regards, > Thomas > > > > On Tuesday, May 27, 2014, too > wrote: > >> Even though I can reproduce this with our app I fail to create separate >> test case to reproduce the problem (i.e. to break the DB). What I can do is >> send you database that H2 fails to open. Perhaps it is not really bug >> during close but not robust enough recovery during startup. Zipped DB has >> 10MB and I can share it for you privately if you think that can help >> >> thanks >> Tono >> >> On Thursday, May 22, 2014 5:47:11 PM UTC+2, too wrote: >> >> I have experimented with this a little and here are few notes >> >>- lock file remains in place - this is consistent with shutdown hooks >>not being executed during JVM abort >>- size of DB file remains intact during runtime but during process >>termination the size rises by approximately 50MB - which contradicts >>previous point and it seems there is something executed during abort >>- I can replicate problem with 100% success rate using our app but I >>fail to create test app to replicate the problem (still have a few ideas >> to >>try) >>- I can replicate the problem with older 1.3.x releases as well as >>with latest 1.4.x release (with MV_STORE=FALSE) >>- even though I do not think that there are transactions in progress >>at the time of termination it is possible there are open cursors or >>something similar - read-related >> >> I can avoid this problem by running H2 in server mode but that is not >> what I want. >> >> Tono >> >> On Tuesday, May 20, 2014 3:16:03 PM UTC+2, too wrote: >> >> Hi, >> Sometimes when our application is shut down forcibly it is unable to >> start again with exceptions below. It does not appear that transaction is >> in progress at the time of shutdown but I can not be sure. I was unable to >> create test case but our application reproduces this quite consistently >> (it's very rare that it starts after being terminated). >> I have attached debug trace file for failed start - not for the shutdown. >> I tried INFO logging for shutdown (breaking the db) but nothing seemed >> interesting there, just bunch of selects. Also no error is logged during >> shutdown or startup. >> >> Environment details are >> >>- H2 - 1.3.176 >>- Java Runtime - Java(TM) SE Runtime Environment (1.7.0_17-b02, >>32b) on Java HotSpot(TM) Client VM (23.7-b01, mixed mode) >>- Operating System - Windows 7 (64b, Service Pack 1, version 6.1) >>- connection URL - jdbc:h2:./dbfile;IFEXISTS=TRUE >> >> >> NullPointerException - this is what usually happens >> >> org.h2.jdbc.JdbcSQLException: General error: >> "java.lang.NullPointerException" >> [5-176] >> at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) >> ~[h2.jar:1.3.176] >> at org.h2.message.DbException.get(DbException.java:167) >> ~[h2.jar:1.3.176] >> at org.h2.message.DbException.convert(DbException.java:294) >> ~[h2.jar:1.3.176] >> at org.h2.engine.Database.openDatabase(Database.java:291) >> ~[h2.jar:1.3.176] >> at org.h2.engine.Database.(Database.java:254) ~[h2.jar:1.3.176] >> at org.h2.engine.Engine.openSession(Engine.java:57) ~[h2.jar:1.3.176] >> at org.h2.engine.Engine.openSession(Engine.java:164) ~[h2.jar:1.3.176] >> at org.h2.engine.Engine.createSessionAndValidate(Engine.java:142) >> ~[h2.jar:1.3.176] >> at org.h2.engine.Engine.createSession(Engine.java:125) ~[h2.jar:1.3.176] >> at org.h2.engine.Engine.createSession(Engine.java:27) ~[h2.jar:1.3.176] >> >> -- >> 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:/
Re: [h2] Corruption in a database created in 1.3.174 when opening and closing it in 1.3.176
2014-06-02 18:04 GMT+02:00 Thomas Mueller : > Yes, this problem was introduced in version 1.3.176, actually by fixing > another bug. It happens when upgrading a database with a special kind of > foreign key constraint from an older version to 1.3.176. I found the > problem now and have a fix for it. I will need to release a new version; a > workaround is to stay with the older version, or upgrade to the newer > version by first converting to a SQL script and then creating a new > database. > Just to be sure. In the new version the problem is solved and I do not need to convert the db? I do not think there are important changes (for me) in 176, so I just wait until the new version is deployed. -- Cecil Westerhof -- 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. For more options, visit https://groups.google.com/d/optout.
