Re: [h2] OutOfMemory when creating an Index
Hi Andrei, The one with the 800MB heap was my initial test case, the 4MB row size was just an example to clarify my problem. Unfortunately, creating the indexes in an adminstrative mode is not a solution for us. H2 is just used as a temporary database helping us to bring together data from different sources (CSV, XML, different databases, ...), results are compacted and rendered to an excel spreadsheet. When our application terminates, the H2 database is deleted. From: Andrei Tokar To: H2 Database Date: 23.05.2019 03:16 Subject:Re: [h2] OutOfMemory when creating an Index Sent by:h2-database@googlegroups.com Something does not click here: If you have "a table with millions of rows and an assumed column width of 4MB of data", then how it is possible, that "with set to 1000 the index creation still required a maximum heap of about 800M, but the OOM Error did not occur anymore" ? Your thousand rows should take at least 4G of RAM, not 800M. IMHO, index creation for a big table is an administrative task, presumably performed on idle (if not exclusively held) database, so what would prevent you from opening db with MAX_MEMORY_ROWS of lets say 3000 (assuming 4g heap), creating index, then restart database with you favorite 10, for application to use? On the other hand, we probably should select buffer size as SQRT(ROWCOUNT) and if it exeeds MAX_MEMORY_ROWS/2, then just fall back to plain vanilla r ebuildIndexBuffered(). It might take forever and will trash b-tree, but at least should not fail with OOM. -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/212abf4b-e484-4253-92e2-5799166b1ce0%40googlegroups.com . For more options, visit https://groups.google.com/d/optout. Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/OF3FE305A0.04F50BF5-ONC1258403.00279D38-C1258403.0028939C%40finaris.de. For more options, visit https://groups.google.com/d/optout.
Re: [h2] OutOfMemory when creating an Index
This could be another optimization, but that wont help if I have a table with millions of rows and an assumed column width of 4MB of data. With 4 GB heap, any value of MAX_MEMORY_ROWS > 1000 will lead to OOM Error when selecting from that table. And a value of 1000 might not be high enough for index creation, if there are millions of records. So both won't be possible for the same session. From: Noel Grandin To: h2-database@googlegroups.com Date: 21.05.2019 18:08 Subject:Re: [h2] OutOfMemory when creating an Index Sent by:h2-database@googlegroups.com If you are trying to prevent users from exceeding memory resources, your best bet is just to use a connection pool and limit the max number of connections. Note that even if a user issues multiple queries in parallel to the same connection, those queries will execute sequentially server-side. I still maintain that your existing performance will be terrible compared to what it could be, since you are effectively limited by very slow disk IO, even for very small queries. If you limit the max number of connections, and raise MAX_MEMORY_ROWS to a reasonable number, you will experience a net gain in performance. -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/CAFYHVnXte%2BOQMcFj6A1RqKXasXdoDFrHhdDaavry4U4qkSk%2B6g%40mail.gmail.com . For more options, visit https://groups.google.com/d/optout. Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/OFCC6B4677.78567CAC-ONC1258402.00341A4E-C1258402.0034DFB7%40finaris.de. For more options, visit https://groups.google.com/d/optout.
Re: [h2] OutOfMemory when creating an Index
Hi Noel, our users typically deal with huge amounts of data, which often do not fit into memory. Tables might have hundreds of columns, so that already a low amount of rows held in memory can occupy a lot of it. Additionally, queries are often issued in parallel, therefore a low value was set so that the users do not need to take care of advanced H2 settings and guarantee there will not be an out of memory error at any time. The performance was still good. Anyway, the problem I still have is, that a high value of MAX_MEMORY_ROWS will require a small amount of memory during index creation, but lots of memory for result sets. a low value of MAX_MEMORY_ROWS will require a huge amount of memory during index creation, but only a small amount of memory for result sets. Would it be possible to introduce a new database property for the index creation? This one could default to the current default for MAX_MEMORY_ROWS. This would help us a lot. On 2019/05/21 9:52 AM, christoff.schm...@finaris.de wrote: > > I tracked this down to the *rebuildIndexBlockMerge *method of the *MVTable *class (see below). > As I saw that the *MAX_MEMORY_ROWS *parameter is used in the method, I changed its values and tried again. > With set to 1000 the index creation still required a maximum heap of about 800M, but the OOM Error did not occur anymore. > This sounds a bit strange to me, because as far as I understood *lowering *the value of that parameter should *decrease > *memory consumption. > > Is anything wrong with my configuration? Or might this be a bug? > > Now that is an interesting failure mode. Given how much memory you seem to have, you should be setting MAX_MEMORY_ROWS *higher*, not lower. Running with MAX_MEMORY_ROWS set to 10 is something we only expect to see in unit tests, when we're trying to test our on-disk temporary-data code-paths. So what is happening is that we create a new temporary map for each block of MAX_MEMORY_ROWS rows, which is your case means we end up creating 8,000,000/10 = 800,000 maps! So I would suggest either leaving MAX_MEMORY_ROWS alone, which will auto-configure to a reasonable number, or configuring it such that virtually all your queries run in-memory without needing to spill temporary data to disk. Which will also mean that your application will run a whole lot faster. -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/277d5c3d-aeff-0a9a-0751-1694d8a0a80c%40gmail.com . For more options, visit https://groups.google.com/d/optout. Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 https://groups.google.com/group/h2-database. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/OFC621AD22.2AF3302E-ONC1258401.004F778A-C1258401.00513315%40finaris.de. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: H2 fails inserting very large CLOB values
Just tested the fix. Works perfectly, thanks. Mit freundlichen Grüßen Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Evgenij Ryazanov <kat...@gmail.com> To: H2 Database <h2-database@googlegroups.com> Date: 13.02.2018 08:38 Subject:[h2] Re: H2 fails inserting very large CLOB values Sent by:h2-database@googlegroups.com A fix was merged. If you need this right now, you can download latest sources and build database from it. https://github.com/h2database/h2database http://www.h2database.com/html/build.html#building -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] AUTO: Christoff Schmitz is out of the office (returning 02.01.2018)
I am out of the office until 02.01.2018. I will reply to your message when I return. Note: This is an automated response to your message "[h2] Re: USE INDEX confusion with joins, v1.4.195" sent on 23.12.2017 1:25:19 PM. This is the only notification you will receive while this person is away. Disclaimer The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Bug in array datatype handling?
Unfortunately, H2 does not treat (1) as array. The same problem occurs when casting an integer to array: cast(1 as ARRAY) results in a single element array containing the String '1'. From: IanP i.pri...@surveybe.com To: h2-database@googlegroups.com Date: 2015-07-10 16:02 Subject:[h2] Bug in array datatype handling? Sent by:h2-database@googlegroups.com Hi, I think I see a bug in array datatype handling. Specifically an array is handled as string[] or an int[] depending on a trailing ',' char. I found it while fixing what looked like a bug in my code that was issuing commands like INSERT INTO TABLENAME VALUES(1, (1,)) and then discovered my fix caused a regression. It can be can recreated like this... drop table if exists arraytest; Update count: 0 (0 ms) create table arraytest(id int, arraycol array); Update count: 0 (1 ms) insert into arraytest values(1, (1)); Update count: 1 (0 ms) insert into arraytest values(2, (1,)); Update count: 1 (0 ms) select arraycol, array_contains(arraycol, 1), array_contains(arraycol, '1') from arraytest where id = 1; ARRAYCOL ARRAY_CONTAINS(ARRAYCOL, 1) ARRAY_CONTAINS(ARRAYCOL, '1') (1) FALSE TRUE (1 row, 3 ms) select arraycol, array_contains(arraycol, 1), array_contains(arraycol, '1') from arraytest where id = 2; ARRAYCOL ARRAY_CONTAINS(ARRAYCOL, 1) ARRAY_CONTAINS(ARRAYCOL, '1') (1) TRUE FALSE (1 row, 0 ms) Note that on the two rows the array_contains functions match differently against int and string types. The only difference in the row inserts is the trailing comma on the array value. I guess it should be treating them as ints if they are unquoted, and only as strings if they are quoted like this: insert into arraytest values(1, (1)); // Should be int insert into arraytest values(1, ('1')); // Should be string A bug, or is it some subtlety about arrays that I have missed? Cheers, Ian. -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Cast to array
Hi, Is there a specific reason why the expression CAST(5 AS ARRAY) results in an array containing a VARCHAR value instead of a numeric value? Or might this be a bug? Kind regards, Christoff Schmitz Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] H2 (embedded) uses a lot of memory for large Union Statement
Hi, H2 SQL statements containing a lof of UNION ALL expressions (top level) consume a lot of memory, especially when the unioned SELECT queries are quite large (like for generated statement that we use). According to JProfiler, this is caused because the H2 SelectUnion objects reference each other in a row: Currently, my workaround is a table function, that receives the SQL statement texts to union. The table function returns a result set that executes all queries in a row and creates the next resultset when a previous one was completely read. The runtime of both union all versions is equal, but using the table function, only the single statements must fit into memory, but not the full cascade of SelectUnion objects. Maybe there is a possibility to updates H2's handling of UNION ALL statements so that they require less memory? Kind regards Christoff Schmitz Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Triggers, finding the table name that trigger fired against?
Hi, org.h2.tools.TriggerAdapter (your Trigger should extend that class), provides the protected members schemaName and tableName. Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: blu10 johnmaddo...@gmail.com To: h2-database@googlegroups.com Date: 2015-05-11 10:12 Subject:[h2] Triggers, finding the table name that trigger fired against? Sent by:h2-database@googlegroups.com I have numerous triggers on my database and i can see the data being i'm entering being output on the console using the code below in the fire function. The problem is i cant see which table the trigger was fired against. Is there something obvious im missing.? Id like to eventually output the table name with the data entered so i can see which tables are being updated. /** * This method is called for each triggered action. * * @param conn a connection to the database * @param oldRow the old row, or null if no old row is available (for *INSERT) * @param newRow the new row, or null if no new row is available (for *DELETE) * @throws SQLException if the operation must be undone */ @Override public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException { BigDecimal diff = null; if (newRow != null) { for (int i =0; i newRow.length; i++) { if (newRow[i] != null) { System.out.println(Col !); System.out.println(newRow[i].toString()); } } } } -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Single element array-literal?
Hi, is there a literal form to describe an array with only 1 element? For 2+ elements it works like this: select (1,2,3) select (1) does not result in SQL type ARRAY, the result type is INTEGER. CAST(1 AS ARRAY) results in SQL type ARRAY, but the result is an array containing the String 1, not 1 as numeric type Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Single element array-literal?
Hi Noel, your suggestion leads to the same result as cast (1 as ARRAY), the type of the array element is VARCHAR, not INTEGER. (tested with 1.3.176 and 1.4.187) The following query's ResultSetMetaData shows the problem: SELECT ARRAY_GET(CAST(CAST( 1 AS INTEGER) AS ARRAY), 1), ARRAY_GET((1,2), 1) Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin noelgran...@gmail.com To: h2-database@googlegroups.com Date: 2015-05-04 11:55 Subject:Re: [h2] Single element array-literal? Sent by:h2-database@googlegroups.com SELECT CAST(CAST( 1 AS INTEGER) AS ARRAY) On 2015-05-04 11:44 AM, christoff.schm...@finaris.de wrote: Hi, is there a literal form to describe an array with only 1 element? For 2+ elements it works like this: *select**(**1*,*2*,*3**)* *select**(**1**)*does not result in SQL type ARRAY, the result type is INTEGER. *CAST**(**1**AS**ARRAY**)*results in SQL type ARRAY, but the result is an array containing the String 1, not 1 as numeric type -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Serialization issue
Hi, I have set a custom serializer for Java object contained in columns of type OTHER (added JAVA_OBJECT_SERIALIZER='custom.h2.Serializer' to the connect string) In H2 code, there are 2 calls to Utils.serialize (H2 1.3.176) [or JdbcUtils.serialize (in H2 1.4.187)] that pass a NULL argument for the data handler parameter. Unfortunatly, my code runs into one of these (in org.h2.value. ValueJavaObject), and my custom serializer is not used. Additionally, I tried to use the system propoerty h2.javaObjectSerializer. But this did not help either (H2 1.3.176), because the Utils class is loaded when the SysProperties class is loaded (due to public static final String FILE_ENCODIN = Utils.getProperty( file.encoding, Cp1252)). At that point of time, the SysProperties class is not fully initialized yet, and the static block in Utils always refers to NULL as serializer class name. Seems a bit buggy to me. Currently, my only workaround is setting the member public static JavaObjectSerializer serialize of class Utils directly from my application (H2 is used as embedded database). Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Re: Serialization issue
Hi Thomas, I have some classes loaded by custom URL class loaders at runtime. I have set that property with the -D switch for my application. That leads to the following stacktrace: Thread [Thread-70] (Suspended) owns: HashMapK,V (id=58) Utils.clinit() line: 76 --- static block refers to SysProperties.JAVA_OBJECT_SERIALIZE, but that static field has not been initialized yet (as the Sysproperties clinit is still running) SysProperties.clinit() line: 51 --- initialization of SysProperties class is still in progress Engine.init() line: 32 Engine.clinit() line: 29 [local variables unavailable] [...] Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Thomas Mueller thomas.tom.muel...@gmail.com To: H2 Google Group h2-database@googlegroups.com Date: 2015-04-27 15:41 Subject:Re: [h2] Re: Serialization issue Sent by:h2-database@googlegroups.com Hi Sim, Can't you create your own topic? I don't understand. As far as I see, it _is_ a separate topic (email subject). I have set a custom serializer for Java object contained in columns of type OTHER Why can't you use the default serialization mechanism? What problem do you want to solve? because the Utils class is loaded when the SysProperties class is loaded Yes. Why can't you set the system property before it is loaded? Regards, Thomas On Mon, Apr 27, 2015 at 2:28 PM, sim sim...@mail.ru wrote: Hi, Can't you create your own topic? On Monday, April 27, 2015 at 2:51:51 PM UTC+3, schmitzc wrote: Hi, I have set a custom serializer for Java object contained in columns of type OTHER (added JAVA_OBJECT_SERIALIZER='custom.h2.Serializer' to the connect string) In H2 code, there are 2 calls to Utils.serialize (H2 1.3.176) [or JdbcUtils.serialize (in H2 1.4.187)] that pass a NULL argument for the data handler parameter. Unfortunatly, my code runs into one of these (in org.h2.value. ValueJavaObject), and my custom serializer is not used. Additionally, I tried to use the system propoerty h2.javaObjectSerializer. But this did not help either (H2 1.3.176), because the Utils class is loaded when the SysProperties class is loaded (due to public static final String FILE_ENCODIN = Utils.getProperty( file.encoding, Cp1252)). At that point of time, the SysProperties class is not fully initialized yet, and the static block in Utils always refers to NULL as serializer class name. Seems a bit buggy to me. Currently, my only workaround is setting the member public static JavaObjectSerializer serialize of class Utils directly from my application (H2 is used as embedded database). Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 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] Wrong meta data returned by ARRAY_GET
In this case the documentation is wrong as ResultSet.getObject() returns an array. Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: FredDaniPandoraAquiles zepf...@gmail.com To: h2-database@googlegroups.com h2-database@googlegroups.com Date: 2015-01-30 16:09 Subject:Re: [h2] Wrong meta data returned by ARRAY_GET Sent by:h2-database@googlegroups.com Hi, According to the documentation, the method ARRAY_GET returns one element of an array as a string, so I think the meta information is correct. See the documentation: http://www.h2database.com/html/functions.html#array_get . Regards, Fred 2015-01-30 10:57 GMT-02:00 christoff.schm...@finaris.de: Hi, When executing the following 2 statements, the ResultSetMetaData is wrong for the column with the ARRAY_GET expression. SET @a = ((1, 2), (2, 4)) / SELECT @a, ARRAY_GET(@a, 1) The metadata says that that column is of type VARCHAR, while ARRAY should be correct (getObject() invoked on that column returns an Object[] as expected). Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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 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.[attachment TestMeta.java deleted by Christoff Schmitz/Finaris] Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Wrong meta data returned by ARRAY_GET
Hi, When executing the following 2 statements, the ResultSetMetaData is wrong for the column with the ARRAY_GET expression. SET @a = ((1, 2), (2, 4)) / SELECT @a, ARRAY_GET(@a, 1) The metadata says that that column is of type VARCHAR, while ARRAY should be correct (getObject() invoked on that column returns an Object[] as expected). Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Wrong results using the BETWEEN operator (H2 1.3.176)
Hi, The following statement returns 0 rows as result, obviously, the result should be one row containing the value 17: SELECT ZEILE_RELATIV FROM ( SELECT ZEILE_RELATIV FROM ( SELECT 16 AS ZEILE_RELATIV UNION ALL SELECT 17 AS ZEILE_RELATIV UNION ALL SELECT 18 AS ZEILE_RELATIV UNION ALL SELECT 19 AS ZEILE_RELATIV ) WHERE ZEILE_RELATIV BETWEEN 16 AND 18 ) WHERE ZEILE_RELATIV = 17 The same query using an equivalent IN clause works as expected (1 record as result): SELECT ZEILE_RELATIV FROM ( SELECT ZEILE_RELATIV FROM ( SELECT 16 AS ZEILE_RELATIV UNION ALL SELECT 17 AS ZEILE_RELATIV UNION ALL SELECT 18 AS ZEILE_RELATIV UNION ALL SELECT 19 AS ZEILE_RELATIV ) WHERE ZEILE_RELATIV IN (16,17,18) ) WHERE ZEILE_RELATIV = 17 Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] ConcurrentModificationException while creating Trigger
.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:109) at org.h2.jdbc.JdbcDatabaseMetaData.getColumns(JdbcDatabaseMetaData.java:285) at org.h2.tools.TriggerAdapter.init(TriggerAdapter.java:70) at org.h2.schema.TriggerObject.load(TriggerObject.java:72) ... 18 more Caused by: java.util.ConcurrentModificationException at java.util.HashMap$HashIterator.nextEntry(Unknown Source) at java.util.HashMap$ValueIterator.next(Unknown Source) at org.h2.engine.Database.getAllTablesAndViews(Database.java:1466) at org.h2.table.MetaTable.getAllTables(MetaTable.java:610) at org.h2.table.MetaTable.generateRows(MetaTable.java:721) at org.h2.index.MetaIndex.find(MetaIndex.java:51) at org.h2.index.BaseIndex.find(BaseIndex.java:128) at org.h2.index.IndexCursor.find(IndexCursor.java:160) at org.h2.table.TableFilter.next(TableFilter.java:330) at org.h2.command.dml.Select.queryFlat(Select.java:533) at org.h2.command.dml.Select.queryWithoutCache(Select.java:646) at org.h2.command.dml.Query.query(Query.java:323) at org.h2.command.dml.Query.query(Query.java:291) at org.h2.command.dml.Query.query(Query.java:37) at org.h2.command.CommandContainer.query(CommandContainer.java:91) at org.h2.command.Command.executeQuery(Command.java:197) ... 22 more Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Benedikt Waldvogel m...@bwaldvogel.de To: h2-database@googlegroups.com Date: 20.11.2014 09:21 Subject:Re: [h2] 1.4.181 BLOB related issue Sent by:h2-database@googlegroups.com Hi, On Wed, November 19, 2014 10:25, Thomas Mueller wrote: To avoid having to change the test case, could you add ;trace_level_system_out=3 to the database URL, and then send me / post the resulting output? This should include all the JDBC API calls. That way it should be quite easy to write a pure JDBC test case. Or append ;trace_level_file=3 and then send the databaseName.trace.db file. please find a trace on https://paste.ee/p/Ri7xi Let me know when you need more info. Best, Benedikt -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] ConcurrentModificationException while creating Trigger
jdbc:h2:file:PATH;MULTI_THREADED=1;DB_CLOSE_DELAY=-1;LOG=0;UNDO_LOG=0 ;QUERY_CACHE_SIZE=0;CACHE_SIZE=16384 The database is only required during runtime of my application. Therefore undo and transaction log are disabled to improve performance. Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin noelgran...@gmail.com To: h2-database@googlegroups.com Date: 20.11.2014 10:53 Subject:Re: [h2] ConcurrentModificationException while creating Trigger Sent by:h2-database@googlegroups.com What does your database URL look like? -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] NOT IN also excludes NULL values, intended?
Hi, Executing the following statements only returns records with num != NULL. Is that the intended behavior? CREATE TABLE yyy (num DECIMAL(7)) / INSERT INTO yyy VALUES (NULL) / INSERT INTO yyy VALUES (1) / INSERT INTO yyy VALUES (5) / INSERT INTO yyy VALUES (NULL) / SELECT * FROM yyy WHERE num NOT IN (-1,-5) Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Index creation ignores MAX_MEMORY_ROWS setting
// there is something wrong with the database trace.error(e2, could not remove index); throw e2; } throw e; } } index.setTemporary(isTemporary()); if (index.getCreateSQL() != null) { index.setComment(indexComment); if (isSessionTemporary) { session.addLocalTempTableIndex(index); } else { database.addSchemaObject(session, index); } } indexes.add(index); setModified(); return index; } Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Timeout trying to lock table SYS during table creation
Hi Viktor, I had similar problems when creating indexes on very large tables in parallel (using last stable build 1.3.176). I debugged this and found out that H2 synchronizes the index creations so that indexes are created sequentially (Maybe this applies to other DML/DDL like yours too). If one index creation thread waits longer than the specified LOCK_TIMEOUT, then Timeout trying to lock table SYS is thrown. My current workaround is to set the LOCK_TIMEOUT to Integer.MAX_VALUE. Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Viktor Voytovych vvoytov...@llnw.com To: h2-database@googlegroups.com Date: 25.09.2014 09:26 Subject:Re: [h2] Timeout trying to lock table SYS during table creation Sent by:h2-database@googlegroups.com I've started getting this: Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table SYS; SQL statement: INSERT INTO TABLE_83e6fcbd876844f39b8a4d5dd53fe021 (time,bytes) VALUES (?,?) [50200-181] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.mvstore.db.MVTable.doLock1(MVTable.java:168) at org.h2.mvstore.db.MVTable.lock(MVTable.java:130) at org.h2.engine.Database.lockMeta(Database.java:884) at org.h2.engine.Database.updateMeta(Database.java:1572) at org.h2.command.ddl.Analyze.analyzeTable(Analyze.java:125) at org.h2.mvstore.db.MVTable.analyzeIfRequired(MVTable.java:654) at org.h2.mvstore.db.MVTable.addRow(MVTable.java:641) at org.h2.command.dml.Insert.insertRows(Insert.java:156) at org.h2.command.dml.Insert.update(Insert.java:114) at org.h2.command.CommandContainer.update(CommandContainer.java:78) at org.h2.command.Command.executeUpdate(Command.java:254) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157) at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1183) at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899) at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:884) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 52 more and this: Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table SYS; SQL statement: CREATE MEMORY TABLE IF NOT EXISTS TABLE_e3af436af66140ff958c008e578c9e33(account_id BIGINT, session_id VARCHAR) NOT PERSISTENT [50200-181] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.mvstore.db.MVTable.doLock1(MVTable.java:168) at org.h2.mvstore.db.MVTable.lock(MVTable.java:130) at org.h2.engine.Database.lockMeta(Database.java:884) at org.h2.command.ddl.CreateTable.update(CreateTable.java:135) at org.h2.command.CommandContainer.update(CommandContainer.java:78) at org.h2.command.Command.executeUpdate(Command.java:254) at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:185) at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:159) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) ... 36 more On Tuesday, September 23, 2014 7:48:11 PM UTC+3, Viktor Voytovych wrote: Thanks. Testing it. On Tuesday, September 23, 2014 11:04:53 AM UTC+3, Noel Grandin wrote: On 2014-09-23 09:57 AM, Viktor Voytovych wrote: Thanks. Would you recommend Version 1.3.176 (2014-04-05) that is Last Stable or Version 1.4.181 (2014-08-06) that is Beta? Since you are running a pure in-memory database, I would recommend 1.4.181 The information in this message may be confidential. It is intended solely for the addressee(s). If you are not the intended recipient, any disclosure, copying or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. The information in this message may be confidential. It is intended solely for the addressee(s). If you are not the intended recipient, any
Re: [h2] Out of memory when Ordering results for very large table
Hi Thomas, in the meantime I could check this using the latest 1.4 release. Unfortunately my test case still fails with an OutOfMemoryError. Only the location of the leak has moved to org.h2.index.TreeIndex, which seems to refer all row data of my table: Source Code for reproduction (Xmx 512m): 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 codetrue/code 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.execute(create unique index IDX_OT on ordertest(ID)); 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.length; ++idx) { buf[idx] = symbols[random.nextInt(symbols.length)]; } return new String(buf); } /** * @param cols Number of cols * @return The insert statement SQL */ private String getInsertStatement(int cols) { StringBuilder builder = new StringBuilder(insert into ordertest (ID); for (int i = 0; i cols; i++) { builder.append(, COL + i); } builder.append() values (?); for (int i = 0; i cols; i++) { builder.append(,?); } builder.append()); return builder.toString(); } /** * @param cols Number of cols * @return The create statement SQL */ private String getCreateStatement(int cols) { StringBuilder builder = new StringBuilder(create table ordertest (ID integer); for (int i = 0; i cols; i++) { builder.append(, COL + i + VARCHAR(4000)); } builder.append()); return builder.toString(); } /** * @return Connection to DB * @throws SQLException */ public Connection connect() throws SQLException { String connectString = jdbc:h2:file: + ./H2 + ;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0;mv_store=false; try { Class.forName(org.h2.Driver); } catch (ClassNotFoundException ex) { throw new SQLException(ex.getMessage(), ex); } Connection connection = DriverManager.getConnection (connectString); connection.setTransactionIsolation(Connection. TRANSACTION_READ_UNCOMMITTED); return connection; } } Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24
[h2] Error in Meta data of ResultSet
Hi, in H2 1.3.173 the meta data for the ResultSet of query SELECT 0.02 tells that the precision of the numeric column is 1 (but obviously it should be = 2 (scale)). For SELECT 1.02 the precision is returned as 3 (as expected). It seems to me that this is a bug for numeric literals in interval ]-1, 1[ Can you tell me if this is already fixed for a later version of the 1.3 branch? Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] ArrayIndexOutOfBoundsException with union query
] at org.h2.message.DbException.getJdbcSQLException(DbException.java:331) at org.h2.message.DbException.get(DbException.java:160) at org.h2.message.DbException.convert(DbException.java:283) at org.h2.command.Command.executeQuery(Command.java:199) at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:78) at com.rapidrep.kernel.internaldb.common.tablefunctions.dataelement.DataElementTableFunctionManager.readTableFunction(DataElementTableFunctionManager.java:182) ... 154 more Caused by: java.lang.ArrayIndexOutOfBoundsException: 2048 at org.h2.store.Data.writeVarLong(Data.java:1243) at org.h2.store.Data.writeValue(Data.java:577) at org.h2.store.Data.writeValue(Data.java:641) at org.h2.index.PageBtreeIndex.writeRow(PageBtreeIndex.java:387) at org.h2.index.PageBtreeLeaf.writeData(PageBtreeLeaf.java:292) at org.h2.index.PageBtreeLeaf.write(PageBtreeLeaf.java:266) at org.h2.store.PageStore.writeBack(PageStore.java:1015) at org.h2.util.CacheLRU.removeOld(CacheLRU.java:209) at org.h2.util.CacheLRU.removeOldIfRequired(CacheLRU.java:138) at org.h2.util.CacheLRU.put(CacheLRU.java:113) at org.h2.store.PageStore.getPage(PageStore.java:832) at org.h2.index.PageDataIndex.getPage(PageDataIndex.java:231) at org.h2.index.PageDataNode.getNextPage(PageDataNode.java:231) at org.h2.index.PageDataLeaf.getNextPage(PageDataLeaf.java:396) at org.h2.index.PageDataCursor.nextRow(PageDataCursor.java:93) at org.h2.index.PageDataCursor.next(PageDataCursor.java:52) at org.h2.index.IndexCursor.next(IndexCursor.java:271) at org.h2.table.TableFilter.next(TableFilter.java:359) at org.h2.command.dml.Select.queryFlat(Select.java:518) at org.h2.command.dml.Select.queryWithoutCache(Select.java:625) at org.h2.command.dml.Query.query(Query.java:314) at org.h2.command.dml.Query.query(Query.java:284) at org.h2.command.dml.Query.query(Query.java:36) at org.h2.command.CommandContainer.query(CommandContainer.java:91) at org.h2.command.Command.executeQuery(Command.java:195) ... 156 more Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Error in Meta data of ResultSet
The result is the same for 1.3.176 Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com -h2-database@googlegroups.com schrieb: - An: h2-database@googlegroups.com Von: Noel Grandin Gesendet von: h2-database@googlegroups.com Datum: 21.05.2014 10:38 Betreff: Re: [h2] Error in Meta data of ResultSet Have you tried testing on the latest version? On 2014-05-21 10:37, christoff.schm...@finaris.de wrote: in H2 1.3.173 the meta data for the ResultSet of query -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Possible deadlock using H2 table functions the query H2
Hi, Our application uses an embedded H2 database (1.3.173). During execution, table functions are created. Some of them execute select-statements in the H2 database. When not running in in multi-threaded mode, and if multiple threads (each with separate connection) are accessing the same table function, then deadlocks can occur. This seems to be due to the fact, that the final statement execution inside H2 is synchronized over the database, and The statement preparation is synchronized over the TableView object: start execution --prepare statement (synchronized over a TableView) query table function --prepare statement (synchronized over a TableView) --execute (synchronized over database) --End of outer prepare --start execution(synchronized over database) query table function --prepare statement (synchronized over a TableView) --execute (synchronized over database) As you can see in this execution flow, due to the fact that the table function executes a query to H2, sometimes TableView synchronization comes before database synchronization, and sometimes it's the other way around. As consequence, if there a multiple parallel threads doing this, deadlocks can occur. My current work-around is a synchronization of H2 query execution over a singleton instance, if no multithreading is used. I know that this usage of H2 is quite special, but perhaps there is a possibility to fix this? Or would this be out of scope of H2? Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: István Bartók bart...@gmail.com To: h2-database@googlegroups.com Date: 14.05.2014 12:55 Subject:Re: [h2] C:/test outside C:/ Sent by:h2-database@googlegroups.com Hi Thomas, Thank you for the quick response and the patch! Is this a one-off fix, or do you plan to include it into future versions? Thanks, -- Bartók István On Wednesday, May 14, 2014 7:44:15 AM UTC+2, Thomas Mueller wrote: Hi, Yes, this is a bug. I have a patch: Index: src/main/org/h2/engine/ConnectionInfo.java === --- src/main/org/h2/engine/ConnectionInfo.java (revision 5657) +++ src/main/org/h2/engine/ConnectionInfo.java (working copy) @@ -183,7 +183,9 @@ throw DbException.get(ErrorCode.IO_EXCEPTION_1, normalizedName + outside + absDir); } -if (normalizedName.charAt(absDir.length()) != '/') { +if (absDir.endsWith(/) || absDir.endsWith(\\)) { +// no further checks are needed for C:/ and similar +} else if (normalizedName.charAt(absDir.length()) != '/') { // database must be within the directory // (with baseDir=/test, the database name must not be // /test2/x and not /test2) The C: case I like to not support, as it means the current working directory for C:, which is unexpected for many people. By the way, for H2 version 1.4.x, the database URL would need to be jdbc:h2:./test (explicitly state that the database is relative to another directory). Regards, Thomas On Tuesday, May 13, 2014, István Bartók bar...@gmail.com wrote: Hi, I get an exception with a strange C:/test outside C:/ message, when: - Opening a database with implicit relative path name - While h2.baseDir is set to the root of a Windows drive (C:/ or C:\\) However, it works as expected with h2.baseDir=C:/anything. Example code: import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class H2BaseDirProblem { public static void main(String[] args) throws SQLException { Connection conn; // Works: System.setProperty(h2.baseDir, C:/anything); conn = DriverManager.getConnection(jdbc:h2:test, sa, ); // Fails with any of these: System.setProperty(h2.baseDir, C:/); conn = DriverManager.getConnection(jdbc:h2:test, sa, ); System.setProperty(h2.baseDir, C:\\); conn = DriverManager.getConnection(jdbc:h2:test, sa, ); conn.close(); } } It throws the following exception: Exception in thread main org.h2.jdbc.JdbcSQLException: IO Exception: C:/test outside C:/ [90028-174] at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) at org.h2.message.DbException.get(DbException.java:172) at org.h2.message.DbException.get(DbException.java:149) at org.h2.engine.ConnectionInfo.setBaseDir(ConnectionInfo.java:192) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:101) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:90) at org.h2.Driver.connect(Driver.java
Re: [h2] Possible deadlock using H2 table functions the query H2
Hi, see the stack trace below, I marked the positions where synchronization occurs over a TableView (red) or Database (blue). The root cause is, as explained, that the table function implementation within my class (DataElementTableFunctionManager) triggers a new query execution to H2, some sort of statement execution within statement execution (or within statement parsing as in the stack trace). Maybe this is not recommended to do, but in my case this should not be a problem, since the data read by the table function never updated within H2. Thread [#4 (working)] (Suspended) CommandContainer(Command).executeQuery(int, boolean) line: 190 (SYNC: Database) JdbcStatement.executeQuery(String) line: 78 DataElementTableFunctionManager.readTableFunction(String, String, String, int, int, String) line: 170 (not part of H2) H2DataElementTableFunctionManager.readTableFunction(Connection, String, String, int, int, String) line: 98 (not part of H2) GeneratedMethodAccessor50.invoke(Object, Object[]) line: not available DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: not available Method.invoke(Object, Object...) line: not available FunctionAlias$JavaMethod.getValue(Session, Expression[], boolean) line: 411 JavaFunction.getValueForColumnList(Session, Expression[]) line: 129 FunctionTable.init(Schema, Session, Expression, FunctionCall) line: 63 Parser.readTableFilter(boolean) line: 1100 Parser.parseSelectSimpleFromPart(Select) line: 1713 Parser.parseSelectSimple() line: 1821 Parser.parseSelectSub() line: 1707 Parser.parseSelectUnion() line: 1550 Parser.parseSelect() line: 1538 Parser.parsePrepared() line: 405 Parser.parse(String, boolean) line: 279 Parser.parse(String) line: 251 Parser.prepare(String) line: 202 Session.prepare(String, boolean) line: 401 ViewIndex.getQuery(Session, int[]) line: 268 ViewIndex.init(TableView, ViewIndex, Session, int[]) line: 71 TableView.getBestPlanItem(Session, int[], SortOrder) line: 212 (SYNC: TableView) TableView.getScanIndex(Session) line: 377 TableFilter.getBestPlanItem(Session, int) line: 163 Plan.calculateCost(Session) line: 111 Optimizer.testPlan(TableFilter[]) line: 177 Optimizer.calculateBestPlan() line: 81 Optimizer.optimize() line: 230 Select.preparePlan() line: 931 Select.prepare() line: 832 Parser.prepare(String) line: 203 Session.prepare(String, boolean) line: 401 Session.prepare(String) line: 388 TableView.compileViewQuery(Session, String) line: 101 TableView.initColumnsAndTables(Session) line: 146 TableView.init(String, ArrayListParameter, String[], Session, boolean) line: 97 TableView.init(Schema, int, String, String, ArrayListParameter, String[], Session, boolean) line: 63 TableView.createTempView(Session, User, String, Query, Query) line: 451 Parser.readTableFilter(boolean) line: 1053 Parser.parseSelectSimpleFromPart(Select) line: 1713 Parser.parseSelectSimple() line: 1821 Parser.parseSelectSub() line: 1707 Parser.parseSelectUnion() line: 1550 Parser.parseSelect() line: 1538 Parser.parsePrepared() line: 405 Parser.parse(String, boolean) line: 279 Parser.parse(String) line: 251 Parser.prepareCommand(String) line: 218 Session.prepareLocal(String) line: 428 Session.prepareCommand(String, int) line: 377 JdbcConnection.prepareCommand(String, int) line: 1138 JdbcStatement.executeQuery(String) line: 72 [...] Kind regards Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin noelgran...@gmail.com To: h2-database@googlegroups.com Date: 14.05.2014 15:05 Subject:Re: [h2] Possible deadlock using H2 table functions the query H2 Sent by:h2-database@googlegroups.com That is a classic example of an ABBA lock, and we are happy to track them down and fix them. Can you identify for us a stack-trace where it synchronizes over the database while holding a lock on a TableView? If you are using the Eclipse debugger, this is very easy - when it is paused, right-click on the thread and select Copy Stack. If you are not, then something like new Throwable().printStackTrace(System.out); in an appropriate place normally does the trick. Thanks. -- You received this message because you are subscribed to the Google Groups H2 Database group
Re: [h2] Join slow on Windows XP, but fast on Windows 7
Hi, a, b and c are true. Additionally, I tested the same Join with the Apache Derby Database. Apache Derby does not show that performance difference between Windows XP and Windows 7 (same Test machines and JVMs used). For me, the main difference between both databases is the way data is stored to the disk drive. While H2 has one database file, Apache Derby has one per Table or Index. Since the H2 file is growing to some GB of size, I would assume that this is the problem. I will try to create a small java program as test case for you within the next days. Kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin noelgran...@gmail.com To: h2-database@googlegroups.com Date: 25.03.2014 07:27 Subject:Re: [h2] Join slow on Windows XP, but fast on Windows 7 Sent by:h2-database@googlegroups.com Firstly, are you sure that (a) the machines have similar disk drives? (b) the machines have similar CPU and memory specs? (c) you are running the same version of Java and H2 on all the machines? If all that is true, then you may need to do some profiling to help us identify the problem area: http://h2database.com/html/performance.html#application_profiling Regards, Noel. On 2014-03-21 11:43, christoff.schm...@finaris.de wrote: Hi, I have the following problem. My Application uses a H2 database (1.3.171) stored to the hardisk. I am running a full outer join (implemented by RIGHT OUTER, UNION , LEFT OUTER) between of 2 tables (30 columns each, 1.1 million records each). On my Windows 7 Test machine, the join completes in about 15 minutes, but on another Windows XP (NTFS file system) machine it takes hours to complete. I watched the CPU usage of my application on both systems and I noticed that it uses one complete core during the whole time of the join on my Windows 7 Machine. On Windows XP, things seem to be different. Shortly after starting the join, the CPU usage behaves like on Windows 7. but after some time (The database has grown to approx. 2 GB on harddisk), the CPU usage reduces and oscillates at a quite low amount. I tested this on several Windows XP machines and the results always seem to be the same. Is there anything known about this problem (reason, workaround, fix)? -- 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. Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] Join slow on Windows XP, but fast on Windows 7
Hi, I have the following problem. My Application uses a H2 database (1.3.171) stored to the hardisk. I am running a full outer join (implemented by RIGHT OUTER, UNION , LEFT OUTER) between of 2 tables (30 columns each, 1.1 million records each). On my Windows 7 Test machine, the join completes in about 15 minutes, but on another Windows XP (NTFS file system) machine it takes hours to complete. I watched the CPU usage of my application on both systems and I noticed that it uses one complete core during the whole time of the join on my Windows 7 Machine. On Windows XP, things seem to be different. Shortly after starting the join, the CPU usage behaves like on Windows 7. but after some time (The database has grown to approx. 2 GB on harddisk), the CPU usage reduces and oscillates at a quite low amount. I tested this on several Windows XP machines and the results always seem to be the same. Is there anything known about this problem (reason, workaround, fix)? Thanks in advance and kind regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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] How to enforce CSV column order with INSERT INTO / CSVREAD command
Hi, In my project we use H2 table functions to read data from other databases. I found out that H2 calls our table functions more often than expected, which can lead to serious performance problems if the execution of the SQL from the other database is quite expensive. The sample code below creates a table function, and creates a wrapping view for it. During creation of the view, H2 calls the table function 3 times, and when selecting from the view again 4 times. I also tried to create the table function with the DETERMINISTIC flag but that made things even worse (6 call when creating the view, 7 when selecting from it) Is it really required that H2 call the function more than once for each operation? Is it possible to configure this some how, or is there a bug in my code? Code: public class Test { public static void main(String[] args) throws SQLException { new Test().run(); } private void run() throws SQLException { Connection connection = connect(); Statement st = connection.createStatement(); st.execute(create alias MYTF for \Test.getResultSet\); System.out.println(creating view...); st.execute(create view MVIEW as select * from MYTF()); System.out.println(selecting from view...); st.executeQuery(select * from MVIEW); st.execute(create alias MYTF2 deterministic for \Test.getResultSet\); System.out.println(creating view (deterministic table function)...); st.execute(create view MVIEW2 as select * from MYTF2()); System.out.println(selecting from view (deterministic table function)...); st.executeQuery(select * from MVIEW2); } public Connection connect() throws SQLException { String connectString = jdbc:h2:mem: + H2 + ;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0; try { Class.forName(org.h2.Driver); } catch (ClassNotFoundException ex) { throw new SQLException(ex.getMessage(), ex); } Connection connection = DriverManager.getConnection (connectString); connection.setTransactionIsolation(Connection. TRANSACTION_READ_UNCOMMITTED); return connection; } public static ResultSet getResultSet() throws SQLException { System.out.println(call to getResultSet()); return new Test().connect().createStatement().executeQuery(select 1 as x); } } Output: creating view... call to getResultSet() call to getResultSet() call to getResultSet() selecting from view... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() creating view (deterministic table function)... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() selecting from view (deterministic table function)... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() Kind regards and thanks in advance Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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/groups/opt_out.
[h2] Tablefunctions called too often
Hi, In my project we use H2 table functions to read data from other databases. I found out that H2 calls our table functions more often than expected, which can lead to serious performance problems if the execution of the SQL from the other database is quite expensive. The sample code below creates a table function, and creates a wrapping view for it. During creation of the view, H2 calls the table function 3 times, and when selecting from the view again 4 times. I also tried to create the table function with the DETERMINISTIC flag but that made things even worse (6 call when creating the view, 7 when selecting from it) Is it really required that H2 call the function more than once for each operation? Is it possible to configure this some how, or is there a bug in my code? Code: public class Test { public static void main(String[] args) throws SQLException { new Test().run(); } private void run() throws SQLException { Connection connection = connect(); Statement st = connection.createStatement(); st.execute(create alias MYTF for \Test.getResultSet\); System.out.println(creating view...); st.execute(create view MVIEW as select * from MYTF()); System.out.println(selecting from view...); st.executeQuery(select * from MVIEW); st.execute(create alias MYTF2 deterministic for \Test.getResultSet\); System.out.println(creating view (deterministic table function)...); st.execute(create view MVIEW2 as select * from MYTF2()); System.out.println(selecting from view (deterministic table function)...); st.executeQuery(select * from MVIEW2); } public Connection connect() throws SQLException { String connectString = jdbc:h2:mem: + H2 + ;DB_CLOSE_DELAY=-1;QUERY_CACHE_SIZE=0; try { Class.forName(org.h2.Driver); } catch (ClassNotFoundException ex) { throw new SQLException(ex.getMessage(), ex); } Connection connection = DriverManager.getConnection (connectString); connection.setTransactionIsolation(Connection. TRANSACTION_READ_UNCOMMITTED); return connection; } public static ResultSet getResultSet() throws SQLException { System.out.println(call to getResultSet()); return new Test().connect().createStatement().executeQuery(select 1 as x); } } Output: creating view... call to getResultSet() call to getResultSet() call to getResultSet() selecting from view... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() creating view (deterministic table function)... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() selecting from view (deterministic table function)... call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() call to getResultSet() Kind regards and thanks in advance Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl -- 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/groups/opt_out.
Re: Memory Usage Problem
Hi Thomas, setting the query cache to 0 solved the problem. With a size of 1 the cache still consumed 70MB. The largest statement is approximately 140KB long. The statement is not a prepared statement. The statement outer joins two tables containing approximately 2 rows/50 columns (Oracle's ALLT_TABLES view was created as a table with all data within a H2 database) and calculates the number of differences in the records (it's a kind of validation for a data migration). If the cache just stores the statements, then I wonder why it is so huge, when the statement is only 140 KB. Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Thomas Mueller thomas.tom.muel...@gmail.com To: H2 Google Group h2-database@googlegroups.com Date: 08.05.2013 21:49 Subject:Re: Memory Usage Problem Sent by:h2-database@googlegroups.com Hi, There is not just one cache. It's in another cache, the queryCache, not the regular row cache. See http://h2database.com/javadoc/org/h2/constant/DbSettings.html?highlight=queryCacheSizesearch=queryCache#QUERY_CACHE_SIZE However, I wonder what data is cached in the query cache. How large are your query statements? Regards, Thomas On Wed, May 8, 2013 at 5:34 PM, christoff.schm...@finaris.de wrote: I did so, but as i see it, that setting does not have any effect for in memory DBs as I use it. Cache Settings The database keeps most frequently used data in the main memory. The amount of memory used for caching can be changed using the setting CACHE_SIZE. This setting can be set in the database connection URL ( jdbc:h2:~/test;CACHE_SIZE=131072), or it can be changed at runtime using SET CACHE_SIZE size. The size of the cache, as represented by CACHE_SIZE is measured in KB, with each KB being 1024 bytes. This setting has no effect for in-memory databases. For persistent databases, the setting is stored in the database and re-used when the database is opened the next time. However, when opening an existing database, the cache size is set to at most half the amount of memory available for the virtual machine (Runtime.getRuntime().maxMemory()), even if the cache size setting stored in the database is larger; however the setting stored in the database is kept. Setting the cache size in the database URL or explicitly using SET CACHE_SIZE overrides this value (even if larger than the physical memory). To get the current used maximum cache size, use the query SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'info.CACHE_MAX_SIZE' An experimental scan-resistant cache algorithm Two Queue (2Q) is available. To enable it, append ;CACHE_TYPE=TQ to the database URL. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. Also included is an experimental second level soft reference cache. Rows in this cache are only garbage collected on low memory. By default the second level cache is disabled. To enable it, use the prefix SOFT_. Example: jdbc:h2:~/test;CACHE_TYPE=SOFT_LRU. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. To get information about page reads and writes, and the current caching algorithm in use, call SELECT * FROM INFORMATION_SCHEMA.SETTINGS. The number of pages read / written is listed. Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From:Noel Grandin noelgran...@gmail.com To:h2-database@googlegroups.com Cc:christoff.schm...@finaris.de Date:08.05.2013 15:59 Subject:Re: Memory Usage Problem I could just tell you, but that would deprive you of discovering the excellent search feature on our website. On 2013-05-08 15:22, christoff.schm...@finaris.de wrote: Hi, I use H2 database as embedded database in memory. The queries processed by the database are quite large, as result my memory profiler tells me that the queryCache (Type org.h2.util.SamllLRUCache) member of an instance of org.h2.engine.Session consumes about 100 MB heap space. Is there a possibility to limit that cache size? Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http
Memory Usage Problem
Hi, I use H2 database as embedded database in memory. The queries processed by the database are quite large, as result my memory profiler tells me that the queryCache (Type org.h2.util.SamllLRUCache) member of an instance of org.h2.engine.Session consumes about 100 MB heap space. Is there a possibility to limit that cache size? Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com === Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873 Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl === -- 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.
Re: Memory Usage Problem
I did so, but as i see it, that setting does not have any effect for in memory DBs as I use it. Cache Settings The database keeps most frequently used data in the main memory. The amount of memory used for caching can be changed using the setting CACHE_SIZE. This setting can be set in the database connection URL ( jdbc:h2:~/test;CACHE_SIZE=131072), or it can be changed at runtime using SET CACHE_SIZE size. The size of the cache, as represented by CACHE_SIZE is measured in KB, with each KB being 1024 bytes. This setting has no effect for in-memory databases. For persistent databases, the setting is stored in the database and re-used when the database is opened the next time. However, when opening an existing database, the cache size is set to at most half the amount of memory available for the virtual machine (Runtime.getRuntime().maxMemory()), even if the cache size setting stored in the database is larger; however the setting stored in the database is kept. Setting the cache size in the database URL or explicitly using SET CACHE_SIZE overrides this value (even if larger than the physical memory). To get the current used maximum cache size, use the query SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME = 'info.CACHE_MAX_SIZE' An experimental scan-resistant cache algorithm Two Queue (2Q) is available. To enable it, append ;CACHE_TYPE=TQ to the database URL. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. Also included is an experimental second level soft reference cache. Rows in this cache are only garbage collected on low memory. By default the second level cache is disabled. To enable it, use the prefix SOFT_. Example: jdbc:h2:~/test;CACHE_TYPE=SOFT_LRU. The cache might not actually improve performance. If you plan to use it, please run your own test cases first. To get information about page reads and writes, and the current caching algorithm in use, call SELECT * FROM INFORMATION_SCHEMA.SETTINGS. The number of pages read / written is listed. Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com From: Noel Grandin noelgran...@gmail.com To: h2-database@googlegroups.com Cc: christoff.schm...@finaris.de Date: 08.05.2013 15:59 Subject:Re: Memory Usage Problem I could just tell you, but that would deprive you of discovering the excellent search feature on our website. On 2013-05-08 15:22, christoff.schm...@finaris.de wrote: Hi, I use H2 database as embedded database in memory. The queries processed by the database are quite large, as result my memory profiler tells me that the queryCache (Type org.h2.util.SamllLRUCache) member of an instance of org.h2.engine.Session consumes about 100 MB heap space. Is there a possibility to limit that cache size? Regards, Christoff Schmitz F I N A R I S Financial Software Partner GmbH Sömmerringstrasse 23 60322 Frankfurt am Main Fon: +49 (0)69 / 254 98 - 24 Mobile: +49 (0)176 / 206 34 186 Fax: +49 (0)69 / 254 98 - 50 eMail:mailto:christoff.schm...@finaris.de www: http://www.finaris.de und http://www.rapidrep.com === Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner GmbH Sömmerringstr. 23, 60322 Frankfurt/Main, Germany Registered at Frankfurt/Main, HRB 52873 Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl === -- 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. === Disclaimer The information contained in this e - mail and any attachments ( together the message) is intended for the addressee only and may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify the sender and do not copy or distribute it or disclose its contents to anyone. FINARIS Financial Software Partner