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 thomas.tom.muel...@gmail.com: 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.
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 ton...@gmail.com javascript: 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.init(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://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are
[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.
[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] 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.
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] 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=recursivesearch=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.
[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] 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] 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] 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.
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 javascript: 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.
