[CONC-586] Connector returns out of sync errors / packets out of order Created: 2022-04-06  Updated: 2022-04-08  Resolved: 2022-04-07

Status: Closed
Project: MariaDB Connector/C
Component/s: API
Affects Version/s: 3.1.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: David Carver Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

macOS 10.15.7, Ubuntu 20.04


 Description   

The following PHP code code causes a packets out of order error

<?php
 
 
 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
 
$mysqli = new mysqli( "127.0.0.1", "", "", "" );
 
 
 
$select1 = $mysqli->prepare( "SELECT 1" );
 
$select1->execute();
 
$select1->store_result();
 
 
 
$select2 = $mysqli->prepare( "SELECT 2" );
 
$select2->execute();
 
 
 
while ( $select1->fetch() )
 
{
 
	;
 
}
 
 
 
$select1->free_result();
 
 
 
$select1 = $mysqli->prepare( "SELECT 1" );
 
$select1->execute();
 
$select1->store_result();
 
 
 
$select2 = $mysqli->prepare( "SELECT 2" );
 
$select2->execute();

Error

dcarver@dcarver:/private/tmp$ php test.php
 
 
 
Warning: Packets out of order. Expected 1 received 4. Packet size=10 in /private/tmp/test.php on line 8
 
 
 
Warning: mysqli::prepare(): MySQL server has gone away in /private/tmp/test.php on line 8
 
 
 
Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in /private/tmp/test.php:8
 
Stack trace:
 
#0 /private/tmp/test.php(8): mysqli->prepare('SELECT 1')
 
#1 {main}
 
  thrown in /private/tmp/test.php on line 8
 
dcarver@dcarver:/private/tmp$ php test.php
 
 
 
Warning: Packets out of order. Expected 1 received 4. Packet size=10 in /private/tmp/test.php on line 20
 
 
 
Warning: mysqli::prepare(): MySQL server has gone away in /private/tmp/test.php on line 20
 
 
 
Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in /private/tmp/test.php:20
 
Stack trace:
 
#0 /private/tmp/test.php(20): mysqli->prepare('SELECT 1')
 
#1 {main}
 
  thrown in /private/tmp/test.php on line 20

The following code cause an out of sync error

<?php
 
 
 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
 
$mysqli = new mysqli( "127.0.0.1", "", "", "" );
 
 
 
$select1 = $mysqli->prepare( "SELECT 1" );
 
$select1->execute();
 
$select1->store_result();
 
 
 
$select2 = $mysqli->prepare( "SELECT 2" );
 
$select2->execute();
 
 
 
while ( $select1->fetch() )
 
{
 
	;
 
}
 
 
 
$select3 = $mysqli->prepare( "SELECT 3" );
 
$select3->execute();
 
 
 
?>

Error

dcarver@dcarver:/private/tmp$ php test.php
 
 
 
Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in /private/tmp/test.php:18
 
Stack trace:
 
#0 /private/tmp/test.php(18): mysqli->prepare('SELECT 3')
 
#1 {main}
 
  thrown in /private/tmp/test.php on line 18
 
dcarver@dcarver:/private/tmp$ php test.php
 
 
 
Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in /private/tmp/test.php:18
 
Stack trace:
 
#0 /private/tmp/test.php(18): mysqli->prepare('SELECT 3')
 
#1 {main}
 
  thrown in /private/tmp/test.php on line 18



 Comments   
Comment by David Carver [ 2022-04-06 ]

Here is another test program written in C so you don't have to dig through the PHP source

#include <mysql.h>
 
 
 
#include <stdio.h>
 
#include <string.h>
 
 
 
MYSQL_STMT *stmt_init( MYSQL *mysql )
 
{
 
	MYSQL_STMT *stmt;
 
 
 
	if ( ( stmt = mysql_stmt_init( mysql ) ) == NULL )
 
	{
 
		fprintf( stderr, "mysql_stmt_init failed: %s\n", mysql_error( mysql ) );
 
		return NULL;
 
	}
 
 
 
	return stmt;
 
}
 
 
 
int stmt_prepare( MYSQL_STMT *stmt, const char *query, int query_length )
 
{
 
	if ( mysql_stmt_prepare( stmt, query, query_length ) )
 
	{
 
		fprintf( stderr, "mysql_stmt_prepare failed: %s\n", mysql_stmt_error( stmt ) );
 
		return 1;
 
	}
 
 
 
	return 0;
 
}
 
 
 
int stmt_execute( MYSQL_STMT *stmt )
 
{
 
	if ( mysql_stmt_execute( stmt ) )
 
	{
 
		fprintf( stderr, "mysql_stmt_execute failed: %s\n", mysql_stmt_error( stmt ) );
 
		return 1;
 
	}
 
 
 
	return 0;
 
}
 
 
 
int stmt_fetch( MYSQL_STMT *stmt )
 
