Last Insert ID unpackaging fails for MySQL when using Default values for autoincremented key

[nag763]

Description

When inserting a value in a table with several primary keys and using the default value for an auto-incremented one, the insert fails.

Known to be reproducible with MySQL.

Steps to Reproduce

1. First create a simple database having a table with several one primary key, one being auto-incremented

- MySQL dump 10.13  Distrib 8.0.28, for Linux (x86_64)
--
-- Host: localhost    Database: sea
-- ------------------------------------------------------
-- Server version   8.0.28-0ubuntu0.20.04.3

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `GAME`
--

DROP TABLE IF EXISTS `GAME`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `GAME` (
  `id` int NOT NULL AUTO_INCREMENT,
  `home_team_id` int NOT NULL,
  `away_team_id` int NOT NULL,
  PRIMARY KEY (`id`,`home_team_id`,`away_team_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-04-04 19:18:04

Table schema :

2. Create a new cargo project and generate the entities with the sea-orm-cli

cargo new sea_orm_issue
cd sea_orm_issue
sea-orm-cli generate entity -o src/entities/

Entity generated :

//! SeaORM Entity. Generated by sea-orm-codegen 0.7.0

use sea_orm::entity::prelude::*;

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "GAME")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub home_team_id: i32,
    #[sea_orm(primary_key, auto_increment = false)]
    pub away_team_id: i32,
}

#[derive(Copy, Clone, Debug, EnumIter)]
pub enum Relation {}

impl RelationTrait for Relation {
    fn def(&self) -> RelationDef {
        panic!("No RelationDef")
    }
}

impl ActiveModelBehavior for ActiveModel {}

3. Add the following dependencies in Cargo.toml :

[dependencies]
sea-orm = { version ="0.7" , features = ["runtime-async-std-native-tls", "sqlx-mysql"]}
tokio = { version = "1.17.0", features = ["full"] }
env_logger = "0.9.0"

4. Create a simple main to insert your entity :

pub mod entities;
use entities::{game};
use sea_orm::{entity::*, error::*, Database, DbConn};

#[tokio::main]
async fn main() -> Result<(), DbErr> {
    env_logger::init();
    let db: DbConn = Database::connect("mysql://sea_usr:password@dbhost:3306/sea").await.unwrap();
    let new_game = game::ActiveModel {
        home_team_id: Set(1),
        away_team_id: Set(2),
        ..Default::default()
    };
    new_game.insert(&db).await?;
    Ok(())
}

5. Run it

cargo r

6. And it should throw the following error (detailed log.txt attached)

cargo r
    Finished dev [unoptimized + debuginfo] target(s) in 0.21s
     Running `target/debug/sea-orm-issue-multiple-pk`
Error: Exec("Fail to unpack last_insert_id")

Let's note that :

• the issue doesn't occur when we have a single primary key in the table
• the entity is persisted despite the error being thrown

Expected Behavior

The entity should be inserted in the table, and no error should be thrown.

Actual Behavior

The entity is inserted in the table, but an error is thrown

Reproduces How Often

On every insert for a mysql database

Versions

• MySQL v8
• Rust 1.57
• Sea-ORM 0.7.0
• Rust 1.57

Additional Information

A sample project will be shared

[nag763]

Update : I thought it was happening when using several primary keys for a table, but it also happened with the expected example I was about to provide

Here is the link to the project summing up the issue

Branch issue, the one described here : https://github.com/nag763/sea-orm-issue-662

Branch issue_2 , the one that happened when I tried to show the expected behavior : https://github.com/nag763/sea-orm-issue-662/tree/issue_2

The difference between the two branches is the sql_init/1_schema.sql file that has for the issue branch three primary keys, and for the issue2 branch only one. The entities generated in consequence by the sea-orm-cli are thus different.

To build any of the branch, use docker-compose up command

[billy1624]

Hey @nag763, I'm sorry about the delay...

The schema seems a bit odd to me. I feel like the primary key is just the id column with auto-increment. Of cause, you can add a unique key: (id, home_team_id, away_team_id)

#[derive(Clone, Debug, PartialEq, DeriveEntityModel)]
#[sea_orm(table_name = "GAME")]
pub struct Model {
    #[sea_orm(primary_key)] // Imply auto_increment = true
    pub id: i32,
    pub home_team_id: i32,
    pub away_team_id: i32,
}

[billy1624]

But yeah, I have to admit there is a "bug" inside the insert when the (composite) primary key is partially auto-incremented

[nag763]

Hello @billy1624 , No worries for the delay ! :+1:
Indeed the schema is a bit complicated, I had to refactor it anyway. :laughing: I just kept the issue open in case it could cover some " more legit " bugs.

[tyt2y3]

In SeaORM, composite primary key must have all columns set to be auto_increment = false. This constraint might be very difficult to workaround.

Actually, we might be able to make the macro check for this and refuse to compile.