Calling stored procedures with variables and then selecting causes parsing errors from MySQL side?

[fagirton]

I used a simple calcUserRep procedure with user_id In parameter and reputation Out parameter.

The code in service.ts is also very straightforward, it takes string from dto and runs it as query:

async postSql(dto: SqlPost): Promise<string> {
    const data = await this.connection.query(dto.query);
    return JSON.stringify(Object.assign([{}], data));
  }

However, a single query with CALL and SELECT operators throws error in MySQL:
The query: CALL calcUserRep(1, @rep); SELECT @rep;
The error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @rep' at line 1
Inside MySQL Workbench under the same user the query works perfectly.

I also tried running two separate this.connection.query() methods, but seems like MySQL doesn't remember variables between the two.

Is this made by design or this is a bug? And how I can workaround this?

My software: nestjs-mysql 0.0.14, nest 10.3.8

[fagirton]

Figured out the method with two queries doesn't works because of queries going asynchronously where SELECT @rep; gets done before CALL calcUserRep(1, @rep);

But it still doesn't explain why it doesn't accept both of them in one query string

[Tony133]

Can you create minimal reproduction in a cloneable git repository?

[fagirton]

Just made one here https://github.com/fagirton/nest-mysql-procedures-multiline-bug
The problem still happens there. I ran CALL getString('test', @test); SELECT @test; and it returned a 500 with an error inside nest: ERROR [ExceptionsHandler] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @test' at line 1

[Tony133]

I checked the repository, you have to add the SQL otherwise it gives error, in the this point here:
https://github.com/fagirton/nest-mysql-procedures-multiline-bug/blob/main/src/app.service.ts#L11

for example:

  async runQuery(dto: dtoQuery) {
    const data = await this.connection.query('SELECT * FROM table_name', [dto.query]);
    return JSON.stringify(Object.assign([{}], data));
  }

Here you can find an example tutorial if you find it useful: https://dev.to/tony133/mysql-module-for-nestjs-8x-framework-5gh9.

My package is a wrapper of the mysql2 package, I created it to understand how dynamic modules work with Nest.

You can also just use the mysql2 package if it's easier for you 🙂