Insert to mysql, can find data inserted in mysql but the method returns with Err(RecordNotFound("Failed to find inserted item")).

[westYY123]

Description

hi, I am developing the backend of an app with axum and sea-orm, so there is a route to register a user, and insert the user to databse, i am using mysql as my database, but any time i call this route, it returns with Err(RecordNotFound("Failed to find inserted item")), however, i can find data in my database.

Steps to Reproduce

the below is the router

pub async fn register(
    State(app_data): State<Arc<AppData>>,
    Json(req): Json<RegisterRequest>,
) -> AppResult<Json<RegisterResponse>> {
    let res = user::insert_user(&app_data.mysql_client, req.username, req.password).await;
    println!("{res:?}");
    // .map_err(|_| AppError::InternalError)?;
    Ok(Json(RegisterResponse { success: true }))
}

the below is the database operator, the res is Err(RecordNotFound("Failed to find inserted item")).

pub async fn insert_user(
    conn: &DatabaseConnection,
    username: String,
    password: String,
) -> AppResult<Model> {
    let existing_user = get_user(conn, &username)
        .await
        .map_err(|_| AppError::InternalError)?;
    match existing_user {
        Some(_) => Err(AppError::ExistingSameUsername),
        None => {
            let user = ActiveModel {
                id: Set("test-id".to_string()),
                username: Set(username),
                password: Set(password),
            };

            let res = user.insert(conn).await;
            println!("{res:?}");
            res.map_err(|_| AppError::InternalError)
        }
    }
}

Expected Behavior

returns the Model.

Actual Behavior

returns err Err(RecordNotFound("Failed to find inserted item"))

Reproduces How Often

every time

Workarounds

Reproducible Example

Versions

sqlx = "0.7"
sea-orm = { version = "0.12", features = [
"sqlx-mysql",
"runtime-tokio-native-tls",
"macros",
"with-chrono",
] }

[zawata]

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:

sea-orm/src/executor/insert.rs

Lines 294 to 297 in 010433b

	let insert_res = exec_insert::<A, _>(primary_key, insert_statement, db).await?;
	<A::Entity as EntityTrait>::find_by_id(insert_res.last_insert_id)
	.one(db)
	.await?

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.