Saving Large Strings in MySQL / Java / Hibernate

Question

I am trying to save a json document into a mysql 8 database. This json document is over 1GB so the column type is longtext to handle the extra size.

However, anytime I attempt to save the json blob on the java size it throws an error about the string being too long. My Code:

package com.cpa.ipf.bo.forecast;

import com.cpa.ipf.bo.CostCentre;
import com.vladmihalcea.hibernate.type.json.JsonStringType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

import javax.persistence.*;
import java.sql.Clob;
import java.time.LocalDateTime;
import java.util.Objects;

@Entity
public class ForecastMetadata {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private Long userId;

    @ManyToOne
    private CostCentre costCentre;



    private String name;

    private Boolean viewableByAll;

    @Enumerated(EnumType.STRING)
    private ForecastStatus status;

    private LocalDateTime tsCreated;

    private LocalDateTime tsFinished;

    @Lob
    private Clob forecastBody;

    public ForecastMetadata(Long id, Long userId, CostCentre costCentre, String name, Boolean viewableByAll, ForecastStatus status, LocalDateTime tsCreated, LocalDateTime tsFinished, Clob forecastBody) {
        this.id = id;
        this.userId = userId;
        this.costCentre = costCentre;
        this.name = name;
        this.viewableByAll = viewableByAll;
        this.status = status;
        this.tsCreated = tsCreated;
        this.tsFinished = tsFinished;
        this.forecastBody = forecastBody;
    }

    public ForecastMetadata() {}

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public CostCentre getCostCentre() {
        return costCentre;
    }

    public void setCostCentre(CostCentre costCentre) {
        this.costCentre = costCentre;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Boolean getViewableByAll() {
        return viewableByAll;
    }

    public void setViewableByAll(Boolean viewableByAll) {
        this.viewableByAll = viewableByAll;
    }

    public ForecastStatus getStatus() {
        return status;
    }

    public void setStatus(ForecastStatus status) {
        this.status = status;
    }

    public LocalDateTime getTsCreated() {
        return tsCreated;
    }

    public void setTsCreated(LocalDateTime tsCreated) {
        this.tsCreated = tsCreated;
    }

    public LocalDateTime getTsFinished() {
        return tsFinished;
    }

    public void setTsFinished(LocalDateTime tsFinished) {
        this.tsFinished = tsFinished;
    }

    public Clob getForecastBody() {
        return forecastBody;
    }

    public void setForecastBody(Clob forecastBody) {
        this.forecastBody = forecastBody;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ForecastMetadata that = (ForecastMetadata) o;
        return Objects.equals(id, that.id) &&
                Objects.equals(userId, that.userId) &&
                Objects.equals(costCentre, that.costCentre) &&
                Objects.equals(name, that.name) &&
                Objects.equals(viewableByAll, that.viewableByAll) &&
                status == that.status &&
                Objects.equals(tsCreated, that.tsCreated) &&
                Objects.equals(tsFinished, that.tsFinished) &&
                Objects.equals(forecastBody, that.forecastBody);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, userId, costCentre, name, viewableByAll, status, tsCreated, tsFinished, forecastBody);
    }

    @Override
    public String toString() {
        return "ForecastMetadata{" +
                "id=" + id +
                ", userId=" + userId +
                ", costCentre=" + costCentre +
                ", name='" + name + '\'' +
                ", viewableByAll=" + viewableByAll +
                ", status=" + status +
                ", tsCreated=" + tsCreated +
                ", tsFinished=" + tsFinished +
                ", forecastBody='" + forecastBody + '\'' +
                '}';
    }
}

The error:

