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)
Oh, the error is very obvious now:
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 formax_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.