Java storedProcedure stops with OutOfMemoryError

Question

I'm working on a Java project, running on Tomcat 6, which connects to a MySQL database. All procedures run as they should, both when testing local as testing on the server of our customer. There is one exception however, and that's for one procedure which retrieves a whole lot of data to generate a report. The stored procedure takes like 13 minutes or so when executing it from MySQL. When I run the application locally and connect to the online database, the procedure does work, the only time it doesn't work, is when it is run on the server of our client.

The client is pretty protective over his server, so we have limited control over it, but they do want us to solve the problem. When i check the log files, no errors are thrown from the function that executes the stored procedure. And putting some debug logs in the code, it shows that it does get to the execute call, but doesn't log the debug right after the call, neither logs the error in the catch, but does get into the finally section.

They claim there are no time-out errors in the MySQL logs.

If anyone has any idea on what might cause this problem, any help will be appreciated.

update:

after some nagging to the server administrator, I've finally got access to the catalina logs, and in those logs, i've finally found an error that has some meaning:

Exception in thread "Thread-16" java.lang.OutOfMemoryError: Java heap space
        at java.util.Arrays.copyOf(Arrays.java:2894)
        at java.lang.AbstractStringBuilder.expandCapacity(AbstractStringBuilder.java:117)
        at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:407)
        at java.lang.StringBuffer.append(StringBuffer.java:241)
        at be.playlane.mink.database.SelectExportDataProcedure.bufferField(SelectExportDataProcedure.java:68)
        at be.playlane.mink.database.SelectExportDataProcedure.extractData(SelectExportDataProcedure.java:54)
        at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1033)
        at org.springframework.jdbc.core.JdbcTemplate.extractReturnedResultSets(JdbcTemplate.java:947)
        at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:918)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:876)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:908)
        at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)
        at be.playlane.mink.database.SelectExportDataProcedure.execute(SelectExportDataProcedure.java:29)
        at be.playlane.mink.service.impl.DefaultExportService$ExportDataRunnable.run(DefaultExportService.java:82)
        at java.lang.Thread.run(Thread.java:636)

Weird tho that this doesn't log to the application logs, even tho it is wrapped within a try catch. Now based upon the error, the problem lies withing this methods:

public Object extractData(ResultSet rs) throws SQLException, DataAccessException
  {
    StringBuffer buffer = new StringBuffer();

    try
    {
      // get result set meta data
      ResultSetMetaData meta = rs.getMetaData();
      int count = meta.getColumnCount();

      // get the column names; column indices start from 1
      for (int i = 1; i < count + 1; ++i)
      {

        String name = meta.getColumnName(i);
        bufferField(name, i == count, buffer);
      }

      while (rs.next())
      {

        // get the column values; column indices start from 1
        for (int i = 1; i < count + 1; ++i)
        {
          String value = rs.getString(i);
          bufferField(value, i == count, buffer);
        }
      }
    }
    catch (Exception e)
    {
      logger.error("Failed to extractData SelectExportDataProcedue: ", e);
    }

    return buffer.toString();
  }

  private void bufferField(String field, boolean last, StringBuffer buffer)
  {
    try
    {
      if (field != null)
      {

        field = field.replace('\r', ' ');
        field = field.replace('\n', ' ');

        buffer.append(field);
      }

      if (last)
      {
        buffer.append('\n');
      }
      else
      {
        buffer.append('\t');
      }
    }
    catch (Exception e)
    {
      logger.error("Failed to bufferField SelectExportDataProcedue: ", e);
    }
  }

The goal of these function is to export a certain resultset to an excel file (which happens on a higher level).

So if anyone has some tips on optimising this, they are very well welcome.

Ouch...without server access you're going to have some trouble with this one. Can you do a shortened subset of a modified stored procedure e.g: TOP 100 that runs quick and run that via a test environment? Alternatively add some logging to the application and debug via the logs, maybe your application is eating the exception. — Daniel B. Chapman, Jul 27 '12 at 15:28
As you can see in the update, i finally got hold of the catalina logs, which does contain a meaningfull errorlog. So I've managed to locate the problem now, however, i'm unsure on how to optimize this. — Andy, Jul 30 '12 at 08:15
The reason you can't catch this, is that it's an `Error` and not an `Exception`! (no, adding `catch (Error e)` is **not** the correct solution) Also: you build one huge `String`, that's likely to be the source of the problem, you should try to stream the data to wherever you want it instead of allocating one huge object for it. — Joachim Sauer, Jul 30 '12 at 08:18
It doesn't necessarily have to be a problem with those methods, it could be that your ResultSet is just *really large* and you're ending up building a *really large StringBuffer* — Jon Lin, Jul 30 '12 at 08:21

score 2 · Accepted Answer · edited May 23 '17 at 10:34

Ok, your stack trace gives you the answer:

Exception in thread "Thread-16" java.lang.OutOfMemoryError: Java heap space

That's why you're not logging, the application is crashing (Thread, to be specific). Judging from your description it sounds like you have a massive dataset that needs to be paged.

      while (rs.next())
      {

        // get the column values; column indices start from 1
        for (int i = 1; i < count + 1; ++i)
        {
          String value = rs.getString(i);
          bufferField(value, i == count, buffer);
        }
      }

This is where you're thread dies (probably). Basically your StringBuffer runs out of memory. As for correcting it, there's a huge amount of options. Throw more memory at the problem on the client side (either by configuring the JVM (Here's a link): How to set the maximum memory usage for JVM?

Or, if you're already doing that, throw more RAM into the device.

From a programming perspective it sounds like this is a hell of a report. You could offload some of the number crunching to MySQL rather than buffering on your end (if possible), or, if this is a giant report I would consider streaming it to a File and then reading via a buffered stream to fill the report.

It totally depends on what the report is. If it is tiny, I would aim at doing more work in SQL to minimize the result set. If it is a giant report then buffering is the other option.

Another possibility that you might be missing is that the ResultSet (depending on implementations) is probably buffered. That means instead of reading it all to strings maybe your report can take the ResultSet object directly and print from it. The downside to this, of course, is that a stray SQL exception will kill your report.

Best of luck, I'd try the memory options first. You might be running with something hilariously small like 128 and it will be simple (I've seen this happen a lot on remotely administered machines).

Sorry for the late reply, I had allready figured out myselve that streaming it to a file was the best approach for this task. Thanks for the answer tho. — Andy, Sep 03 '12 at 09:22

Java storedProcedure stops with OutOfMemoryError

1 Answers1