java.lang.OutOfMemoryError: Requested array size exceeds VM limit
        at java.lang.AbstractStringBuilder.<init>(AbstractStringBuilder.java:68) ~[na:1.8.0_252]
        at java.lang.StringBuilder.<init>(StringBuilder.java:101) ~[na:1.8.0_252]
        at com.mysql.cj.ClientPreparedQueryBindings.setString(ClientPreparedQueryBindings.java:628) ~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at com.mysql.cj.ClientPreparedQueryBindings.setCharacterStream(ClientPreparedQueryBindings.java:335) ~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at com.mysql.cj.ClientPreparedQueryBindings.setCharacterStream(ClientPreparedQueryBindings.java:366) ~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at com.mysql.cj.jdbc.ClientPreparedStatement.setCharacterStream(ClientPreparedStatement.java:1519) ~[mysql-connector-java-8.0.20.jar!/:8.0.20]
        at org.hibernate.type.descriptor.sql.ClobTypeDescriptor$4$1.doBind(ClobTypeDescriptor.java:124) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:90) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:286) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:281) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:56) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2843) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2818) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister$4.bindValues(AbstractEntityPersister.java:3025) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:334) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:195) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:126) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:84) ~[hibernate-entitymanager-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:206) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:149) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:75) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]
        at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784) ~[hibernate-core-4.3.11.Final.jar!/:4.3.11.Final]

Is there any way to get around this?

Answer 1

I see a lot of comments saying you should increase the heap size, but the error message

Requested array size exceeds VM limit

actually means an attempt was made to create an array whose length would have exceeded the platform-specific maximum possible array length (which is usually around Integer.MAX_VALUE). Increasing the heap size will not help. It's not a limit on the size of an array in memory, but rather, on the maximum valid index an array can have.

Now, let's see the code that is causing the issue:

StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));

In the code above, x represents the contents of your Clob as a String. The constructor of StringBuilder delegates to AbstractStringBuilder constructor, which looks like this:

AbstractStringBuilder(int capacity) {
        if (COMPACT_STRINGS) {
            value = new byte[capacity];
            coder = LATIN1;
        } else {
            value = StringUTF16.newBytesFor(capacity);
            coder = UTF16;
        }
    }

Here we see the issue, plain as day: the size of the String representing your CLOB must be so dangerously close to the maximum array length limit, that 1.1 times its size exceeds the limit.

Now, let's see if we can prevent MySQL Connector from trying to initialize such an array (note that we should also aim for MySQL Connector not initializing x, since String is also represented as an array internally, and your data already got dangerously close to the array size limit). Moving up the stack a little bit to ClientPreparedQueryBindings.setCharacterStream(), we get:

boolean useLength = this.useStreamLengthsInPrepStmts.getValue();
String forcedEncoding = this.session.getPropertySet().getStringProperty(PropertyKey.clobCharacterEncoding).getStringValue();
if (useLength && (length != -1)) {
    c = new char[length];
    int numCharsRead = Util.readFully(reader, c, length); // blocks until all read
    if (forcedEncoding == null) {
        setString(parameterIndex, new String(c, 0, numCharsRead));
    } else {
        setBytes(parameterIndex, StringUtils.getBytes(new String(c, 0, numCharsRead), forcedEncoding));
    }
} else {
    c = new char[4096];
    StringBuilder buf = new StringBuilder();
    while ((len = reader.read(c)) != -1) {
        buf.append(c, 0, len);
    }
    if (forcedEncoding == null) {
        setString(parameterIndex, buf.toString());
    } else {
        setBytes(parameterIndex, StringUtils.getBytes(buf.toString(), forcedEncoding));
    }
}

Looking at the code, it seems you are out of luck. You could try to force the execution to jump into one of the other branches of the if statement (using MySql connection properties), but no matter where you go, a large array the size of your input is eventually going to be created.

You might be better off using a BLOB, since setBinaryStream() is implemented as follows:

public void setBinaryStream(int parameterIndex, InputStream x, int length) {
        if (x == null) {
            setNull(parameterIndex);
        } else {
            this.bindValues[parameterIndex].setNull(false);
            this.bindValues[parameterIndex].setIsStream(true);
            this.bindValues[parameterIndex].setMysqlType(MysqlType.BLOB); // TODO use length to find the right BLOB type
            this.bindValues[parameterIndex].setStreamValue(x, length);
        }
    }

...but unfortunately, you would need to implement that TODO yourself, to get a LONGBLOB instead of BLOB. I suppose it all boils down to how badly you want to put that 2GB-sized blob into MySQL using the JDBC driver, which is clearly not suited for the job.