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.
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.