Marek,
We are using Cayenne, MySQL with InnoDB storage engine, both configured for MySQL PK generation. We had some problems which I am convinced was associated with the documented table locking bugs in early versions of MySQL 5.0.x. It appears that there may have been multiple problems which do seem have been resolved in MySQL 5.1.x (You can read up on this by googling the MySQL bug reports.)
With respect to these issues, the most current stable version (currently 5.1.49) is recommended. (Warning: there appears to be some sort of conceptual 'holy-war' with 5.0.x vs the 5.1.x zealots; so I would just stick with the most recent stable version recommendation and the associated bug fixes as the argument)
I cannot speak to comparing the Cayenne PK generation strategy vs MySQL, but I believe both are very reliable. I think that the only reason that you might consider using MySQL vs Cayenne PK-generation is if you are ever going to perform non-Cayenne inserts. If you are always going to use an ORM, then you can use either method.
If you do any performance tests comparing the two, I would be interested in hearing the results.
Hope this helps,
Joe
On Jul 26, 2010, at 10:40 PM, Marek Stanisław Wawrzyczny wrote:
> Hi all,
>
> It has been a while since I last posted in this forum.
>
> First, a bit of a background. We have an existing MySQL schema that was driven by a number of WebObjects applications. These obviously rely on EOF to handle PK generation.
> We have decided to refactor the schema in a new database. The new web applications are Cayenne based.
>
> We now have a bit of discussion on how to handle PK generation in the new schema. My personal preference is auto incrementing PK columns. It certainly allows for a more generic approach to the handling of primary keys - it is not necessary for people to be aware of any additional infrastructure should records be inserted outside of any applications.
>
> Others have expressed concern with MySQL handling of the LAST_INSERT_ID() functionality where there used to be (still is?) a problem with MySQL's threading implementation in high concurrency/volume environments. (I presume this is precisely the mechanism used by Cayenne to populate IDs within the Entities?)
> Another cited advantage being suggested is that this approach allows for a vendor-independent PK generation strategy.
>
> I guess my vested interest here is to dismiss arguments against the use of the auto incrementing PK strategy :) but that said, I'm very interested to hear whether anyone has done any performance analysis or has had particular problems with either strategy.
>
>
> Cheers,
>
> Marek Wawrzyczny
> ish group
This archive was generated by hypermail 2.0.0 : Tue Jul 27 2010 - 07:26:10 UTC