Bug description

The saveAll() method in JdbcChatMemoryRepository attempts to guarantee message ordering using millisecond precision, but fails due to MySQL and MariaDB's default TIMESTAMP precision of 0 (seconds only).

Specifically, in the AddBatchPreparedStatement class:

  1. Instant.now().toEpochMilli() initializes the current time in milliseconds
  2. AtomicLong.getAndIncrement() attempts to guarantee ordering by incrementing each message by 1 millisecond
  3. However, MySQL/MariaDB's default TIMESTAMP has precision 0, causing millisecond information to be lost
  4. As a result, all messages stored within the same second have identical timestamps, causing random ordering

Message ordering must be accurately guaranteed for maintaining conversation context, but this is impossible with the current MySQL/MariaDB schema.

saveAll

@Override
public void saveAll(String conversationId, List<Message> messages) {
    Assert.hasText(conversationId, "conversationId cannot be null or empty");
    Assert.notNull(messages, "messages cannot be null");
    Assert.noNullElements(messages, "messages cannot contain null elements");

    this.transactionTemplate.execute(status -> {
        deleteByConversationId(conversationId);
        this.jdbcTemplate.batchUpdate(this.dialect.getInsertMessageSql(),
                new AddBatchPreparedStatement(conversationId, messages));
        return null;
    });
}

AddBatchPreparedStatement

private record AddBatchPreparedStatement(String conversationId, List<Message> messages,
        AtomicLong instantSeq) implements BatchPreparedStatementSetter {

    private AddBatchPreparedStatement(String conversationId, List<Message> messages) {
        this(conversationId, messages, new AtomicLong(Instant.now().toEpochMilli()));
    }

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        var message = this.messages.get(i);

        ps.setString(1, this.conversationId);
        ps.setString(2, message.getText());
        ps.setString(3, message.getMessageType().name());
        ps.setTimestamp(4, new Timestamp(this.instantSeq.getAndIncrement()));
    }

    @Override
    public int getBatchSize() {
        return this.messages.size();
    }
}

schema-mysql

CREATE TABLE IF NOT EXISTS SPRING_AI_CHAT_MEMORY (
    `conversation_id` VARCHAR(36) NOT NULL,
    `content` TEXT NOT NULL,
    `type` ENUM('USER', 'ASSISTANT', 'SYSTEM', 'TOOL') NOT NULL,
    `timestamp` TIMESTAMP NOT NULL,

    INDEX `SPRING_AI_CHAT_MEMORY_CONVERSATION_ID_TIMESTAMP_IDX` (`conversation_id`, `timestamp`)
);

schema-mariadb

CREATE TABLE IF NOT EXISTS SPRING_AI_CHAT_MEMORY (
    conversation_id VARCHAR(36) NOT NULL,
    content TEXT NOT NULL,
    type VARCHAR(10) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL,
    CONSTRAINT TYPE_CHECK CHECK (type IN ('USER', 'ASSISTANT', 'SYSTEM', 'TOOL'))
);

CREATE INDEX IF NOT EXISTS SPRING_AI_CHAT_MEMORY_CONVERSATION_ID_TIMESTAMP_IDX
ON SPRING_AI_CHAT_MEMORY(conversation_id, `timestamp`);

Proposed solution

-- Current (schema-mysql.sql, schema-mariadb.sql):
`timestamp` TIMESTAMP NOT NULL

-- Proposed fix:
`timestamp` TIMESTAMP(3) NOT NULL