C# Question

"Duplicate entry for key primary" on one machine but not another, with same data?

My issue: inserting a set of data works on my local machine/MySQL database, but on production it causes a

Duplicate entry for key 'PRIMARY'
error. As far as I can tell both setups are equivalent.

My first thought was that it's a collation issue, but I've checked that the tables in both databases are using
utf8_bin
.

The table starts out empty and I am doing
.Distinct()
in the code, so there shouldn't be any duplicate entries.

The table in question:

CREATE TABLE `mytable` (
`name` varchar(100) CHARACTER SET utf8 NOT NULL,
`appid` int(11) NOT NULL,
-- A few other irrelevant fields
PRIMARY KEY (`name`,`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Database.cs
:

[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class Database : DbContext
{
public DbSet<MyTable> MyTable { get; set; }
public static Database Get()
{
/* Not important */
}
//etc.
}


MyTable.cs
:

[Table("mytable")]
public class MyTable : IEquatable<MyTable>, IComparable, IComparable<MyTable>
{
[Column("name", Order = 0), Key, Required, DatabaseGenerated(DatabaseGeneratedOption.None)]
public string Name
{
get { return _name; }
set { _name = value.Trim().ToLower(); }
}

private string _name;

[Column("appid", Order = 1), Key, Required, DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ApplicationId { get; set; }

//Equals(), GetHashCode(), CompareTo(), ==() etc. all auto-generated by Resharper to use both Name and ApplicationId.
//Have unit-tests to verify they work correctly.
}


Then using it:

using(Database db = Database.Get())
using(DbContextTransaction transaction = db.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
{
IEnumerable<MyTable> newEntries = GetNewEntries();
//Verify no existing entries already in the table; not necessary to show since table is empty anyways
db.MyTable.AddRange(newEntries.Distinct());
}


I'm at a loss how there could be duplicate entries in the database after doing a
.Distinct()
in the code, when using
utf8_bin
, especially since it works on one machine but not another. Does anyone have any ideas?

Answer

I ended up solving it by unicode-escaping non-ascii characters, similar to this solution.

However, I still have no idea why this could have possibly happened...

Comments