{
 
	int ret;
 
 
 
	if ( ( ret = mysql_stmt_fetch( stmt ) ) == 1 )
 
	{
 
		fprintf( stderr, "mysql_stmt_fetch failed: %s\n", mysql_stmt_error( stmt ) );
 
		return 1;
 
	}
 
 
 
	return ret;
 
}
 
 
 
int stmt_store_result( MYSQL_STMT *stmt )
 
{
 
	if ( mysql_stmt_store_result( stmt ) )
 
	{
 
		fprintf( stderr, "mysql_stmt_store_result failed: %s\n", mysql_stmt_error( stmt ) );
 
		return 1;
 
	}
 
 
 
	return 0;
 
}
 
 
 
void execute( MYSQL *mysql, int trigger_eof )
 
{
 
	MYSQL_STMT *stmt1, *stmt2, *stmt3;
 
 
 
	fprintf( stdout, "Test %d Started\n", trigger_eof );
 
 
 
	if ( ( stmt1 = stmt_init( mysql ) ) == NULL )	return;
 
	if ( stmt_prepare( stmt1, "SELECT 1", 8 ) )		return;
 
	if ( stmt_execute( stmt1 ) )					return;
 
	if ( stmt_store_result( stmt1 ) )				return;
 
 
 
	if ( ( stmt2 = stmt_init( mysql ) ) == NULL )	return;
 
	if ( stmt_prepare( stmt2, "SELECT 1", 8 ) )		return;
 
	if ( stmt_execute( stmt2 ) )					return;
 
	if ( stmt_fetch( stmt2 ) == 1 )					return;
 
 
 
	// fetch stmt1 first result
 
	if ( stmt_fetch( stmt1 ) == 1 )					return;
 
 
 
	// if set to trigger EOF, fetch again
 
	if ( trigger_eof )
 
	{
 
		if ( stmt_fetch( stmt1 ) == 1 )				return;
 
	}
 
 
 
	// attempt to store stmt2 results
 
	if ( stmt_store_result( stmt2 ) )				return;
 
 
 
	fprintf( stdout, "Test %d Finished\n\n", trigger_eof );
 
}
 
 
 
int main( int argc, char **argv )
 
{
 
	MYSQL *mysql;
 
 
 
	if ( ( mysql = mysql_init( NULL ) ) == NULL )
 
	{
 
		fprintf( stderr, "mysql_init failed\n" );
 
		return 1;
 
	}
 
 
 
	if ( !mysql_real_connect( mysql, "127.0.0.1", "root", "", "", 3306, NULL, 0 ) )
 
	{
 
		fprintf( stderr, "mysql_real_connect failed\n" );
 
		return 1;
 
	}
 
 
 
	execute( mysql, 0 ); // this will succeed
 
	execute( mysql, 1 ); // this will fail
 
 
 
	return 0;
 
}

Outputs

dcarver@dcarver.:/private/tmp$ gcc -I /Users/dcarver/Downloads/mariadb-connector-c-3.2.6-src/include/ -o test test.c -lz /Users/dcarver/Downloads/mariadb-connector-c-3.2.6-src/libmariadb/libmariadb.a
 
dcarver@dcarver:/private/tmp$ ./test
 
Test 0 Started
 
Test 0 Finished
 
 
 
Test 1 Started
 
mysql_stmt_store_result failed: Commands out of sync; you can't run this command now

Comment by Georg Richter [ 2022-04-07 ]

It's an expected behaviour, also reproducable with mysqlnd. By default result sets from prepared statements are unbuffered, which means you need either to fetch entire result or to store them before executing another command.

Comment by David Carver [ 2022-04-07 ]

Can you clarify on this please? I store the results from my stmt1, and then prepare and execute my stmt2. I do not store stmt2 results before fetching stmt1 results, but since I stored stmt1 results how is fetching the results executing another command? I'm not making anymore calls to the server by fetching my stored stmt1 results, correct?

Comment by David Carver [ 2022-04-07 ]

From looking at the source, fetching stored results changes the value of stmt->mysql->status? Why would fetching stored results change the status of the MySQL connection? What is the point of storing results if I can't execute other commands while the previous results are stored? It also seems wrong that I can in fact execute all the other commands I want, and even fetch stored results, but it's once the stored results reach EOF that the commands get out of sync.

Comment by Georg Richter [ 2022-04-08 ]

Please note that this is bug tracker, not a support forum.

As I mentioned in my previous comment, the behavior is expected and reproducible with mysqlnd, so it's defenitly not a bug in MariaDB Connector/C.

Comment by David Carver [ 2022-04-08 ]

To be clear I'm not asking for support, simply clarification. The MySQL 5.7 and 8.0 connectors do not have this bug. The bug is "fetching stored results changes the existing MySQL state."

After the stmt1 results are stored, stmt2 is prepared and executed. Iterating stmt1 results changes the state from MYSQL_STATUS_STMT_RESULT to MYSQL_STATUS_READY which is the bug, as iterating stored results should not change this. If you don't want to fix this that's one thing, but considering the API worked / works fine in MySQL 5.7 and 8.0, I would consider this a bug.

Generated at Thu Feb 08 03:06:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.