MySQL rand() causing error - DOUBLE value is out of range

Question

I have a trigger that generates a random string of length 10. I built my trigger by modifying the accepted answer from this post.

The error I'm receiving is ER_DATA_OUT_OF_RANGE: DOUBLE value is out of range in '(round(rand(),4) * 26)'

The trigger will work fine for hundreds of inserts then it will start failing. Once it starts failing it fails anywhere from 1 to 30 times. If I drop and reinsert the trigger it will start working again, then later on it will start failing.

Trigger causing the error:

DELIMITER //
CREATE TRIGGER add_provider_registration_code BEFORE INSERT ON Care_Provider
  FOR EACH ROW
  BEGIN
    DECLARE code VARCHAR(10);
    SET code = (SELECT concat(
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
      substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1)
      )
    );
    WHILE (SELECT COUNT(registration_code) FROM Care_Provider WHERE registration_code = code) > 0 DO
      SET code = (SELECT concat(
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1),
        substring('abcdefghijklmnopqrstuvwxyz', floor(round(rand(), 4)*26+1), 1)
      )
    );
    END WHILE;
    SET NEW.registration_code = code;
  END;//
DELIMITER ;

I've tried with and without round().
floor(round(rand(), 4)*26+1)
floor(rand()*26+1)

Either way, I still get the error.

@jpmarinier if you're asking about the substring, yes. `SELECT SUBSTRING('abcdefg', 1, 1);` will return the letter 'a' — Imagin8ion, Aug 17 '21 at 16:03

score 1 · Answer 1 · answered Feb 07 '23 at 05:08

We generally get a double value out of range when directly multiplying rand() with a constant. This happens as rand can take any value between 0 and 1 and does not truncate to a fixed number of decimals. Thus, it's a possibility that when multiplied with a constant, the overall digits exceed the allowable limit. To avoid the error, create a separate variable with the round/truncate and use that variable in the main trigger.

set @new_variable = round(rand(), 4);
select substring('abcdefghijklmnopqrstuvwxyz', floor(@new_variable*26+1), 1);

MySQL rand() causing error - DOUBLE value is out of range

1 Answers1