1

I am using Cayenne to add records to a MySQL database, and I am seeing some strange behavior.

When I run my application, I create a DataContext, perform a series of adds, then close the application. This works out well, because I am using an integer for a primary key, and when I add a record to the database, the key automatically increments. For some reason, it starts at 200 for the first record, then goes to 201 for the second record, etc.

If, however, I stop the application, then run it again, the primary key starts at 200 again! This, of course, causes an exception to be thrown because a new record ends up having a duplicate primary key. It is looking like when I create a new object using the DataContext's newObject() after starting my application, Cayenne does not "remember" how far the primary key was incremented when the application was previously run.

Does anyone know what is causing this reset of the primary key values, and (more importantly) how to stop it from happening??? Or have I found a bug in the current version of Cayenne? I am using Version 3.0.2.

Someone please advise...

Factor Three
  • 2,094
  • 5
  • 35
  • 51

1 Answers1

2

The last used PK for a given table is stored in a special table called AUTO_PK_SUPPORT. Please check the contents of this table between the restarts of your app. Also check you application Cayenne logs for reads and writes to AUTO_PK_SUPPORT. This should give you an idea of what's happening.

Aside from that you might switch to auto-increment PK (see "Primary Key Provided by Database" section here). MySQL supports auto-incremented PK columns and if you have an option of altering the schema, this IMO is the cleanest PK generation strategy out of all available. (And it doesn't require AUTO_PK_SUPPORT).

andrus_a
  • 2,528
  • 1
  • 16
  • 10
  • Yes, I had already checked the AUTO_PK_SUPPORT table and I read the SQL generated by the modeler. Apparently, when you create the schema, for some reason Cayenne Modeler sets the "next" primary key to 200 (why I do not know!). It seems to do this even when I set the key generation to "from database" (I figured that out from reading other entries here). I have been unable to find any way to stop the Modeler from doing this. I may have to screw with the Key support table by hand -- something I really don't want to do... – Factor Three May 10 '13 at 10:01
  • I was pretty stupid, actually. It helps that when you are using the Modeler to create the database schema to make sure that the "Create PK Support" checkbox is unchecked. Problem solved. – Factor Three May 10 '13 at 12:18
  • Glad you figured it out. BTW "200" starting value is somewhat of a legacy from the early Cayenne days when we thought it was a good idea to keep a guaranteed small block of IDs "reserved" for use outside of Cayenne. This is totally arbitrary (it could be 10 or 1000, etc.) It shouldn't affect anything. I guess we might rest it to 1 to reduce confusion. – andrus_a May 10 '13 at 17:43