Sunday, 29 September 2013

SQL Truncate Error when Inserting UUID

SQL Truncate Error when Inserting UUID

I'm building a database driven PHP application that uses UUID's to store
each row of data. The application currently generates the UUID's using the
following query:
SELECT UUID()
which generates an output similar to 84058227-294c-11e3-916a-7a7919b2b2bc.
Many online sources suggest that UUID's be stored in BINARY(16) to improve
overall application performance. But when I attempt to insert the UUID
into the database, I receive the following error:
Warning: #1265 Data truncated for column 'x' at row x
I realize that this error occurred because the UUID was too large for the
column to store, and that this issue can be easily fixed by simply
increasing the amount of characters the column may store (ex: BINARY(20)),
but I fear that doing so may reduce the application's performance in the
future.
Considering that so many online sources suggest using BINARY(16) to store
UUID's, I'm assuming that I have made a mistake.
Could someone please point me in the right direction?
For extra information, here is the code I'm using (in PHP) to insert data
into the database:
//PDO Query
$this->query(
INSERT INTO users
(
user_id, //the UUID is stored in this column
taxonomy_id,
user_email,
user_password,
user_salt,
user_activation_key,
user_is_administrator
)
VALUES(?,?,?,?,?,?,?)
',
array(
$this->uuid(), //method that generates UUID
$taxonomy_id,
$user_email,
$user_password,
$user_salt,
$user_activation_key,
$user_is_administrator
)
)
);
and the method that generates each UUID:
public function uuid()
{
$uuid = $this->query("SELECT UUID() AS uuid");
return $uuid[0]['uuid'];
}

No comments:

Post a Comment