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.
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);
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);
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
.