Apr 30

When processing a large file (for example using ‘LOAD DATA LOCAL INFILE’) the connection to mysql can get lost. Reason for this is the mysql server has a limit on the size of a ‘packet’ it receives and when this limit is exceeded it drops the connection.

This limit can be changed using the system variable max_allowed_packet. This specifies the maximum size of a packet in bytes.

A packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.

written by objects \\ tags: ,

Apr 01

When using the mysql jdbc drivers to run queries with that return large result sets you are very likely to get an OutOfMemoryError. Reason for this is that by default the mysql jdbc driver implementation tries to store the entire result set in memory. This is for performance reasons, with the assumption that the majority of result sets are relatively small and will fit in memory (which is fair enough).

If you do have a large result set then this is just not going to work and you will either get an OutOfMemoryError, or at best your application footprint drastically increases and slows to a crawl. Solution is to stream the result set, which is done by using a read only, forward only statement and setting the fetch size to Integer.MIN_VALUE.

Statement statement = connection.createStatement(
    java.sql.ResultSet.TYPE_FORWARD_ONLY,
    java.sql.ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);

Downside to this is that you can’t use the connection for anything else until your streaming result set has been closed.

written by objects \\ tags: , , ,

Feb 04

Depending on your jdbc driver version the following code can fail to run.

File file = new File("image.jpg");
InputStream in = new FileInputStream(file);
preparedStatment.setBinaryStream(1, in, file.length());

The error you will see is as follows.

java.lang.AbstractMethodError: com.mysql.jdbc.ServerPreparedStatement.setBinaryStream(ILjava/io/InputStream;J)V

To fix this problem you need to change the call to setBinaryStream so the last parameter is passed as an integer instead of a long.

File file = new File("image.jpg");
InputStream in = new FileInputStream(file);
preparedStatment.setBinaryStream(1, in, (int) file.length());

written by objects \\ tags: , , ,