1

The Problem

Overview

So I'm trying to create records that have:

  1. id (primary key, auto-increment)

  2. 4 columns that are each a different UUID generated using the MySQL functions: REPLACE(UUID(), '-', ''). These 'obfuscated keys' are used for external lookup in other tables. REPLACE() is used to strip the dashes (-) from the UUID generated by MySQL, which is required to later transform the UUID into a binary format. Please don't worry about that!!!

The Table structure is as follows:
+-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | info | varchar(32) | YES | UNI | NULL | | | note | varchar(32) | YES | UNI | NULL | | | auth | varchar(32) | YES | UNI | NULL | | | test | varchar(32) | YES | UNI | NULL | | +-----------------+-------------+------+-----+---------+-------+

Environment

OS         : CentOS 7
Database   : MariaDB/MySQL
DB Version : 5.5


Partial Solutions Found

I have found a few articles that seem to point "a direction", however I have been unable to solve this.

  • Trigger before insert : Partially works, but only on the first column. The MySQL error is: Error Code: 1235. This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table' Also requires the columns to allow null defaults. Seems like too much of a band-aid hack.

    CREATE TRIGGER before_insert_infoid 
      BEFORE INSERT ON user 
      FOR EACH ROW 
          SET new.info = replace(uuid(),'-',''); 
    CREATE TRIGGER before_insert_noteid 
      BEFORE INSERT ON user 
      FOR EACH ROW 
          SET new.note = replace(uuid(),'-',''); 
    CREATE TRIGGER before_insert_authid 
      BEFORE INSERT ON user 
      FOR EACH ROW 
          SET new.auth = replace(uuid(),'-',''); 
    CREATE TRIGGER before_insert_testid 
      BEFORE INSERT ON user 
      FOR EACH ROW 
          SET new.test = replace(uuid(),'-',''); 
    
  • Direct using insert : Works, but means that the logic is not contained in the database. Also, I get a MySQL warning warning(s): 1364 Field 'id' doesn't have a default value.

    INSERT INTO `meanlearn`.`students` 
    (`info`, 
    `note`, 
    `auth`, 
    `test`) 
    VALUES 
    (REPLACE(UUID(),'-',''), 
    REPLACE(UUID(),'-',''), 
    REPLACE(UUID(),'-',''), 
    REPLACE(UUID(),'-','')); 
    


Preliminary Conclusion / Request for Solutions

I'm sure I'm missing something. Any and all suggestions are greatly appreciated.

5
  • This isn't SO. Not much poo-poo here ;) Sep 7, 2016 at 20:47
  • By the way, the error you got on the multiple trigger, shows a direction to a possible solution. Create one trigger, that sets all 4 columns. Sep 7, 2016 at 20:49
  • @ypercubeᵀᴹ could you show me a rewrite on using the trigger to set all 4 columns using the single trigger? It still leaves the structure as the columns are null-able, which is not really ideal. Sep 7, 2016 at 21:25
  • 1
    I've added an answer. To be honest, I haven't really understood why you would need 4 UUIDs in a table, so I can't think of any other solution. Perhaps you can explain your situation a bit more. Sep 7, 2016 at 21:53
  • @ypercubeᵀᴹ The short answer is to obfuscate linked record on external tables. Each uuid is a lookup on another external table. I am envisioning storing the external ids as binary and indexed, but i need the human readable for logging chain of access. The stored uuids will be dehexed and converted prior to lookup externally. Does that kind of makes sense? I know it's a bit convoluted but it was requested by my client. I will follow up on your answer shortly. Thanks a ton though. Sep 9, 2016 at 15:00

1 Answer 1

2

One way to vercome the multiple trigger error would be ot use one trigger - and not 4 - to set the new UUID values:

DELIMITER //
CREATE TRIGGER before_insert_on_user_set_uuids
  BEFORE INSERT ON user 
  FOR EACH ROW 
    BEGIN
      SET new.info = replace(uuid(),'-',''); 
      SET new.note = replace(uuid(),'-',''); 
      SET new.auth = replace(uuid(),'-',''); 
      SET new.test = replace(uuid(),'-',''); 
    END ;  //
DELIMITER ;

It would also be good idea to store the UUIDs in BINARY(16) columns instead of VARCHAR(32). Saves 16x4 bytes per row (plus 16 per row multiplied by the number of simple indexes on the columns) and some speed for not considering collations and character sets. And I assume you had the varchar column as ASCII and not UTF-8 which would be a real waste.

1
  • So this does work, but (of course) it generates a warning 1 row(s) affected, 4 warning(s): 1364 Field 'info' doesn't have a default value 1364 Field 'note' doesn't have a default value 1364 Field 'auth' doesn't have a default value 1364 Field 'test' doesn't have a default value Do you think I would be better off generating the UUID's externally and passing them in to the insert statement? I was trying to insulate my web-app from having to directly handle this logic, but maybe it is better to do that. FYI, I am using Node.js, Angular/Express, Sequalize as my primary Web Development stack Sep 10, 2016 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.