Yeah, there is quite a few bugs involved.
First an explanation on Cayenne strategies... Long works with
auto_increment as long as the driver supports it, but doesn't not
work with any other strategy, as all of them are based on overriding
"int JdbcPkGenerator.pkFromDatabase(..)".
What we can do about it now... The simplest way seems to use INT PK
with "auto_pk_table" generation strategy and a custom PkGenerator
that doesn't perform locking.
Here is a mailing list thread on implementing an "optimistic" pk for
MySQL:
http://objectstyle.org/cayenne/lists/cayenne-user/2006/07/0067.html
Wouldn't mind including this in Cayenne at some point.
Andrus
On Sep 29, 2006, at 11:03 AM, Tobias SCHOESSLER wrote:
I am a bit confused now ... so java.lang.Long PKs are known to not
work at
all with cayenne 1.2 no matter which database is used?
I had the impression everything worked fine when using MySQL
directly with
BIGINT, Long and database side AUTO_INCREMENT.
My last bug I ran into was only when I connected through the
sequoia jdbc
driver / mysql jdbc driver chain when the test case broke again ...
Anyway I am flexible with the pk column types but because of this
MySql
jdbc driver bug mentioned before
http://lists.mysql.com/java/6383
- returning meta data of type java.lang.Long for any AUTO_INCREMENT
column
defined -
I thought I was forced to use some database type that consistently
maps as
java.lang.Long for the PK to not have to deal with changing pk
types on
creation and retrieval . That's why i tried INT UNSIGNED and BIGINT.
Is your advise still correct for this ?
Andrus Adamchik
<[EMAIL PROTECTED]
le.org> To
cayenne-
[EMAIL PROTECTED]
Friday,
29 cc
September 2006
15:57
Subject
Re: Problems with MySql id type
with AUTO_INCREMENT
Please respond to
[EMAIL PROTECTED]
bator.apache.org
Tobias,
If you have flexibility redefining your columns, I suggest mapping a
PK as INTEGER until we implement Long PK support for real (http://
issues.apache.org/cayenne/browse/CAY-329). As far as I can tell in
the your latest case you are hitting the int pk limitation of PK
generator. This issue is pretty high on our priority list by the way,
so Long pks should become an option soon.
Andrus
On Sep 29, 2006, at 9:51 AM, Tobias SCHOESSLER wrote:
Andrus, thanks a lot you for looking into this.
That is quite a pile of bugs I am running into here. First this
MySQL JDBC
driver bug returning java.lang.Long for any auto-increment column,
then
cayenne treating 'INT UNSIGNED' as an Integer. Now issues with
org.apache.cayenne.access.jdbc.ColumnDescriptor and SQLTemplate.
To get this running for me I understand that I only have to pick
the right
datatype for my PKs so all the bugs are bypassed. Looking at
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-
conversions.html
I thought BIGINT should be unambigiously treated as a
java.lang.Long for
all bugs mentioned.
Actually it worked, I tried setting the PKs to mysql type BIGINT
and the
test case mentioned below passes. So for the record this is a
solution for
using AUTO_INCREMENT PK generation with cayenne 1.2 and MySQL.
But...
If you recall my ultimate goal was to use a setup in which MySql is
used in
a Sequoia/CJDBC cluster - the reason I did not use AUTO_PK_SUPPORT
as LOCK
was not supported by Sequoia ...
Connecting through the cjdbc/sequoia driver to the database above,
again
breaks the test case. Sequoia returns java.lang.Integer for the
BIGINT
column ... : -[
I guess this is rather something to address in a sequoia forum or
is there
a chance you add a sequoia setup in your test cases?
thanks
Tobias
Andrus Adamchik
<[EMAIL PROTECTED]
le.org> To
cayenne-
[EMAIL PROTECTED]
Thursday,
28 cc
September 2006
17:44
Subject
Re: Problems with MySql id
type
with AUTO_INCREMENT
Please respond to
[EMAIL PROTECTED]
bator.apache.org
Just got some time to test it, and the problem seems to be in
org.apache.cayenne.access.jdbc.ColumnDescriptor constructor... It is
getting confused by MySQL ResultSetMetaData returning JDBC type for
such column as INTEGER.
I created a new Jira issue with description of this problem:
http://issues.apache.org/cayenne/browse/CAY-672
I'll commit a fix to 3.0, as 1.2 users can have some code relying on
the old behavior and we want to keep 1.2 stable.
Andrus
On Sep 26, 2006, at 7:11 AM, Tobias SCHOESSLER wrote:
Andrus,
I am referring to http://issues.apache.org/cayenne/browse/CAY-639
and our
discussion below.
I am using cayenne 1.2.1 now. I mapped all AUTO_INCREMENTED primary
keys of
sql type unsigned int as BIG_INT in cayenne.
Cayenne correctly returns DataObjects with a primary key of type
Long when
using SelectQuery, when creating new objects or using getToXXX
navigation
to retrieve the object .
But when I use SQLTemplate to retrieve the same DataObject cayenne
still
returns a pk of type Integer ...
These are my test cases:
public void testDocumentPkViaSelectQuery() {
List<TldDocument> docList =
dataContext.performQuery(new
SelectQuery(TldDocument.class, null));
assertEquals(Long.class,
docList.get(0).getObjectId().getIdSnapshot().get
(TldDocument.DOCUMENT_UID_PK_COLUMN).getClass());
}
public void testDocumentPkViaSQLTemplate() {
String sqlSelect = " select * ";
String sqlFrom = " from TLD_DOCUMENT doc ";
SQLTemplate query = new SQLTemplate
(TldDocument.class,
sqlSelect + " " + sqlFrom);
List<TldDocument> docList =
dataContext.performQuery(query);
assertEquals(Long.class,
docList.get(0).getObjectId().getIdSnapshot().get
(TldDocument.DOCUMENT_UID_PK_COLUMN).getClass());
}
testDocumentPkViaSelectQuery passes.
testDocumentPkViaSQLTemplate fails.
thanks, regards
Tobias.
Andrus Adamchik
<[EMAIL PROTECTED]
le.org> To
cayenne-
[EMAIL PROTECTED]
Tuesday,
29 cc
August 2006 15:35
Subject
Re: Problems with MySql id
type
Please respond to with AUTO_INCREMENT
[EMAIL PROTECTED]
bator.apache.org
I'd appreciate that.
Andrus
On Aug 29, 2006, at 5:31 PM, Tobias SCHOESSLER wrote:
ok, I got it. You want me to add a Jira entry for this? I never did
this
before ... : |
Andrus Adamchik
<[EMAIL PROTECTED]
le.org> To
cayenne-
[EMAIL PROTECTED]
Tuesday,
29 cc
August 2006 15:19
Subject
Re: Problems with MySql id
type
Please respond to with AUTO_INCREMENT
[EMAIL PROTECTED]
bator.apache.org
On Aug 29, 2006, at 4:16 PM, Tobias SCHOESSLER wrote:
But still you are right there is a bug in mysql too. If the column
type is
INT and not INT UNSIGNED it should return java.lang.Integer but it
returns
java.lang.Long instead. This was my first observation.
Still probably worth fixing the mapping to follow the docs.
but why does cayenne need BIGINT mapping set for the column to
retrieve it
as a java.lang.Long? I understand that this is unrelated to the
bug
in the
mysql jdbc driver mentioned above.
This is simply a workaround for the bug. BIGINT has a default JDBC
mapping to java.lang.Long. So this consistently produced a Long
during select and when reading auto-incremented value via JDBC.
Andrus
