SqlBulkCopy, MySqlDataReader, BLOB, and IndexOutOfRangeException

Question

So, I am hitting infamous IndexOutOfRangeException when using MySqlDataReader with BLOB columns. My situation is quite specific though. The story is following. I am pulling data from MySQL into SQL Server via first getting MySqlDataReader from executing a command, and then passing it to SqlBulkCopy for "streaming" bulk insert. The code is plain and simple:

    public static void BulkCopyMySqlDataReader(string destinationConnectionString, int batchSize, string destinationTableName, IEnumerable<string> sourceColumns, MySqlDataReader mySqlDataReader)
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.Default))
        {
            bulkCopy.BulkCopyTimeout = 0;
            bulkCopy.BatchSize = batchSize;
            bulkCopy.DestinationTableName = destinationTableName;
            bulkCopy.EnableStreaming = true;

            foreach (var dataColumn in sourceColumns)
                _ = bulkCopy.ColumnMappings.Add(dataColumn, dataColumn);

            try
            {
                bulkCopy.WriteToServer(mySqlDataReader);
            }
            catch (Exception)
            {
                throw;
            }
        }
    }

All works fine, however, on MySQL side i have 2 tables with BLOB columns and here is where the problem starts. When loading these tables, it throws:

System.IndexOutOfRangeException: Data index must be a valid index in the field
System.IndexOutOfRangeException: 
   at MySql.Data.MySqlClient.Interceptors.ExceptionInterceptor.Throw(Exception exception)
   at MySql.Data.MySqlClient.MySqlDataReader.Throw(Exception ex)
   at MySql.Data.MySqlClient.MySqlDataReader.GetBytes(Int32 i, Int64 fieldOffset, Byte[] buffer, Int32 bufferoffset, Int32 length)

So, as i understand, SqlBulkCopy decides itself that it must call GetBytes() method to stream data for BLOB columns, and this is where i get this exception. For specific reasons I am bound to MySql.Data library of version 8.0.15. Thus, even if it was fixed in the newer versions, I have to deal with it somehow myself. My idea was to override GetBytes() somehow to overcome the issue.

Since MySqlDataReader is sealed, it is not possible to inherit from it. So, I was thinking to use Decorator pattern and do like:

public sealed class MySqlDataReaderFixed : DbDataReader, IDataReader, IDisposable, IDataRecord
{
    public MySqlDataReader mySqlDataReader { get; set; }
//override required methods like...
    public override bool GetBoolean(int ordinal)
    {
        return mySqlDataReader.GetBoolean(ordinal);
    }
}

Now then, how do i do GetBytes() correctly? I.e. what shall be here in my MySqlDataReaderFixed class:

    public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)
    {
        var bufferLength = mySqlDataReader.GetBytes(ordinal, 0, null, 0, 0);
        // what's next?
    }

Could anyone help here? Or, maybe there is another way to overcome the issue, considering still using MySqlDataReader (from MySql.Data v 8.0.15.0) and SqlBulkCopy?

score 0 · Accepted Answer · answered Jan 16 '21 at 23:47

It turns out i had to look one "step" before. SqlBulkCopy was actually calling GetStream() method, which, in turn, was calling the trouble-maker GetBytes(). So, i overrode GetStream() in my decorator class as following (taken from here):

public override Stream GetStream(int ordinal)
{
    return new MemoryStream((byte[])mySqlDataReader[ordinal]);
}

and, the problem is gone! Even performance is fine, however, the tables which I've tested it were relatively small. I will have an opportunity to test it later on larger volumes, let's see.

SqlBulkCopy, MySqlDataReader, BLOB, and IndexOutOfRangeException

1 Answers1