auto increment in mysql query in golang code not working

Question

I am working on a project using ethereum blockchain and I want to populate db with blocks data but for block_id autoincrement is not working.

code below is the create query

stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS block( block_id bigint NOT NULL AUTO_INCREMENT, block_num varchar(200), block_hash varchar(200), tx_count int, PRIMARY KEY (block_id) );")

code below is used to insert the data

func InsertBlock(db *sql.DB, block_num string, block_hash string, tx_count int) {
    stmt, err := db.Prepare("INSERT INTO block VALUES(?, ?, ?)")
    if err != nil {
        fmt.Println(err.Error())
    } else {
        fmt.Println("Preparation successfull for block insert: ")
    }

    _, err = stmt.Exec(block_num, block_hash, tx_count)
    if err != nil {
        fmt.Println(err.Error())
    } else {
        fmt.Println("Entry is block table is successfull: ")
    }
}

How can I make it auto increment?

Here is the error i am getting:

Error 1136: Column count doesn't match value count at row 1
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x38 pc=0x4e1930]

The problem might be with you SQL query, try INSERT INTO block (block_num, block_hash, tx_count) VALUES(?, ?, ?) — fedemengo
– fedemengo, Commented Aug 1, 2019 at 21:28
@fedemengo if his table only contains these columns and in that order his SQL should work but i agree using explicit columns in the INSERT is more better or less error prone.. — Raymond Nijland
– Raymond Nijland, Commented Aug 1, 2019 at 21:32

gere · Accepted Answer · 2022-03-24 21:05:45Z

2

As specified in the documentation:

If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

That means that your query INSERT INTO block VALUES(?, ?, ?) will always fail because you are specifying only three out of four values.

So you need to specify the list of the columns, like this:

INSERT INTO block (block_num, block_hash, tx_count) VALUES(?, ?, ?)

At that point MySql will not complain anymore because it knows that the missing column block_id is auto incremented so it doesn't need a value for that.

edited Mar 24, 2022 at 21:05

answered Aug 1, 2019 at 22:06

gere

1,7801 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

auto increment in mysql query in golang code not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related