MariaDB, Data::UUID GUID: data too long for column binary(16)

Question

I have a table:

CREATE TABLE `sessions` (
  id BINARY(16) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

I created a value to insert into the id field:

$sql = 'INSERT INTO sessions (id) VALUES(?)';
my $generator = Data::UUID->new;
my $session_id = $generator->create();
$result = $dbh->do($sql, undef, $session_id);

According to the documentation to Data::UUID module:

# creates binary (16 byte long binary value) UUID.
$ug->create();

https://metacpan.org/pod/Data::UUID

But when I try to INSERT the data I get an error from MariaDB:

Data too long for column 'id'

Why is it too long if the field it 16 bytes long and according to docs the size of $session_id is also 16 bytes?

The issue I suspect is the driver: I connect using "dbi:MariaDB:$dbinst:$dbhost", while another user who tried mysql driver didn't face any errors.

Answer 1

MariaDB >= 10.7

You're better off using the UUID type in MariaDB, and using Data::UUID::create_str. That would look something like this:

CREATE TABLE `sessions` (
  id UUID NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

$sql = 'INSERT INTO sessions (id) VALUES(?)';
my $generator = Data::UUID->new;
my $session_id = $generator->create_str();
$result = $dbh->do($sql, undef, $session_id);

MariaDB < 10.7

The simplest solution is just to use varchar(16), preferably with the MyRocks engine if it is available (MyRocks will drastically improve varchar key performance and compression):

CREATE TABLE `sessions` (
  id VARCHAR(16) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=MyRocks;

$sql = 'INSERT INTO sessions (id) VALUES(?)';
my $generator = Data::UUID->new;
my $session_id = $generator->create_bin();
$result = $dbh->do($sql, undef, $session_id);

Explanation

For some reason, Data::UUID returns a value with a length(?) varied from 23 to 27 bytes, but a char_length(?) of 16. This is probably a bug with either Data::UUID or DBD::MariaDB/DBD::mysql.