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
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
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
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,
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,
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
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
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
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
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
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
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
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 =
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
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
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
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
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
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
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
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
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
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
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
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
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
>
> 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
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
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
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
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
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
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
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
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
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
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
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,
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
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
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
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
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
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.
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
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
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
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
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.
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
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
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
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
>
> 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
>
> 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
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
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
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
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
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;
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
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
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
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
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
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
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"
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
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
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
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
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
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
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
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
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"
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
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
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
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:
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
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
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
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
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
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
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
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
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
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
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);");
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
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
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
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
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.
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
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
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
Hello.
You can build H2 from its current sources:
https://github.com/h2database/h2database
Building instructions are here:
https://h2database.com/html/build.html#building
You need a jar target.
There was a change since the last release that may help you.
--
You received this message because
301 - 400 of 536 matches
Mail list logo
