Generated error but record inserted (unable to populate default values for users: sql: no rows in result set)

[akira-19]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

v.4.8.3

What is your database and version (eg. Postgresql 10)

MySQL v.8.0.28

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

ctx := context.Background()
newUser := entity.User{
	Name: name,
}

err := newUser.Insert(ctx, db, boil.Infer()) 
if err != nil {
	fmt.Println(err)
}

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Here is my schema.

CREATE TABLE users (
	id varchar(36) NOT NULL DEFAULT (UUID()),
	name varchar(255) NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
	PRIMARY KEY(id)
);

+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field      | Type         | Null | Key | Default           | Extra                                         |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+
| id         | varchar(36)  | NO   | PRI | uuid()            | DEFAULT_GENERATED                             |
| name       | varchar(255) | NO   |     | NULL              |                                               |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------------------------+

Further information. What did you do, what did you expect?

At first, I tried to run the code above and the error generated below.
entity: unable to populate default values for users: sql: no rows in result set
but record was inserted.

Next, I changed this line.

# err := newUser.Insert(ctx, db, boil.Infer())
err := newUser.Insert(ctx, db, boil.Whitelist("id", "name", "created_at", "updated_at"))

this code worked and no error was generated, but the id column is null in the inserted record.

And, I tried to run the first code again.

err := newUser.Insert(ctx, db, boil.Infer())
# this code generates the following sql
# INSERT INTO `users` (`name`,`created_at`,`updated_at`) VALUES (?,?,?)

The code somehow worked. No error and a record was inserted.
However, newUser didn't have id value. (but has created_at and updated_at values)

At last, newUser is constructed with ID.

ctx := context.Background()
newUser := entity.User{
        ID: "newuserid",
	Name: name,
}

err := newUser.Insert(ctx, db, boil.Infer()) 
if err != nil {
	fmt.Println(err)
}

This code works as expected.

My expectation is that the code err := newUser.Insert(ctx, db, boil.Infer()) works fine.
How do I solve this problem?
Or I can't use MySQL UUID() function?

Thank you.

[stephenafamo]

The schema you provided, can it be in the form of a CREATE TABLE statement since that is easier to use in an attempt to reproduce.

[akira-19]

@stephenafamo I edited the discussion. Thank you.

[stephenafamo]

I've been able to reproduce this. It is caused because of how MySQL works.

Your specific issue is caused by having a primary key that has a default value but is not an AUTO INCREMENT column.
This means that we cannot retrieve the generated primary key and so we also cannot retrieve the full inserted row.

For a DB like Postgres, we can use RETURNING. But this isn't supported in MySQL.

Instead, for MySQL we do the insert and then retrieve the inserted record using the primary key. If it was an AUTO INCREMENT column, then we could retrieve this primary key using the LastInsertID(), but in this case, there is no way for us to do that so we query the DB for the full model using a blank primary key which then matches no rows.

The solution, in this case, would be one of the following:

1. Use an AUTO INCREMENT field as your primary key. You would need to no longer use UUID for this.
2. Make sure to generate an id before every insert.
3. Create a custom type for that column (and use the type replace). Where the Value() method of this type generates a new UUID if it is empty.

[akira-19]

I have to use uuid in my case, so solution 2 or 3 would work.
I just want to make sure what you mean in the solution 2.
"generate an id before every insert" means some unique id must be generated in the application (not database) and insert with sqlboiler Insert() function, right ?

[stephenafamo]

Yes. You can use one of the popular uuid packages to generate one. The design of UUIDs mean that you shouldn't get a collision.

Some other things to note:

1. MySQL's uuid() generates a v1 UUID, which is different from the v4 that you may be expecting. Take a look at this Stack Overflow answer about generating v4 UUIDs in MySQL.
2. Since technically, UUIDs are 128 bytes, It may be better to store UUID as binary(16) rather than varchar(36) there is some inconsistencies when you convert it to string and compare, upper/lower case, hypens, e.t.c.

Alternatively, use Postgres which has much better UUID support. It has a dedicated UUID type and a gen_random_uuid() function. 😂

[akira-19]

I thought MySQL uuid() generates v4 uuid 😂
I usually use postgresql but MySQL is required in the current project.
Anyway, thank you for your help!