cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joe Baldwin <jfbald...@earthlink.net>
Subject Re: Cayenne, MySQL and PK generation strategies
Date Tue, 27 Jul 2010 07:25:31 GMT
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


Mime
View raw message