Duplicate entry error even after "delete from account where 1;"

[zishon]

T1：create an account with username "abc", result is success.
T2：create an account with username "abc", result is error, error: "Duplicate entry 'abc' for key 'account.username'"
T3：login to mysql, and execute "delete from account where 1;"
T4：replay the action of T2, failed again with error: "Duplicate entry 'abc' for key 'account.username'"

after enable the general log of mysql
T2 with general log:
2024-07-06T12:51:43.202730Z 83 Execute SELECT 1
2024-07-06T12:51:43.203200Z 83 Execute INSERT INTO account (username) VALUES ('abc')

T4 with general log:
2024-07-06T12:51:43.202730Z 83 Execute SELECT 1

so it seems a little wired, kind of caching result of unique key?
project layout

.
├── Cargo.lock
├── Cargo.toml
├── migrations
│   └── 2024-06-16-125405_create_account
│       ├── down.sql
│       └── up.sql
├── src
│   └── main.rs
└── target
    ├── CACHEDIR.TAG
    └── debug

up.sql

-- Your SQL goes here
CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT '' NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Cargo.toml

[package]
name = "diesel-test"
version = "0.1.0"
edition = "2021"

[dependencies]
axum = { version = "0.7.5", features = ["json", "macros"] }
chrono = "0.4.38"
diesel = { version = "2.2.1", features = ["mysql", "chrono", "r2d2"] }
tokio = { version = "1.38.0", features = ["full"] }
serde = { version = "1.0.203", features = ["derive"] }
serde_json = "1.0.117"
validator = { version = "0.18.1", features = ["derive"] }
lazy_static = "1.4.0"
dotenvy = "0.15.7"
thiserror = "1.0.61"

main.rs

extern crate chrono;
#[macro_use]
extern crate diesel;

use axum::{debug_handler, Json, Router};
use axum::extract::rejection::JsonRejection;
use axum::response::IntoResponse;
use axum::routing::post;
use diesel::{MysqlConnection, RunQueryDsl};
use diesel::ExpressionMethods;
use diesel::prelude::*;
use diesel::query_builder::AsQuery;
use diesel::query_dsl::methods::LoadQuery;
use diesel::r2d2::{ConnectionManager, Pool};
use diesel::result::Error;
use dotenvy::dotenv;
use lazy_static::lazy_static;
use serde::Deserialize;
use serde::Serialize;
use thiserror::Error;
use validator::{Validate, ValidationErrors};
//use account::dsl::*;

