Re: [h2] MVSTORE, how to encrypt?
Hi, My guess is that you made a mistake. I suggest you re-read the documentation and try again (with a new database). If it doesn't work, please exactly describe what you did, so we can reproduce or tell you where you made a mistake. Regards, Thomas On Thu, Dec 18, 2014 at 3:49 PM, Wimpie wim%dewijngaert...@gtempaccount.com wrote: I used to create encrypted H2 databases using the Tools/Change file encryption menu in the webinterface, However, doing this for an MVSTORE database corrupts the database. How can I add encryption as easy as possible to an existing MVSTORE database? Using the webinterface, SQL or a java command prompt? -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Recurring data corruption issues
Hi, rather big ( 2 Mo ...) With 1Mo = 1MB, this is a few megabytes? That sounds relatively small... What I would do is add a feature to backup the database from time to time. Maybe I can help you with the details. I would try an automatic algorithm, keep the last few backups, and also test the backups; first use backup to ... so that the application is blocked only very shortly, then in a background thread open the backup and run script to ..., this is to verify the backup was successful. You don't need an automatic restore feature, so no GUI changes are needed, but support would be much easier, and it might be possible to find out what the root cause is (network drive, hardware, software, if software where exactly). At least some of the user have stored the DB on a network drive where they may have been concurrent access attempts. Yes, this can be a problem. For version 1.3.x, you could try file level locking by appending ;file_lock=fs to the database URL. Version 1.4.x (I don't suggest to use that version for production) uses file locking by default. Also, the file format, and specially the recovery algorithm are much simpler. And there are a lot less write operations. So once it is stable, overall the risk of corruption should be smaller. What I would do is run some tests with version 1.4.184, to find out if everything works as expected, and once the MVStore is stable, use version 1.4.x. Regards, Thomas On Thu, Dec 18, 2014 at 1:02 PM, Noel Grandin noelgran...@gmail.com wrote: On 2014-12-18 09:52 AM, Marc Heinz wrote: - Concurrent access attempts when stored on a network drive ? Probably that. Network drives are notorious for data corruption, especially when concurrently accessing the same file. I would do my best to detect that situation and prevent your users from doing that. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Re: Column not found when column is quoted
Hi, This should be fixed in version 1.4.184. Regards, Thomas On Thu, Oct 23, 2014 at 4:46 AM, thilopl...@googlemail.com wrote: This seems to be the same problem as in our Issue 578 http://code.google.com/p/h2database/issues/detail?id=578sort=-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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] Transaction is closed [1.4.181/4] [50000-181]
Hi, With never versions of H2, the error message should contain more details now (if it is still a problem). For example, the root cause could be out of memory. Regards, Thomas On Tue, Sep 30, 2014 at 11:21 AM, Daniele Renda daniele.re...@gmail.com wrote: Unfortunally there are not logs. The only thing I see when I try to run a query is : Errore SQL [5] [HY000]: General error: java.lang.IllegalStateException: Transaction is closed [1.4.181/4] [5-181] General error: java.lang.IllegalStateException: Transaction is closed [1.4.181/4] [5-181] Transaction is closed [1.4.181/4] 2014-09-30 9:59 GMT+02:00 Noel Grandin noelgran...@gmail.com: On 2014-09-30 09:58 AM, Daniele Renda wrote: Yes I can but when I try to make also a simple Select I've the error-- Transaction is closed [1.4.181/4] [5-181] Thanks Now that is weird. I'd really like to see a full exception from Dbeaver. Does it not have a log file? -- You received this message because you are subscribed to a topic in the Google Groups H2 Database group. To unsubscribe from this topic, visit https://groups.google.com/d/ topic/h2-database/sDw2Jvhlims/unsubscribe. To unsubscribe from this group and all its topics, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- Daniele Renda -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] 1.4 beta creates much bigger database file
Hi, Version 1.4.184 should produce smaller database files than previous version (1.4.x - 1.4.182), maybe half or a third of the old file size. It would be great to get some real-world results! Regards, Thomas On Tue, May 6, 2014 at 6:24 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Some initial results: you can shrink the database by running shutdown compact or shutdown defrag. Each time this is run, it shrinks a few MB (up to some point, of course). This works, but it's relatively slow. Now the task is to make it faster. There are two ways: shrink it fully to the minimum size, and shrink it incrementally (like now) but faster. I'm working on that now. Regards, Thomas On Tue, May 6, 2014 at 11:39 AM, Steve McLeod steve.mcl...@gmail.com wrote: Hi Thomas, I've sent you a private email with a link to the new database file, made with H2 1.4.178 Regards, Steve On Monday, 5 May 2014 07:46:16 UTC+2, Thomas Mueller wrote: Hi, The database file should shrink if you run shutdown defrag. The current compact algorithm is quite inefficient, that means the databases file is quite big on average. The highest priority is still to ensure it always works correctly, and when that's done I will work on more efficiently re-using disk space and specially compact the file faster when closing the database. Could you send me the new database file? It would be nice to have a real-world database file to test this. The last file you sent helped a lot, thanks to it I found some problems that completely prevented the file to shrink. Regards, Thomas On Sunday, May 4, 2014, Steve McLeod steve@gmail.com wrote: Hi Thomas, I tested the same large data import with H2 1.4.178, and there is no improvement over H2 1.4.177. Here are the file sizes, in both cases after the app has stopped: H2 1.3.176: pokercopilot.h2.db 301,669,352 bytes H2 1.4.178: pokercopilot.mv.db 1,023,037,440 bytes Let me know what I can do to help. Regards, Steve On Saturday, 19 April 2014 11:44:05 UTC+2, Steve McLeod wrote: Hi Thomas, Great! Glad I could help make your superb product even better. On Friday, 18 April 2014 21:38:27 UTC+2, Thomas Mueller wrote: Hi, Thanks a lot for the database! I know what the problem is now, but I couldn't fix it yet. The database file (pokercopilot2.mv.db) has about 181 MB of live data, the rest (about 78%) is not used. The mechanism to get rid of the unused space is not working as it should for this case (I think the problem is that b-tree nodes are not processed correctly). This will be fixed in the next release. Regards, Thomas On Fri, Apr 18, 2014 at 5:29 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, I've sent a link to file privately to your email address. Regards, Steve On Friday, 18 April 2014 14:04:37 UTC+2, Thomas Mueller wrote: Hi, Hm, that didn't help much. Could you send me the (compressed) database files please? If it's too big, what is the compressed size of the files? Regards, Thomas On Fri, Apr 18, 2014 at 1:07 PM, Steve McLeod steve@gmail.com wrote: Hi Thomas, Thanks for the suggestion. I tried adding ;retention_time=1000 to the URL, and this resulted in a small improvement. pokercopilot.h2.db 302,018,560 bytes pokercopilot.mv.db 999,120,896 bytes pokercopilot.mv.db with RETENTION_TIME=1000: 811,728,896 bytes These numbers all reflect a loading of data in a newly created database that consisted of roughly 2,400,000 INSERTS and UPDATES with plenty of SELECTS and almost no DELETES. After the loading was complete, I let the application keep running with the database open for a few minutes, then close the application and therefore the database. Here is the full JDBC url I'm using: jdbc:h2:/Users/steve/Library/Application Support/com.barbarysoftware.po kercopilot/database/pokercopilot;DATABASE_EVENT_LISTENER='co m.barbarysoftware.pokercopilot.database.DatabaseListener';COMPRESS_ LOB=DEFLATE;CACHE_SIZE=65536;RETENTION_TIME=1000 Let me know if there is anything else I can do to help diagnose this. Regards, Steve On Thursday, 17 April 2014 17:15:50 UTC+2, Thomas Muel -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For
Re: [h2] 1.4.182 corruption
Thank's Thomas for your great work and dedication. I don't need this data to be recovered, so i'm lucky :) Op donderdag 18 december 2014 22:07:16 UTC+1 schreef Thomas Mueller: Hi, Yes, this looks like the bug that was fixed in version 1.3.183. I'm afraid this database is not recoverable. Regards, Thomas On Thu, Dec 18, 2014 at 1:53 PM, Dieter Cailliau dieter@gmail.com javascript: wrote: File corrupted while reading record: /tmp/bms.auto.mv.db. Possible solution: use the recovery tool [90030-182] http://127.0.1.1:8082/login.do?jsessionid=cd6441ab484bba9d2abe4db0d5455f69# 90030/90030 (Help) http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90030 org.h2.jdbc.JdbcSQLException: File corrupted while reading record: /tmp/bms.auto.mv.db. Possible solution: use the recovery tool [90030-182] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=345build=182 ) at org.h2.message.DbException.get(DbException.java:168 http://h2database.com/html/source.html?file=org/h2/message/DbException.javaline=168build=182 ) at org.h2.mvstore.db.MVTableEngine.init(MVTableEngine.java:115 http://h2database.com/html/source.html?file=org/h2/mvstore/db/MVTableEngine.javaline=115build=182 ) at org.h2.engine.Database.getPageStore(Database.java:2376 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=2376build=182 ) at org.h2.engine.Database.open(Database.java:666 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=666build=182 ) at org.h2.engine.Database.openDatabase(Database.java:266 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=266build=182 ) at org.h2.engine.Database.init(Database.java:260 http://h2database.com/html/source.html?file=org/h2/engine/Database.javaline=260build=182 ) at org.h2.engine.Engine.openSession(Engine.java:60 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=60build=182 ) at org.h2.engine.Engine.openSession(Engine.java:167 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=167build=182 ) at org.h2.engine.Engine.createSessionAndValidate(Engine.java:145 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=145build=182 ) at org.h2.engine.Engine.createSession(Engine.java:128 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=128build=182 ) at org.h2.engine.Engine.createSession(Engine.java:26 http://h2database.com/html/source.html?file=org/h2/engine/Engine.javaline=26build=182 ) at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:347 http://h2database.com/html/source.html?file=org/h2/engine/SessionRemote.javaline=347build=182 ) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:108 http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.javaline=108build=182 ) at org.h2.jdbc.JdbcConnection.init(JdbcConnection.java:92 http://h2database.com/html/source.html?file=org/h2/jdbc/JdbcConnection.javaline=92build=182 ) at org.h2.Driver.connect(Driver.java:72 http://h2database.com/html/source.html?file=org/h2/Driver.javaline=72build=182 ) at org.h2.server.web.WebServer.getConnection(WebServer.java:750 http://h2database.com/html/source.html?file=org/h2/server/web/WebServer.javaline=750build=182 ) at org.h2.server.web.WebApp.login(WebApp.java:957 http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.javaline=957build=182 ) at org.h2.server.web.WebApp.process(WebApp.java:211 http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.javaline=211build=182 ) at org.h2.server.web.WebApp.processRequest(WebApp.java:170 http://h2database.com/html/source.html?file=org/h2/server/web/WebApp.javaline=170build=182 ) at org.h2.server.web.WebThread.process(WebThread.java:137 http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.javaline=137build=182 ) at org.h2.server.web.WebThread.run(WebThread.java:93 http://h2database.com/html/source.html?file=org/h2/server/web/WebThread.javaline=93build=182 ) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.IllegalStateException: Chunk 42948 not found [1.4.182/6] at org.h2.mvstore.DataUtils.newIllegalStateException(DataUtils.java:762 http://h2database.com/html/source.html?file=org/h2/mvstore/DataUtils.javaline=762build=182 ) at org.h2.mvstore.MVStore.getChunk(MVStore.java:853 http://h2database.com/html/source.html?file=org/h2/mvstore/MVStore.javaline=853build=182 ) at org.h2.mvstore.MVStore.readPage(MVStore.java:1805 http://h2database.com/html/source.html?file=org/h2/mvstore/MVStore.javaline=1805build=182 ) at org.h2.mvstore.MVMap.readPage(MVMap.java:769
[h2] MERGE error cased by NOT NULL columns (that are not set null)
Hi, I was trying to fix some screwed up data by running this by hand: MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), (171, '2014-11-27 10:35:09.764', 3), (172, '2014-11-27 10:35:41.147', 3), (173, '2014-11-27 11:58:11.06', 3), (174, '2014-11-27 12:00:32.21', 3), (176, '2014-12-01 14:58:41.081', 3), (178, '2014-12-03 09:48:16.415', 3), (179, '2014-12-03 10:07:49.815', 3), (181, '2014-12-05 09:36:35.754', 3), (182, '2014-12-08 08:47:11.349', 3), (183, '2014-12-09 12:48:11.538', 3), (184, '2014-12-09 14:16:26.757', 3), (185, '2014-12-11 14:43:40.76', 3), (186, '2014-12-11 16:23:17.315', 3), (187, '2014-12-12 09:02:29.67', 3), (188, '2014-12-12 12:10:01.006', 3); On a table defined as: CREATE TABLE IF NOT EXISTS SheetOrders ( id IDENTITY PRIMARY KEY, printOrder BIGINT NULL, product BIGINT NULL, sheet BIGINT NOT NULL, amount INT NOT NULL, note VARCHAR(255), status INT NOT NULL, createdAt TIMESTAMP NOT NULL, createdBy BIGINT NOT NULL, FOREIGN KEY (printOrder) REFERENCES PrintOrders(id), FOREIGN KEY (product) REFERENCES Products(id), FOREIGN KEY (sheet) REFERENCES Sheets(id), FOREIGN KEY (createdBy) REFERENCES Users(id) ); What I get is: NULL not allowed for column SHEET; SQL statement: MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), ... I don't understand this behavior. I updated several other tables in the same manner - all had NOT NULL and FOREIGN KEY columns and none gave me any errors. I would suppose that when I don't specify the NOT NULL column as NULL explicitly in the merge, the DB should not even care about that column in a MERGE that does not mention that column. What is happening? How do I get around this? I also tried to set the columns NULL with ALTER before the MERGE (then the MERGE worked fine) and then back to NOT NULL which gave me this error afterwards: Column SHEET contains null values; SQL statement: ALTER TABLE SheetOrders ALTER COLUMN sheet SET NOT NULL [90081-175] [ERROR:90081, SQLSTATE:90081] But when I queried the table for NULL values like such: SELECT * FROM SheetOrders WHERE sheet = NULL; I got 0 rows back, which means there are no NULL values in that column. So what's the deal? Is the DB corrupt? Is this a bug? Undocumented feature? Why can't I MERGE into the table and set the column to NOT NULL? Regards, David -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] MERGE error cased by NOT NULL columns (that are not set null)
Merge does either an insert or update. If it does an insert it must always insert a complete row. If the user does not provide a value for some column then dbms uses the default value for that column. If the user did not explicitly define a default value for the column then the default value is NULL. Ergo the dbms tries to insert a row where column sheet has value NULL but NULL is not allowed for that column. Does this make sense? - rami On 19.12.2014 21:04, David Hájek wrote: Hi, I was trying to fix some screwed up data by running this by hand: MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), (171, '2014-11-27 10:35:09.764', 3), (172, '2014-11-27 10:35:41.147', 3), (173, '2014-11-27 11:58:11.06', 3), (174, '2014-11-27 12:00:32.21', 3), (176, '2014-12-01 14:58:41.081', 3), (178, '2014-12-03 09:48:16.415', 3), (179, '2014-12-03 10:07:49.815', 3), (181, '2014-12-05 09:36:35.754', 3), (182, '2014-12-08 08:47:11.349', 3), (183, '2014-12-09 12:48:11.538', 3), (184, '2014-12-09 14:16:26.757', 3), (185, '2014-12-11 14:43:40.76', 3), (186, '2014-12-11 16:23:17.315', 3), (187, '2014-12-12 09:02:29.67', 3), (188, '2014-12-12 12:10:01.006', 3); On a table defined as: CREATE TABLE IF NOT EXISTS SheetOrders ( id IDENTITY PRIMARY KEY, printOrder BIGINT NULL, product BIGINT NULL, sheet BIGINT NOT NULL, amount INT NOT NULL, note VARCHAR(255), status INT NOT NULL, createdAt TIMESTAMP NOT NULL, createdBy BIGINT NOT NULL, FOREIGN KEY (printOrder) REFERENCES PrintOrders(id), FOREIGN KEY (product) REFERENCES Products(id), FOREIGN KEY (sheet) REFERENCES Sheets(id), FOREIGN KEY (createdBy) REFERENCES Users(id) ); What I get is: NULL not allowed for column SHEET; SQL statement: MERGE INTO SheetOrders (id, createdAt, createdBy) KEY(id) VALUES (154, '2014-11-20 08:36:07.209', 4), (164, '2014-11-24 10:25:06.395', 3), (165, '2014-11-24 10:25:46.987', 3), ... I don't understand this behavior. I updated several other tables in the same manner - all had NOT NULL and FOREIGN KEY columns and none gave me any errors. I would suppose that when I don't specify the NOT NULL column as NULL explicitly in the merge, the DB should not even care about that column in a MERGE that does not mention that column. What is happening? How do I get around this? I also tried to set the columns NULL with ALTER before the MERGE (then the MERGE worked fine) and then back to NOT NULL which gave me this error afterwards: Column SHEET contains null values; SQL statement: ALTER TABLE SheetOrders ALTER COLUMN sheet SET NOT NULL [90081-175] [ERROR:90081, SQLSTATE:90081] But when I queried the table for NULL values like such: SELECT * FROM SheetOrders WHERE sheet = NULL; I got 0 rows back, which means there are no NULL values in that column. So what's the deal? Is the DB corrupt? Is this a bug? Undocumented feature? Why can't I MERGE into the table and set the column to NOT NULL? Regards, David -- 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 mailto:h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com mailto:h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
