Bug #14790 Transaction problem after trying to store BLOB data > max_allowed_packet
Submitted: 9 Nov 2005 15:39 Modified: 20 Sep 2006 22:37
Reporter: Jelle Elsen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:1.0.6 OS:Microsoft Windows (Windows XP Pro)
Assigned to: Reggie Burnett CPU Architecture:Any

[9 Nov 2005 15:39] Jelle Elsen
Description:
Hi all,

We found a problem in the MySQL .Net Connector 1.0.6. This is also applicable for version 1.0.4 of the connector.

We have written an application which is trying to store a Blob field into the MySQL Catalog. When this Blob field is bigger than the max_allowed_packet property, which is set to 1M by default on the MySQL Server, MySQL Server will close the connection and we are not able to Roll Back the Transaction. After handling the exception which occured, we are not able to store any data in the Catalog at all. 

When trying to execute the second query, we will get the following exception:
"Stream does not support writing."

StackTrace:
"   at System.IO.__Error.WriteNotSupported()\r\n   at System.IO.BufferedStream.WriteByte(Byte value)\r\n   at MySql.Data.MySqlClient.PacketWriter.FlushBuffer()\r\n   at MySql.Data.MySqlClient.PacketWriter.Flush()\r\n   at MySql.Data.MySqlClient.NativeDriver.Authenticate411()\r\n   at MySql.Data.MySqlClient.NativeDriver.Authenticate()\r\n   at MySql.Data.MySqlClient.NativeDriver.Reset()\r\n   at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection()\r\n   at MySql.Data.MySqlClient.MySqlPool.GetConnection()\r\n   at MySql.Data.MySqlClient.MySqlPoolManager.GetConnection(MySqlConnectionString settings)\r\n   at MySql.Data.MySqlClient.MySqlConnection.Open()\r\n   at MySQLTestNameSpace.QueryTester.AddBlobToMySQL(String _strConnectionString, String _strDatabaseName, Byte[] _arrBytes) in c:\\temp\\consoleapplication7\\mysqltest.cs:line 24"

My questions are: 
- Is there a workaround for this problem?
- Do we need to close this transaction at all when the connection is closed?
- Is there a possibility to request the max_allowed_packet property? If this is possible, we can check if the Data which is being stored is bigger than the allowed size.
- Is there a problem in my code sample?

Thanks in advance,
Jelle van den Elsen

How to repeat:
using System;
using MySql.Data.MySqlClient;

namespace MySQLTestNameSpace
{
	class QueryTester
	{
        private static void AddBlobToMySQL( string _strConnectionString, string _strDatabaseName, byte[] _arrBytes )
        {
            MySqlConnection     mysqlConnection;
            MySqlCommand        mysqlCommand;
            MySqlTransaction    mysqlTransaction;
            
            mysqlConnection = null;
            mysqlCommand = null;
            mysqlTransaction = null;

            try
            {
                // Create the MySQL Connection:
                mysqlConnection = new MySqlConnection( _strConnectionString );
                
                // Open the connection:
                mysqlConnection.Open( );
                mysqlCommand = mysqlConnection.CreateCommand( );
            
                // Begin the Transaction:
                mysqlTransaction = mysqlConnection.BeginTransaction( );

                // Set the connection
                mysqlCommand.Connection = mysqlConnection;
                mysqlCommand.Transaction = mysqlTransaction;
                
                // Add the query:
                mysqlCommand.Parameters.Add( "?", MySqlDbType.LongBlob, _arrBytes.Length ).Value = _arrBytes;
                mysqlCommand.CommandText = string.Format( "INSERT {0} (Data, Size) VALUES( ?, {1})", _strDatabaseName, _arrBytes.Length );
                
                // execute the command:
                mysqlCommand.ExecuteNonQuery( );

                // commit the transaction:
                mysqlTransaction.Commit( );
            }
            catch( MySqlException exception )
            {
                try
                {
                    if ( mysqlTransaction != null )
                        mysqlTransaction.Rollback();
                }
                catch ( MySqlException ex )
                {
                    throw ex;
                }
                catch ( System.Exception ex )
                {
                    throw ex;
                }

                throw exception;
            }
            catch( System.Exception exception )
            {
                try
                {
                    if ( mysqlTransaction != null )
                        mysqlTransaction.Rollback();
                }
                catch ( MySqlException ex )
                {
                    throw ex;
                }
                catch ( System.Exception ex )
                {
                    throw ex;
                }
                
                throw exception;
            }
            finally 
            {
                if ( mysqlConnection != null && mysqlConnection.State == System.Data.ConnectionState.Open )
                    mysqlConnection.Close( );
            }
        }
        
        /// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
        static void Main( string[] args )
        {
            string  strTableName;
            string  strConnectionString;
            byte[]  arrData;

            // To run this sample you have to make sure you have created a Catalog named 'mysqltest' and the following Table:
            //CREATE TABLE `mysqltest`.`TestTable` (
            //  `ID` INTEGER UNSIGNED AUTO_INCREMENT,
            //  `Data` LONGBLOB NOT NULL DEFAULT '',
            //  `Size` INTEGER UNSIGNED NOT NULL DEFAULT 0,
            //  PRIMARY KEY(`ID`)
            //)
            //ENGINE = InnoDB;
            

            // Initialsation:
            strTableName = "TestTable";
            strConnectionString = string.Format( "server=localhost;database=mysqltest;uid=root;pwd=******;" );
            
            try
            {
                // Fails due to MySQL Server max_allowed_packet size:
                arrData = new byte[ 1048576 ];
                AddBlobToMySQL( strConnectionString, strTableName, arrData );
            }
            catch ( System.Exception exception )
            {
                Console.WriteLine( exception.Message );
            }

            try
            {
                // This should succeed:
                arrData = new byte[ 20 ];
                AddBlobToMySQL( strConnectionString, strTableName, arrData );
            }
            catch ( System.Exception exception )
            {
                Console.WriteLine( exception.Message );
            }
            Console.ReadLine( );
        }
	}
}

Suggested fix:
n.a.
[15 Nov 2005 19:44] Vasily Kishkin
I was able to reproduce the bug. The test case returned:

Connection must be valid and open to commit transaction
Stream does not support writing.

Where:

mysql> show variables like "max_allowed_packet";
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

I attached the test case.
[15 Nov 2005 19:44] Vasily Kishkin
Test case

Attachment: 14792.zip (application/force-download, text), 6.59 KiB.

[20 Sep 2006 22:37] Reggie Burnett
This bug has been fixed in a previous release.  As of  1.0.7, an attempt to execute a command larger than max allowed packet will throw an exception and close the connection, thereby rolling back the transaction.

In 1.0.8 and 5.0.1, the connector will attempt to detect that the query is larger than max allow packet and, if it can, throw the exception before executing the query.  In this case, the connectino will remain open and undamaged.