mysql - SQL Query Error - "Column 'block_index' cannot be null" when adding transaction - Stac

admin管理员组

文章数量:1201361

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?

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?

• mysql
• rust

Share Improve this question Follow edited Jan 22 at 0:57 cafce25 27.2k55 gold badges4242 silver badges5454 bronze badges asked Jan 21 at 16:40 CodingApperenticeCodingApperentice 1144 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I believe I figured it out. I added the genesis block, but I was mistakenly trying to add transactions to the genesis block instead of adding them to the next block, which would be the first block after the genesis block. The issue was that I had the next block set with an index of 1 and a previous hash of 0. Once I switched to using a block index of 2 and a previous hash of the genesis block, all my tests passed.

本文标签： mysqlSQL Query ErrorquotColumn 39blockindex39 cannot be nullquot when adding transactionStack Overflow