sqlgoparallel-processingmariadbprepared-statement

'Error 1615 (HY000): Prepared statement needs to be re-prepared' error occures on SELECT with default values and sequences


In MariaDB 11.4 (and other versions like 10.6 or 10.8), when I try to execute multiple times in parallel this request with prepared statement :

SELECT DEFAULT(name) FROM table1

with this schema :

create sequence seq;
create table table1
(
    id   int          default nextval(`booking`.`seq`) not null primary key,
    name varchar(200) default 'auto'                   not null
);

This error can be eventually raised by MariaDB :

Error 1615 (HY000): Prepared statement needs to be re-prepared

This error never occurs if I apply one of these actions :

I have the same issue if I create only one or many prepared statements in my process.

No change if I update table_definition_cache.

No data needed to reproduce it.

I don't really know why I'm faced to this problem.

I reproduced this issue with a PHP application (8.2) with mysqli module and golang v1.22 with go-sql-driver/mysql v1.8.1 library.

below my go code :

package main

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

const myrequest = `SELECT
        DEFAULT(name)
        FROM table1`

func createDB() *sql.DB {
    db, err := sql.Open("mysql", "dev:dev@tcp(localhost:3306)/dev")
    if err != nil {
        panic(err.Error())
    }
    return db
}

func request(request string, db *sql.DB) {
    stmt, err := db.Prepare(request)

    if err != nil {
        panic(err.Error())
    }
    _, err = stmt.Query()
    if err != nil {
        panic(err.Error())
    }
}

func main() {
    db := createDB()
    for i := 0; i < 100; i++ {
        go func() {
            request(myrequest, db)
        }()
    }

    time.Sleep(1 * time.Second)
}

and the output :

panic: Error 1615 (HY000): Prepared statement needs to be re-prepared

goroutine 76 [running]:
main.request({0x640f56?, 0x0?}, 0x0?)
        /home/bperrard/Documents/booking/testmariadb/main.go:336 +0x7c
main.main.func1()
        /home/bperrard/Documents/booking/testmariadb/main.go:344 +0x25
created by main.main in goroutine 1
        /home/bperrard/Documents/booking/testmariadb/main.go:343 +0x25

Solution

  • Good question. There's no good reason to return the error.

    As such I a bug report based on this question (MDEV-34669).

    A fix has been written, however it may not make it into the next release (in progress) as it needs a good review..