[FIXED] VARCHAR column breaks Hibernate quietly when content is more than 141 characters

Issue

We have a very strange issue using Spring Boot (v2.1.5) and Hibernate (5.3.10). We have a table DocumentRevision which has foreign keys e.g. Document -> DocumentRevision -> User. When we fetch Document, DocumentRevision is eager-fetched, and User is lazy-fetched. This is working correctly in most cases, but failing in very specific cases and in a very specific way.

DocumentRevision actually has two foreign keys to User: uploader (NOT NULL) and approver (NULL).

One of the columns in DocumentRevision is source VARCHAR(255) NULL.

When DocumentRevision.source contains 142 or more characters in length, uploader is not fetched by Hibernate, and a NullPointerException results. For the same record, when the source column is null or 0-141 characters in length, the uploader is correctly fetched. This is 100% reproducible, 142 characters is the breaking point. And note, these are ASCII characters, nothing special (collation is utf8mb4_unicode_ci).

• completely filling all other VARCHAR fields in a non-broken record has no effect -- everything works correctly (it is literally only the source column which causes this issue, and only when it contains 142 or more characters)
• I can break a non-broken record by inserting 142 or more characters in the source column
• setting approverId to null for a broken record fixes the issue
• setting it to a valid user's id, but a different one than uploaderId does not fix the issue
• when both creator and approver hold the exact same value in the database (point to the same User), hibernate is able to fetch approver but not creator -- that is, when I examine the resulting Document, its DocumentRevision does not have a creator, but it does have an approver -- this is only true when debugging and inspecting the value
• turning on SQL logging shows X statements executed for a broken record and X+3 for a non-broken record -- the additional SQL statements are related to fetching the User (they simply do not happen in the broken case)

Debugging Failures

I've tried the following with the same results.

1. increase size of source column
2. replace @Column annotation with @Column(length = X)
3. rename table
4. rename column
5. upgrade Spring / Hibernate
6. mark uploader as FetchType.EAGER
7. remove optional = false from uploader

Update: We've noticed that this can also be fixed by removing the many-to-one relationship from Document to DocumentRevision (Document.documentRevisions). Also, removing the one-to-one relationship from DocumentRevision to Document (DocumentRevision.document) fixes the issue. My current theory is some sort of circular reference -- but again, nothing from Hibernate in the logs.

Here are some snippets:

Table: Document

CREATE TABLE `Document` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deletedDateTime` timestamp NULL DEFAULT NULL,
  `lastModifiedDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL,
  `sectionId` int(11) NOT NULL,
  `currentRevisionId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_Document_sectionId_Section_id` (`sectionId`),
  KEY `FK_Document_currentRevisionId_DocumentRevision_id` (`currentRevisionId`),
  CONSTRAINT `FK_Document_currentRevisionId_DocumentRevision_id` FOREIGN KEY (`currentRevisionId`) REFERENCES `DocumentRevision` (`id`),
  CONSTRAINT `FK_Document_sectionId_Section_id` FOREIGN KEY (`sectionId`) REFERENCES `Section` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34074 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table: DocumentRevision

CREATE TABLE `DocumentRevision` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deletedDateTime` timestamp NULL DEFAULT NULL,
  `lastModifiedDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL,
  `contentLength` bigint(20) DEFAULT NULL,
  `uploaderId` binary(16) NOT NULL,
  `approverId` binary(16) DEFAULT NULL,
  `fileId` int(11) NOT NULL,
  `parsed` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sourcePublishDateTime` timestamp NULL DEFAULT NULL,
  `description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coordinateSystem` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mgrs` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `latitude` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `longitude` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `location` point DEFAULT NULL,
  `sourceUrl` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marking` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approvedDateTime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_DocumentRevision_fileId_Document_id` (`fileId`),
  KEY `FK_DocumentRevision_uploaderId_User_id` (`uploaderId`),
  KEY `FK_DocumentRevision_approverId_User_id` (`approverId`),
  CONSTRAINT `FK_DocumentRevision_approverId_User_id` FOREIGN KEY (`approverId`) REFERENCES `User` (`id`),
  CONSTRAINT `FK_DocumentRevision_fileId_Document_id` FOREIGN KEY (`fileId`) REFERENCES `Document` (`id`),
  CONSTRAINT `FK_DocumentRevision_uploaderId_User_id` FOREIGN KEY (`uploaderId`) REFERENCES `User` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table: User

CREATE TABLE `User` (
  `id` binary(16) NOT NULL,
  `createdDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deletedDateTime` timestamp NULL DEFAULT NULL,
  `lastModifiedDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `version` bigint(20) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `firstName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `primaryEmailAddress` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `IDX_Person_username` (`username`),
  KEY `IDX_Person_primaryEmailAddress` (`primaryEmailAddress`),
  KEY `IDX_Person_lastName` (`lastName`),
  KEY `IDX_Person_firstName` (`firstName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Mapping: Document

@Entity
@Table(name = "Document")
@Data
@EqualsAndHashCode(callSuper = true)
public class Document extends BaseEntity<Integer>
{
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "sectionId")
    private Section section;

    @ManyToOne(cascade = CascadeType.REMOVE)
    @JoinColumn(name = "currentRevisionId")
    private DocumentRevision currentRevision;

    @OneToMany(mappedBy = "document", cascade = CascadeType.REMOVE)
    private List<DocumentRevision> documentRevisions = new ArrayList<>();
}

Mapping: DocumentRevision

@Entity
@Table(name = "DocumentRevision")
@Data
@EqualsAndHashCode(callSuper = true)
public class DocumentRevision extends BaseEntity<Integer>
{
    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "documentId", nullable = false)
    private Document document;

    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "uploaderId")
    private User uploader;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "approverId")
    private User approver;

    @Column
    private String source;

    // other fields removed for brevity
}

Mapping: User

@EqualsAndHashCode
@Data
@Entity
@Table(name = "User")
public class User extends BaseEntity<UUID>
{
    @Column(length = 60, nullable = false)
    private String username;

    @Column(length = 25, nullable = false)
    private String firstName;

    @Column(length = 25, nullable = false)
    private String lastName;

    @Column(length = 255)
    private String primaryEmailAddress;

    @Column(nullable = false)
    private boolean active;

    @Column(nullable = false)
    private boolean hidden;
}

I realize this is a lot of information, but this one's got me stumped!

Solution

We ran into this problem again a while back, and then just last week. I did some more messing around and was able to fix it by upgrading the MySQL Connector dependency from 6.0.6 to 8.0.22.

Answered By - Josh M.