GORM UUID too long

Question

Currently I am using GO-GORM for all of my database queries (mostly CRUD) and I am having some issues inserting a generated UUID into a MySQL database column.

The column is a BINARY(16) as suggested in multiple blogs, the UUID is generated using github.com/satori/go.uuid package for Golang.

I am using GORM's BeforeCreate hook to generate the UUID if one does not already exist on the user, the code that I am using is as follows:

func (u *User) BeforeCreate(scope *gorm.Scope) (err error) {
    if u.UserID == uuid.Nil {
        uuid, err := uuid.NewV4().MarshalBinary()
        scope.SetColumn("user_id", uuid)
    }
}

I have also used len to get the length that MarshalBinary outputs and it returns as 16.

The error I get from GORM when trying to insert the UUID into MySQL is as follows:

(Error 1406: Data too long for column 'user_id' at row 1)

I have also fmt.Println(uuid) to see the results and they are also as follows (obviosuly changes as the UUID is generated every insert)

[93 132 59 55 102 96 72 35 137 185 34 21 195 88 213 127]

My MYSQL schema is as follows also:

CREATE TABLE users
(
    id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP,
    user_id BINARY(16) NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    email VARCHAR(255),
    address_id VARCHAR(255)
);
CREATE INDEX idx_users_deleted_at ON users (deleted_at);
CREATE UNIQUE INDEX username ON users (username);
CREATE UNIQUE INDEX user_id ON users (user_id);

I have tried different methods and libraries to generate UUIDs and convert them to binary to insert with similar results.

maybe stupid question but why do you use binary for `user_id`? why dot you use something like https://en.wikipedia.org/wiki/Universally_unique_identifier? — pregmatch, May 08 '17 at 13:28
i can see here http://stackoverflow.com/questions/15130321/is-there-a-method-to-generate-a-uuid-with-go-language that is really easy to generate that. — pregmatch, May 08 '17 at 13:29
i am always using that, but my best guess that all depend on database. I can see that you have `id` and then `user_id`, why do you have `user_id` at all? — pregmatch, May 08 '17 at 13:30
Mysql does not have a uuid field. Gorm generated the id column itself. I want a uuid column and will look into removing the gorm generated columns if need be... — Joshua Lawson, May 08 '17 at 13:36
I have asked you why you have `user_id` at all if your primary key in users table is `id`? And you should consider renaming your table to `user` since it is one model. Take a look at here http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names. having `id` as primary and `user_id` as far as i can see it is completely redundant. — pregmatch, May 08 '17 at 13:43
The way it's written, there's inconsistent behavior; when you insert a new user, if their ID is nil, you set it, manually marshal it, and pass it directly to GORM. In any other case, you let GORM figure it out for itself. I'd think BeforeCreate would either only set the ID if it's nil, and let GORM handle marshaling, or it would marshal the value and call `scope.SetColumn` outside the if block, so that it behaves the same whether or not the ID was set. Not sure if it's relevant to your issue but it could be a confounding factor. — Adrian, May 08 '17 at 14:27
How if you change the len of `user_id` (for debugging purpose, e.g. set it to 100 --> `user_id BINARY(100) NOT NULL`) then observe the result. If no error, then look at the content of `user_id` which was saved to MySQL. — putu, May 08 '17 at 14:29
Now take a look at `user_id` record in MySQL, what was added to the record which causing the length exceed 16-bytes? — putu, May 08 '17 at 22:16

score 4 · Accepted Answer · answered May 08 '17 at 23:54

I think the problem is in the definition of model User. To save the GUID as 16-bytes binary, you need to define the UserID column as []byte not uuid.UUID.

type User struct {
    //other fields ..
    UserID    []byte

    //other fields ...
}

func (u *User) BeforeCreate(scope *gorm.Scope) (err error) {
    if u.UserID == nil {
        uuid, err := uuid.NewV4().MarshalBinary()
        scope.SetColumn("user_id", uuid)
    }
    return nil
}

If you define the field as uuid.UUID, gorm "misinterpreted" the field as string and then insert that string into the database as binary. For example, the following UUID,

uuid: 16ac369b-e57f-471b-96f6-1068ead0bf98
//16-bytes equivalent
bytes: [22 172 54 155 229 127 71 27 150 246 16 104 234 208 191 152]

will be inserted to database as the ASCII codes of the UUID which are

0x31 0x36 0x61 0x63 0x33 0x36 0x39 0x62 0x2D 0x65 ...
('1' '6'  'a'  'c'  '3'  '6'  '9'  'b'  '-'  'e'  ...)

which are 36-bytes in length, thus you're getting Error 1406: ...

based on this issue https://github.com/jinzhu/gorm/issues/1687 jinzhu advises against using []byte as the type — NeXtMaN_786, Jan 11 '19 at 18:43
@NeXtMaN_786 The question is specifically for MySQL which AFAIK doesn't have UUID column type. The issue you've mention is for PostgreSQL which has UUID data type for its column, and is also being supported by the Go driver. In the later case, I agree that we should not use `[]byte` to store GUID in Golang. — putu, Jan 13 '19 at 22:54

GORM UUID too long

1 Answers1

Linked