Hi @hanson,
Thanks for the quick reply.
After a little more digging, I was able to reproduce the problem. Sorry for the long reply.
Considering a simple SP, like this:
DELIMITER //
CREATE OR REPLACE PROCEDURE test_sp(v int)
AS
DECLARE
c int = 1;
BEGIN
ECHO SELECT c + v as s;
END //
DELIMITER ;
CALL test_sp(1); -- s = 2
CALL test_sp(2); -- s = 3
It works with following node code:
const mysql = require('mysql');
// MemSQL connections
const conn = mysql.createPool({
connectionLimit: 10,
host: process.env.MEMSQL_HOST,
port: process.env.MEMSQL_PORT,
user: process.env.MEMSQL_USER,
password: process.env.MEMSQL_PASS,
database: process.env.MEMSQL_DATABASE,
debug: true,
multipleStatements: true,
});
conn.query(`CALL test_sp(?)`, 1, (error, data) => {
if (error) {
console.log(error)
conn.end();
return;
}
console.log(data);
conn.end();
});
And here is the protocol messages:
--> (3958195) ComQueryPacket { command: 3, sql: 'CALL test_sp(1)' }
<-- (3958195) ResultSetHeaderPacket { fieldCount: 1, extra: undefined }
<-- (3958195) FieldPacket {
catalog: 'def',
db: '',
table: '',
orgTable: '',
name: 's',
orgName: '',
charsetNr: 63,
length: 12,
type: 8,
flags: 128,
decimals: 0,
default: undefined,
zeroFill: false,
protocol41: true
}
<-- (3958195) EofPacket {
fieldCount: 254,
warningCount: 0,
serverStatus: 2,
protocol41: true
}
<-- (3958195) RowDataPacket { s: 2 }
<-- (3958195) EofPacket {
fieldCount: 254,
warningCount: 0,
serverStatus: 10,
protocol41: true
}
<-- (3958195) OkPacket {
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
Now consider a similar SP with temporary tables:
DELIMITER //
CREATE OR REPLACE PROCEDURE test_sp_2(v int)
AS
DECLARE
c int = 1;
BEGIN
DROP TABLE IF EXISTS temp_aux;
CREATE TEMPORARY TABLE temp_aux(
val int
);
DELETE FROM temp_aux;
INSERT INTO temp_aux VALUES (c + v);
ECHO SELECT val from temp_aux;
END //
DELIMITER ;
Now the code breaks. Here is the protocol message:
--> (3958129) ComQueryPacket { command: 3, sql: 'CALL test_sp_2(1)' }
<-- (3958129) OkPacket {
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
And we get a PROTOCOL_PACKETS_OUT_OF_ORDER error.
This also happens if I don’t use the temporary table at all, for instance returning the following in the previous SP:
ECHO SELECT 1;
I have a SP that really needs a temporary table for computation. The strange thing is that this works fine at least with SingleStore Studio and Sequel Pro. A similar issue is happening with PHP as well.
This looks like something the server is doing wrong as it is not returning the result set before, or maybe it is returning it differently.
Please let me know if you need more info.