How to Use ‘Update on Duplicate Key’ in MariaDB

duplicationmariadbupdate

As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through the FKs in related tables, such as the dates they held certain positions.

If I then set the names of the member in table 'member' to empty strings I can still use my related tables, for example to determine that there was someone holding a certain position at a certain time but I will no longer be able to ascertain either the name or the id of the person who held it, which is what I want.

I have written a simple a UDF obfuscate_id() that takes a positive integer ID and returns a negative ID calculated by combining the id passed with a random number.

FUNCTION obfuscate_id(id INT) RETURNS INT
COMMENT 'returns the negative version of the id passed, obfuscated by a random number'

BEGIN
  DECLARE result INT;
  SET result =  (FLOOR(RAND(id)*10000) + id ) * -1;
  IF result > 0  THEN -- ensures result is -ve even if called with a -ve parameter
     SET result = result * -1; 
  END IF;  
  RETURN  result;
END;

And I am using the sql

UPDATE member
SET  member_id = obfuscate_id(member_id)
WHERE member_id = 1234;

However, this might generate a negative id that already exists from a previous update of another member_id. If so I want to set it to something else, maybe by obfuscating the id again as in

SET  member_id = obfuscate_id(obfuscate_id(member_id));

Is there a simple way, like ON DUPLICATE KEY … to check if the value I am about to update to already exists and if so update to something else?

I did look at
how-to-use-on-duplicate-key-for-update
and
update-on-duplicate-key-update
and
mysql-update-with-on-duplicate-key-update
but am no wiser

I would add that given the size of table 'member' and the particular application, even the first update is going to be a very rare occurrence, so the chance of having any obfuscated IDs is extremely slim, let alone duplicate ones, but I'd like to cover the eventuality if I can.

Best Answer

Wouldn't it be simpler / easier to just choose a random number who's range is outside your current range of values in the member table? E.g. something like (FLOOR(RAND(id)) * -1) + (SELECT MIN(id) FROM member) (semi-pseudocode)?

Related Solutions

Mysql – How to do something like UPDATE DELAYED in MySQL

While there is no such command as UPDATE DELAYED, you can create this mechanism in a rather strange way. Please follow my rather crazy train of thought...

SCENARIO : Let's say you have just a single DB Server called M1

You want to fire UPDATEs at a single table called mydb.timing
You don't want UPDATEs to log jam the table

SOLUTION : Serialize UPDATEs

MECHANISM : Would you believe MySQL Replication ?

That's right, I said MySQL Replication.

You are probably saying right now

How in the world would having a Slave create UPDATE DELAYED ?

Here is how you do it:

STEP 01 : Using another DB Server (call it M2), create a MySQL Instance with binary logging enabled.

STEP 02 : mysqldump all user databases with no data, just structures (called it MySQLSchema.sql)

STEP 03 : Edit MySQLSchema.sql, change all ENGINE=MyISAM into ENGINE=BLACKHOLE

STEP 04 : Edit MySQLSchema.sql, change all ENGINE=InnoDB into ENGINE=BLACKHOLE

STEP 05 : Run source MySQLSchema.sql on M2.

STEP 06 : Run RESET MASTER; on M2

STEP 07 : Add replicate-do-table=mydb.timing to /etc/my.cnf on M1

STEP 08 : service mysql restart on M1

STEP 09 : Setup replication from M2 to M1

STEP 10 : In your application, fire your code

set @value=4;
set @name="myAverageValue";
UPDATE `mydb.timing` SET
  `max` = greatest(`max`,@value),
  `average` = `average` + ((@value - `average`) / (`count` + 1)),
  `count` = `count` + 1
WHERE `name` = @name

only at M2

That's it. No locking can ever happen on M2 since mydb.timing on M2 uses the BLACKHOLE Storage Engine (a.k.a /dev/null Storage Engine). Having MySQL Replication from M2 to M1 forms a queue that serializes UPDATEs. The delay appears in the form of MySQL Replication's asynchronous operation. Replication lag (in this instance only) is totally acceptable since you are looking for UPDATE DELAYED.

Give it a Try !!!

CAVEAT

This turns MySQL Replication upside down because the production server becomes a Slave to a BlackHole Server. With this strange topology, you can also implement DELETE DELAYED.

Make sure you include this option

[mysqld]
expire-logs-days=1

on M2. Otherwise, lots of UPDATEs makes for lots of binlogs

Mysql – On duplicate key do nothing

Three ways. Either IGNORE duplicate errors (but don't use that, see note in the end):

INSERT IGNORE 
  ... ;                   -- without ON DUPLICATE KEY

or try to do a redundant update when there is a duplicate:

INSERT 
  ... 
ON DUPLICATE KEY UPDATE
  id = id ;

or check for duplicates before inserting:

INSERT INTO requests
  (id, ctg, msg, nick, filled, dated, filldate)
SELECT
  NULL, 'urgent', 'Help!', 'Hermione', 'Y', NOW(), NOW()
FROM
  dual
WHERE NOT EXISTS
      ( SELECT *  FROM requests  WHERE (nick, msg) = ('Hermione', 'Help!') )
  AND NOT EXISTS
      ( SELECT *  FROM requests  WHERE (ctg, msg) = ('urgent', 'Help!') ) ;

A difference between the 3rd way and the first two is that when there are duplicates, the id will not be incremented. With INSERT IGNORE and INSERT ... ON DUPLICATE KEY, it will be auto incremented and since the insert will not be done, you'll have gaps in the values of id.

I should also add that your scripts should always check for errors anyway and not fail when there is one. Any query or statement can fail and return error occasionally, for various reasons. The tricks above will only save you from one kind of error.

*Note: INSERT IGNORE will ignore all insert related errors, even not null constraint violations, so it is best to avoid it.

Best Answer

Related Solutions

Mysql – How to do something like UPDATE DELAYED in MySQL

CAVEAT

Mysql – On duplicate key do nothing

Related Question