IntegrityConstraintViolation when trying to modify inventory data of offline player through API

When trying to modify the contents of the current data (inventory) of an offline Player, it fails to save the modifications due to an SQL constraint violation. When the player is online, the data modifies as intended and is correctly saved to the database.

Attached are an image of the current data snapshots of a player, first taken when modified when the player was online (snapshots 4-5) and when the player was offline (snapshot 1)

The thrown stacktrace:

[19:14:38 ERROR]: [HuskSync] Failed to set user data in the database
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'c75d7e5d-d9d2-43b3-8122-78b9899a7bdb-3d849e7f-04f3-41df-b486-...' for key 'PRIMARY'
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:555) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:339) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994) ~[mysql-connector-j-8.0.32.jar:8.0.32]
        at net.william278.husksync.libraries.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.libraries.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.database.MySqlDatabase.createSnapshot(MySqlDatabase.java:384) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.database.Database.addSnapshot(Database.java:180) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.sync.DataSyncer.addSnapshotToDatabase(DataSyncer.java:142) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.sync.DataSyncer.lambda$saveData$0(DataSyncer.java:118) ~[HuskSync-3.4.jar:?]
        at net.william278.husksync.event.EventDispatcher.lambda$fireEvent$0(EventDispatcher.java:46) ~[HuskSync-3.4.jar:?]
        at org.bukkit.craftbukkit.v1_19_R3.scheduler.CraftTask.run(CraftTask.java:101) ~[purpur-1.19.4.jar:git-Purpur-1985]
        at org.bukkit.craftbukkit.v1_19_R3.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:57) ~[purpur-1.19.4.jar:git-Purpur-1985]
        at com.destroystokyo.paper.ServerSchedulerReportingWrapper.run(ServerSchedulerReportingWrapper.java:22) ~[purpur-1.19.4.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
        at java.lang.Thread.run(Thread.java:833) ~[?:?]

Output of /husksync status

> husksync status
[19:15:19 INFO]: HuskSync | System status report:
[19:15:19 INFO]: • Plugin Version: v3.4
[19:15:19 INFO]: • Platform Type: Bukkit
[19:15:19 INFO]: • Language: en-gb
[19:15:19 INFO]: • Minecraft Version: 1.19.4-R0.1-SNAPSHOT
[19:15:19 INFO]: • Java Version: 17.0.2
[19:15:19 INFO]: • Java Vendor: Oracle Corporation
[19:15:19 INFO]: • Sync Mode: Lockstep
[19:15:19 INFO]: • Delay Latency: 500ms
[19:15:19 INFO]: • Server Name: dev-game-1
[19:15:19 INFO]: • Database Type: MySQL
[19:15:19 INFO]: • Is Database Local: No
[19:15:19 INFO]: • Using Redis Sentinel: No
[19:15:19 INFO]: • Using Redis Password: No
[19:15:19 INFO]: • Redis Using Ssl: No
[19:15:19 INFO]: • Is Redis Local: No
[19:15:19 INFO]: • Data Types: husksync:inventory ✔, husksync:ender_chest ✔, husksync:advancements ✔, husksync:location ❌, husksync:health ✔, husksync:hunger ✔, husksync:game_mode ✔, husksync:potion_effects ✔, husksync:statistics ❌, husksync:experience ✔, husksync:persistent_data ✔

Java code that is used for the interaction with HuskSync

private BukkitHuskSyncAPI huskSyncAPI;

public HuskSyncHandler() {
    this.huskSyncAPI = BukkitHuskSyncAPI.getInstance();
}

// Called for online player modifications
public CompletableFuture<List<ItemStack>> clearInventory(Player player) {
    return this.clearInventory(this.huskSyncAPI.getUser(player));
}

// Called for offline player modifications
public CompletableFuture<List<ItemStack>> clearInventory(UUID uuid) {
    return this.huskSyncAPI.getUser(uuid).thenCompose(user -> user.map(this::clearInventory).orElseGet(() -> CompletableFuture.completedFuture(Collections.emptyList())));
}

private CompletableFuture<List<ItemStack>> clearInventory(User user) {
    CompletableFuture<List<ItemStack>> completableFuture = new CompletableFuture<>();

    this.huskSyncAPI.editCurrentInventory(user, items -> {
        DataSnapshot.Packed beforeModificationSnapshot =
                this.huskSyncAPI
                        .snapshotBuilder()
                        .saveCause(DataSnapshot.SaveCause.of("Plugin Modification Backup"))
                        .inventory(items)
                        .buildAndPack();

        this.huskSyncAPI.addSnapshot(user, beforeModificationSnapshot);

        List<ItemStack> drops = this.filterInventory(items.getContents());
        completableFuture.complete(drops);
    });

    return completableFuture;
}

WiIIiam278 / HuskSync

IntegrityConstraintViolation when trying to modify inventory data of offline player through API #275