Cannot retrieve inserted row when using UUID as primary key

[korECM]

Description

I'm encountering an issue when trying to create a record with a UUID primary key using Bob ORM. While the system works fine with auto-increment columns, it fails when using UUID as the primary key.

Environment

• Bob ORM
• MySQL database
• Go

Database Schema

create table api_keys
(
    id          binary(16) default (uuid_to_bin(uuid(), 1)) not null
        primary key,
    `key`       varchar(64)                                 not null,
    secret      varchar(255)                                null,
    constraint api_keys_key_uniq
        unique (`key`)
)
    row_format = DYNAMIC;

Code Example

f := factory.New()
createdAPIKey, err := f.NewAPIKey().Create(ctx, writerBob)
require.NoError(t, err)

Error Message

cannot retrieve inserted row

Looking at the source code in dialect/mysql/table.go, it appears that the issue occurs because:

The system checks for autoIncrementColumn first, which is empty in this case
Then it falls back to uniqueSet, but fails to find it
This results in returning orm.ErrCannotRetrieveRow
Here's the relevant code from dialect/mysql/table.go:

if t.autoIncrementColumn != "" {
    lastID, err := results[i].LastInsertId()
    if err != nil {
        return nil, err
    }
    autoIncrArgs = append(autoIncrArgs, Arg(lastID))
} else {
    uIdx, uArgs := t.uniqueSet(w, val)
    if uIdx == -1 || len(uArgs) == 0 {
        return nil, orm.ErrCannotRetrieveRow
    }
    idArgs[uIdx] = append(idArgs[uIdx], ArgGroup(internal.ToAnySlice(uArgs)...))
}

Questions

1. Is this a known limitation when using UUID as primary key?
2. Is there a workaround available?
3. If this is a bug, what would be the best approach to fix it?

Additional Context

The system works fine with auto-increment primary keys, but fails specifically with UUID primary keys. This seems to be related to how the ORM handles record retrieval after insertion when using non-auto-increment unique identifiers.

[stephenafamo]

So............................. this is a limitation of MySQL.

MySQL does not support the RETURNING clause, so it is tricky to retrieve a row that was just inserted.

IF a table has an auto increment field, we can use LastInsertID() to retrieve this.

If not, Bob tries to see if you have set any unique rows while inserting. For example, if email is a unique field and you insert a row with the email [email protected], then Bob can try to retrieve the row with that email and guarantee that it was the row that was just inserted.

However, in this case, you are not manually setting any unique rows (the UUID is set by default), so there is no way for Bob to retrieve what row was inserted.

If using the factory, you can for example add a mod to generate the UUID in code. Since Bob will then know the UUID before insertion, it will be able to retrieve it after.

[korECM]

@stephenafamo
I understand the limitation that MySQL requires an auto-increment column to retrieve rows. However, even after setting a primary key, it seems Bob still can't retrieve the rows. What could be the reason?

I'm not sure if I'm inserting correctly since the Bob example documentation differs from the actual code.

// create by factory
f := factory.New()
createdAPIKey, err := f.NewAPIKey(
	factory.APIKeyMods.IDFunc(idtype.NewId),
).Create(ctx, writerBob)
require.NoError(t, err)
assert.NotNil(t, createdAPIKey)

cannot retrieve inserted row

// create by insert
createdAPIKey, err := models.APIKeys.Insert(
	&models.APIKeySetter{
		ID:          omit.From(idtype.NewId()),
		Key:         omit.From("some key"),
		Secret:      omitnull.From("optional secret"),
	},
).One(ctx, writerBob)
require.NoError(t, err)
assert.NotNil(t, createdAPIKey)

cannot retrieve inserted row

[stephenafamo]

That is.... unexpected 🤔

I'll take a look