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

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.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 16 (13 by maintainers)

Most upvoted comments

Oh, the error is very obvious now:

Got a packet bigger than 'max_allowed_packet' bytes

The failing packet capture is sending a packet that’s 0x400062 (4,194,402) bytes, so you must be using the MySQL 5.7 default of 4 MB for max_allowed_packet. Going from 20,060 to 20,061 packets must have just crossed the 4MB boundary.

(That also explains why MySqlBulkLoader didn’t have this problem, because it breaks uploaded CSV files up into 1 MB chunks.)

To fix this, the packet size that MySqlBulkCopy sends (which also happens to be the limit of the largest row that it can send) could be reduced to 1 MB or 2MB; or, it could query for @@max_allowed_packet before starting the copy and reduce the maximum size dynamically.