Fail to insert entity with Buffer type of primary column under some circumstances.

[angushe]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

There are two entities which are called Foo and Bar respectively. Foo and Bar have a 1-1 relationship defined on primary columns <Foo.id>/<Bar.id>.

@Entity()
export class Foo {
    @PrimaryColumn({ type: "varbinary", length: 16 })
    id: Buffer;

    @Column()
    name: string;
}

@Entity()
export class Bar {
    @PrimaryColumn({ type: "varbinary", length: 16 })
    id: Buffer;

    @Column()
    name: string;

    @OneToOne(type => Foo)
    @JoinColumn({ name: "id", referencedColumnName: "id" })
    foo: Foo;
}

After running the following test code:

class App {
    public static async start() {
        const connection = await createConnection({
            type: "mysql",
            synchronize: true,
            host: "localhost",
            port: 3306,
            username: "root",
            password: "",
            database: "test",
            entities: [Foo, Bar],
            logging: true,
        });

        await getConnection().transaction(async transactionalEntityManager => {
            const id = new Buffer("foobar");
            const foo = new Foo();
            foo.id = id;
            foo.name = "foo";
            await transactionalEntityManager.save(foo);

            const bar = new Bar();
            bar.id = id;
            bar.name = "bar";
            await transactionalEntityManager.save(bar);
        });
    }
}

I got the error:

query: START TRANSACTION
query: SELECT `Foo`.`id` AS `Foo_id`, `Foo`.`name` AS `Foo_name` FROM `foo` `Foo` WHERE `Foo`.`id` IN (?) -- PARAMETERS: [{"type":"Buffer","data":[102,111,111
,98,97,114]}]
query: INSERT INTO `foo`(`id`, `name`) VALUES (?, ?) -- PARAMETERS: [{"type":"Buffer","data":[102,111,111,98,97,114]},"foo"]
query: INSERT INTO `bar`(`id`, `name`) VALUES (DEFAULT, ?) -- PARAMETERS: ["bar"]
query failed: INSERT INTO `bar`(`id`, `name`) VALUES (DEFAULT, ?) -- PARAMETERS: ["bar"]
error: { Error: ER_NO_DEFAULT_FOR_FIELD: Field 'id' doesn't have a default value                                                                                  at Query.Sequence._packetToError (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
    at Protocol._parsePacket (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/Connection.js:91:28)
    at Socket.<anonymous> (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/Connection.js:525:10)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    --------------------
    at Protocol._enqueue (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at PoolConnection.query (/Users/angus/tmp/typeorm-test-fb/node_modules/mysql/lib/Connection.js:201:25)
    at MysqlQueryRunner.<anonymous> (/Users/angus/tmp/typeorm-test-fb/src/driver/mysql/MysqlQueryRunner.ts:153:36)
    at step (/Users/angus/tmp/typeorm-test-fb/node_modules/tslib/tslib.js:133:27)
    at Object.next (/Users/angus/tmp/typeorm-test-fb/node_modules/tslib/tslib.js:114:57)
    at fulfilled (/Users/angus/tmp/typeorm-test-fb/node_modules/tslib/tslib.js:104:62)
    at <anonymous>
    at process._tickDomainCallback (internal/process/next_tick.js:229:7)
  code: 'ER_NO_DEFAULT_FOR_FIELD',
  errno: 1364,
  sqlMessage: 'Field \'id\' doesn\'t have a default value',
  sqlState: 'HY000',
  index: 0,
  sql: 'INSERT INTO `bar`(`id`, `name`) VALUES (DEFAULT, \'bar\')' }
query: ROLLBACK

If I change the data types of Foo.id and Bar.id from Buffer to string or other primitive types), it works.

After some investigations into typeorm source codes, I made a quick dirty change(Buffer type check added) to

typeorm/src/metadata/ColumnMetadata.ts

Line 588 in f380452

} else if (entity[this.propertyName] && entity[this.propertyName] instanceof Object && !(entity[this.propertyName] instanceof FindOperator) && !(entity[this.propertyName] instanceof Function)) {

            } else if (entity[this.propertyName] && entity[this.propertyName] instanceof Object && !(entity[this.propertyName] instanceof FindOperator) && !(entity[this.propertyName] instanceof Function) && !(entity[this.propertyName] instanceof Buffer)) {
                value = this.referencedColumn.getEntityValue(PromiseUtils.extractValue(entity[this.propertyName]));
            } else {

it works again.

Any thoughts?

[MechJosh0]

A recent fix with Typeorm resolved an issue with updating an entry with a Buffer which is not the primary key. However, this is still an issue v0.2.28.

This is the payload I send into my entity create function:

{
  id: <Buffer 11 eb 0c 81 c9 f3 1e 40 87 3a 63 96 87 e2 ca 27>,
  anotherBufferColumn: <Buffer 11 e8 46 cb b2 9d 9d d4 b7 9a 02 42 ac 11 00 04>,
  text: "Hello world"
}

This is what is created:

{
  id: <Buffer 11 eb 0c 81 c9 f3 1e 40 87 3a 63 96 87 e2 ca 27>,
  text: "Hello world"
}

@angushe fix is still relevant today. The fix fixes inserting but breaks updating.

[elovin]

@MechJosh0 what recent fix are you referring to ?
With Typeorm 0.2.29 create works but not update, update is only possible when using the queryBuilder and using the binary primary key in a where statement.

[MechJosh0]

@elovin I actually realised the error was my side.

I've created a small working demo repo for you which will:

1. Create a new Foo entry
2. Update the Foo entry
3. Create a new Bar entry which has a foreign key to the Foo entry

https://github.com/MechJosh0/typeorm-buffer-example

Hopefully, this helps you to see what I've done to have it working.

[elovin]

I extended the example provided by @MechJosh0 for 16bit hex encoded uuids and this works just fine with mysql/mariadb.