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

Question

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 1

I would investigate the following points:

• check the exact MySQL version on both machines. You can do this in a MySQL client with SHOW VARIABLES LIKE "%version%";
• use SELECT HEX(name) to see how the data is encoded, for the duplicate row.
• investigate if the "utf8" data is stored in utf8mb3 or utf8mb4

https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb3.html

https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html

Assuming that on the dev machine, data is inserted from a fresh install, and that on the production machine, data could have been inserted with an older version and then the server upgraded, I would check in particular if actions are needed (and were done) to complete correctly the upgrade process.

In particular, see these sections (for the proper version) in the MySQL reference manual:

https://dev.mysql.com/doc/refman/5.7/en/checking-table-incompatibilities.html

If necessary, rebuild the indexes.

https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html

Edit (2016-10-12)

All the above focuses on the table, and on storage.

Another part to check is the connection between client and server, with variables such as character_set_connection.

Please check all character set related system variables, to compare them.

mysql> show variables like "%character%";
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /home/malff/GIT_TRUNK/sql/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.02 sec)