I've been playing around with an MVC application connecting to a MySQL database.
I have a simple entity called 'Person' as below
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?
asked Dec 21 '14 at 01:58
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
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
I am interested in that too. So anything has changed since then?
answered Mar 21 '15 at 13:27