MySqlBulkCopy failed when data is too big (over 80,000 rows)

[szmcdull]

MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to read the result set.
 ---> System.Net.Sockets.SocketException (10054): An existing connection was forcibly closed by the remote host.
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
   at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.GetResult(Int16 token)
   at MySqlConnector.Protocol.Serialization.SocketByteHandler.DoWriteBytesAsync(ReadOnlyMemory`1 data) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\SocketByteHandler.cs:line 133
   at MySqlConnector.Protocol.Serialization.ProtocolUtility.<WritePacketAsync>g__WritePacketAsyncAwaited|8_0(ValueTask`1 task_, Byte[] buffer_) in C:\projects\mysqlconnector\src\MySqlConnector\Protocol\Serialization\ProtocolUtility.cs:line 548
   at MySqlConnector.Core.ServerSession.SendReplyAsyncAwaited(ValueTask`1 task) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ServerSession.cs:line 802
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 118
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 130
   at MySql.Data.MySqlClient.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 391
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\CommandExecutor.cs:line 62
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlCommand.cs:line 220
   at MySql.Data.MySqlClient.MySqlBulkLoader.LoadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlBulkLoader.cs:line 116
   at MySql.Data.MySqlClient.MySqlBulkCopy.WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlBulkCopy.cs:line 167
   at MySql.Data.MySqlClient.MySqlBulkCopy.WriteToServerAsync(IDataReader dataReader, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlBulkCopy.cs:line 99
   at KlineImport.Program.Test() in C:\code\quant\KlineImport\Program.cs:line 252
   at KlineImport.Program.Main2(String[] args) in C:\code\quant\KlineImport\Program.cs:line 273

my code:

            using (var conn = await Common.GetDBAsync())
            {
                var kline = new KlineData("usdt_eth", 1, true);

                using (var stream = File.OpenRead(kline.path))
                using (var tx = new StreamReader(stream))
                using (var reader = new CsvHelper.CsvReader(tx, new CsvConfiguration(CultureInfo.InvariantCulture)))
                using (var rd = new CsvDataReader(reader))
                {
                    //var headers = reader.Context.HeaderRecord;

                    var bcp = new MySqlBulkCopy((MySqlConnection)conn)
                    {
                        DestinationTableName = kline.tableName
                    };
                    //Assume the file headers and table fields have the same names
                    //foreach (var header in headers)
                    //{
                    //    bcp.ColumnMappings.Add(header, header);
                    //}

                    //var i = 0;
                    //while (rd.Read())
                    //{
                    //    i++;
                    //}
                    //Log.Information($"{i} line read");

                    await bcp.WriteToServerAsync(rd);
                }
            }

If I uncomment the rd.Read() loop, the log will be read 80473 line read. So the reader is all right. Also I tried removing part of the data from the file and try to find if some ill-formed data cause the error. It turns out the error occurs whenever the file is too big (I guess about 32765), no matter which part of the file is removed.

[bgrainger]

It looks like you might be starting with a CSV file; are you able to load it successfully with MySqlBulkLoader?

[szmcdull]

@bgrainger I can successfully import the CSV using mysqlimport

[bgrainger]

I was curious if using a different C#-based approach MySqlBulkLoader instead of MySqlBulkCopy works. (MySqlBulkLoader is the "traditional" way that's been implemented for a long time; MySqlBulkCopy is the experimental new API that can stream an IDataReader to the MySQL Server.)

[szmcdull]

Oh. I didn't know there is a MySqlBulkLoader. Because it is not on your doc site. I found it on dev.mysql.com, applied it to MySqlConnector, and it works.

                    var bcp = new MySqlBulkLoader((MySqlConnection)conn)
                    {
                        TableName = kline.Value.tableName,
                        FieldTerminator = ",",
                        NumberOfLinesToSkip = 1,
                        FileName = kline.Value.path,
                        ConflictOption = MySqlBulkLoaderConflictOption.Ignore,
                    };
                    await bcp.LoadAsync();

[bgrainger]

Thanks for confirming that MySqlBulkLoader works but MySqlBulkCopy doesn't. They mostly use the same underlying code, so it should be possible to investigate and determine why only one succeeds.

I've opened #781 to improve the docs.

[bgrainger]

I can't reproduce yet (with a 82MB file containing 1.2m rows).

[chester89]

I can reproduce that with 30k rows (yes, my table has many columns). I'm using mysql 5.7.29 with 0.66.0 driver

[bgrainger]

I still haven't been able to reproduce the problem. Is it possible that anyone who can reproduce this could get a Wireshark packet capture (ideally of a large batch that's still small enough to work, and then a batch that's large enough to fail)? CC @MaceWindu

[MaceWindu]

Finally found time to check it. I've updated to recent 8.0.20.0 version and issue still persists, so it is not version specific issue. Will see if I can grab netflow logs.

Update: I think it is mysql issue, not provider issue.

Update 2: What I found interesting is that probably it also depends on target table size, as at some batch size, it fails not on first batch, but on second (of same size)

[bgrainger]

Update: I think it is mysql issue, not provider issue.

It does seem like MySQL is closing the connection, but there are reports that MySqlBulkLoader works in situations where MySqlBulkCopy doesn't, so it still seems possible there's something that could be changed on the client side to improve robustness.

[MaceWindu]

Wireshark logs for 0.69.2
Test table https://github.com/linq2db/linq2db/blob/ef947d30b52f7f0012b86ec009c24602efa05ac5/Data/Create%20Scripts/MySql.sql#L329

inserting 20060 records works always
20061 records always fails
Mysql version: : 8.0.20.0 (win)

wireshark.zip