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

[10 Mar 2016 16:11] Adam Latchem
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()
[11 Mar 2016 8:27] Chiranjeevi Battula
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.
[11 Mar 2016 8:28] Chiranjeevi Battula
Screenshot.

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

[11 Mar 2016 15:14] Adam Latchem
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