Rust: SQL Query Error - "Column 'block_index' cannot be null" when adding transaction

I'm working on a blockchain implementation in Rust using MySQL as the database. I have two tables: blocks and transactions. Transactions are associated with blocks via a foreign key (block_index).

CREATE TABLE blocks (
    block_index BIGINT NOT NULL PRIMARY KEY,
    previous_hash VARCHAR(255) NOT NULL,
    block_hash VARCHAR(255) NOT NULL,
    validator_signature VARCHAR(255) NOT NULL,
    timestamp DATETIME NOT NULL
);

CREATE TABLE transactions (
    transaction_id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    block_index BIGINT NOT NULL,
    file_hash VARCHAR(255) NOT NULL,
    uploader_id BIGINT,
    uploader_name VARCHAR(255) NOT NULL,
    transaction_checksum VARCHAR(255) NOT NULL,
    timestamp DATETIME NOT NULL,
    FOREIGN KEY (block_index) REFERENCES blocks(block_index)
);

#[derive(Debug, Clone)]
pub struct Transaction {
    pub transaction_id: u64,
    pub block_index: Option<u64>, // Associated block index
    pub file_hash: String,
    pub uploader_id: Option<u64>,
    pub uploader_name: String,
    pub transaction_checksum: String,
    pub timestamp: chrono::DateTime<chrono::Utc>,
}

pub async fn add_transaction(
    &mut self,
    transaction: Transaction,
    pool: &mysql_async::Pool,
) -> Result<(), mysql_async::Error> {
    if transaction.block_index.is_none() {
        return Err(mysql_async::Error::Other(Box::new(std::io::Error::new(
            std::io::ErrorKind::InvalidInput,
            "Transaction must have a valid block_index",
        ))));
    }

    let mut conn = pool.get_conn().await?;
    conn.exec_drop(
        r"INSERT INTO transactions (
            transaction_id, block_index, file_hash, uploader_id, uploader_name, 
     transaction_checksum, timestamp
        ) VALUES (
            :transaction_id, :block_index, :file_hash, :uploader_id, :uploader_name, 
     :transaction_checksum, :timestamp
        )",
        mysql_async::params! {
            "transaction_id" => transaction.transaction_id,
            "block_index" => transaction.block_index.unwrap(),
            "file_hash" => transaction.file_hash,
            "uploader_id" => transaction.uploader_id,
            "uploader_name" => transaction.uploader_name,
            "transaction_checksum" => transaction.transaction_checksum,
            "timestamp" => transaction.timestamp.to_string(),
        },
    ).await?;

    Ok(())
}

#[tokio::test]
async fn test_add_transaction() {
    let database_url = "mysql://root:password@localhost:3306/test_public_blockchain";
    let pool = mysql_async::Pool::new(database_url);

    let mut conn = pool.get_conn().await.unwrap();
    conn.exec_drop("DELETE FROM transactions", ()).await.unwrap();
    conn.exec_drop("DELETE FROM blocks", ()).await.unwrap();

    conn.exec_drop(
        r"INSERT INTO blocks (block_index, previous_hash, block_hash, validator_signature, 
    timestamp)
          VALUES (1, '0', 'genesis_hash', 'validator1', NOW())",
        (),
    ).await.unwrap();

    let transaction = Transaction {
        transaction_id: 1,
        block_index: Some(1),
        file_hash: "test_hash".to_string(),
        uploader_id: Some(1),
        uploader_name: "Test Uploader".to_string(),
        transaction_checksum: "checksum".to_string(),
        timestamp: chrono::Utc::now(),
    };

    let mut blockchain = Blockchain::new(Block::new(0, "0".to_string(), vec![], 
    "validator1".to_string()), vec![]);
    blockchain.add_transaction(transaction.clone(), &pool).await.unwrap();
}

When I run the test, I get the following error:

Server(ServerError { code: 1048, message: "Column 'block_index' cannot be null", state: 
    "23000" })

I've verified the block_index value in the debug logs before the SQL query.

I've confirmed block_index = 1 exists in the block table

I've ran the query manually, and it works when directly executed in MySQL

I've added println! to pront the databefore inserting the data to make sure nothing was null or specific block_index wasn't null, I've hard coded the block_index to 1 and still got the error of it being null, i tried disabling the foreign keys to see if that was the issue and still same error

Question:

Why does block_index appear as NULL in the query execution when it is set to Some(1) in the Rust code? How can I resolve this issue in Rust with the mysql_async crate?

Source: View source

Read Next