"Error 1210: Incorrect arguments to mysqld_stmt_execute" with empty strings and 65535 placeholders

[ClemOzil]

Issue description

In our "BatchInsert" function, we prepare a query with the maximum number of placeholders.

But when we try to execute the prepared statement, with empty strings in the arguments, we get an error.

stmt.Exec(args...) returns an "Error 1210" when args contains empty strings, and when its length is close to 65536 (maximum possible length of placeholders)

When we limit the number of placeholders to 60000, it works again.

Example code

// caller
columnNames := []string{"id", "id1", "text", "id2", "id3"}
params := []interface{}{}

nbLine := 100000

for i := 0; i < nbLine; i ++ {
	params = append(params, i+1, i+1, "", i+1, i+1)
}
BatchInsert(db, "tablename", columnNames, params_tmp...)

//in BatchInsert

func BatchInsert(db *sql.DB, tableName string, columnNames []string, args ...interface{}) error {

...
//stuff before

    stmt, err := db.Prepare(finalRequest)
    if err != nil {
	return errors.Wrap(err, " Mass Insert : Prepare FinalRequest")
    }
    defer stmt.Close()

//stuff

    _, err := stmt.Exec(args[indexStart:indexEnd]...)
    //this returns an error, when args contains an empty string, and its length approaches 65536

//stuff after
}

Error log

Error 1210: Incorrect arguments to mysqld_stmt_execute

Configuration

Driver version (or git SHA):
9181e3a

Go version: run go version in your console
1.9.1

Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 5.7.19

Server OS: E.g. Debian 8.1 (Jessie), Windows 10
Debian GNU/Linux 8.9 (jessie)

[methane]

I need complete reproducible example.

[ClemOzil]

Bug

serverInsert := "login:pw@tcp(server:test)/db"
db, err := sql.Open("mysql", serverInsert)

defer db.Close()
if err != nil {
	log.Fatal(err)
}

columnNames := []string{"id", "id1", "text", "id2", "id3"}
params_tmp := []interface{}{}
nbLine := 65536/len(columnNames)

sStmt := "insert into tablename (id, id1, text, id2, id3) values "

for i := 0; i < nbLine; i ++ {
	params_tmp = append(params_tmp, i+1, i+1, "", i+1, i+1)
	sStmt += "(?,?,?,?,?),"
}
sStmt = sStmt[:len(sStmt)-1]

stmt, err := db.Prepare(sStmt)
if err != nil {
	fmt.Println("Err prepare ", err)
}
defer stmt.Close()

_, err = stmt.Exec(params_tmp...)

if err != nil {
	fmt.Println("Err exec", err)
}

stmt.Exec returns : Err exec Error 1210: Incorrect arguments to mysqld_stmt_execute

If you replace 65536 by 60000, or if you replace the empty string "" (line 16) by " " or by nil, stmt.Exec works fine.

The limit seems to be 63435

[methane]

@ClemOzil Why do you think it's driver's bug rather than MySQL's bug or limitation?

[ClemOzil]

Firstly, the first few responses from Google when searching "Error 1210: Incorrect arguments to mysqld_stmt_execute" come from this github.

See #227, #201, #209, #210

Secondly, doing it directly in MySQL Workbench works :

SET @req = 'insert into tablename (id, id1, text, id2, id3) values 
(?,?,?,?,?),
(?,?,?,?,?),
 ... , // x 13104
(?,?,?,?,?)';

PREPARE testInsert FROM @req;

SET @id = 1;
SET @text = "";
EXECUTE testInsert USING 
@id,@id,@text,@id,@id,
@id,@id,@text,@id,@id,
...,  // x 13104
@id,@id,@text,@id,@id

[methane]

See #227, #201, #209, #210

They are fixed already.

Secondly, doing it directly in MySQL Workbench works :

SQL level prepared statement and protocol level prepared statement is different.

[methane]

And your example is not "complete reproducible".

"Complete example" should be include SQL for creating table and main function.
All we need to do should be just replacing username and password.

[methane]

I confirmed PHP can run prepared statement with 65535 placeholders and Go can't.
I'll capture packet and compare them.

[methane]

@ClemOzil I found the issue happens when max_allowed_packet is not large enough
and this driver tried to use STMT_LONG_DATA mechanizm for empty string.
I recommend you to enlarge max_allowed_packet, and not use so many placeholders.

Prepare one statement with about 1000 placeholders, and reuse it as much as possible
is much efficient than 65535 placeholders.