Symfony2 & FOSRestBundle: Getting UUID packed in a BINARY(16) field from MySQL

Question

I am facing a weird problem relating to UUIDs.

I have developed a REST API using Symfony2+FOSRestBundle+JMSSerializer. As I need to update some tables from two sources I thought of using UUID as primary key for one entity.

I did a doctrine:mapping:import to generate entities in my Symfony project. Everything correct. I ended up with the following entity (only exposing the key field and generated getter for simplicity):

<?php 
namespace Stardigita\TgaAPIBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * TgaBookings
 *
 * @ORM\Table(name="tga_bookings", indexes={[...]})
 * @ORM\Entity
 */
class TgaBookings
{
    /**
     * @var string
     *
     * @ORM\Column(name="book_cd_booking_UUID", type="blob", length=16, nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
    */
    private $bookCdBookingUuid;

    /**
     * Get bookCdBookingUuid
     *
     * @return string 
    */
    public function getBookCdBookingUuid()
    {
        return $this->bookCdBookingUuid;
    }
...

No setter was generated. I can still do it myself and I will, as I will need to know the key beforehand.

The data for this field is correctly stored in the table as a BINARY(16). When I recover the data calling the REST GET method, I get this:

[
{
    "book_cd_booking_uuid": "Resource id #1244",
    "book_cd_booking": 8,
    ....

My question is: how can I get the actual data from the field?

I suppose something has to be done in the field getter, but I tried some solutions without any success.

Thanks.

UPDATE: I've managed to get the actual data logged, modifying the getBookCdBookingUuid method this way:

    /**
     * Get bookCdBookingUuid
     *
     * @return string 
    */
    public function getBookCdBookingUuid()
    {
        return bin2hex($this->bookCdBookingUuid);
    }

and changed the type to "guid" in the property annotation:

/**
 * @var string
 *
 * @ORM\Column(name="book_cd_booking_UUID", type="guid", length=16, nullable=false)
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $bookCdBookingUuid;

I have represented the hex UUID correctly in the log before returning the results in the controller:

[2014-11-03 19:52:07] app.ERROR: 1046684e5f6711e4a09f00089bce936a [] []

But still getting an exception relating UTF invalid characters:

request.CRITICAL: Uncaught PHP Exception RuntimeException: "Your data could not be encoded because it contains invalid UTF8 characters." at /var/www/tga_api/vendor/jms/serializer/src/JMS/Serializer/JsonSerializationVisitor.php line 36 {"exception":"[object] (RuntimeException: Your data could not be encoded because it contains invalid UTF8 characters. at /var/www/tga_api/vendor/jms/serializer/src/JMS/Serializer/JsonSerializationVisitor.php:36)"} []

Also I got no response from the service. A 500 error is returned.

Please, I need to solve this issue. Any ideas are welcome.

Thanks.

Kenosis

Would it be more simple to use an integer as the id and add a column for the UUID? – Leninakan

I considered that solution for some time but that table is partially copied to different mobile devices. They may insert records offline and synchronize them when they are online. I thought of assigning different autonumeric thresholds but discarded it as using UUIDs is more simple. Or that is what I thought at first... In fact, the other field you can see in the exit is the former primary key. – Colver

Please, I need help with this. I can not solve a critical functional area. I need to use binary UUID in primary key and read the value correctly. – Colver

Answer 1

GeneratedValue

I notice you're using the annotation @ORM\GeneratedValue(strategy="IDENTITY") for the UUID property. IDENTITY means the database should/will use auto-increments, which shouldn't be done when using UUIDs. Please change it to @ORM\GeneratedValue(strategy="NONE") or remove it completely.

Conversion

The string form of a UUID (like 01234567-89ab-cdef-0123-456789abcdef) should be converted to binary when it's persisted in the database, and converted back when fetched from the database.

The easiest way to do this is to introduce a custom type. See here for an example.

Bugs

Doctrine (even master/2.5) has some issues with using UUIDs in associations. I'm attempting to fix these issues in PR #1178.

If you need UUIDs in associations and can't wait till it's fixed, then use regular integer ids and have the UUID is a separate column.

Isolating