| 1 |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> |
| 2 |
<!-- |
| 3 |
Copyright 2004-2019 H2 Group. Multiple-Licensed under the MPL 2.0, |
| 4 |
and the EPL 1.0 (http://h2database.com/html/license.html). |
| 5 |
Initial Developer: H2 Group |
| 6 |
--> |
| 7 |
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"> |
| 8 |
<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> |
| 9 |
<meta name="viewport" content="width=device-width, initial-scale=1" /> |
| 10 |
<title> |
| 11 |
Frequently Asked Questions |
| 12 |
</title> |
| 13 |
<link rel="stylesheet" type="text/css" href="stylesheet.css" /> |
| 14 |
<!-- [search] { --> |
| 15 |
<script type="text/javascript" src="navigation.js"></script> |
| 16 |
</head><body onload="frameMe();"> |
| 17 |
<table class="content"><tr class="content"><td class="content"><div class="contentDiv"> |
| 18 |
<!-- } --> |
| 19 |
|
| 20 |
<h1 id="faq_index">Frequently Asked Questions</h1> |
| 21 |
|
| 22 |
<a href="#support"> |
| 23 |
I Have a Problem or Feature Request</a><br /> |
| 24 |
<a href="#known_bugs"> |
| 25 |
Are there Known Bugs? When is the Next Release?</a><br /> |
| 26 |
<a href="#open_source"> |
| 27 |
Is this Database Engine Open Source?</a><br /> |
| 28 |
<a href="#commercial_support"> |
| 29 |
Is Commercial Support Available?</a><br /> |
| 30 |
<a href="#create_database"> |
| 31 |
How to Create a New Database?</a><br /> |
| 32 |
<a href="#connect"> |
| 33 |
How to Connect to a Database?</a><br /> |
| 34 |
<a href="#database_files"> |
| 35 |
Where are the Database Files Stored?</a><br /> |
| 36 |
<a href="#size_limit"> |
| 37 |
What is the Size Limit (Maximum Size) of a Database?</a><br /> |
| 38 |
<a href="#reliable"> |
| 39 |
Is it Reliable?</a><br /> |
| 40 |
<a href="#slow_open"> |
| 41 |
Why is Opening my Database Slow?</a><br /> |
| 42 |
<a href="#query_slow"> |
| 43 |
My Query is Slow</a><br /> |
| 44 |
<a href="#database_slow"> |
| 45 |
H2 is Very Slow</a><br /> |
| 46 |
<a href="#column_names_incorrect"> |
| 47 |
Column Names are Incorrect?</a><br /> |
| 48 |
<a href="#float_is_double"> |
| 49 |
Float is Double?</a><br /> |
| 50 |
<a href="#how_to_translate"> |
| 51 |
How to Translate this Project?</a><br /> |
| 52 |
<a href="#how_to_contribute"> |
| 53 |
How to Contribute to this Project?</a><br /> |
| 54 |
|
| 55 |
<h3 id="support">I Have a Problem or Feature Request</h3> |
| 56 |
<p> |
| 57 |
Please read the <a href="build.html#support">support checklist</a>. |
| 58 |
</p> |
| 59 |
|
| 60 |
<h3 id="known_bugs">Are there Known Bugs? When is the Next Release?</h3> |
| 61 |
<p> |
| 62 |
Usually, bugs get fixes as they are found. There is a release every few weeks. |
| 63 |
Here is the list of known and confirmed issues: |
| 64 |
</p> |
| 65 |
<ul><li>When opening a database file in a timezone that has different |
| 66 |
daylight saving rules: the time part of dates where the daylight saving doesn't match |
| 67 |
will differ. This is not a problem within regions that use the same rules (such as, within |
| 68 |
USA, or within Europe), even if the timezone itself is different. As a workaround, export the |
| 69 |
database to a SQL script using the old timezone, and create a new database in the new |
| 70 |
timezone. |
| 71 |
</li><li>Tomcat and Glassfish 3 set most static fields (final or non-final) to <code>null</code> when |
| 72 |
unloading a web application. This can cause a <code>NullPointerException</code> in H2 versions |
| 73 |
1.1.107 and older, and may still not work in newer versions. Please report it if you |
| 74 |
run into this issue. In Tomcat >= 6.0 this behavior can be disabled by setting the |
| 75 |
system property <code>org.apache.catalina.loader.WebappClassLoader.ENABLE_CLEAR_REFERENCES=false</code>, |
| 76 |
however Tomcat may then run out of memory. A known workaround is to |
| 77 |
put the <code>h2*.jar</code> file in a shared <code>lib</code> directory |
| 78 |
(<code>common/lib</code>). |
| 79 |
</li><li>Some problems have been found with right outer join. Internally, it is converted |
| 80 |
to left outer join, which does not always produce the same results as other databases |
| 81 |
when used in combination with other joins. This problem is fixed in H2 version 1.3. |
| 82 |
</li></ul> |
| 83 |
<p> |
| 84 |
For a complete list, see <a href="https://github.com/h2database/h2database/issues">Open Issues</a>. |
| 85 |
</p> |
| 86 |
|
| 87 |
<h3 id="open_source">Is this Database Engine Open Source?</h3> |
| 88 |
<p> |
| 89 |
Yes. It is free to use and distribute, and the source code is included. |
| 90 |
See also under license. |
| 91 |
</p> |
| 92 |
|
| 93 |
<h3 id="commercial_support">Is Commercial Support Available?</h3> |
| 94 |
<p> |
| 95 |
No, currently commercial support is not available. |
| 96 |
</p> |
| 97 |
|
| 98 |
<h3 id="create_database">How to Create a New Database?</h3> |
| 99 |
<p> |
| 100 |
By default, a new database is automatically created if it does not yet exist when |
| 101 |
<a href="features.html#database_url">embedded</a> URL is used. |
| 102 |
See <a href="tutorial.html#creating_new_databases">Creating New Databases</a>. |
| 103 |
</p> |
| 104 |
|
| 105 |
<h3 id="connect">How to Connect to a Database?</h3> |
| 106 |
<p> |
| 107 |
The database driver is <code>org.h2.Driver</code>, |
| 108 |
and the database URL starts with <code>jdbc:h2:</code>. |
| 109 |
To connect to a database using JDBC, use the following code: |
| 110 |
</p> |
| 111 |
<pre> |
| 112 |
Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", ""); |
| 113 |
</pre> |
| 114 |
|
| 115 |
<h3 id="database_files">Where are the Database Files Stored?</h3> |
| 116 |
<p> |
| 117 |
When using database URLs like <code>jdbc:h2:~/test</code>, |
| 118 |
the database is stored in the user directory. |
| 119 |
For Windows, this is usually |
| 120 |
<code>C:\Documents and Settings\<userName></code> or |
| 121 |
<code>C:\Users\<userName></code>. |
| 122 |
If the base directory is not set (as in <code>jdbc:h2:./test</code>), |
| 123 |
the database files are stored in the directory where the application is started |
| 124 |
(the current working directory). |
| 125 |
When using the H2 Console application from the start menu, |
| 126 |
this is <code><Installation Directory>/bin</code>. |
| 127 |
The base directory can be set in the database URL. |
| 128 |
A fixed or relative path can be used. When using the URL |
| 129 |
<code>jdbc:h2:file:./data/sample</code>, the database is stored in the directory |
| 130 |
<code>data</code> (relative to the current working directory). |
| 131 |
The directory is created automatically if it does not yet exist. |
| 132 |
It is also possible to use the fully qualified directory name (and for Windows, drive name). |
| 133 |
Example: <code>jdbc:h2:file:C:/data/test</code> |
| 134 |
</p> |
| 135 |
|
| 136 |
<h3 id="size_limit">What is the Size Limit (Maximum Size) of a Database?</h3> |
| 137 |
<p> |
| 138 |
See <a href="advanced.html#limits_limitations">Limits and Limitations</a>. |
| 139 |
</p> |
| 140 |
|
| 141 |
<h3 id="reliable">Is it Reliable?</h3> |
| 142 |
<p> |
| 143 |
That is not easy to say. It is still a quite new product. A lot of tests have been written, |
| 144 |
and the code coverage of these tests is higher than 80% for each package. |
| 145 |
Randomized stress tests are run regularly. But there are probably still |
| 146 |
bugs that have not yet been found (as with most software). Some features are known |
| 147 |
to be dangerous, they are only supported for situations where performance is more important |
| 148 |
than reliability. Those dangerous features are: |
| 149 |
</p> |
| 150 |
<ul><li>Disabling the transaction log or FileDescriptor.sync() using LOG=0 or LOG=1. |
| 151 |
</li><li>Using the transaction isolation level <code>READ_UNCOMMITTED</code> |
| 152 |
(<code>LOCK_MODE 0</code>) while at the same time using multiple |
| 153 |
connections. |
| 154 |
</li><li>Disabling database file protection using (setting <code>FILE_LOCK</code> to |
| 155 |
<code>NO</code> in the database URL). |
| 156 |
</li><li>Disabling referential integrity using <code>SET REFERENTIAL_INTEGRITY FALSE</code>. |
| 157 |
</li></ul> |
| 158 |
<p> |
| 159 |
In addition to that, running out of memory should be avoided. |
| 160 |
In older versions, OutOfMemory errors while using the database could corrupt a databases. |
| 161 |
</p> |
| 162 |
<p> |
| 163 |
This database is well tested using automated test cases. The tests run every night |
| 164 |
and run for more than one hour. But not all areas of this database are equally well tested. |
| 165 |
When using one of the following features for production, please ensure your use case |
| 166 |
is well tested (if possible with automated test cases). The areas that are not well tested are: |
| 167 |
</p> |
| 168 |
<ul> |
| 169 |
<li>Platforms other than Windows, Linux, Mac OS X, or runtime environments other than Oracle / OpenJDK 7, 8, 9. |
| 170 |
</li><li>The features <code>AUTO_SERVER</code> and <code>AUTO_RECONNECT</code>. |
| 171 |
</li><li>Cluster mode, 2-phase commit, savepoints. |
| 172 |
</li><li>Fulltext search. |
| 173 |
</li><li>Operations on LOBs over 2 GB. |
| 174 |
</li><li>The optimizer may not always select the best plan. |
| 175 |
</li><li>Using the ICU4J collator. |
| 176 |
</li></ul> |
| 177 |
<p> |
| 178 |
Areas considered experimental are: |
| 179 |
</p> |
| 180 |
<ul> |
| 181 |
<li>The PostgreSQL server |
| 182 |
</li><li>Clustering (there are cases were transaction isolation can be broken |
| 183 |
due to timing issues, for example one session overtaking another session). |
| 184 |
</li><li>Multi-threading within the old PageStore engine using <code>SET MULTI_THREADED=1</code>. |
| 185 |
Default MVStore engine is multi-threaded by default. |
| 186 |
</li><li>Compatibility modes for other databases (only some features are implemented). |
| 187 |
</li><li>The soft reference cache (<code>CACHE_TYPE=SOFT_LRU</code>). It might not improve performance, |
| 188 |
and out of memory issues have been reported. |
| 189 |
</li></ul> |
| 190 |
<p> |
| 191 |
Some users have reported that after a power failure, the database cannot be opened sometimes. |
| 192 |
In this case, use a backup of the database or the Recover tool. Please report such problems. |
| 193 |
The plan is that the database automatically recovers in all situations. |
| 194 |
</p> |
| 195 |
|
| 196 |
<h3 id="slow_open">Why is Opening my Database Slow?</h3> |
| 197 |
<p> |
| 198 |
To find out what the problem is, use the H2 Console and click on "Test Connection" |
| 199 |
instead of "Login". After the "Login Successful" appears, click on it (it's a link). |
| 200 |
This will list the top stack traces. Then either analyze this yourself, or |
| 201 |
post those stack traces in the Google Group. |
| 202 |
</p> |
| 203 |
<p> |
| 204 |
Other possible reasons are: the database is very big (many GB), or contains linked tables |
| 205 |
that are slow to open. |
| 206 |
</p> |
| 207 |
|
| 208 |
<h3 id="query_slow">My Query is Slow</h3> |
| 209 |
<p> |
| 210 |
Slow <code>SELECT</code> (or <code>DELETE, UPDATE, MERGE</code>) |
| 211 |
statement can have multiple reasons. Follow this checklist: |
| 212 |
</p> |
| 213 |
<ul> |
| 214 |
<li>Run <code>ANALYZE</code> (see documentation for details). |
| 215 |
</li><li>Run the query with <code>EXPLAIN</code> and check if indexes are used |
| 216 |
(see documentation for details). |
| 217 |
</li><li>If required, create additional indexes and try again using |
| 218 |
<code>ANALYZE</code> and <code>EXPLAIN</code>. |
| 219 |
</li><li>If it doesn't help please report the problem. |
| 220 |
</li> |
| 221 |
</ul> |
| 222 |
|
| 223 |
|
| 224 |
<h3 id="database_slow">H2 is Very Slow</h3> |
| 225 |
<p> |
| 226 |
By default, H2 closes the database when the last connection is closed. |
| 227 |
If your application closes the only connection after each operation, |
| 228 |
the database is opened and closed a lot, which is quite slow. |
| 229 |
There are multiple ways to solve this problem, see |
| 230 |
<a href="performance.html#database_performance_tuning">Database Performance Tuning</a>. |
| 231 |
</p> |
| 232 |
|
| 233 |
<h3 id="column_names_incorrect">Column Names are Incorrect?</h3> |
| 234 |
<p> |
| 235 |
For the query <code>SELECT ID AS X FROM TEST</code> the method |
| 236 |
<code>ResultSetMetaData.getColumnName()</code> returns <code>ID</code>, I expect it to |
| 237 |
return <code>X</code>. What's wrong? |
| 238 |
</p> |
| 239 |
<p> |
| 240 |
This is not a bug. According the JDBC specification, the method |
| 241 |
<code>ResultSetMetaData.getColumnName()</code> should return the name of the column |
| 242 |
and not the alias name. If you need the alias name, use |
| 243 |
<a href="https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel(int)"><code>ResultSetMetaData.getColumnLabel()</code></a>. |
| 244 |
Some other database don't work like this yet (they don't follow the JDBC specification). |
| 245 |
If you need compatibility with those databases, use the <a href="features.html#compatibility">Compatibility Mode</a>, |
| 246 |
or append <a href="http://www.h2database.com/javadoc/org/h2/engine/DbSettings.html#ALIAS_COLUMN_NAME"><code>;ALIAS_COLUMN_NAME=TRUE</code></a> to the database URL. |
| 247 |
</p> |
| 248 |
<p> |
| 249 |
This also applies to DatabaseMetaData calls that return a result set. |
| 250 |
The columns in the JDBC API are column labels, not column names. |
| 251 |
</p> |
| 252 |
|
| 253 |
<h3 id="float_is_double">Float is Double?</h3> |
| 254 |
<p> |
| 255 |
For a table defined as <code>CREATE TABLE TEST(X FLOAT)</code> the method |
| 256 |
<code>ResultSet.getObject()</code> returns a <code>java.lang.Double</code>, I expect it to |
| 257 |
return a <code>java.lang.Float</code>. What's wrong? |
| 258 |
</p> |
| 259 |
<p> |
| 260 |
This is not a bug. According the JDBC specification, the JDBC data type <code>FLOAT</code> |
| 261 |
is equivalent to <code>DOUBLE</code>, and both are mapped to <code>java.lang.Double</code>. |
| 262 |
See also |
| 263 |
<a href="https://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html#1055162"> |
| 264 |
Mapping SQL and Java Types - 8.3.10 FLOAT</a>.</p> |
| 265 |
<p>Use REAL or FLOAT(24) data type for <code>java.lang.Float</code> values.</p> |
| 266 |
|
| 267 |
<h3 id="how_to_translate">How to Translate this Project?</h3> |
| 268 |
<p> |
| 269 |
For more information, see |
| 270 |
<a href="build.html#translating">Build/Translating</a>. |
| 271 |
</p> |
| 272 |
|
| 273 |
<h3 id="how_to_contribute">How to Contribute to this Project?</h3> |
| 274 |
<p> |
| 275 |
There are various way to help develop an open source project like H2. The first step could be to |
| 276 |
<a href="build.html#translating">translate</a> the error messages and the GUI to your native language. |
| 277 |
Then, you could |
| 278 |
<a href="build.html#providing_patches">provide patches</a>. |
| 279 |
Please start with small patches. That could be adding a test case to improve the |
| 280 |
<a href="build.html#automated">code coverage</a> (the target code coverage for this project is 90%, higher is better). |
| 281 |
You will have to <a href="build.html">develop, build and run the tests</a>. |
| 282 |
Once you are familiar with the code, you could implement missing features from the |
| 283 |
<a href="http://www.h2database.com/html/roadmap.html">feature request list</a>. |
| 284 |
I suggest to start with very small features that are easy to implement. |
| 285 |
Keep in mind to provide test cases as well. |
| 286 |
</p> |
| 287 |
|
| 288 |
<!-- [close] { --></div></td></tr></table><!-- } --><!-- analytics --></body></html> |