Open
Description
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:
Instant.now().toEpochMilli()initializes the current time in millisecondsAtomicLong.getAndIncrement()attempts to guarantee ordering by incrementing each message by 1 millisecond- However, MySQL/MariaDB's default
TIMESTAMPhas precision 0, causing millisecond information to be lost - 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