[h2] Re: Exception after the update from 1.4.197 to 1.4.200
Hello. It looks like some kind of corruption caused by unclean shutdown. If you have a backup copy of the database before the migration, open it with 1.4.197 and use SCRIPT TO 'filename.sql' to export the database to a script. After it create a new database with 1.4.200 and execute RUNSCRIPT FROM 'filename.sql' in it. https://h2database.com/html/tutorial.html#upgrade_backup_restore Automatic upgrade is not always clean. If you don't have a backup copy and 1.4.197 can't open that database too try the recover tool: https://h2database.com/html/advanced.html#using_recover_tool -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/dac189ab-0d8e-481e-8995-a010a147d51e%40googlegroups.com.
[h2] Re: Connect to h2 server using SSL
Oracle disabled anonymous suits due to security reasons. https://bugs.openjdk.java.net/browse/JDK-8213090 You you can't use older JRE you need to create a key store and set system properties javax.net.ssl.keyStore and javax.net.ssl.keyStorePassword. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c2f75013-d440-4554-88f4-d54056b872b4%40googlegroups.com.
[h2] Re: A negative scale value produces incorrect precision and scale values
Hello. Scale should be unsigned according to the SQL Standard, so such definition is invalid. On the other hand, H2 supports the negative scale in numeric data types and such support was broken by accident. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e27b1507-774a-43b1-97e2-3d419fcc3798%40googlegroups.com.
[h2] Re: using ALIAS function in a select
Hello. Please, take a look on the documentation of identifiers: https://h2database.com/html/grammar.html#name You need to use "firstDay" and "lastDay" with double quotes instead, because "firstDay" and firstDay = "FIRSTDAY" are different identifiers. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1225f458-69e0-4299-9835-38f73ca80cda%40googlegroups.com.
[h2] Re: Interval day to second feature not supported in H2 1.4.199
According to the SQL Standard, result of subtraction of one datetime value from another is an interval, but Oracle violates the Standard and returns a DATE JULIAN for (DATE - DATE) and NUMBER for (TIMESTAMP - TIMESTAMP) for historic reasons. H2 does not provide compatibility on that level, that's too much, H2 is not an emulator of other databases. It works in older versions of H2 only because they also violate the Standard and incorrectly return BIGINT values from that subtraction operation. H2 was fixed in 1.4.198, so you can try to use 1.4.197 if you cannot rewrite your query in some correct way. Unfortunately, it looks like Oracle, at least version 18c, doesn't support the datetime and interval arithmetic from the Standard well enough and you need different queries for Oracle and H2. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d3356451-61b9-4dfa-b490-c02d67b906b1%40googlegroups.com.
[h2] Re: Interval day to second feature not supported in H2 1.4.199
Hello. I think it can be caused only by invalid SQL that was used with the old version of H2 as a workaround over its missing features, and that workaround doesn't work any more; or by some other invalid SQL expression that somehow worked in the old version. You need to show us your query, without it we can only guess what can be wrong. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/959b2dfc-7080-4503-b18a-bc04d187d4ba%40googlegroups.com.
[h2] Re: Connect to h2 server using SSL
Hello. H2 uses the anonymous SSL by default on older Java versions up to 10, unless a security manager prevents it. It also doesn't work on Java 11+ due to changes in JDK. https://github.com/h2database/h2database/issues/1303 I have multiple installations of different versions of Java, and in one of them exception like yours appears, but it looks like a problem with that specific JRE. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/17a46b73-eba7-4ca3-afed-eff0ea1d4625%40googlegroups.com.
[h2] Re: ALIAS function not supporting Number parameter type
Hello. java.lang.Number is an abstract class in Java that has different implementations, such as `BigInteger`, `BigDecimal`, wrappers over primitive data types and you can create your own implementations too. So, technically, it can hold everything. This data type is not supported by JDBC. It cannot be supported as a number, because it can hold different not really compatible with each other objects. Its mapping to a JAVA_OBJECT looks like being perfectly valid. Note that NUMBER data type from SQL is mapped to a BigDecimal in JDBC specification and in H2. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a9dcf9cc-bb34-4df9-a975-796702803282%40googlegroups.com.
[h2] Re: Can't create full text index (H2: v1.4.200 , lucene: v7.7.2 or v8.2.0)
The situation is even worse, the binary compatibility with 6 is broken too. So you need to use 5.5.5 (it was used for compilation of official release of H2 1.4.200), or you can build H2 from its current sources if you definitely need a newer version of Lucene for a some reason. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/595cf338-0715-433e-8f80-728acc0e20f5%40googlegroups.com.
[h2] Re: Can't create full text index (H2: v1.4.200 , lucene: v7.7.2 or v8.2.0)
Oh, I see. We definitely have a problem. Try some older version of Lucene such as 6.6.*. 7.0.0 and newer versions are source compatible but binary incompatible, if you need them, you need to recompile H2 with that version. I'll try to fix the binary compatibility for a next version of H2. I filled a new issue on GitHub: https://github.com/h2database/h2database/issues/2179 -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ae58f47f-a48a-4514-886f-09935a8fec7f%40googlegroups.com.
[h2] Re: Can't create full text index (H2: v1.4.200 , lucene: v7.7.2 or v8.2.0)
Hello. I cannot reproduce this issue with Lucene 7.7.1 and Lucene 8.0.0. I can test 7.7.2 and 8.2.0 too, but I don't think that results will be different. You need to click on the error message to see the complete stack trace, it could describe the situation better. Most likely you don't really have lucene-core-*.jar, lucene-analyzers-common-*.jar, and lucene-queryparser-*.jar (all three jars with the same version are necessary) in the classpath of the Console. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e13c61e3-a290-4d49-ad70-e02953aa8bbf%40googlegroups.com.
[h2] Re: VARCHAR comparison and trailing spaces
In some other use cases such strength is required for normal operation and it is configurable in H2, but only in a new empty database, to change it later database needs to be exported to SQL, edited (with some stream editor) and re-created. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d9e2a93f-3efe-4491-ab9f-632f11ddae8e%40googlegroups.com.
[h2] Re: VARCHAR comparison and trailing spaces
Hello. The current version of the SQL Standard is SQL:2016 plus corrigenda and additional parts from 2019. Please, don't talk about SQL-92, it is completely outdated, there are six (!) versions that were published after it. I don't have the very recent version of ISO/IEC 9075-2, but in previous versions in section “Comparison of character strings”, comparison operations are delegated to the collation, and definition of comparison predicate talks about the same. Technically the standard still requires padding, but it should be a character that is not defined in the character set and that is smaller than any character from the character set if collation has NO PAD characteristic. Supported collations are implementation-defined in the Standard, so you can't assume that all databases will work in the same way. >From my point of view PRIMARY collation shouldn't be the default, it is too relaxed for normal use cases, it will break many applications. I already saw such problems when a custom collation were used (for non-English languages) and this strength was accidentally chosen. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/373879c2-baa3-4fa3-b036-23491253778d%40googlegroups.com.
Re: [h2] Re: Inserting text into a blob in H2
BLOB or CLOB values in in-memory database? Bad idea in general, when you use an in-memory database, it should be small enough. If you want to connect to the database from another process, you need to start a TCP Server inside of the process that owns the database. org.h2.tools.Server tcp = org.h2.tools.Server.createTcpServer(…); tcp.start(); Another process should use the remote URL to connect to this server. You can also launch a separate H2 Server process and connect to it from both your applications. If you use Hibernate, you can use Hibernate.getLobCreator(…) I guess. Why you want to mix different methods to deal with the database? -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c63549c4-8ab6-4d61-854a-9383d0afcc65%40googlegroups.com.
[h2] Re: Inserting text into a blob in H2
Hello. H2 is not different from others, JDBC has java.sql.Blob interface that should be used, use Collection.createBlob() to allocate a BLOB instance, use one of its methods to put your data into it, and use PreparedStatement.setBlob() to pass this BLOB to the command. There are also an alternative PreparedStatement.setBlob() methods that take a java.io.InputStream as a parameter. If you data is small enough, you can use the byte[] with PreparedStatement.setBytes(). If you can build H2 from its current sources, you can use the JSON data type instead of BLOB, but it is not designed for really large JSON documents. If you need to store large JSON data in H2, please let us know. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6a8a057f-e96b-4e83-8025-0a632aae4243%40googlegroups.com.
[h2] Re: "SELECT ... WHERE ... IN" on extremely large tables
BTW, what version of H2 do you use? IN (query) condition needs a lot of memory in 1.4.197 and older versions. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e7d29e7c-dbac-4aed-b562-1e1a499760ff%40googlegroups.com.
[h2] Re: Failed to change "not null" option on a column on version 1.4.199
Hello. Your command is definitely not valid. Where did you find it? We don't have such syntax in the documentation. It was accepted due to bug. Use ALTER TABLE tableName ALTER COLUMN columnName SET NOT NULL instead. The SET DATA TYPE clause may be used only to change the data type of the column, it is officially supported since 1.4.198. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9a983220-552e-417e-a1ed-0feec726f87a%40googlegroups.com.
[h2] Re: Compatibility modes
Hello. On Sunday, 29 September 2019 18:09:22 UTC+8, pr1 wrote: > > Hello, > could someone explain the compatibility modes in H2? > If you use the Oracle-db driver in H2, must you write you SQL statements > using the Oracle-SQL syntax? > What exactly do you mean? If you use the H2 Console to connect to the Oracle, you work with Oracle and should use its syntax, H2 will not translate anything, and the database engine of H2 will not be used. If you create a linked table from an Oracle database inside a H2 database it does not affect the syntax of H2 in any way. If you use the Oracle compatibility mode of H2 database you don't need Oracle's driver, it is not used. The behavior of H2 in compatibility modes is different from regular mode as described in the documentation: https://h2database.com/html/features.html#compatibility Some differences may stay undocumented. The compatibility modes provide only very limited compatibility. H2 is not an emulator of other databases. You can try to use the compatibility mode in an application that were designed to work with Oracle, but usually you need to adjust its SQL queries anyway. Compatibility modes are usually used for unit testing of applications that normally work with other databases. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e3cdcc18-c507-4899-9c69-3e10032a60d7%40googlegroups.com.
[h2] Re: Admin rights issue
Hello. When you created this database a user with ADMIN rights were created using the user name and password that were specified to create a first connection. You need to use its user name and password to connect to the database. After it you can either execute this script under that account or give ADMIN privileges to your current user using ALTER USER userName ADMIN TRUE https://h2database.com/html/commands.html#alter_user_admin If you lost the credentials of your initial ADMIN user you can't do anything from the SQL. You need to use the recovery tool. https://h2database.com/html/advanced.html#using_recover_tool -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f3eb42b0-bbbc-46b7-9fec-8624dd877210%40googlegroups.com.
[h2] Re: Generic H2 Configuration Gone
Hello. You can type a some name of the setting, org.h2.Driver as a driver, your JDBC URL and user name and save such custom setting. Predefined settings aren't intended to be used as is, they are just templates. Another solution is to edit the configuration file. The configuration is stored in the file .h2.server.properties (note the leading dot) in your home directory. You can create them by appending SOME_NUMBER=Generic H2 (Embedded)|org.h2.Driver|jdbc\:h2:~/test|sa SOME_NUMBER=Generic H2 (Server)|org.h2.Driver|jdbc\:h2\:tcp\: //localhost/~/test|sa Replace SOME_NUMER with some real numbers, presets with higher number are listed at the first positions. If you don't have any custom settings in this file, you can also delete it, it will be recreated with default settings on startup of the H2 Console. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e877cc9e-2685-400f-bd09-d4f0b63163f6%40googlegroups.com.
[h2] Re: Drop a database
Hello. If you have a connection, use DROP ALL OBJECTS DELETE FILES https://h2database.com/html/commands.html#drop_all_objects If there are no active connections, you can delete file(s) with DeleteDbFiles or by yourself. https://h2database.com/javadoc/org/h2/tools/DeleteDbFiles.html -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3781da8d-0725-48af-8202-b4f3d6ffb898%40googlegroups.com.
[h2] Re: H2 1.4.196 and free space allocated
Hello. 1.4.196 is an old unsupported version. You should try 1.4.199 first, it has some improvements in that area. However, MVStore is not the best storage for massive insertions quickly followed by massive updates and massive deletions and so on. It is more suitable for normal use cases. It also provides much better scalability for multi-threaded access. Old storage backend (PageStore) is still available, you can use it if you wish. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/dad499fd-0a7c-4305-bf0c-4bf1ee1f302e%40googlegroups.com.
[h2] Re: Can CSVREAD use direct data as argument?
That's not supported. You can create an own user-defined function and use Csv.read(Reader, String[]) in it as a workaround, but if you'll do so, you will need to check compatibility of your code with next versions of H2 before an upgrade. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9ff97246-88ec-4a8a-84a7-79d3aaded352%40googlegroups.com.
[h2] Re: CSVREAD looses column (1.4.196)
Sorry, it isn't fixed yet. Of course, you may submit a pull request with a fix if you wish. The relevant code is in org.h2.tools.Csv. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1e2ec566-5458-4508-b187-2fb3d966b7d3%40googlegroups.com.
[h2] Re: CSVREAD creates uppercased column names for single word names
On Saturday, 14 September 2019 04:28:30 UTC+8, George Ivanov wrote: > > sure! should one use caseSensitiveColumnNames anywhere else too? > I'm not sure what do you mean, but if you use quoted names of columns (" ColumnName") in your SQL commands, they are case sensitive by default. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ef3a461e-1896-4f11-9444-27f164432c3c%40googlegroups.com.
[h2] Re: CSVREAD creates uppercased column names for single word names
I meant caseSensitiveColumnNames=true, of course. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/5edcb69c-a3e5-4028-884a-3d8592ae60a2%40googlegroups.com.
[h2] Re: CSVREAD creates uppercased column names for single word names
No, it's not a bug. Such behavior is documented, but in obscure way: https://h2database.com/html/functions.html#csvread Use caseSensitiveColumnNames option if you want to preserve case of column names unconditionally. CSVREAD('filename.csv', NULL, 'caseSensitiveColumnNames=false') -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/bb021eda-63dc-4590-84fe-ccf6cec543bb%40googlegroups.com.
Re: [h2] My sequences are not in the correct order
> > Do you have an option to enable this on h2? > What exactly do you want to enable? You can use GENERATED BY DEFAULT AS IDENTITY (CACHE 1) instead of AUTO_INCREMENT to disable the cache of the sequence. The default cache size is 32. Cached identities may be lost on abnormal termination of the process. However, you should check why your application does not shutdown the database properly. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3aa90f02-f140-48ef-aac5-fc077710d558%40googlegroups.com.
Re: [h2] Re: LINEKED TABLE contains a select query to a row name with slachs
Could you try to build H2 from its current sources and re-test your code? Sources are here: https://github.com/h2database/h2database Building instructions are here: https://h2database.com/html/build.html#building You need the jar target. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/bf390ba4-a0e0-4ad5-8b7f-09c7180f6eeb%40googlegroups.com.
[h2] Re: LINEKED TABLE contains a select query to a row name with slachs
Hello. It looks like the problem is caused by H2's attempt to fetch metadata of the source table. H2 tries to pass its name as is even when it definitely is a query and not a plain name. I filled an issue about it on GitHub: https://github.com/h2database/h2database/issues/2073 I don't think that there are workarounds if you really need to use that query for a some reason, but you can try to create a link to the whole table by specifying its name instead of such query. Do you really need to have a column with such unusual name? It's just asking for trouble. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e20cd825-5957-4700-90b3-bcdbdcfca153%40googlegroups.com.
[h2] Re: PREPAREDSTATMENT - SCRIPT COLUMNS HELP
You don't need a COLUMNS clause. It only changes insert statements from INSERT INTO TBL VALUES (1, 2, 3) to INSERT INTO TBL (A, B, C) VALUES (1, 2, 3), it can be useful if you want to load the script into some other database that don't support insert statements without names of columns. I only need a script with all my database inserts. Unfortunately, H2 doesn't export such scripts. And I don't think that such script will be suitable for your use case, that was described here: https://groups.google.com/forum/#!topic/h2-database/1j9OAZpy5NA -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/5ea23b0d-2a3d-40c8-970f-12f3b18c82ca%40googlegroups.com.
[h2] Re: UPDATE MY DATABASE WITH BACKUPSCRIPT
Hello. H2 can export a database in two formats. 1. Regular backup (BACKUP TO 'filename.zip'). You can unpack the generated archive and use it as a database. 2. Export format (SCRIPT TO 'filename.sql'). You can execute it in a new empty database with RUNSCRIPT FROM 'filename.sql'. So you need to delete an existing database. If the database is not damaged and you have an active connection to it, you can execute SET EXCLUSIVE 2; DROP ALL OBJECTS; RUNSCRIPT FROM 'filename.sql'; SET EXCLUSIVE 0; You don't need to go to the exclusive mode and back if you don't have other connections. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1f382994-4992-4cea-bd0e-0c4c106bf787%40googlegroups.com.
Re: [h2] Re: CSVREAD looses column (1.4.196)
Both lines have 3 columns, it's just a bug in Csv.readHeader() / Csv.readValue(). -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/765a319a-49db-4662-9c7c-806c68b60337%40googlegroups.com.
[h2] Re: CSVREAD looses column (1.4.196)
Hello. Yes, it looks like a bug. You can use some non-empty name of the last column as a workaround. A,B,C 0, 1, 2 -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/68ecad47-d4ba-4fd0-9863-532ae0da244d%40googlegroups.com.
[h2] Re: JdbcSQLNonTransientException: Unknown data type
Hello. H2 compiles your query immediately, so it needs to know the data types of *some* arguments. The usual workaround is to wrap the parameter into CAST function. This function doesn't need to know the type of its argument. SELECT * FROM (SELECT CAST(? AS INT)) Of course, you need to know the data types of such parameters. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0bdbbf7d-7dd0-4b1e-9d1e-a2ba6516e84c%40googlegroups.com.
[h2] Re: Syntax error in 1.4.197
BTW, take a look on the section “Prepared Statements and IN(...)” in the documentation: https://h2database.com/html/performance.html#database_performance_tuning Please note that ANY(?) is a non-standard PostgreSQL-style feature that is supported since H2 1.4.197. It can be not suitable for cross-database applications. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ad4acf2d-6eb1-4941-b1a5-bdf1d2f2c477%40googlegroups.com.
[h2] Re: Syntax error in 1.4.197
Hello. IN predicate with empty list is not valid according to the SQL Standard. However, some databases, including H2, support it. H2 1.4.193 and later versions don't allow it in DB2, MySQL, Oracle, and PostgreSQL compatibility modes because these databases don't allow it. When you use a compatibility mode you shouldn't rely on the H2-specific features. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e27e263f-9de1-495c-9c82-002bae6eccf0%40googlegroups.com.
[h2] Re: DROP COLUMN cause table table to disappear
1.4.196 and older versions have a problem with some constraints. This issue was fixed in 1.4.197, but the fix isn't fully compatible with older databases. Execution of DDL command in 1.4.197 in the database that was initially created by some older version may corrupt a database. This issue was fixed for default MVStore engine and wasn't fixed for legacy PageStore engine (there is a separate issue on GitHub for it). I suggest you to use the recommended way to upgrade your databases. 1. Execute a SCRIPT TO 'filename.sql' command with old version of H2. 2. Create a new empty database with a new version of H2 and execute a RUNSCRIPT FROM 'filename.sql' You can also use the Java tools as described in the documentation: https://h2database.com/html/tutorial.html#upgrade_backup_restore Usually it's not really required and database can be used as it, but due to large amount of changes in recent releases it's much safer to use the recommended way. If you don't have a backup copy you can add SET_EVENT_LISTENER parameter to the connection URL: https://h2database.com/html/commands.html#set_database_event_listener The listener must implement org.h2.api.DatabaseEventListener interface. With such listener some exceptions can be ignored. You need to check a content of the database with H2 Console and fix the problems. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/7d35d455-f1ae-4a77-8c59-d5740b0c92f1%40googlegroups.com.
[h2] Re: H2 Trigger doesn't Trigger properly
OK, let's test it with JDBC: import java.sql.*; import org.h2.api.Trigger; public class TestTrigger { public static class T implements Trigger { @Override public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before, int type) throws SQLException {} @Override public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException { newRow[1] = 1; } @Override public void close() throws SQLException {} @Override public void remove() throws SQLException {} } public static void main(String[] args) throws Exception { try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) { Statement s = c.createStatement(); s.execute("CREATE TABLE NOTES(" // + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " // + "VERSION INT)"); s.execute("CREATE TRIGGER T1 BEFORE INSERT ON NOTES FOR EACH ROW CALL \"" + T.class.getName() + '"'); s.executeUpdate("INSERT INTO NOTES() VALUES ()"); s.executeUpdate("INSERT INTO NOTES(VERSION) VALUES (5)"); c.setAutoCommit(false); s.executeUpdate("INSERT INTO NOTES() VALUES ()"); s.executeUpdate("INSERT INTO NOTES(VERSION) VALUES (10)"); c.commit(); try (ResultSet rs = s.executeQuery("TABLE NOTES")) { while (rs.next()) { System.out.printf("ID=%d, VERSION=%d%n", rs.getLong(1), rs.getInt(2)); } } } } } With the latest version of H2 (1.4.199) it prints ID=1, VERSION=1 ID=2, VERSION=1 ID=3, VERSION=1 ID=4, VERSION=1 So such trigger works as expected. Because you're using some implementation of JPA your problem seems to be related with it and not with H2. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9854ef44-b694-40ae-a6e2-4a3fe7c2701d%40googlegroups.com.
Re: [h2] Unit test result differs in 1.4.188 Vs 1.4.198
If you have a problem with some implementation of JPA it's better to post your question on StackOverflow with appropriate tags or in some other place where your question will be visible by many people familiar with JPA, Hibernate and other related stuff. I have no idea how to while a correct query with API that you use. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/ca12190f-a835-4295-a3f8-1675ab64b061%40googlegroups.com.
Re: [h2] Unit test result differs in 1.4.188 Vs 1.4.198
I assume you understand that order of results in queries without ORDER BY can be changed again at any time. It's better to change tests in order-independent way. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4d913b12-ba05-470a-8060-e825dfb83a1b%40googlegroups.com.
Re: [h2] Unit test result differs in 1.4.188 Vs 1.4.198
Most likely the results will be the same as in 1.4.199. But 1.4.199 is more reliable in other areas. You can also sort the results by your own in your test cases. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/128a505a-227e-49cc-8c28-b7afe76cb8f7%40googlegroups.com.
Re: [h2] Unit test result differs in 1.4.188 Vs 1.4.198
1.4.197 and 1.4.198(199) are huge releases with a lot of changes. BTW, don't use 1.4.198, this release has many regressions, it was replaced with 1.4.199. Some queries may return result in different orders on Java 7 and on later versions. Some queries may change the order of results depending on different unpredictable factors, such as amount of available memory. You need to change your tests to accept results in any order or change your queries by including the ORDER BY clause with all necessary columns. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/1cd98e8a-bc82-4ffe-b66a-2b880fa4b382%40googlegroups.com.
[h2] Re: H2 Trigger doesn't Trigger properly
Hello. On Sunday, 4 August 2019 23:48:40 UTC+8, Piotr wrote: > > I've created trigger: > > CREATE TRIGGER update_date Before INSERT ON notes FOR EACH ROW CALL > TriggerBeforeInsert.class.getName() > > I would like to set the version field to 1 whenever a new record is > inserted. Here is the code for the Trigger: > > public class TriggerBeforeUpdate implements Trigger { > > TriggerBeforeInsert and TriggerBeforeUpdate are different classes. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9fb76b3a-aec6-4aa3-b81f-a880d18584b0%40googlegroups.com.
[h2] Re: error
Hello. It's just a bad error message, it shouldn't mention the IFEXISTS parameter. The next version will throw a better message. Recent versions of H2 don't allow remote creation of databases any more due to security reasons. You need to create the new empty database in some other way. https://h2database.com/html/tutorial.html#creating_new_databases -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/00b2e6c9-d410-4d4b-9237-5ce9c2fec3ea%40googlegroups.com.
[h2] Re: Investigation on the diffusion of innovation along with java releases
Hello. I think you don't understand well what is API of a database engine or its driver and what is not. Code of H2 is not used directly with few exclusions. Databases are accessed by Java programs with a JDBC. JDBC is described by the JDBC Specification and its classes are located in java.sql and javax.sql packages. We do not control development of JDBC in any way. There are some modern alternatives for JDBC such as ADBA and R2DBC, but they are still in development and don't have stable API, so we don't provide drivers for them. However, R2DBC community has an own driver for H2. H2 is compatible with Java 7 and Android API 21 (and some people still use old versions on Java 6), but H2 provides new features such as JSR-310 datetime data types on Java 8+. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/6e386ecc-d163-45e7-90fe-c3d575e65323%40googlegroups.com.
[h2] Re: Migrating from 1.3.x to 1.4.196
Hello. 1. You use an old unsupported version with known issues, including issue with unreasonably large databases after execution of huge queries. Only issues with 1.4.199 are accepted. Note that there are some issues with automatic upgrade, it's safer to create a new database and populate it with SQL script that was exported by the old version. 2. MVStore is a copy-on-write storage that persist every transaction for at least 45 seconds by default. It can use more space than old PageStore engine. 3. Database file after migration can be not yet compacted depending on many factors and size difference can be caused by such situation. You can try to use SHUTDOWN COMPACT after import, it can help in some cases. 4. If you don't like the new storage engine and you don't need row-level locks and high concurrency you can continue to use the old PageStore engine in new versions of H2. Beware: with recent versions it's enough to add ;MV_STORE=FALSE to database URL to create a new database with this engine, but in older versions, including 1.4.196, you need to add ;MV_STORE=FALSE;MVCC=FALSE because PageStore can be corrupted if MVCC is enabled. New versions don't have the MVCC setting any more and use MVCC only with MVStore engine. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/72a203ae-848f-4535-a760-c09dfe86efca%40googlegroups.com.
[h2] Re: Password
Hello. If you have an icon of H2 Console in the system tray, you can launch a browser session from it by double click or from its context menu. In this session you will be able to use these utilities without an additional authentication and also will be able to create a new database. If you launch the H2 Console in some other way, you have to set a password for it explicitly: https://h2database.com/html/tutorial.html#console_settings There is no default due to security reasons. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/46fd9f29-d167-45a8-a6c4-ec4d28509b26%40googlegroups.com.
[h2] Re: the database grows
Maybe your SELECT statement returns some LOBs or it has FOR UPDATE clause? Otherwise it should not affect the size of database in any way. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/58d71870-4b3a-454b-950c-df49e8997b78%40googlegroups.com.
[h2] Re: 1.3.172 vs 1.4.199
Hello. Your version is very old. Automatic upgrade is not always clean. You need to export your database to SQL with a SCRIPT TO 'filename.sql' command using 1.3.172. After in you can create a new database with 1.4.199 and execute RUNSCRIPT FROM 'filename.sql' to populate it with your data. See also https://h2database.com/html/tutorial.html#upgrade_backup_restore Note that 1.4.199 uses another storage engine by default, it allows concurrent access from multiple threads and transactions, but it isn't as mature as an old engine. However, you can still use the old storage engine if you wish. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/677cdf81-c547-4e63-8cac-4e70891b1613%40googlegroups.com.
[h2] Re: mapping interval to duration
Hello. You need to use getObject(1, Duration.class); -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/649234ed-99ed-47cd-824e-ce424454a5e4%40googlegroups.com.
Re: [h2] Corrupt Database Chunk Wont Recover
Building instructions are here: https://h2database.com/html/build.html#building However, you you can load the sources from h2/src/main into any Java IDE and launch the recovery process directly from the IDE. When you see a failure that isn't handled automatically you can add some try-catch blocks to the code and run the recovery process again. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/adce7095-4ba2-45f5-a7c5-0fb5457bd43b%40googlegroups.com.
[h2] Re: How do I safely interrupt a mid flight transaction?
Thread.interrupt() is a way to database corruption. Take a look on the SHUTDOWN command: https://h2database.com/html/commands.html#shutdown You can execute this command and wait for its completion. After its execution you can interrupt your threads as you wish. Of course, you need to prevent allocation of new connections after this command, because a new connection can open the database again. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/12c16c7c-9f1a-4d61-9972-ed4e87418661%40googlegroups.com.
[h2] Re: Function "LEAD" not found.
Hello. Window functions aren't available in old versions of H2, only 1.4.198 and 1.4.199 have them. 1.4.198 has a lot of regressions, so use 1.4.199. Online documentation in both HTML and PDF formats covers only the latest available version. When you use some older version you need to use the documentation from the ZIP distribution of that version. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/cdb17158-5284-4f28-b4a4-e1a056f1ca1b%40googlegroups.com.
Re: [h2] H2 corrupted DB
> > We do actually make a copy of the file, but we call SET EXCLUSIVE 1 before > we do that. It does not matter, such copy may still be unusable. Database can write something in background and copy can read inconsistent older and newer data. If you really want to copy the file, you need to shutdown the database completely and somehow make sure that it will not be opened again while file is being copied. The normal way for regular backup procedure is a BACKUP command: https://h2database.com/html/commands.html#backup -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/605e70f3-08b0-47b6-ba20-a7ca772a502c%40googlegroups.com.
[h2] Re: Full text Lucene not working with last version
> > The indexes I created using the older versions don't seem to work anymore. They aren't compatible with newer versions of Lucene. They should be re-created, but it may take some time. We are still considering sticking to the older version (1.3.160 with > lucene-core 3.4.0 for compatibility with all the users we have so far). > There are a few issues we have when using this version When you use historic versions of H2 you're on your own. SELECT COUNT(*) FROM FTL_SEARCH_DATA("+beton", 0, 0) returns about 70 results, although the column "NORMADESCRIERE" in > "BURSUCUL.NORMA" is indexed The second argument of FTL_SEARCH_DATA is a limit. The javadoc of FullTextLucene.seach() is not correct, if 0 is specified the actual limit is about 100. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/bcebd330-10c6-4ab7-97ba-28c2f20733c8%40googlegroups.com.
[h2] Re: Full text Lucene not working with last version
Hello. There is a bug in latest releases of H2 and very recent releases of Lucene aren't really supported. You can build H2 from its current sources if you need them for a some reason. Lucene 5.5.5 should work in released versions properly. Exception that you see means that your classpath doesn't contain all necessary libraries. You need the following three libraries for H2: lucene-core-5.5.5.jar lucene-analyzers-common-5.5.5.jar lucene-queryparser-5.5.5.jar -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/12d247a6-1790-48f1-a152-0f9b91d58cb1%40googlegroups.com.
[h2] Re: Corrupted database - new scenario
Unfortunately, this tool exports medatadata (that may contain unquoted identifiers) as is. Databases that were created by 1.4.199 should not be affected. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/c52c23d0-c895-48d9-98a4-23e3cc595777%40googlegroups.com.
[h2] Re: Corrupted database - new scenario
Unfortunately, you need to enclose the "TABLE" into parentheses here with a some text editor. It is a new keyword. Recent versions of H2 can load old databases with such unquoted identifiers, but they can't load a script that contains them. Recent versions also always quote all identifiers during SQL generation, so this issue will not appear again when new keywords will appear. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2d579f99-1542-4f2a-a689-45ba9d35a2a4%40googlegroups.com.
[h2] Re: generated keys are "cleared" inside of Session during INSERT (to the table with AUTOINCREMENT column) when there is a trigger (Java code, obviously) that does another INSERT (to different tab
Hello. No, it's not a TODO. It's a comment why the conditions below are necessary. Nested DML commands weren't not supported. However, the current implementation is entirely different and hypothetically it should work in your use case. You can build H2 from the sources and test it with your application. Current sources are available on GitHub: https://github.com/h2database/h2database Building instructions are here: https://h2database.com/html/build.html#building You need a jar target. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/70bcc1bf-5ab0-4ce8-a001-44a981c0d4de%40googlegroups.com.
[h2] Re: Nested transactions - do they exist? Cannot find any documentation about this
Hello. You can't nest one transaction into another in H2, but you can use a savepoint inside a transaction. -- some work SAVEPOINT name; -- some work -- Reverts to the specified savepoint ROLLBACK TO SAVEPOINT name; -- Reverts the whole transaction ROLLBACK; https://h2database.com/html/commands.html#savepoint https://h2database.com/html/commands.html#rollback -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2757e4df-c0dd-4a3e-b674-80bea8eae367%40googlegroups.com.
[h2] Re: Wrong user name or password on freshly installed h2 database
You can also change the file name in JDBC URL to a different one to create a new database. -- 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/0ef0a52f-c6f3-4ecb-bc56-1b77e03b671e%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Wrong user name or password on freshly installed h2 database
Hello. > > It looks like you already have a database in the file with name test.h2.db or test.mv.db in your home directory and that database doesn't have a user with same sa and empty password. If you need this database, use the recovery tool, it should be able to restore unencrypted database without knowing its password. If you sure that you don't need it, just delete the database file. -- 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/33bafa2c-06fc-49c7-a7f8-1943fadeaceb%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[h2] Re: FORMATDATETIME produces incorrect hour
Hello. It looks like your timezone has a DST transition from 2019-03-10 02:00:00 to 2019-03-10 03:00:00, so timestamp 2019-03-10 02:00:00 is not valid. This function always normalizes such timestamps. You can use the TIMESTAMP WITH TIME ZONE data type to avoid issues on DST boundaries. SET @date = TIMESTAMP WITH TIME ZONE '2020-03-08 02:00:00 -07'; -- 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/ee8747a2-86dc-4499-af16-4e2ad54ac848%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Recent drops in performance
PreparedStatement re-parses such queries during each invocation. And we also have another known problem with CTEs: temporary views from different statements may conflict with each other. Such views should not be visible outside of the command. -- 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] Re: Recent drops in performance
org.h2.table.TableView.destroyShadowTableForRecursiveExpression (boolean, org.h2.engine.Session, org.h2.table.Table) calls org.h2.engine.Session.removeLocalTempTable (org.h2.table.Table) that calls org.h2.engine.Database.lockMeta (org.h2.engine.Session) We have a bottleneck here. -- 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] Re: Recent drops in performance
Previous message was truncated, so check the attachment. -- 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. import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Random; public class CTETest { static String[] A = { "ID", "88", "000188", "000288", "000388", "000488", "000588", "000688", "000788", "000888", "203688", "000988", "001088", "001188", "001288", "001388", "001488", "001688", "001788", "001888", "001988", "002088", "002188", "002288", "002488", "002588", "002688", "002788", "002888", "002988", "003088", "003188", "003288", "003388", "003488", "003588", "003688", "003788", "004188", "004388", "004488", "004688", "004288", "003888", "003988", "004088", "193788", "004788", "004888", "004988", "005088", "005188", "005288", "005388", "005488", "180788", "005588", "005688", "005788", "005888", "005988", "006088", "006188", "006288", "006388", "006488", "006588", "006688", "006788", "006888", "006988", "007088", "007188", "007288", "189488", "007388", "007488", "007588", "007888", "007988", "008288", "008388", "008488", "008588", "008688", "008788", "008088", "008188", "007688", "007788", "00", "008988", "009088", "009188", "009288", "203788", "009388", "009488", "009588", "009688", "009788", "180888", "009888", "009988", "183888", "010088", "010188", "010288", "010388", "010488", "010588", "010688", "010788", "010888", "010988", "011088", "011188", "011288", "011388", "182988", "011488", "011588", "011688", "011788", "011888", "011988", "012088", "012188", "012288", "012388", "012488", "012588", "012688", "012788", "012888", "012988", "013088", "013288", "013388", "013488", "013588", "013688", "181288", "181388", "013788", "013888", "181488", "181588", "013988", "181688", "014088", "189688", "189788", "189888", "189988", "190188", "190288", "190388", "190488", "190588", "190688", "190788", "190888", "190988", "191088", "191188", "191288", "191388", "191488", "191588", "191688", "191788", "191888", "194188", "194288", "194388", "194588", "194688", "014288", "014388", "014588", "014688", "014888", "014988", "015088", "015288", "015388", "015488", "015688", "015888", "015988", "016088", "016188", "016288", "016388", "016488", "016588", "016688", "016788", "016888", "016988", "017088", "017188", "192088", "017288", "181788", "017388", "017488", "017588", "017688", "017788", "192188", "017888", "184088", "017988", "183488", "194888", "018088", "018188", "018288", "018388", "018488", "194988", "018688", "018788", "01", "018988", "019088", "019188", "019288", "195088", "019388", "195188", "181888", "019488", "019588", "019688", "195288", "019788", "019888", "019988", "020088", "020188", "020288", "020388", "020488", "192288", "020588", "020688", "020788", "020888", "020988", "021088", "195488", "021188", "195588", "021288", "021388", "021488", "021588", "181988", "021688", "021788", "021888", "021988", "195688", "022088", "022188", "022288", "022388", "022488", "022588", "022688", "184388", "022788", "022888", "022988", "023088", "023188", "195788", "023288", "023388", "023488", "023588", "023788", "023888", "023988", "024088", "024188", "024288", "024388", "024488", "024588", "024688", "024788", "024888", "025088", "025188", "195888", "025288", "024988", "025388", "025488", "025788", "025888", "025988", "026188", "026288", "026388", "182088", "026488", "026588", "192888", "026688", "026788", "026888", "026988", "192588", "025588", "025688", "026088", "027088", "027188", "027288", "027388", "027488", "027588", "027688", "027788", "027888", "027988", "028088", "028188", "184688", "028288", "195988", "028388", "028488", "028588", "028688", "028788", "02", "028988", "029088", "029188", "029288", "029388", "029488", "029588", "182188", "029688", "029788", "029888", "029988", "030088", "196088", "030188", "030288", "030388", "030488", "030588", "030688", "030788", "030888", "030988", "031088", "031188", "031288", "031388", "031488", "031588", "031688", "031788", "031888", "031988", "032088", "032188", "032288", "032388", "032488", "032588", "032688", "032788", "032888",
Re: [h2] Re: Recent drops in performance
I run a git bisect. https://github.com/h2database/h2database/commit/7977b90bd13e9088a3dc1ed8b60efb2699e97724 is the first slow commit. It was merged in https://github.com/h2database/h2database/pull/640 -- 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] Re: Recent drops in performance
With 200 threads both 1.4.197 and 1.4.199 work about 6–7 times slower than 1.4.196. -- 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] Re: Recent drops in performance
I used the same query as in you test case. I can repeat the test with 200 threads. -- 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] Re: Recent drops in performance
I wrote and run a simple 4-thread test with 4 connections and 4 prepared statements with values from population.csv. I tested all values distributed between these threads (All) and also 10,000 randomly selected values per thread (Random). Measured times in seconds are average times between multiple runs. They include time taken to open the database. These measures are approximate. All Random 1.4.196 PageStore 2.5 11.9 1.4.196 MVStore 2.3 14.0 1.4.197 PageStore 2.9 22.6 1.4.197 MVStore 2.8 25.5 1.4.199 PageStore 2.5 11.7 1.4.199 MVStore 2.28 10.4 1.4.198 is definitely slower (this version includes some temporary bugfixes, but such slowness is still surprising), but 1.4.199 is not. Of course, this test is not equal to original test case that runs these queries through the web interface I guess. -- 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] Re: Recent drops in performance
We need code that can be profiled directly with any suitable tool. But not the third-party environment that does something with the web interface of H2. It looks like population.csv contains the identities that should be passed to the provided query, but I'm not sure. -- 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] Re: Recent drops in performance
We need a standalone test case (Java/SQL only) for further investigation. -- 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] Re: What is the latest stable release?
Hello. If your database is in PageStore format you need to export it to SQL script with 1.4.196, create a new database with a new version and populate it with this script due to issue #1247. https://github.com/h2database/h2database/issues/1247 https://h2database.com/html/tutorial.html#upgrade_backup_restore If your database is in MVStore format you need to do the same if you want to upgrade H2 to 1.4.197. If you want to upgrade directly to 1.4.199 it is not really required, because that issue was resolved (for MVStore only), but you may run into some other incompatibility, so you should have a backup copy of your database anyway. 1.4.197 and 1.4.199 have different known regressions that affect only some users. It's possible that one of these versions doesn't suitable for your application. Also there were many changes since 1.4.196. H2 is more standard-compliant now and has many new features. Some old tricks and workarounds may not work any more and should be replaced with correct syntax. For example, IN with row value was reimplemented properly for 1.4.198: https://github.com/h2database/h2database/issues/1760 You may need to change something in your application if you used some of such tricks. -- 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] Re: Recent drops in performance
Hello. Your cte.sql contains runscript from 'init.sql'; Do you measure performance of the query or performance of database population with data? I don't see any noticeable differences in execution time of the query itself between 1.4.196 and 1.4.199 in both PageStore and MVStore databases with their default settings. -- 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] Re: Removal of SELECT_FOR_UPDATE_MVCC 1.4.197 -> 1.4.199
BTW, you can't lock rows that don't exist, so if there were no rows with the specific report_id your select will be unsafe (unless you use some additional locking in this corner case). -- 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] Re: Removal of SELECT_FOR_UPDATE_MVCC 1.4.197 -> 1.4.199
It's not related in any way with compatibility modes. Perhaps you can lock the rows in a subquery. select coalesce(max(sequence) + 1, 1) from (select sequence from r where r.report_id = ? for update) -- 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] Re: Removal of SELECT_FOR_UPDATE_MVCC 1.4.197 -> 1.4.199
Hello. SELECT_FOR_UPDATE_MVCC was useless after other changes in H2. The exception that you got describes the situation pretty clear: you can't use FOR UPDATE clause when you use aggregates (or distinct). Grouped queries don't return the original rows, FOR UPDATE is disallowed in such queries by many databases. You need to perform a regular query that selects and returns all rows that you want to lock. https://h2database.com/html/commands.html#select BTW, do you really need to lock all rows with specific report_id? -- 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] Re: Req: make FROM optional in DELETE statement
So you use either Oracle or H2 in MySQL compatibility mode? It's a pretty strange choice, you're just looking for trouble. If your query looks like SELECT DISTINCT A FROM someTable ORDER BY someFunction(A) you don't need a compatibility mode, such query is accepted by H2 since 1.4.198. (But don't use 1.4.198, use 1.4.199 instead.) If your query looks like SELECT DISTINCT A FROM someTable ORDER BY B it is obliviously invalid. Such queries aren't allowed by the standard and all sane (in that specific area) databases don't allow them too. If there is only one value of B for each value of A you can include column B to list of selected columns. If multiple values of B are possible for each distinct value of A, such ORDER BY clause in meaningless and should be removed. Old versions of MySQL allow such invalid queries, so H2 also allows them in MySQL mode only (since 1.4.198). More recent versions (since 5.7) of MySQL don't allow them any more. -- 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] Re: Req: make FROM optional in DELETE statement
Sorry, I was wrong and H2 actually accepts DELETE without FROM in all versions available on Maven Central. The only exclusion is MySQL compatibility mode. In this mode recent versions of H2 do not accept DELETE without FROM, older versions accept it too. Because MySQL does not accept it, this is not an issue. Please, provide a complete error message. -- 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] Re: Req: make FROM optional in DELETE statement
Hello. What version of H2 do you use? H2 incorrectly accepts DELETE without FROM in all modes except MySQL compatibility mode since version 1.4.197 I guess. This is caused by incorrect implementation of feature request 585 for MySQL compatibility: https://github.com/h2database/h2database/issues/585 I think that such implementation should be revisited and limited to relevant compatibility modes, otherwise it may be incorrectly misused. H2 has compatibility modes for multiple databases, but such statement is only supported by Oracle and SQL Server. -- 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] Re: Recent drops in performance
Hello. CTEs are experimental in H2 and have known bugs, recent versions contain some fixes, so this performance drop may be somehow related with them. But older versions of H2 do not support CTEs at all, so I don't understand how you can test a feature that does not exist. We still need a complete standalone test case for your issue. -- 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] Re: classes found in the wrong directory
I think you need to discuss your problem with developers of software that you use. A lot of modern libraries are packed in multi-release jars, they should support them properly. Of course, you can create an own repository somewhere and place a patched jar into it, or use some other way. But it's just a workaround and not a proper solution. -- 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] Re: Corrupted databases revisited
There is a DB_CLOSE_ON_EXIT setting. It in enabled by default, but it can be disabled. SHUTDOWN command may be executed with other alive connections, they will be closed too, but it's possible that an application will reopen the database after execution of this command, you need to prevent it in some way. Thread.interrupt() may definitely corrupt a database. TCP client threads are not affected, but server threads and threads that use an embedded database should not be interrupted. It's not supported. There is an async filesystem that can be used as a workaround, but it should be carefully tested. Unclean process termination may corrupt the database too in some cases. -- 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] Re: Corrupted databases revisited
Hello. No, you can't copy a database file if database was not closed properly, but you can use a SHUTDOWN command to close it. However, if you need a backup copy it can be created with BACKUP TO 'filename.zip' command, there is no reason to close other connections for it. Usually it's safe to send a SIGTERM to a process, but it depends on your database settings, there is an option that prevents H2 from automatic clean shutdown on process termination. If you can create a standalone test case that reproduces a database corruption, please share it with us. -- 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] Re: H2 for Raspberry PI and ARM
Hello. Standalone MVStore is not a database at all. It's just a copy-on-write key-value storage with multiple maps and optional transactions (with TransactionStore). Of course, you can iterate over MVMap from MVStore by your own code and perform some computations, but I guess it's not that you want. If you need database capabilities, such as SQL queries with aggregate and window functions you need to use a database, such as H2 1.4.199 or some other (PostgreSQL, MySQL 8.0, latest versions of SQLite, etc.) -- 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] Unsupported connection setting DATABASE_TO_LOWER
This setting is only supported by latest versions of H2, 1.4.197 does not have it. You need to upgrade to 1.4.199. If you're required to use 1.4.197 you can use DATABASE_TO_UPPER=FALSE instead, but it works in different way. -- 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] Re: Corrupted databases with 1.4.199
Hello. Error code 1.4.196/1 clearly indicates that exception was thrown by 1.4.196 and not by 1.4.199. You need to check your environment, it looks like you still use 1.4.196 somewhere. Maybe you have multiple jars in your classpath. However, it's reasonable to export your database into a SQL script and create a new database with it using a new version of H2. But you need to find out why 1.4.196 is still used before this operation. -- 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] Re: Bug: sql grammar variably enforced
Hello. Unfortunately, H2 detects such problems with GROUP BY only when more than one distinct value for a column was selected. It's an old known limitation that is not fixed yet. -- 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] Re: Urgent : Database Corrupt problem randomly on many computer.
Hello. The whole database file is filled with zeroes. I have no idea why your data was erased, such file does not look like being produced by H2. H2 always write at least own headers, even empty databases have them. Do you have some other code or some other software that can access the database? Also do not use such old version of H2 with MVStore databases, many issues were resolved since then. -- 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] Re: classes found in the wrong directory
H2 is still fully compatible with Java 7. This problem is not related with Java. But atlassian-plugin or some other piece of software that you use performs incorrect unexpected checks and needs to be fixed to be compatible with multi-release jars. And yes, you can remove the entire META-INF/versions directory from multi-release jars as a workaround. -- 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] Re: Bug alias for column in query
Hello. How exactly do you read names of aliases? I cannot reproduce this issue in 1.4.197 and in other versions. try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) { Statement s = c.createStatement(); s.execute("CREATE TABLE test (ID int);"); s.execute("INSERT INTO test (ID) VALUES (1);"); try (ResultSet rs = s.executeQuery("SELECT ID AS VALUE_ID FROM test;")) { System.out.println(rs.getMetaData().getColumnLabel(1)); while (rs.next()) { System.out.println(rs.getInt("VALUE_ID")); } } } VALUE_ID 1 It looks like you incorrectly use getColumnName(), this method returns ID as expected. JDBC drivers of many databases incorrectly return name of alias from this method too, but you should not expect that all databases will do the same. H2 can emulate such behavior, you need to use a compatibility mode or ALIAS_COLUMN_NAME setting. -- 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] Re: classes found in the wrong directory
It looks like software that you use does not support Multi-release jar files: https://openjdk.java.net/jeps/238 Multi-relese jars are very common now. If you use the latest version of that software, check its bugtracker and post a new issue in it, if such issue does not exist yet. You can remove META-INF/versions directory from H2's jar by yourself as a temporary workaround. It is used only on Java 9 and later versions. If you use a recent version of Java, you can replace classes in regular directories with modified versions from META-INF/versions instead. -- 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] Re: SET SCHEMA FOR REMOTE CONNECTION THROWING ERROR [ Feature not supported: "setSchema && remote session"]
Hello. You can execute SET SCHEMA name command or add schema to a database URL (;SCHEMA=name). Connection.getSchema() and Connection.setSchema() work only with embedded databases and don't work over TCP (I don't know why). -- 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] Re: how to trigger serialization of data objects contained in MVStore Collections
Hello. That's not possible. Why you need a HashMap inside of MVMap? I think you should create a separate MVMap for user_accounts content, in this case you can simply put the modified object into it and only that object will be serialized. -- 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] Re: Recent drops in performance
Hello. Could you build a standalone test case that can be used to reproduce such slowdown? I don't see any reasons for it. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Linked Tables & Indexes
Database B will pass some commands to database A, how they will be actually executed and how indexes will affect execution plan is a problem of database A, database B has nothing to do with it. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] Re: Linked Tables & Indexes
Hello. You can see these indexes only in the source database and only the source database can use them. The linked table simply delegates queries and modification commands to the source database using its own auto-generated SELECT, INSERT, UPDATE, or DELETE commands. -- 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] Re: Explanation about LAZY_QUERY_EXECUTION
Hello. AFAIK, this feature was implemented for Apache Ignite that uses query planner of H2 internally. However, you can use it in H2 too. Note that this setting actually affects only the current session and does not require any privileges (online documentation is not correct), so you can enable or disable it in different connections separately using SET LAZY_QUERY_EXECUTION 1|0. Connection pools don't reset value of this setting to default, so use them with caution and always enable or disable it explicitly when connection pool is used and you want to use it only in some connections. Also note that for some (many) queries lazy query execution is not possible and this setting does not affect them. For example, lazy execution is not possible if result needs to be sorted and there is no compatible index. - how does this affect query speed? Any scenario's where this option may be > much quicker/slower than the default mode? This setting is useful when you have a very large query, but need only some first rows, but don't know how many rows you need before execution of the query, because required number of rows depends somehow on values in the first rows. In such exotic use cases it can improve speed and reduce disk I/O significantly, because you can break execution earlier. However, it depends on possibility to produce rows one by one, it is not possible in some queries. It may be useful in some large queries to avoid buffering of result set on the disk. Typically lazy queries are not expected to be faster. - how does this affect memory use? It reduces memory and / or disk space usage for queries with large results. But if your application reads result sets very slowly lazy execution may have negative impact on overall performance. - does this have any positive/negative influence on concurrency? You need to measure it by yourself with your application. - are there restrictions to using this option? For example: can I have > hundreds of connections at the same time using this option? There are no restrictions. If some query is not compatible with lazy execution it will be executed in normal way automatically. - can this be considered stable and fit for production? The release notes > say it is no longer "experimental" but can I interpret that as it being as > stable as the default mode? It should produce correct results. Some queries were very slow with this setting enabled and that issue was resolved. However, I don't think that many people use it in H2. -- 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] Re: java -cp h2*.jar org.h2.tools.Recover -> Exception in thread "main" java.nio.BufferUnderflowException
Unfortunately, in this case you need to insert some try-catch blocks into sources to skip broken pages. Perhaps you can comment out invocations of MVStoreTool.dump() and MVStoreTool.info() in Recover.process() first. They aren't required. -- 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.