Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Storing uuid byte sequence to postgres. #179

Open
N-Zaaijer opened this issue Aug 24, 2017 · 33 comments
Open

Storing uuid byte sequence to postgres. #179

N-Zaaijer opened this issue Aug 24, 2017 · 33 comments

Comments

@N-Zaaijer
Copy link

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
Copy link
Contributor

mhujer commented Aug 24, 2017

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
Copy link
Owner

ramsey commented Aug 24, 2017

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

@N-Zaaijer
Copy link
Author

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

@ramsey
Copy link
Owner

ramsey commented Aug 24, 2017

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
Copy link
Author

I'll try and come up with something.

@ramsey
Copy link
Owner

ramsey commented Aug 24, 2017

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
Copy link
Author

N-Zaaijer commented Aug 24, 2017

Is this something you can work on?
UuidTest.txt

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

@ramsey
Copy link
Owner

ramsey commented Aug 24, 2017

I can try to make that work. Thanks!

@N-Zaaijer
Copy link
Author

@ramsey did you get it to work?

@N-Zaaijer
Copy link
Author

N-Zaaijer commented Aug 29, 2017

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
Copy link
Owner

ramsey commented Aug 29, 2017

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
Copy link
Contributor

enumag commented Sep 13, 2017

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
Copy link
Contributor

enumag commented Sep 13, 2017

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
Copy link
Owner

ramsey commented Sep 13, 2017

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
Copy link
Contributor

enumag commented Sep 13, 2017

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

@mpaic
Copy link

mpaic commented Oct 22, 2017

@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
Copy link

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
Copy link
Owner

ramsey commented Oct 23, 2017

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

@jobe1986
Copy link

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
Copy link
Owner

ramsey commented Oct 23, 2017

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

@jobe1986
Copy link

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
Copy link
Contributor

enumag commented Oct 23, 2017

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
Copy link
Owner

ramsey commented Nov 6, 2017

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
Copy link
Owner

ramsey commented Nov 10, 2017

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
Copy link

mvrhov commented Nov 30, 2017

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
Copy link
Contributor

enumag commented Nov 30, 2017

@mvrhov Yeah, I wrote the same thing here.

@a-menshchikov
Copy link

@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
Copy link
Owner

ramsey commented Oct 9, 2020

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
Copy link

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
Copy link

kgilden commented Oct 31, 2020

@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
Copy link
Owner

ramsey commented Nov 1, 2020

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

@Antarian
Copy link

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
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests