Hi,

I've been playing around with an MVC application connecting to a MySQL database.

I have a simple entity called 'Person' as below

[HierarchyRoot]
[KeyGenerator(KeyGeneratorKind.Default, Name = "PersonGenerator")]
public class Person: Entity
{
    [Field, Key]
    public long Id { get; private set; }

    [Field(Length = 100)]
    public string GivenName { get; set; }

    [Field(Length = 100)]
    public string  FamilyName { get; set; }

    [Field]
    public DateTime? DateOfBirth { get; set; }
}

I can create as many Person records as I want in my application without any problem, however, if I restart the database I can no longer create records in that table (nor in any other table that has already records).

The exception I get is Duplicate entry '1' for key 'PRIMARY'

I know that now my application is trying to create a record with a key that already exists in the table, obviously the database throws a Primary Key violation exception.

I know that Dataobjects.Net has a way of keeping track of the keys that have been fetched from the database, so it can always assign a unique primary key to a new record, but I don't think that it is working for me in this case.

Looks like that Dataobjects fetches keys starting from 1 again when I restart the database.

If Dataobjects fetches say 128 keys (1 to 128) for the first time, and I create two entities, which will be assigned keys 1 and 2, then I restart the database, the next entity should be assigned the key 3, but in my case it tries to assign 1 again.

Am I doing anything wrong?

regards,

Jamal

asked Dec 21 '14 at 01:58

Jamal%20Lucas's gravatar image

Jamal Lucas
5556


3 Answers:

I've connected to SQL Server and it works as expected, so the problem is only when connecting to MySQL.

answered Dec 23 '14 at 06:48

Jamal%20Lucas's gravatar image

Jamal Lucas
5556

Hello Jamal Lucas

It's a bug. Thank you for the report. The problem is that MySql stores auto increment in memory and if you restart server then MySql restores auto increment according data which table already has, but DO do not store any rows in generators tables. So MySql starts auto_increment with 1 after restart.

answered Dec 26 '14 at 00:19

Alexey%20Kulakov's gravatar image

Alexey Kulakov
77225

We've changed key generation algorithm for MySql. You can try it. Fix is available since 5.0.4 RC

(Mar 27 '15 at 07:05) Alexey Kulakov Alexey%20Kulakov's gravatar image

I am interested in that too. So anything has changed since then?

answered Mar 21 '15 at 13:27

murlex's gravatar image

murlex
25334

Yes. We've changed key generation algorithm for MySql. Fix is available since 5.0.4 RC

(Mar 27 '15 at 05:52) Alexey Kulakov Alexey%20Kulakov's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA