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
However, if remote access was enabled transparent authentication does not
work anymore, so you need to specify a password in a configuration file and
restart the console.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this
Hello.
Names of properties are case-sensitive, you need to specify
webAdminPassword instead.
In 1.4.199 password is not needed if your browser session was opened by H2
Console during its startup or from its icon in the system tray.
--
You received this message because you are subscribed to
PageStore + MVCC was an easy way to get different problems. Your corruption
may be caused by it, but of course I can't be sure.
If you always shutdown your database properly, your code doesn't try to
interrupt() threads that work with embedded database and your system has
reliable power source
Hello.
You need to check the generated SQL and remove incorrect commands from it.
You have two rows with ID=318631 according to that error message.
Do not use PageStore with MVCC in production, it was always experimental
and buggy.
Newer versions of H2 do not support MVCC setting, it's no-op
Hello.
Do not use 1.4.198, use 1.4.199 instead, it contains fixes for regressions
in 1.4.198, including some fixes for database creation (in H2 Console).
The possible ways to create a new database are described in documentation:
http://h2database.com/html/tutorial.html#creating_new_databases
Hello.
Yes, that change was intentional. H2 since 1.4.198 supports row value
expressions.
See documentation for current complete list of keywords:
http://h2database.com/html/advanced.html#keywords
--
You received this message because you are subscribed to the Google Groups "H2
Database"
Also your database path looks very suspicious. You should not run a
database server with super-user permissions.
--
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.
You need to add -ifNotExists option to your TCP Servers to allow remote
creation of databases.
Note that this option creates a security hole in your system unless your
servers are protected with a firewall or some other solution from
unauthorized access, so use it with caution and do
Hello.
Database indexes are not a part of the SQL standard. They are
database-specific.
PRIMARY KEY (…) is not an index, it's a constraint. Constrains and their
behavior are described in the standard, but their actual implementations
can be different.
When you use getPrimaryKeys() H2 returns
Hello.
No, they aren't missing, but your tool can't load them for some reason.
jar file is a Zip-based archive, there are a lot of tools that you can use
to check its content, including IDEs, archivers, jar command-line utility
and so on.
--
You received this message because you are
Hello.
Issue with your query was fixed.
You can get the current sources on GitHub and compile H2 from them:
https://github.com/h2database/h2database
Building instructions are here:
http://h2database.com/html/build.html#building
You need a jar target.
--
You received this message because you are
>
> Can you remind me what we discussed? I don't remember that.
>
https://github.com/h2database/h2database/issues/1389
--
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
On Saturday, 23 February 2019 23:11:33 UTC+8, Noel Grandin wrote:
>
> If you are running the console against local databases, you're doing
> anything you're already allowed to do. We just made it less convenient.
>
Yes, there is a way to break “security”. Another user on the same home
computer
H2 is not a library. It's a database engine. Database engines are used via
JDBC.
If you read H2 documentation (not the Javadoc) you can find that H2
additionally provides some API and tools for specific operations where JDBC
is not enough, like usage of data types that doesn't have JDBC
Creation of in-memory databases is obscure now, we definitely need some
better UI for it, but creation of normal databases is more or less
intuitive and it is described (very briefly) in documentation.
H2 allows unlimited access to underlying system for everyone if creation of
new databases is
Hello.
You should not use internals of H2 unless you're ready that they can be
changed or removed in any version without a warning.
This method was removed intentionally. You can try to use
Value.getResult()` instead, but it returns an instance of
org.h2.api.ResultInterface that also is not a
Standalone H2 Console does not allow creation of new databases from a web
interface any more for security reasons. To create an in-memory database
right-click on a system tray icon, choose Create a new database…, specify
something like
mem:1;DB_CLOSE_DELAY=-1
instead of database path and set
Hello.
In H2 you need to specify parentheses after this function.
USER()
Without them H2 will try to parse it as a column name that results in *Column
"USER" not found [42122-197]* error.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To
Hello.
You should not use 1.4.191 in production. It's an old beta version.
Try to use recovery tool from the most recent version. If it will not work
too, use the latest sources from the GitHub:
https://github.com/h2database/h2database
Unfortunately, you may need to insert some try-catch blocks
Hello.
It looks like you updated version of H2 database to 1.4.197 from some older
version, executed a DDL command after it, and closed the database. There is
an incompatible change in this version.
The safe way to update your database to 1.4.197 format is to export it to
SQL with SCRIPT TO
Hello.
You can use CSVWRITE function to export some table or query results to CSV:
http://h2database.com/html/functions.html#csvwrite
There in so way to export the whole database to CSV, because database may
contain multiple tables and other objects.
But you can use SCRIPT TO command to export
Hello.
If you use PageStore databases do not enable multi-threaded mode, it has
different issues.
With default MVStore engine there are known issues in all released versions
too, but they were resolved and multi-threaded mode was enabled by default
(only in MVStore databases).
You can build
Unfortunately, I cannot reproduce such problem with latest soures and 1.5
GB PageStore database even with -Xmx64M parameter.
I think we need a test case for this issue. Could you write some java code
that creates a database, inserts some random data into it, and executes a
SCRIPT command that
Please, try to add something like
;MAX_MEMORY_ROWS=1000
to your connection URL. You may want to try different values to find good
setting for your application.
The default is 40,000 rows per GB of memory, this default may be too high
for some use cases. SCRIPT command can create large rows.
H2 can't optimize MAX() aggregate in presence of WHERE condition. Scan
count will be exactly the same as it was in the initial slow query.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails
Hello.
It looks like all or almost all rows in your table match the WHERE criteria
and database must return all of them, that's why scan count is so large.
If you read only the first row you need to do two things.
1. Create an index that can be used to read rows in the same order as it
Hello.
SCRIPT command can compress and encrypt the output file.
http://h2database.com/html/grammar.html#script
http://h2database.com/html/grammar.html#script_compression_encryption
You cannot pass OutputStream as parameter and I don't think that database
should support such dangerous feature.
Hello.
These alerts are invalid from my point of view. DB administrator in H2 has
full access to JVM, and JVM has access to operating system. This is by
design. You should never give ADMIN permission to untrusted users or
applications, regular users with necessary grants should be used
I don't think that multi-threaded kernel reduces delays significantly, but
there were a lot of other changes since 1.4.197, situation with your
application may be better, may be not.
It's better not to wait for 1.4.198. Test your application right now, if
you can. If you'll find some failures,
On Sunday, 6 January 2019 23:15:55 UTC+8, Diego Rivera wrote:
>
> Evgenij, can you elaborate on what "issues" arose with "specific use
> cases" with regards to MULTI_THREADED?
>
For example, SELECT may skip rows that are concurrently updated in another
thread and transaction. 1.4.197 and older
Hello.
CREATE ROLE is supported by H2, but it's only a role, not a user definition:
http://h2database.com/html/grammar.html#create_role
You need a CREATE USER command instead:
http://h2database.com/html/grammar.html#create_user
--
You received this message because you are subscribed to the
Hello.
MULTI_THREADED setting is to allow concurrent execution of statements.
MVCC setting is to use row-level locks instead of table-level locks. If it
is not in use you should always lock tables in the same order in all
transactions.
In 1.4.197 MVCC is set by default. However, if you use
Hello.
Please, provide a complete test case.
ARRAY_AGG() in H2 returns an ARRAY value that can be read from a result set
with
(Object[]) ResultSet.getArray(…).getArray()
or with
(Object[]) ResultSet.getObject(…)
If you got a Clob somehow (for example, using
ResultSet.getClob(…)
or by
Hello.
You need to build H2 from its current sources and enable MySQL
compatibility mode in it to resolve this issue.
Sources are here:
https://github.com/h2database/h2database
You need a jar target:
http://h2database.com/html/build.html#building
getBytes() is not a proper method to read
Hello.
Your current problem is not related with H2 at all and most likely is not
related with JayDeBeApi.
jpype library cannot find JVM on your system. You need to ensure that
Python and JVM use the same architecture (either 32-bit or 64-bit). If they
match with each other try to ask jpype
Hello.
You need either
SELECT ID, ARRAY_AGG(DISTINCT Item) Item FROM tbl2 GROUP BY ID
or
SELECT ID, GROUP_CONCAT(DISTINCT Item SEPARATOR ', ') Item FROM tbl2 GROUP
BY ID
depending on data type of expected result. Use ARRAG_AGG for ARRAY result
or GROUP_CONCAT for VARCHAR result.
ARRAY_AGG is
Hello.
Both org.h2.api.AggregateFunction and better org.h2.api.Aggregate
interfaces are for user-defined aggregate functions. They should not be
placed inside H2 sources and do not require modification of H2. You need to
write and compile your implementation of one of these interfaces, place
I cannot reproduce such issue on my system without abnormal termination of
process.
Database needs some space for operation. Its size may be different with the
same data. I guess the database was shut down on your system too early
without a chance to collect and free disk space that is not
Database is not an archive.
If you stored a BLOB or BINARY value in a database, you have to connect to
this database again and execute an appropriate SQL query. After it you can
read the data from the returned ResultSet and store it where you want.
--
You received this message because you are
Hello.
Did you try the latest version? Can you provide a standalone test case?
With trivial test case I cannot reproduce such problem.
Connection c = DriverManager.getConnection("jdbc:h2:./tst" +
Constants.BUILD_ID);
Statement s = c.createStatement();
s.execute("CREATE
Hello.
Which version of H2 do you use? How your JDBC connection URL looks like?
There are two possible sources of locking.
1. Table-level locks can cause deadlocks between transactions if tables are
not locked in the same order when MVCC feature is not used.
2. A long command blocks all other
Hello.
Add -ifExists flag to your TCP server.
http://www.h2database.com/html/advanced.html#remote_access
You should not run TCP / Web / PG server without proper configuration in
production or in unsafe testing environments, especially if remote access
in enabled.
Default configuration is fine
Hello.
If you want to check presence of in-memory database you can add
;IFEXISTS=TRUE
to your database URL (something like
jdbc:h2:mem:database_name;IFEXISTS=TRUE).
With this argument an exception with error code 90013 will be thrown if
database is not exists.
If you want to check presence of
Hello.
What version of H2 do you use? Could you provide a complete test case?
I cannot reproduce this problem with 1.4.197. LIKE '%' || ? || '%' works as
expected.
SELECT * FROM TESTTABLE WHERE NAME '%' || ? || '%'
This statement is not correct, LIKE should be used.
Note that ?, _, and \
Hello.
There are to kinds of backups.
1. BACKUP command creates a ZIP archive with a new database file in it.
Such archive can be unpacked into some directory, path to unpacked file can
be specified in database connection URL. You can also rename this file if
you need it.
2. SCRIPT command
Hello. This file is hidden, make sure that tool that you use for search
looks for hidden files too.
File should be located in ~/.h2.server.properties by default, where ~ is
your home directory. I don't know what environment is available on your AIX
system, but command-line and GUI text editors
Hello.
You created a new empty database with such weird name that you can see in
CATALOG_NAME column.
You need to add
;BUILTIN_ALIASES_OVERRIDE=1
after name of the your database in your connection URL instead.
Also a bug with this setting was fixed only after the last release, you
need to
Hello.
It is available:
https://search.maven.org/search?q=g:com.h2database%20AND%20a:h2-mvstore=gav
--
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.
A lot of libraries are multi-release jars now, H2 is not an exclusion.
Jetty got support of multi-release jars in 2017 *before* release of H2
1.4.197.
If you need to use old version of Jetty with a new version of H2 or other
multi-release jar you can simply remove META-INF/versions
Hello.
No, the minimum requirement is JDK 7.
It seems that your use an outdated version of Jetty that does not support
multi-release jars.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving
Hello.
Execution of OUTER joins cannot swap it sides in H2, H2 does not have hash
joins yet. That's why only [A, J, X] and [J, X, A] plans are considered.
For LEFT OUTER join H2 iterates over left side and lookups right side.
Index on PROP(ID, OWNER_PK) may be wanted here if there are many
Hello.
Yes, this class is here on purpose. It's a multi-release jar file.
http://openjdk.java.net/jeps/238
Here is a workaround that you can use until ProGuard will be improved to
support new Java features:
https://sourceforge.net/p/proguard/bugs/671/
--
You received this message because you
A keyset pagination is usually used for large tables. A last value of
search key (id in your query) from previous page is remembered and next
page is loaded with WHERE id > _previous_id_ FETCH NEXT 1000 ROWS ONLY. The
last returned id is remembered again and is used to load a next page on
Hello.
64-bit offsets are only supported by a current development version of H2
that you can build for yourself from the sources
https://github.com/h2database/h2database
http://h2database.com/html/build.html#building
You cannot use large offsets in released versions of H2.
Usage of OFFSET,
Hello.
Yes, this behavior is correct, ExpressionColumn.getAlias() should return
only the name (or possibly alias) of the column for real columns. In recent
version of H2 it returns an alias of column if derived column list is used.
Plain aliases of columns are represented by Alias class in the
Hello.
Do you really need to select all these rows at once?
You can try to add
;LAZY_QUERY_EXECUTION=1
to your connection URL.
Also if you use default MVStore engine such huge selects can be very slow,
this problem was resolved some time ago, so if you can build database from
its currents
Issue with this query was fixed.
--
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
Hello.
If you can build H2 from its current sources it's better to use
t1.valid_from - INTERVAL '1' DAY
for date-time arithmetic instead of such non-standard subtraction operation
with an integer number.
https://github.com/h2database/h2database
However, I found the reason of this bug. I'll try
For information:
The only one reliable way to upgrade from 1.4.196 to a more recent version
is to run SCRIPT TO 'filename.sql' in this version, then create a new empty
database in a new version and finally run RUNSCRIPT FROM 'filename.sql'.
Script tool from 1.4.196 and RunScript from 1.4.197
It does not matter, neither built-in date-time function nor alias for
date-time function nor other date-time value cannot be used together with
parameter in subtraction operation in H2. This is a known limitation.
To use such arithmetic operation parameter should be wrapped in CAST
operator or
Hello.
This issue was resolved. You can build database from its current sources or
wait for a next release.
https://github.com/h2database/h2database
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop
This is not really a “hole” in H2, it is an unsafe non-default
configuration that is used in some third-party products.
You have to enable remote access to H2 explicitly, but if you're doing it
you should also set additional restrictions that suit your environment and
needs. -ifExists can be
I don't know when the next version will be released. There is no release
schedule.
I created a separate issue for problem with upgrade from 1.4.197 and older
versions:
https://github.com/h2database/h2database/issues/1331
--
You received this message because you are subscribed to the Google
I found a way to reproduce it. 1.4.197 creates an unexpected index and such
database does not work in a snapshot version.
Creation of a new database using only the snapshot version should help.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
I guess you should try create a new database with RUNSCRIPT using a
snapshot version. There are too many changes between 1.4.197 and current
snapshot.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop
I'll try to reproduce it, but right now it looks like your database was
already damaged. Meta index should be a MVDelegateIndex, not
a MVSecondaryIndex.
I think it's reasonable to export database into an SQL script and load this
script into a new database.
--
You received this message
Also exceptions are usually logged in _database_name_.trace.db file in the
directory with database. You can open it in some text editor.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails
You got this exception on connection attempt or when you tried to execute
some command?
DB Console (if I remember it well) can show stack traces too, please copy
it here.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from
I think these problems have a common source. Parameters in H2 are not
supported in DDL statements (this is not a big problem by itself) and also
in some inner constructions, WITH and MERGE USING are the most problematic.
WITH creates own DDL commands (so parameters don't work too), and current
Hello.
I tried to send you it in attachment, but it does not work.
You can download the current snapshot here:
https://drive.google.com/open?id=1nud8GV4zhKo-aAx4z7rfXcZqk6LSFoRH
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe
Hello.
I have several tables with >100,000 rows and about 10 columns (H2 1.4.197,
> database about 5GB). If I want to update one column for a large number of
> rows
>
A regression in 1.4.197 with MVStore mode was found and fixed. It appears
in large updates in presence of unique constraints
Hello.
This is a known issue with MERGE INTO … USING statement that is not yet
resolved:
https://github.com/h2database/h2database/issues/1189
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving
Hello.
Only archaic 3.6.2.
Lucene is not backward-compatible, it's not easy to support multiple
versions at once. Also all modern versions of Lucene require Java 8, but H2
still supports Java 7. The latest version that can be used on Java 7 is
Lucene 5 that is also old enough. There is a
Maven build also does not copy all necessary files so a lot of failures in
tests are expected, does not support latest Java versions and so on. It is
not really usable.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this
No, it is not configurable.
--
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
Hello.
It creates a subdirectory with the same name as a database.
/path/to/testdb.mv.db — database file
/path/to/testdb/ — directory with Lucene index
Lucene creates different own files in this directory.
--
You received this message because you are subscribed to the Google Groups "H2
Hello.
Support of MEDIAN aggregate function was introduced in H2 1.4.197.
You can also register an own function in 1.4.196, but upgrade can be a
simpler solution.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group
If you use 1.4.197 or an older version you can add ;MULTI_THREADED=TRUE and
remove ;LOCK_MODE=0 (they should not be used together in these versions).
This is safe if you don't modify these tables while reports are generated
and is not safe in case of concurrent modification, some rows can be
Hello.
This problem is not related with H2, but related with broken MySQL driver
that is also included in your classpath. DriverManager does not map URLs to
drivers by itself, it probes all registered drivers instead. Some versions
of MySQL driver incorrectly throw similar exceptions instead
>
> These issues that were resolved recently.
>
These issues were resolved recently.
Usage of Connection.TRANSACTION_READ_UNCOMMITTED (or LOCK_MODE=0) does not
allow dirty reads in H2 with MVStore engine even with current sources. It
may be not expected.
--
You received this message because
Hello.
Multi-threaded mode had some issues with concurrent reading and
modification. If you definitely don't use concurrent queries and updates in
the same table you can add
;MULTI_THREADED=TRUE
to your database URL, and remove
;LOCK_MODE=0
because these settings will conflict with each other.
Hello.
This function in H2 does not have exact return type, it may return
TIMESTAMP, TIMESTAMP WITH TIME ZONE, DATE, or TIME depending on type of the
second argument. That's why its return type is not defined. You can add an
explicit cast.
SELECT CAST(DATE_TRUNC('MONTH', column) AS TIMESTAMP)
Hello.
This issue was fixed.
You can build database from the current sources that are available on the
GitHub:
https://github.com/h2database/h2database
You need jar target as described here:
http://www.h2database.com/html/build.html#building
You can also use
DATE_TRUNC('SECOND', '2018-06-27
Hello. I can't reproduce such issue with SCRIPT, SCRIPT TO, or BACKUP
commands with large database and different settings. May be your database
schema contains something special. You can try to create a some standalone
test case for this issue and share it with us.
H2 had a long-standing
Hello.
If you start a H2 Server in your application with
Server.createTcpServer(optional_parameters).start();
you can connect to database from the same process using local URLs and
remotely with remote URLs.
If you don't care which application will use the database directly and all
of them
Hello.
Do you use 1.4.194 server and 1.4.197 client?
This is a known regression in 1.4.197 that was already fixed, the fix will
be included in the next release. You can upgrade your database server to
the 1.4.197, this should resolve your problem or downgrade client driver to
1.4.196 or to an
Hello.
Such behavior is specified in documentation:
http://www.h2database.com/html/grammar.html#alter_user_rename
Now you can use a recover tool to export your database into plain SQL file:
http://www.h2database.com/html/advanced.html#using_recover_tool
After this you can create a new database
Hello.
This issue was already fixed. Wait for a next release or build database by
yourself using the latest sources from GitHub:
https://github.com/h2database/h2database
You need a jar target:
http://www.h2database.com/html/build.html#building
--
You received this message because you are
Hello.
Java has standard javax.security.auth.spi.LoginModule interface for custom
login modules and provides some implementations, such as Krb5LoginModule
that works perfectly with Windows Domain authentication (may be with MIT
implementation too), LdapLoginModle and others. I'm not sure that
Hello.
These ports in your system are blocked for local connections too?
Also you can try to use
;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=12345
with some specific port number.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this
Hello.
JDBC specification and Javadoc from JDK require only that you can get keys
from autogenerated columns when you use INSERT statement, auto-commit is
disabled, batch updates are not used, keys are explicitly requested with
Statement.RETURN_GENERATED_KEYS or indexes or names for the
Hello.
What exactly do you mean by second index key? Indexes are not a part of SQL
standard, but almost all databases have them and many databases have very
similar syntax. Berkeley's own documentation have SQL examples.
The complete H2's syntax can be found here:
Hello.
Old rows are indexed on creation of an index.
The new index will be used for both old and new rows.
--
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.
Usage of standard SQL subclasses for exceptions are in H2 roadmap for a
long time.
But this may be quite complicated because H2 actually use own subclass for
exceptions with additional functionality, so either this functionality
should be implemented in some other way or all exceptions
Hello.
Released version of H2 are available on Maven Central and on H2 webcite,
there should be no reason to build them by yourself.
But if you need this for some reason use build script with jar target to
build the database, or use other targets to run tests or do something else.
Hello.
You can try to specify a database listener in database URL parameter:
;DATABASE_EVENT_LISTENER='sample.MyListener'
Listener should be in your classpath and implement
org.h2.api.DatabaseEventListener.
http://www.h2database.com/javadoc/org/h2/api/DatabaseEventListener.html
--
You received
Hello.
It's better to use GitHub for contributions. Pull requests are tested
automacally and GitHub provides a good interace to review and discuss
submitted changes.
https://github.com/h2database/h2database
http://www.h2database.com/html/build.html#providing_patches
--
You received this
Hello.
H2 has own script tool
http://www.h2database.com/html/tutorial.html#upgrade_backup_restore
and also SQL command for export.
http://www.h2database.com/html/grammar.html#script
SCRIPT NODATA TO 'filename.sql'
Your SQL script from IntelliJ is not valid. There is no data type
TIMESTAMP WITH
TIMESTAMP WITH TIME ZONE is fully supported only since H2 1.4.197. Such
query runs in this version without any problems.
In older versions this data type was experimental and was not supported by
many date and time functions. 1.4.196 and older versions
throw UnsupportedOperationException on
401 - 500 of 537 matches
Mail list logo