"Malformed communication packet" when using prepared statements

[bkotos]

Hello,

I get the following MySQL error when I invoke .execute() with a prepared statement:

{
  type: "Error",
  message: "Malformed communication packet.",
  stack:
    "Error: Malformed communication packet.\n    at PromiseConnection.execute (/path/to/project/node_modules/mysql2/promise.js:111:22)\n    at UserRepository.<anonymous> (/path/to/project/src/lib/repository/UserRepository.ts:11:52)\n    at step (/path/to/project/src/lib/repository/UserRepository.ts:26:23)\n    at Object.next (/path/to/project/src/lib/repository/UserRepository.ts:7:53)\n    at /path/to/project/src/lib/repository/UserRepository.ts:8:71\n    at new Promise (<anonymous>)\n    at __awaiter (/path/to/project/src/lib/repository/UserRepository.ts:4:12)\n    at UserRepository.fetchUserById (/path/to/project/src/lib/repository/UserRepository.ts:51:16)",
  code: "ER_MALFORMED_PACKET",
  errno: 1835,
  sql: "SELECT * FROM `User` WHERE `userId` = ?",
  sqlState: "HY000",
  sqlMessage: "Malformed communication packet.",
}

My code looks like this:

import { Connection } from "mysql2/promise";
import { User } from "./model";

export class UserRepository {
    private mysqlConnection: Connection;

    constructor(mysqlConnection: Connection) {
        this.mysqlConnection = mysqlConnection;
    }

    async fetchUserById(userId: number): Promise<User> {
        const params = [userId];
        const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = ?"), params) as any;
        return rows[0];
    }
}

I've tried hard-coding the bound value, which produces the same result:

async fetchUserById(userId: number): Promise<User> {
    const params = [2];
    const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = ?"), params) as any;
    return rows[0];
}

I also tried removing the params array and hard-coding the value into the query, to eliminate the query itself as an issue. When I do this, the query executes properly and returns data.

async fetchUserById(userId: number): Promise<User> {
    const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = 2")) as any;
    return rows[0];
}

I am using MySQL 8:

bkotos@bkotos-pi:~$ mysql -V
mysql  Ver 8.0.27-0ubuntu0.21.04.1 for Linux on aarch64 ((Ubuntu))

Am I doing something wrong? Or is there maybe an issue with my MySQL setup? Any help is greatly appreciated!

[sidorares]

I suspect root cause is the same as described in #1239 (comment)

I'm working on a fix in #1407

[bkotos]

Thank you!

[sidorares]

just to confirm its the issue I suspect ( incompatible parameter type ) can you try casting your parameter to string and see if you still get an error?

    async fetchUserById(userId: number): Promise<User> {
        const params = [`${userId}`];
        const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = ?"), params) as any;
        return rows[0];
    }

[bkotos]

@sidorares I was about to try your suggestion and then realized this is actually just an issue with me closing parenthesis out of order 🤦‍♂️ .

I had:

/* ... */ execute("SELECT * FROM `User` WHERE `userId` = ?"), params)

When instead I should have had:

/* ... */ execute("SELECT * FROM `User` WHERE `userId` = ?", params))

So basically, my issue was params wasn't getting passed as the second argument to the execute() method even though my query was expecting bound parameters.

Just to be sure, I tried binding both a number and casting it to a string like you suggested, and both work fine once my parens are closed in the correct order.

Code with number param:

    async fetchUserById(/*userId: number*/): Promise<User> {
        const userId = 6
        const params = [userId];
        console.log('input', { userId, params })
        const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = ?", params)) as any;
        console.log('output', { rows })
        return rows[0];
    }

input { userId: 6, params: [ 6 ] }
output {
  rows: [
    { userId: 6, username: 'jdoe', firstName: 'John', lastName: 'Doe' }
  ]
}

Code with string param:

    async fetchUserById(/*userId: number*/): Promise<User> {
        const userId = 6
        const params = [`${userId}`];
        console.log('input', { userId, params })
        const [rows] = (await this.mysqlConnection.execute("SELECT * FROM `User` WHERE `userId` = ?", params)) as any;
        console.log('output', { rows })
        return rows[0];
    }

input { userId: 6, params: [ '6' ] }
output {
  rows: [
    { userId: 6, username: 'jdoe', firstName: 'John', lastName: 'Doe' }
  ]
}

[sidorares]

@bkotos hopefully this line can save some time debugging similar error in the future

[sidorares]

closing for now as not directly related to bind parameters serialization ( linked PR will help with better error message though )