diesel::table! {
    account (id) {
        id -> Integer,
        #[max_length = 50]
        username -> Varchar,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}

#[derive(Queryable, Selectable)]
#[diesel(table_name = account)]
#[diesel(check_for_backend(diesel::mysql::Mysql))]
pub struct Account {
    pub id: i32,
    pub username: String,
    pub created_at: chrono::NaiveDateTime,
    pub updated_at: chrono::NaiveDateTime,
}

type DbPool = Pool<ConnectionManager<MysqlConnection>>;

lazy_static! {
    pub static ref POOL: DbPool = init_pool();
}

fn init_pool() -> DbPool {
    dotenv().ok();
    let database_url = std::env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let manager = ConnectionManager::<MysqlConnection>::new(database_url);
    Pool::builder().build(manager).expect("Failed to create pool")
}

pub fn get_router() -> Router {
    Router::new()
        .route("/demo", post(demo))
}

#[tokio::main]
async fn main() {
    let app = Router::new()
        .merge(get_router());

    let listener = tokio::net::TcpListener::bind("0.0.0.0:3000").await.unwrap();
    axum::serve(listener, app).await.unwrap();
}

#[derive(Debug, Error)]
pub enum AppError {
    #[error(transparent)]
    ValidationError(#[from] ValidationErrors),

    #[error("Internal server error")]
    InternalServerError,

    #[error("register error")]
    RegisterError(String),

    #[error(transparent)]
    JsonExtractorRejection(#[from] JsonRejection),

    #[error(transparent)]
    DieselError(#[from] Error)
}

impl IntoResponse for AppError {
    fn into_response(self) -> axum::response::Response {
        match self {
            AppError::RegisterError(e) => {
                let error_response = ErrorResponse {
                    code: 400,
                    message: e,
                    data: "",
                };
                serde_json::to_string(&error_response).unwrap().into_response()
            }
            _ => {
                let error_response = ErrorResponse {
                    code: 500,
                    message: "Internal server error".to_owned(),
                    data: "",
                };
                serde_json::to_string(&error_response).unwrap().into_response()
            }
        }
    }
}

#[derive(Debug, Serialize, Deserialize)]
pub struct ErrorResponse<T>
    where
        T: Serialize
{
    code: u32,
    message: String,
    data: T,
}


#[derive(Debug, Serialize, Deserialize, Validate)]
struct RegisterRequest {
    username: String,
}

#[debug_handler]
async fn demo(Json(payload): Json<RegisterRequest>) -> Result<(), AppError> {
    let _ = payload.validate()?;
    register_account(payload)?;
    Ok(())
}

fn register_account(request: RegisterRequest) -> Result<(), AppError> {
    let connection = &mut POOL.get().unwrap();
    let binding = (
        account::dsl::username.eq(&request.username),
    );

    let result = diesel::insert_into(account::table)
        .values(&binding).execute(connection);
    if let Err(e) = result {
        return Err(AppError::RegisterError(e.to_string()));
    }
    Ok(())
}

/path/to/project/.env

DATABASE_URL=mysql://root:password@localhost/demo

mysql version

mysql --version
mysql  Ver 8.0.37 for Linux on x86_64 (MySQL Community Server - GPL)

diesel version

diesel --version
diesel
 Version: 2.2.1
 Supported Backends: mysql

init database

diesel migration run

run the axum project

cargo run

T1: create an account with username "abc"

curl -X POST http://127.0.0.1:3000/demo -H "Content-Type:application/json" -d '{"username":"abc"}'  | jq .

succeed, check the database

mysql> select * from account;
+----+----------+---------------------+---------------------+
| id | username | created_at          | updated_at          |
+----+----------+---------------------+---------------------+
|  1 | abc      | 2024-07-07 22:39:26 | 2024-07-07 22:39:26 |
+----+----------+---------------------+---------------------+

T2: create an account with username "abc" again, error response

[root@thinkpad diesel-test]# curl -X POST http://127.0.0.1:3000/demo -H "Content-Type:application/json" -d '{"username":"abc"}'  | jq .
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   101  100    83  100    18  25189   5462 --:--:-- --:--:-- --:--:-- 41500
{
  "code": 400,
  "message": "Duplicate entry 'abc' for key 'account.username'",
  "data": ""
}

T3: login to mysql and delete from account

mysql> delete from account;
Query OK, 1 row affected (0.02 sec)

mysql> select * from account;
Empty set (0.00 sec)

T4: create an account with username "abc" again, go error response again

 curl -X POST http://127.0.0.1:3000/demo -H "Content-Type:application/json" -d '{"username":"abc"}'  | jq .
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   101  100    83  100    18  25824   5600 --:--:-- --:--:-- --:--:-- 41500
{
  "code": 400,
  "message": "Duplicate entry 'abc' for key 'account.username'",
  "data": ""
}

check the database

mysql> select * from account;
Empty set (0.00 sec)

need some helps, thank you all.

[weiznich]

It's hard to help here without knowing what's in your database. The only thing that I can say is that diesel does not cache any query results, so if you get a "Duplicated entry" error that means there is really a duplicated entry in your database. For additional help you would need to provide detailed steps to reproduce the problem starting from a new database.

[zishon]

I had update the post, adding the details to reproduce the problem.

[weiznich]

Thanks for providing these instructions. Unfortunately it does not reproduce for me locally with the specified versions and the provided code. That means I cannot really help you here. I still would expect that the entry remains in your database.

[Mingun]

Are you sure that mysql does commit after destructive operations? What if you manually do commit after delete?

[zishon]

Yes, I'm sure.

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

As I did not manual start the transaction, it will be committed every time I enter the sql command.
And I double checked the result by open another terminal and executed "select * from account;"

[Mingun]

Maybe you gets cached result from web-server? What happens if you select in another terminal after step T3? Whats happens if you run another instance of your application and try step T4 from it?