MySQL Bugs: #80693: Commands ignored and no error produced when packet over max_allowed

Bug #80693	Commands ignored and no error produced when packet over max_allowed_packet
Submitted:	10 Mar 2016 16:11	Modified:	29 Nov 2021 16:58
Reporter:	Adam Latchem	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.9.6, 6.9.8	OS:	Windows (Any)
Assigned to:		CPU Architecture:	Any
Tags:	Critical, error, ignored, max_allowed_packet

Description:
The .NET connector, when given a batch of DataRow objects to insert, will silently ignore commands that result in packets over max_allowed_packet size. The connector does not report this fact and further claims all rows have been affected by the call to Update(DataRow[]).

How to repeat:
Send a 'large' number of data rows (or set the max_allowed_packet value very low e.g. 200bytes if your INSERT statements render to around say 150 chars 2 inserts will then show the problem with the second one not being applied to the database) and do something similar to :

MySqlConnection dbConnection = ... ;
MySqlDataAdapter dbAdapter = new MySqlDataAdapter(
    "SELECT col1, col2 FROM Table", dbConnection);
dbAdapter.InsertCommand = new MySqlCommand(
    " INSERT INTO Table(col1,col2) VALUES ( ?col1, ?col2 )"
    dbConnection);

DataRow[] dr = ... /* your new set of new datarows e.g. from a dataset */ ;
int nrows = dbAdapter.Update(dr);
if (nrows != dr.Length)
    throw new Exception("Not all rows worked");

You will find nrows is always equal to dr.Length even when the packet size required to hold all statements is exceeded and the connector will silently have ignored the rows that make the packet too long.

Suggested fix:
Instead of continuing to loop round in MySql.Data.MySqlClient.Statement.BindParameters() once the packet size is breached throw an exception at the very least so the client knows the update is not fully sent to the server. Some care is needed to respect transactions and ideally a partial batch would not be sent at all. Ideally the return value would correctly indicate the number of rows applied so if for some reason you find it impossible to throw an exception the client code can check to see that there was a problem.

Workarounds
==========

The dirty workaround is to increase the max_allowed_packet on the server to an 'impossible value'. However that just puts the problem off for another day when the impossible becomes possible!

A better workaround is to dynamically generate an UpdateBatchSize that takes into account the value of max_allowed_packet size and a generous estimation of the SQL each row will generate e.g. using similar logic to that in the MySql connector see : MySqlCommand.EstimatedSize()

Hello Adam Latchem,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.8.

Thanks,
Chiranjeevi.

Screenshot.

Attachment: 80693.PNG (image/png, text), 56.34 KiB.

Chiranjeevi - looking at the screenshot I don't think you have the same issue, please ensure there is a SPACE at the start of the insert statement this is to workaround Bug #71626. Then set a small number for max_allowed_packet on the server. Then you will not hit the exception (but it would be better if we did) as the number of rows will erroneously match the number you send and in addition you will see the rows never made it to the server.

Kind Regards

Adam

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.28 release, and here's the proposed changelog entry from the documentation team:

Batch inserts that exceeded the max_allowed_packet value were not
processed and skipping the insert attempt did not emit an error.

Thank you for the bug report.