Upserts with version awareness in a relational database - Part Two: MariaDB / MySQL

Insert, or Conditional Update 

I started off trying out MariaDB as a comparison to how PostgreSQL supports upserts with version awareness.  MariaDB started off as a fork of MySQL, so it makes sense that the same approach is also applicable to MySQL. 

From part one of this blog post series, we have a simple table definition:

CREATE TABLE event (id UUID PRIMARY KEY, 
        name varchar(255) NOT NULL,
        version bigint NOT NULL DEFAULT 0)

Unlike PostgreSQL, we don't have:

ON CONFLICT

as a clause to fall back from insert to update.

Instead there is:

ON DUPLICATE

that can be applied as follows (once again, the syntax is JDBC, where each '?' character represents a value that is passed in at runtime):

INSERT INTO event (id, name, version) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    name = (IF(version < VALUES(version), VALUES(name), name)),
    version = (IF(version < VALUES(version), VALUES(version), version))

Stylistically it doesn't seem like a scalable approach that I would want to apply for a table that has more attributes, as we have to specify the version clause on each individual property. I would prefer it if we could specify a where clause to guard the update, but that is not available. 

An anomaly in rows affected? 

Normally when we use executeUpdate on a JDBC Statemenbt we receive back a response that is the row count of the number of rows that were added or altered by the statement. In this situation there is different behaviour, as documented on the MariaDB website. Basically an insert is represented by the value 1, and an update is represented by the value 2 (Sidenote: this is something ChatGPT does not seem to be aware of, as it takes a literal approach to the actual number of rows affected).

I thought that the rows affected values would be useful for being able to keep track of the relative frequencies of inserts versus updates, until I noticed something odd when running some tests...

It seems that when the update is only setting the existing values then the rows affected value that will be returned is 1, making that situation indistinguishable from the insert.

Update

Based on this documentation it seems that we have an explanation for why the update can return rows affected as being 1, "existing row is set to its current values, and the CLIENT_FOUND_ROWS is set".

If I change my JDBC Connection setup to specify the non-default value for useAffectedRows then we get a 1 when an insert occurs, a 2 when an update changes the existing record, and a 0 when the update is setting the values to be the same as the existing state.

Properties properties = new Properties();
properties.put("user", DB_USER);
properties.put("password", PASSWORD);

properties.put("useAffectedRows", true);

Connection connection = DriverManager.getConnection(jdbcUrl, properties);

Disclaimer

The approach shared in this post is not something that I am offering up as a recommended or optimal way to get conditional logic applied when upserting into MariaDB or MySQL.

It's more a case of me thinking aloud, because thinking is allowed.