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.

Improve this question
5
  • This isn't SO. Not much poo-poo here ;) Commented 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. Commented 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. Commented 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. Commented 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. Commented Sep 9, 2016 at 15:00

1 Answer 1

Reset to default
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.

Improve this answer
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 Commented Sep 10, 2016 at 20:01

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.