Description:
Trying to convert a UUID-string to Binary using the UUID_TO_BIN() function in a Before Insert trigger to insert it into a BINARY(16) column fails with an Error 1406: Data too long for column.
Apparently, the string is already casted to binary BEFORE the on insert trigger is fired.
Why doing this in a Trigger?
I'm trying to insert multiple rows in a single statement from a NodeJS Application (via https://www.npmjs.com/package/mysql) as bulk insert like
"INSERT INTO table (col1, col2) VALUES ?" and then supply the values as an Array of Arrays. In the App I have UUID strings (32 Chars) that I need to convert to binary and insert into the col1 which is BINARY(16).
Therefore doing the conversion in the statement is not possible.
It's also not possible (as far as I know) to pass the data (Array of Arrays) to a Stored Procedure and do the conversion there. It might be possible to do the conversion in the Node App, but than the Index-Optimization via the swap_flag in the UUID_TO_BIN Function is not usable...
The UUIDs were generated by MySQL using UUID() before, so they should be valid.
How to repeat:
Create a Table:
CREATE TABLE `t_test` (
`col1` BINARY(16) NOT NULL,
`col2` VARCHAR(45) NULL,
PRIMARY KEY (`col1`));
WHAT WORKS:
insert into t_test SET
col1 = UUID_TO_BINARY('31D98195DA95EA119EBD005056806AF5'),
col2 = 'TEST'
;
WHAT DOES FAIL:
Create a Trigger
CREATE DEFINER=`test_db`@`%` TRIGGER `ConvertUUID` BEFORE INSERT ON `t_test` FOR EACH ROW BEGIN
SET New.col1 = UUID_TO_BIN(New.col1);
END
And then insert Data Like
insert into t_test SET
col1 = '31D98195DA95EA119EBD005056806AF5',
col2 = 'TEST'
;
Suggested fix:
It seems like the string is converted to binary before the Trigger is executed, therefor the result is mixed up.
Is there a way to cast it back to a string that can than be converted using the UUID_TO_BIN() Function or to seomehow prevent the type-casting before the trigger fires?
