MySQL: Packets out of order error

[antonio.ift]

Hi everyone,
I have an issue when I'm creating a table via mysql.
When I'm sending the query, the first time is ok but if I send the same query again I get this error:
[error] [mysql:268ef42f.aea6f4] Error: Packets out of order. Got: 1 Expected: 172

If I test the query in an external sw (i'm using heidiSQL) everything is ok.

How can I solve that?

Thank you.
Regards,
Antonio

[Colin]

Welcome to the forum @antonio.ift.

You say the issue is when creating a table, but is it actually when adding data to the table?

What OS are you running on and what versions of nodejs (node -v) and node-red are you using (look at the bottom of the Hamburger dropdown menu for that). Also what version of the mysql node (in Manage Palette). If not the latest then update the mysql node.

[zenofmud]

@antonio.ift Also what is the query you are sending?

[antonio.ift]

Hi Colin, Hi zenofmud,
Nodered: v1.1.3
Nodejs: v12.8.4.
Actual OS is Lubuntu 20.04.1 LTS (Linux 5.4.0-48-generic x64 LE)
MySQL plugin: node-red-node-mysql version 0.1.0

The issue is when I read the SQL to get some data and after that format for a dashboard table, I didn't explain correctly.

After some test I discovered that I have this problem when I run my query and filter the results with the function in SQL "BETWEEN".

The query is:

SET @columnsName = "";
SET @i = 1;
SET @i2 = 1;
SET @i3 = 1;
SET @sensorsNumber = (SELECT COUNT(*) FROM list_sensor);
SET @enableDateFilter = "{{flow.enableFilter}}";
SET @initialDate = "{{flow.startDatetime}}";
SET @endDate = "{{flow.endDatetime}}";

WHILE @i <= @sensorsNumber DO
SET @func = CONCAT("SET @descr", @i, " = (SELECT description FROM list_sensor WHERE nr=", @i, ")");
PREPARE stmt1 FROM @func;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; 
SET @i = @i + 1;
END WHILE;

WHILE @i2 <= @sensorsNumber DO
SET @func  = CONCAT("SET @appoggio = @", "descr", @i2);
PREPARE stmt2 FROM @func;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
IF @appoggio=''
    THEN
    	SET @func = CONCAT("SET @funcStruct", @i2, " = ''");
    	PREPARE stmt3 FROM @func;
    	EXECUTE stmt3;
    	DEALLOCATE PREPARE stmt3; 
    	SET @i2 = @i2 + 1;
    ELSE
    	SET @func = CONCAT("SET @funcStruct", @i2, " = ", "', datalog_sensor.", @i2, " AS " ,"`", @appoggio, "`" , "'");
    	PREPARE stmt4 FROM @func;
    	EXECUTE stmt4;
    	DEALLOCATE PREPARE stmt4; 
    	SET @i2 = @i2 + 1;
    END IF;
END WHILE;

WHILE @i3 <= @sensorsNumber DO
SET @func = CONCAT("SET @columnsName = CONCAT(@columnsName, @funcStruct", @i3, ")");
PREPARE stmt5 FROM @func;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;
SET @i3 = @i3 + 1;
END WHILE;

SET @func = CONCAT("SELECT datalog_sensor.DATETIME AS 'Orario' ", @columnsName, " FROM datalog_sensor");
IF @enableDateFilter = 1 THEN
SET @func = CONCAT(@func, " WHERE datetime Between '",@initialDate ,"' AND '",@endDate ,"'");
END IF;
SET @func = CONCAT(@func , ";");
PREPARE stmt6 FROM @func;
EXECUTE stmt6;
DEALLOCATE PREPARE stmt6;

The problem looks like to be in this part of the query, when @enableDateFilter is 1. When @enableDateFilter is 0 the query is working.

SET @func = CONCAT("SELECT datalog_sensor.DATETIME AS 'Orario' ", @columnsName, " FROM datalog_sensor");
IF @enableDateFilter = "1" THEN
SET @func = CONCAT(@func, " WHERE datetime Between '",@initialDate ,"' AND '",@endDate ,"'");
END IF;
SET @func = CONCAT(@func , ";");
PREPARE stmt6 FROM @func;
EXECUTE stmt6;
DEALLOCATE PREPARE stmt6;

In HeidiSQL if I set fixed values instead of the flow variables, the query is working good.

Thanks.
Antonio.

[Nodi.Rubrum]

I ran into this as well, just this week, I had to update the max packet size allowed, ensure the credentials where correct to update the given table, and that the total connection count was sufficient. All 3 of these issues can cause the error of X expect Y packet out of order scenario. For whatever reason, mysql node was orphaning connections or not pooling right, or the connections where not closing as expected. This is quite a common issue with mariaDB and mySQL DB instances... The error message being indicative of various issues, of course.

[antonio.ift]

Hi Nodi.Rubrum,
I've tried to change this setting with the maximum value allowed (1073741824 = 1GB) and nothing changed...

[antonio.ift]

Hi everyone,
I've found the problem.

The main problem is because i'm running mariadb on a virtual machine. When I run the db in local machine no problems.

So, the solution is to avoid virtual machines.

[Colin]

Perhaps the VM didn't have sufficient resources.

[antonio.ift]

The vm is configured with 4 processors, 4gb og ram, 50gb of hdd and unlimited network.
So I don't think is a problem of resources.

[Colin]

There are other possible resource issues, connectivity for example. There must be some reason, as mysql doesn't know whether it is in a VM or not. Alternatively perhaps it ended up being configured slightly differently.

[Nodi.Rubrum]

I had to make mine about 2G, I also noticed that connection pooling was not working, I finally narrowed it down, some of the time, being large queries, and too many connections held open too long. So I increased the connection count as well. I have noticed the NR mysql module does some strange things with my mariaDB. I am not saying bugs or such, just that the nodes will go offline inconsistently, and reconnect on next query/insert. Whereas other instances of the same node will stay connected for basically forever. This is likely because I am in some flows doing just frequently enough transactions to abuse the connection count, as well as the timeout defaults, otherwise pooling should be avoiding the same issues.

Being in a VM should NOT be an issue at all unless your VMs are dropping packets, that should never happen under typical situations. It is easy to rule that out, just sniff the network traffic.