Issue: BinaryUUIDFields do not seem to work with Mysql/pymysql
Author: stevenj
State: closed
Created: 2020-06-07 02:18:51

I am using Mysql8 and pymysql.

I have a field defined thus:

```
class Adapter(BaseModel):
    id = BinaryUUIDField(primary_key=True)

    class Meta:
        """Metadata."""
        table_name = "adapter"
```

the matching sql is:

```
CREATE TABLE `adapter` (
  `id` VARBINARY(16) NOT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
```

When trying to create records I get:
```
 ... lib/python3.8/site-packages/pymysql/cursors.py:170: Warning: (1300, "Invalid utf8mb4 character string: 'C65E72'")
    result = self._query(query)
```

The `C65E72` is part of the UUID in Hex.

The record does not get created, so its fatal to the create, not just a warning.  The bigger issue is, when this happens the create returns with a new object indicating the create succeeded.  But there is actually no record in the database.

BinaryUUIDField is tested against all databases, including MySQL, and seems to work just fine.

If you can produce a failing test-case I will look into it some more.

https://github.com/coleifer/peewee/blob/master/tests/fields.py#L841-L897

I ran the tests, and the warning is generated by them.  The fields however are generated so there must be something else wrong with my code.  However, the warnings end up in my logs and fill up the logs unnecessarily.   It seems with mysql, ALL binary fields should be prefaced with the "introducer"  _binary
like so:

replace:

cursor.execute("""
    INSERT INTO `user` (uuid)
    VALUES (%s)
""", my_uuid)

with

cursor.execute("""
    INSERT INTO `user` (uuid)
    VALUES (_binary %s)
""", my_uuid)

I'd suggest taking it up with the pymysql devs.

There is a connection option "binary_prefix=True" i am experimenting to see if it fixes the issue. 

Ok,  I can confirm if you pass "binary_prefix=True" to the init the spurious warning goes away.
like so:

```
db = MySQLDatabase(None)

...
        db.init(
            dbname,
            host=dburl,
            user=dbuser,
            password=dbpassword,
            charset="utf8mb4",
            binary_prefix=True,
        )
```

pymysql does not do it by default because some orms do it for themselves.  Would suggest at the very least a comment in the docs relating to peewee and mysql, in much the same way charset is documented.

Since MySQL is raising warnings because of binary data being stored into a VARBINARY column, I really think this is a MySQL bug. I read their issue tracker and frankly it's a complete bullshit response. Prefixing with "_binary" is the workaround they suggest, or alternatively using a literal parameter prefixed with "x". Neither of these is palatable to me for inclusion in Peewee. I'd suggest that MySQL users just use the regular UUIDField instead, which stores the hex representation at the cost of a few more bytes of storage... Or switch to MariaDB, which doesn't seem to have this problem.

`charset` is not really documented per-se, but rather given as an example that you should consult your driver documentation. Not everyone who uses Peewee with MySQL will be using pymysql, so you're encouraged to read the docs for whichever db driver you're using.

Yeah, i agree its a bullshit response on the mysql side.  Its not a true warning, its just pointless noise and they know it.  I would use a different DB but i am limited by what the company chose.  For my part, it works fine with the option, if anyone else has the issue i am sure they will find this thread in the issues and see the workaround.  Sorry for saying the UUID didn't work.  It clearly does.  I must have had another issue and was being bamboozled by the crap warning.   Its OT, but i am actually really liking peewee so, thankyou.

To be honest, these sorts of workarounds should be in the driver, again i don't know why its so difficult for pymysql to check binary fields and prefix them if they are not already prefixed.  But cest la vie.

No worries at all, I understand the confusion. I am surprised I hadn't seen this come up until now, but I'm glad the driver at least provides some measure of workaround.