Insert to mysql, can find data inserted in mysql but the method returns with Err(RecordNotFound("Failed to find inserted item")). about sea-orm HOT 1 OPEN

I encountered this bug yesterday and spent a few hours troubleshooting it

Tl;Dr: Either use Entity::insert(user).exec(conn) or make the table's primary key AUTO_INCREMENT.

The ActiveModel.insert function attempts to return the freshly-inserted row if it was told that the insert was successful. If the database supports returning inserted rows then it will convert the row result to a model and return it. If it doesn't, it uses the "last_insert_id" field of the result to look up the row it just inserted. MySQL doesn't set the last_insert_id on the insert result unless the primary key of the table is auto_increment:
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

but sea_orm doesn't account for this, assuming that the last_insert_id is valid:

Given that there's separate insert_with_returning and insert_without_returning functions, I have to assume this is expected behavior but I still wish this function could handle this error more gracefully or more obviously, or at least document it somehow/somewhere?

The simple fix is to use Entity::insert(user).exec(conn) which instead returns the insert result, which, if Ok, contains last_insert_id but it's set to 0. Doesn't really matter since you know the insert succeeded and you should already have the ID you set in the active model so you can just follow it up with a Entity::find_by_id(id).one(conn) to get the fresh model.

this is what I'm doing:

  let new_user_id = rand::random::<u64>();

  let user = ActiveModel {
      id: Set(new_user_id),
      email: Set(email.clone()),
      password_hash: Set(password_hash.clone())
  };

  Entity::insert(user).exec(conn).await?;
  let new_user = Entity::find_by_id(new_user_id).one(conn).await?.ok_or(anyhow!("inserted user not found"))?;

  return Ok(new_user);

The more complex fix is to make sure your primary key column is set to auto-increment so mysql will actually return the inserted ID.

or just switch to postgres which doesn't seem to have this issue.

from sea-orm.