Go panics when too many goroutines querying MySQL

user_11589183 0
Viewed 144

I want to run separate goroutines that would work with a MySQL database. I wrote the code and it does work if the amount of goroutines is less than a 1000. But than I change it to a 1000, Go starts to panic.

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "time"
)

func routine(db *sql.DB, id int, ch chan<- string) {
    update, _ := db.Prepare("SELECT salary FROM users WHERE id = (?)")
    defer update.Close()
    var salary string
    update.QueryRow(id).Scan(&salary)
    ch <- salary
}

func main() {
    n := 1000

    ch := make(chan string)
    list := make([]string, n)

    db, _ := sql.Open("mysql", "root:root@/database")
    db.SetConnMaxLifetime(time.Minute * 3)
    defer db.Close()

    for i := 0; i < n; i++ {
        go routine(db, 123, ch)
    }
    for i := 0; i < n; i++ {
        list[i] = <-ch
    }
}

And here's the error

panic: runtime error: invalid memory address or nil pointer dereference
        panic: runtime error: invalid memory address or nil pointer dereference

goroutine 948 [running]:
database/sql.(*Stmt).Close(0x0)
        C:/Program Files/Go/src/database/sql/sql.go:2872 +0x37
panic({0x4e8b20, 0x689240})
        C:/Program Files/Go/src/runtime/panic.go:838 +0x207
database/sql.(*Stmt).QueryContext(0x0, {0x5788e8, 0xc000018050}, {0xc000d5bf60, 0x1, 0x1})
        C:/Program Files/Go/src/database/sql/sql.go:2767 +0x82
database/sql.(*Stmt).QueryRowContext(0x0?, {0x5788e8?, 0xc000018050?}, {0xc000d5bf60?, 0x27?, 0x0?})
        C:/Program Files/Go/src/database/sql/sql.go:2845 +0x2c
database/sql.(*Stmt).QueryRow(...)
        C:/Program Files/Go/src/database/sql/sql.go:2867
main.routine(0x0?, 0x0?, 0x0?)
        C:/Users/me/Desktop/go/5. MySQL/main.go:13 +0xfb
created by main.main
        C:/Users/me/Desktop/go/5. MySQL/main.go:28 +0xba

I'm not really sure what is the reason of this error. The code does work with less goroutines. In addition, I tried to use SQLite adapter, and 1000 goroutines worked just fine. But 1000 in MySQL is not.

Could you please describe how to get rid of these panics and make 1000 or even 10 thousands of goroutines work with the database?

My Go version is 1.18.3 windows/amd64
github.com/go-sql-driver/mysql - v1.6.0

goroutine go asynchronous mysql sql
2 Answers

Check your errors, don't ignore them. For example...

update, _ := db.Prepare("SELECT salary FROM users WHERE id = (?)")

The second value returned from db.Prepare is an error which will explain what went wrong, but you're ignoring it. If prepare fails, update will be corrupted. When you try to use it in update.QueryRow(id).Scan(&salary) or defer update.Close() you get a panic.

Check the error and deal with it. In this case, print it and return.

update, err := db.Prepare("SELECT salary FROM users WHERE id = (?)")
if err != nil {
  fmt.Println("db.Prepare failed:", err)
  return
}

// This has to come afterwards else you'll try to close nil.
defer update.Close()

Do this for everything which can return an error. That means db.Prepare, sql.Open, and Row.Scan.

See also Errors and Exception Handling in Golang.

Note: MySQL has a maximum number of prepared statements at one time. If this is set low, for example 1024, that could be the problem. But the error should tell you.

Note: Preparing, executing, and closing the same statement over and over defeats the point of prepared statements. In a real application you'd prepare the statement once and pass it into each Goroutine. You'd only close the statement once all Goroutines are complete.

user_146600

Given this code...

update, _ := db.Prepare("SELECT salary FROM users WHERE id = (?)")
defer update.Close()

When db.Prepare returns an error, update will be nil*. You then attempt to call nil.Close(), yielding your error.

Don't blindly call Close on a nil object.

* This is not obvious from reading the documentation, but it's clear from the implementation (which ultimately lands on this line) that when the returned error is not nil, the *Stmt will be nil.

user_2290440
Related