Hi again rmuller, The BINARY data type should not be used for large objects (more than a few kB) unless you have a lot of memory, and the documentation makes this clear. H2 doesn't put arbitrary limits on BINARY or VARCHAR size like MySQL or many other engines, but this is for convenience and will cause problems if abused.
You need to switch to BLOB if you want to do multi-row updates on MB- sized objects. OR, you can split big transactions into very small ones to fit in memory. To migrate, you'll want to add the BLOB column and then migrate BINARY data to blob in one-row transactions to avoid memory issues. This can easily be done using JDBC, or by running a number of one-row (or few-row) UPDATE statements with WHERE conditions that use the primary key to limit transaction size. Regards, Sam On Jan 20, 2:43 am, rmuller <[email protected]> wrote: > Hi Sam, > > Thanks for the quick reply! > > Sorry, i am not clear enough (now I read the title again): the point > is that processing 100 files of 1MB in one transaction with 128MB > memory is not possible with VARBINARY's even if i set > "MAX_MEMORY_UNDO" to a (very) low value. > > Regards, > > Ronald > > On Jan 19, 10:52 pm, Sam Van Oort <[email protected]> wrote: > > > Hi, > > > This behavior is normal and expected -- it's one of the main > > differences between BINARY and BLOB types. > > See the > > documentation:http://www.h2database.com/html/datatypes.html#binary_type > > > Cheers, > > Sam Van Oort > > On Jan 19, 10:56 am, rmuller <[email protected]> wrote: > > > > Hi, > > > > If i store binary data (actual photo's) into H2 in *one* transaction i > > > run into memory problems when using VARBINARY. If i use BLOB's > > > everything goes well. See code sample. Is this a H2 issue or do I miss > > > a configuration option? > > > > final Properties props = new Properties(); > > > props.setProperty("USER", "admin"); > > > props.setProperty("PASSWORD", "admin"); > > > props.setProperty("MAX_MEMORY_UNDO", "100"); > > > > Class.forName("org.h2.Driver"); > > > final Connection c = DriverManager.getConnection("jdbc:h2:./ > > > test", props); > > > c.setAutoCommit(false); > > > > final Statement stm = c.createStatement(); > > > //stm.executeUpdate("create table TEST (id IDENTITY, data > > > BLOB)"); // OK > > > stm.executeUpdate("create table TEST (id IDENTITY, data > > > VARBINARY)"); // OOM > > > stm.close(); > > > > final PreparedStatement pstm = c.prepareStatement( > > > "INSERT INTO test (data) VALUES (?)"); > > > > // H2 keeps the undo log in memory by default, MAX_MEMORY_UNDO > > > must be set > > > // to a low value if memory is limited > > > // When using VARBINARY and -Xmx128M OoM always occurs > > > int count = 0; > > > while (++count < 100) { > > > pstm.setBytes(1, new byte[1024 * 1024]); > > > pstm.executeUpdate(); > > > } > > > > c.commit(); > > > c.close(); > > > > Regards, > > > > Ronald
-- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
