Storing uuid byte sequence to postgres.

[N-Zaaijer]

Hi,

I'm currently having problems to store the optimized byte sequence to my postgres database. I keep getting the error SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xe7 0x87 0xf3

I've followed the guide and used the OrderedTimeCodec in my uuid factory.

My database table alteration script: DB::statement('ALTER TABLE entity_groups ADD uuid bytea');

I'm using php 7.1, laravel 5.4 and postgres 9.4.

Thanks in advance.

[mhujer]

Isn't it better to use native UUID type that PostgreSQL provides?

I think that the guide you linked in your post is about MySQL/MariaDB which does not provide native UUID type.

[ramsey]

@N-Zaaijer, can you share the code you're using to try to store the UUID to the database?

[N-Zaaijer]

Uuid.txt @mhujer That makes no difference. @ramsey I've added the trait as a text file

[ramsey]

Thanks, @N-Zaaijer. I'm not very familiar with Laravel's Eloquent ORM. Can you provide a short reproducible script that I can use to run against Postgres to see the problem?

[N-Zaaijer]

I'll try and come up with something.

[ramsey]

Thanks. That will help a lot. All I need is an SQL CREATE TABLE statement that shows the exact column definition for PostgreSQL and maybe a few lines using PDO to bind the UUID as a parameter to an INSERT statement. If you can work up something that generates the same error, that'll help me debug this.

[N-Zaaijer]

Is this something you can work on? UuidTest.txt

I added a native uuid field and a bytea field for comparison.

[ramsey]

I can try to make that work. Thanks!

[N-Zaaijer]

@ramsey did you get it to work?

[N-Zaaijer]

I stumbled upon something. The hex2bin doesn't convert all characters. Dumping the 'binary' results in b"\x11þîØ\x18·■\x1C¥õ\fx╬#\x029" for example.

Here's an other test setup https://3v4l.org/ND2O3

[ramsey]

The hex2bin() conversion looks correct to me. What characters didn’t it convert that you are expecting it to convert, and what is your expected result of the conversion?

I haven’t been able to reproduce your issue yet. It will take some time, as I’m unfamiliar with Laravel and will need to learn how to set it up to run the script. Sorry for the delay.

[enumag]

I ran into this issue as well today while using Symfony, Doctrine and Postgres. The error was thrown when inserting a row into a table with Ramsey\Uuid\Doctrine\UuidBinaryOrderedTimeType. When I switched to Ramsey\Uuid\Doctrine\UuidType it works fine. So it seems there is some problem with binary type and Postgres.

[enumag]

You don't need Laravel to reproduce it. You can just use Symfony + Doctrine + Postgres, define a doctrine entity with binary uuid field and try to insert something. I assume this is the same issue.

[ramsey]

Thanks, @enumag. I didn’t think this was specific to Laravel; the reproduction script requires Laravel, though. Would you be able to provide a small reproduction script that I can run outside of a framework to help debug this? Thanks!

[enumag]

@ramsey Making framework-independent script is a little more complicated. Not sure when or if I'll have time for that.

[mpaic]

@ramsey Same for me with Symfony + Doctrine + Postrgres when i try to use binary type uuid. Im new to PostgreSQL so im wondering if the problem is in my configuration of database (i kept default posgre settings) or its in library. Any news about this problem ?

[jobe1986]

It should be noted that when using postgresql's UUID data type, inserting/updating UUIDs stored in the field should specify the UUID in one of the following formats as a string rather then as the raw bytes in the SQL query: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' 'A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11' '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}' 'a0eebc999c0b4ef8bb6d6bb9bd380a11' 'a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11' '{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}'

[ramsey]

@jobe1986, I think the issue here is when attempting to store the bytes to a BYTEA field.

[jobe1986]

In that case, whilst I'm not familiar with Illuminate\Support\Facades\DB for postgresql database support in PHP, in the code I am familiar with I would use pg_escape_bytea() to escape the binary data first. Or I would look at pg_query_params() or a similar method to avoid directly concatenating the parameters into the SQL query itself. I offer this information as it may provide a hint or point you in the right direction.

[ramsey]

Thanks for that, @jobe1986. Do you know if bound parameters with PDO will escape the bytea data properly?

[jobe1986]

I believe so, as https://stackoverflow.com/questions/5313066/bind-bytea-to-pgsql-pdo-prepared-statement-in-php5 suggests using PDO::PARAM_LOB as the data type for bindParam()

[enumag]

In that case we just need to add getBindingType method to the binary types in ramsey/uuid-doctrine. https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Types/BinaryType.php#L74-L77

I will try to test it tomorrow and send a PR if it works.

[ramsey]

Thanks to @enumag, this has been fixed in 1.4.2 of ramsey/uuid-doctrine. I'm leaving this issue open until I've been able to provide direction in how to address this problem in Laravel (and potentially other frameworks). Once I come up with a good, easy-to-explain solution, I'll update the README or the wiki with examples.

[ramsey]

We had to roll-back the changes made to ramsey/uuid-doctrine, since they broke MySQL/MariaDB implementations. I'll leave this issue open to continue researching and see if there's a solution I can offer.

[mvrhov]

The solution is TO NOT use bytea and use native uuid type. The native type already stores uuid as a binary, but returns/requires the text representation for SQL

[enumag]

@mvrhov Yeah, I wrote the same thing here.

[a-menshchikov]

@ramsey Hello. There are any news about this issue?

I have critical problem because of this. I'm using symfony/messenger component that store failed messages through Doctrine into PostgreSQL table. And messages that contain Uuid can't be saved.

[ramsey]

Are you able to provide a short script (that doesn't depend on a framework) to reproduce the issue? I've not been able to reproduce it on my own.

[a-menshchikov]

I don't know at the moment how to reproduce this problem, but I see that when I try to serialize Uuid, I get C:35:"Ramsey\Uuid\Lazy\LazyUuidFromString":36:{...}, whereas problem message (that cannot be written in PostgreSQL) contains C:16:\"Ramsey\\Uuid\\Uuid\":16:{ (after that goes problem byte sequence).

Maybe it somehow related on ramsey/uuid-doctrine package that I'm using to be able to work with Uuid values as object ids.

[kgilden]

@ramsey I think the problem is that people are using UuidBinaryType with postgres. But using it doesn't make any sense, because postgres comes with its own native uuid type. And UuidType uses that native type already. At least that's how I got the same error message as the OP (using uuid_binary in postgres).

So solution would be to perhaps nudge people to switching to UuidType instead, when using postgres.

[ramsey]

@kgilden Thanks for posting! This sounds like the missing piece here. I'll be sure to update the docs so others know. Thanks!

[Antarian]

Took me while to figure out that binary type should not be used with postgres. DBAL driver will fail for postgres with message: An exception occurred while executing 'INSERT INTO shopping_list (id, title, notes) VALUES (?, ?, ?)' with params ["\x11\xeb\x48\x94\x08\xc7\xe0\x58\x82\x5c\x02\x42\xac\x19\x00\x07", "Newest List 1", null]: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xeb 0x48 0x94 Also using Symfony + Doctrine + Postgres (api-platform)

[RodrigoDornelles]

I had the same problem, I'm using binary (16) to store with postgres, I solved it in this simple way

'\x'.bin2hex(Uuid::fromString($uuid)->getBytes());