Packets out of order. Error retrieving data from MariaDB

community

#1

We have the latest DF 2.2 instance hooked up to the latest MariaDB instance.
Failed to call database stored procedure. Packets out of order. Expected 1 received 0. Packet size=0 (SQL: SELECT @o_message;)

Some guys are posting on the forums that this is because the MariaDB and latest MySQL DB’s are running sub-millisecond accuracy?! Dunno what this means, and that the way to fix it is to use:
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Anybody else have this issue?


#2

According to the PHP documentation,

PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool.

If this indeed is the fix, try adding the driver attribute to your service configuration in DreamFactory.


#3

This appears to be an issue caused by using a cursor in the MySQL stored procedure you are calling. As a workaround, rather make use of a temporary table instead of a cursor, if possible.


#4

Just to add to this, rather use a temp table with an auto-incrementing id field. Then you can use a while loop if you know the size of the table (select count(1) as numRecords from tbl_x).

(Pseudo code)
WHILE (i <= numRecords)

i++;
END