Error while using BulkCopy (Failed to read the result set)

Question

I developed a vb.net application that fills tables with API requests from another server and transfers those tables to MySql database using MySqlBulkCopy.

Here is the helper class(initialized only once) that I use to bulk copy multiple tables. Executing writeToServer() fires the following error: "Failed to read the result set." without any further information.

    Private connStr As String
    Private conn As MySqlConnection
    Private blkInsert As MySqlBulkCopy
    Public ErrorMessage As String
    Public ErrorTableName As String

    Public Sub New()
        connStr = My.MySettings.Default.MyBaseConnectionString
        conn = New MySqlConnection(connStr)

        blkInsert = New MySqlBulkCopy(conn)
    End Sub

    Public Function WriteToMySql(destinationTableName As String, localTable As DataTable) As Boolean
        blkInsert.DestinationTableName = destinationTableName

        Try
            blkInsert.WriteToServer(localTable)
        Catch ex As Exception
            ErrorMessage = ex.Message
            ErrorTableName = destinationTableName
            Return False
        End Try

        Return True
    End Function
End Class

To the WriteToMySql I pass the name of the table to be copied to, and the table that contains data.

If Not (blkInsert.WriteToMySql("tb_vendor", Ds.Vendor) AndAlso
                blkInsert.WriteToMySql("tb_group", Ds.Group) AndAlso
                blkInsert.WriteToMySql("tb_subgroup", Ds.SubGroup) AndAlso
                blkInsert.WriteToMySql("tb_product", Ds.Product) AndAlso
                blkInsert.WriteToMySql("tb_product_attribute", Ds.Characteristic) AndAlso
                blkInsert.WriteToMySql("tb_product_attributes", productAttributes) AndAlso
                blkInsert.WriteToMySql("tb_encoded_names", productNamesEncoded) AndAlso
                blkInsert.WriteToMySql("tb_pictures", Ds.PictureNew) AndAlso
                blkInsert.WriteToMySql("tb_group_category", groupCategories)) Then
...

The error appears inconsistently, and when it does, re-executing(with the same tables) does not help, it triggers the same error, like it is something with the table itself. I am inserting 6 tables, and the one that contains the most rows fires that error(always the same table), while any other table is being copied successfully.

The only way to fix the error is to load the data from API's(reinitialize and fill the tables all over again) and try inserting. Most of the time it works without causing that error, it fires super random.

Any help would be appreciated, I can provide more info if needed. Thanks in advance.

Edit(answer to questions): The data table that is creating the problem consists of no more than 200k rows, but I have already tried inserting much larger tables(2-3m rows) to check if it has to do something with the size, no errors were present.

When the error occurs, I see that BulkCopy inserted a portion of the rows, and then threw the error. Basically there are inserted rows on the DB, but not all of them.

Ds is the name of my variable(dataset) which I fill from API's. The other standalone tables are generated by me afterwards for processing data and generating needed values.

I have not set any timeout, but while debugging I see that it is equal to 0, which means it should wait no matter how long it takes.

I am trying to trigger the error while debugging, it appeared on the release version lately. When I do so, I will provide more info.

Edit2(Stack trace and Inner Exception):

Failed to read the result set.

StackTrace:

   at MySqlConnector.MySqlDataReader.ActivateResultSet()
   at MySqlConnector.MySqlDataReader.<CreateAsync>d__97.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__69.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlBulkLoader.<LoadAsync>d__73.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.MySqlBulkCopy.<WriteToServerAsync>d__29.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at MySqlConnector.MySqlBulkCopy.WriteToServer(DataTable dataTable)
   at PolyBridge.BulkInsert.WriteToMySql(String destinationTableName, DataTable localTable)

InnerException:

Error during LOAD DATA LOCAL INFILE

Welcome to Stack Overflow. How big is the big table? Does the operation time out? That is, does it run for a certain amount of time and then fail? How do you create those `Ds.Tablename` objects? Can you glean anything about the cause of the failure from `ex.StackTrace` or `ex.InnerException`? If you [edit] your question to put those values here we may be able to help you further. — O. Jones, Dec 17 '20 at 14:34
Also, it would be helpful if you could add the stacktrace output to you question. — RBarryYoung, Dec 17 '20 at 15:04
I have edited the post to answer your questions. I am actively trying to trigger that error now. 5 time, no errors for now, I hope it fires soon again. Then, I will provide more info. — thearctoss, Dec 17 '20 at 15:09
@O.Jones The error fired again, i have provided ex.StackTrace and ex.InnerException. — thearctoss, Dec 21 '20 at 09:32
@RBarryYoung I have provided Stack trace and Inner exception. — thearctoss, Dec 21 '20 at 09:33
@O.Jones Well, it doesn't, at least not me. What could cause that fail? — thearctoss, Dec 21 '20 at 14:43
OK, great! So, ... I hate to do this to you, *BUT*, is there an Inner, Inner Exception? The exceptions can keep being nested like this, I didn't mention it before because it's rare for it to go more than one level deep, but in this case it appears that it may keep going for at least one more level. If you can, keep following the exception nesting and report them all here. Usually, the innermost exception is the root cause of the problem. (I have a guess though, which I will post). — RBarryYoung, Dec 21 '20 at 14:50

score 2 · Answer 1 · answered Dec 17 '20 at 14:46

2

Try setting the bulk copy timeout to a large number, like this 3600 seconds for one hour.

    blkInsert = New MySqlBulkCopy(conn)
    blkInsert.BulkCopyTimeout = 3600

You can also disable the timeout altogether with this

    blkInsert.BulkCopyTimeout = 0

answered Dec 17 '20 at 14:46

O. Jones

81,279
15
96
133

I have added a watch to check that value while debugging, and it appears to be set to 0, even though I have not done so. I believe it is something by default. – thearctoss Dec 17 '20 at 15:15
1

I don't know specifically about Connector/NET, but I know the SQL Server version of this API has a default setting, and needs to be overridden. I use one hour with that API. (3600s). I suggest this because the intermittent situation you're facing smells like a timeout to me. It might be something else. – O. Jones Dec 17 '20 at 19:07

score 1 · Answer 2 · answered Dec 21 '20 at 14:54

1

I know SqlServer and Oracle pretty well, but MySql is not my particular area of expertise. However, in checking Google for similar cases, I did find something that might explain it: exceeding the max_allowed_packet size.

This would only happen on the largest files and the apparent solution is to raise this setting to its maximum value:

set global max_allowed_packet=268435456

answered Dec 21 '20 at 14:54

RBarryYoung

49,956
12
91
127

1

I already thought about that but I have not tried modifying it since I have succesfully inserted much larger tables, but trying doesn't harm. I have just set it and will inform you if it solved the problem. – thearctoss Dec 21 '20 at 15:10
1

@thearctoss Note that something else on your server may be setting/resetting this value also, which might explain why the problem occurs randomly, so you need to make sure that it is always set right before you run the SqlBulkCopy. – RBarryYoung Dec 21 '20 at 15:15

Error while using BulkCopy (Failed to read the result set)

2 Answers2