Attempting to insert data with MySqlBulkCopy always ends up with X rows copied, 0 inserted-CodePudding

I have a MySQL database with a table named "mytable" where I want to insert objects of type MyObject I am using C# with .NET Framework 4.7.2 (currently I can not upgrade this specific project to newer versions as it is not my decision)

public class MyObject
{
    public int Number {get; set;}
    public int Number2 {get; set; }
    public string Name {get; set;}
    public DateTime Date {get; set;}
    
    public MyObject() {}
}

The table looks like this:

'number' - INT(11)
'number2' - INT(11)
'name' - CHAR(16)
'date' - DATETIME

where 'number' and 'number2' make up the primary key

I am attempting to insert a list of MyObject instances into the table using MySqlBulkCopy from MySqlConnector.NET

I do this:

MySqlBulkCopy bulkCopy = new MySqlBulkCopy(myConnection); //I can not share the connection details like the password, but it is a valid one that I successfully write to with other commands
bulkCopy.DestinationTableName = "mytable";

List<MyObject> objects = GetHugeNumberOfObjects();

DataTable dataTable = new DataTable("mytable");
dataTable.Clear();

List<string> primaryKey = new List<string>();
primaryKey.Add("number");
primaryKey.Add("number2");

dataTable.PrimaryKey = primaryKey.ToArray();
dataTable.MinimumCapacity = objects.Count;

dataTable.Columns.Add("number", typeof(int));
dataTable.Columns.Add("number2", typeof(int));
dataTable.Columns.Add("name", typeof(string));
dataTable.Columns.Add("date", typeof(DateTime));

foreach(MyObject current in objects)
{
    DataRow newRow = dataTable.NewRow();
    newRow["number"] = current.Number;
    newRow["number2"] = current.Number2;
    newRow["name"] = current.Name;
    newRow["date"] = current.Date;
    
    dataTable.Rows.Add(newRow);
}

dataTable.AcceptChanges();

bulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(0, "number"));
bulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(1, "number2"));
bulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(2, "name"));
bulkCopy.ColumnMappings.Add(new MySqlBulkCopyColumnMapping(3, "date"));

bulkCopy.WriteToServer(dataTable);

What happens is, when calling WriteToServer, I always get an exception with a message:

X rows copied but 0 inserted

where X is the correct count matching objects.Count and there is no additional information or inner exception. I have tried incorrectly setting rows and column mappings and have gotten different exceptions related to them, so this ensures me that at least they are properly set. One thing that seemed to make no difference was not setting the primary key in dataTable, it made no difference if I set it or not, I still got the "X copied but 0 inserted" error.

The connection string for myConnection includes "AllowLoadLocalInfile=true", so that isn't the issue either, plus I am using this connection for regular MySQL commands and they all work fine.

I have tried inserting only a single row and looking at the debugger to see that there are no null values and everything is as it should be, I don't think it's from invalid data, but it fails the same way even with a single row - "1 rows copied 0 inserted"

I tried calling MySqlBulkCopy.WriteToServerAsync, but it makes no difference, in fact I think the other method just calls the async version internally and waits on it.

I have tried looking for examples on how to use it over the Internet and also tried looking trough the source code of MySqlConnector.NET, but to no avail, I couldn't figure out what the issue is, and there aren't many examples to begin with.

CodePudding user response：

The lack of exception detail is a known issue in MySqlConnector. Please try this workaround of using the MySqlConnection.InfoMessage event to get more details:

var myConnection = ...get connection...
myConnection.InfoMessage  = (s, e) =>
{
    // use logging infrastructure of your choice
    foreach (var error in e.Errors)
        Console.WriteLine(error.Message);
};

// ...

myConnection.Open();
MySqlBulkCopy bulkCopy = new MySqlBulkCopy(myConnection);

// rest of your code...