You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Borut Bolčina <bo...@gmail.com> on 2008/11/04 14:48:13 UTC
Re: MySQL, DbGenerator and auto_increment
Hello,
I am doing something wrong as Cayenne is trying to use auto_pk_suport table
although I specified PK Generation Strategy to Database generated on User
and Role db entities. There is a join db entity (UserRole) with default
strategy, but i guess this is supposed to be set to default as one can not
select both foreign keys (userId, roleId). I am using mysql.
This happens when first trying to insert a record:
[INFO] access.QueryLogger LOCK TABLES AUTO_PK_SUPPORT WRITE
[INFO] access.QueryLogger UNLOCK TABLES
2008-11-04 14:40:20.585::WARN: /iopenid-webapp/j_spring_security_check
org.apache.cayenne.CayenneRuntimeException: [v.3.0M4 May 28 2008 23:57:15]
Commit Exception
at
org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:1192)
at
org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:1066)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
'iopenid.auto_pk_support' doesn't exist
Mapping file:
<?xml version="1.0" encoding="utf-8"?>
<data-map project-version="3.0">
<property name="defaultPackage" value="com.interseek.iopenid.model"/>
<property name="defaultSchema" value="iopenid"/>
<db-entity name="Role" schema="iopenid">
<db-attribute name="authority" type="VARCHAR" isMandatory="true"
length="200"/>
<db-attribute name="id" type="INTEGER" isPrimaryKey="true"
isGenerated="true" isMandatory="true"/>
</db-entity>
<db-entity name="User" schema="iopenid">
<db-attribute name="id" type="INTEGER" isPrimaryKey="true"
isGenerated="true" isMandatory="true"/>
<db-attribute name="username" type="VARCHAR" isMandatory="true"
length="32"/>
</db-entity>
<db-entity name="UserRole" schema="iopenid">
<db-attribute name="roleId" type="INTEGER" isPrimaryKey="true"
isMandatory="true"/>
<db-attribute name="userId" type="INTEGER" isPrimaryKey="true"
isMandatory="true"/>
</db-entity>
<obj-entity name="Role" className="com.interseek.iopenid.model.Role"
dbEntityName="Role">
<obj-attribute name="authority" type="java.lang.String"
db-attribute-path="authority"/>
</obj-entity>
<obj-entity name="User" className="com.interseek.iopenid.model.User"
dbEntityName="User">
<obj-attribute name="username" type="java.lang.String"
db-attribute-path="username"/>
</obj-entity>
<db-relationship name="toUserRole" source="Role" target="UserRole"
toDependentPK="true" toMany="true">
<db-attribute-pair source="id" target="roleId"/>
</db-relationship>
<db-relationship name="toUserRole" source="User" target="UserRole"
toDependentPK="true" toMany="true">
<db-attribute-pair source="id" target="userId"/>
</db-relationship>
<db-relationship name="toRole" source="UserRole" target="Role"
toMany="false">
<db-attribute-pair source="roleId" target="id"/>
</db-relationship>
<db-relationship name="toUser" source="UserRole" target="User"
toMany="false">
<db-attribute-pair source="userId" target="id"/>
</db-relationship>
<obj-relationship name="toUserRole" source="Role" target="User"
db-relationship-path="toUserRole.toUser"/>
<obj-relationship name="toUserRole" source="User" target="Role"
db-relationship-path="toUserRole.toRole"/>
</data-map>
Please advise,
Borut
2008/7/16 Andrus Adamchik <an...@objectstyle.org>
> Yes, auto increment flags are added to PK columns by Cayenne. Just set "PK
> Generation Strategy" for each entity in question to "Database Generated".
>
> Andrus
>
>
> On Jul 16, 2008, at 9:53 AM, Borut Bolčina wrote:
>
> Hello,
>>
>> is it possible to generate AUTO_INCREMENT with MySQLDbAdapter when running
>> DbGenerator via API?
>>
>> The goal is to use auto_increment instead of AUTO_PK_SUPPORT table.
>>
>> Regards,
>> Borut
>>
>
>
Re: MySQL, DbGenerator and auto_increment
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cool. I figured there was something env-specific, as generally auto
increment works without any glitches.
Andrus
On Nov 5, 2008, at 11:38 AM, Borut Bolčina wrote:
> Never mind - everything works "as advertised"! I was changing
> mapping files,
> but not actualy using them (conf files were outside web app in
> another maven
> module) - shame on me. :-(
>
> 2008/11/4 Borut Bolčina <bo...@gmail.com>
>
>> Hello,
>>
>> I am doing something wrong as Cayenne is trying to use
>> auto_pk_suport table
>> although I specified PK Generation Strategy to Database generated
>> on User
>> and Role db entities. There is a join db entity (UserRole) with
>> default
>> strategy, but i guess this is supposed to be set to default as one
>> can not
>> select both foreign keys (userId, roleId). I am using mysql.
>>
>> This happens when first trying to insert a record:
>> [INFO] access.QueryLogger LOCK TABLES AUTO_PK_SUPPORT WRITE
>> [INFO] access.QueryLogger UNLOCK TABLES
>> 2008-11-04 14:40:20.585::WARN: /iopenid-webapp/
>> j_spring_security_check
>> org.apache.cayenne.CayenneRuntimeException: [v.3.0M4 May 28 2008
>> 23:57:15]
>> Commit Exception
>> at
>> org
>> .apache.cayenne.access.DataContext.flushToParent(DataContext.java:
>> 1192)
>> at
>> org
>> .apache.cayenne.access.DataContext.commitChanges(DataContext.java:
>> 1066)
>> Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
>> 'iopenid.auto_pk_support' doesn't exist
>>
>> Mapping file:
>> <?xml version="1.0" encoding="utf-8"?>
>> <data-map project-version="3.0">
>> <property name="defaultPackage"
>> value="com.interseek.iopenid.model"/>
>> <property name="defaultSchema" value="iopenid"/>
>> <db-entity name="Role" schema="iopenid">
>> <db-attribute name="authority" type="VARCHAR"
>> isMandatory="true"
>> length="200"/>
>> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
>> isGenerated="true" isMandatory="true"/>
>> </db-entity>
>> <db-entity name="User" schema="iopenid">
>> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
>> isGenerated="true" isMandatory="true"/>
>> <db-attribute name="username" type="VARCHAR"
>> isMandatory="true"
>> length="32"/>
>> </db-entity>
>> <db-entity name="UserRole" schema="iopenid">
>> <db-attribute name="roleId" type="INTEGER" isPrimaryKey="true"
>> isMandatory="true"/>
>> <db-attribute name="userId" type="INTEGER" isPrimaryKey="true"
>> isMandatory="true"/>
>> </db-entity>
>> <obj-entity name="Role"
>> className="com.interseek.iopenid.model.Role"
>> dbEntityName="Role">
>> <obj-attribute name="authority" type="java.lang.String"
>> db-attribute-path="authority"/>
>> </obj-entity>
>> <obj-entity name="User"
>> className="com.interseek.iopenid.model.User"
>> dbEntityName="User">
>> <obj-attribute name="username" type="java.lang.String"
>> db-attribute-path="username"/>
>> </obj-entity>
>> <db-relationship name="toUserRole" source="Role" target="UserRole"
>> toDependentPK="true" toMany="true">
>> <db-attribute-pair source="id" target="roleId"/>
>> </db-relationship>
>> <db-relationship name="toUserRole" source="User" target="UserRole"
>> toDependentPK="true" toMany="true">
>> <db-attribute-pair source="id" target="userId"/>
>> </db-relationship>
>> <db-relationship name="toRole" source="UserRole" target="Role"
>> toMany="false">
>> <db-attribute-pair source="roleId" target="id"/>
>> </db-relationship>
>> <db-relationship name="toUser" source="UserRole" target="User"
>> toMany="false">
>> <db-attribute-pair source="userId" target="id"/>
>> </db-relationship>
>> <obj-relationship name="toUserRole" source="Role" target="User"
>> db-relationship-path="toUserRole.toUser"/>
>> <obj-relationship name="toUserRole" source="User" target="Role"
>> db-relationship-path="toUserRole.toRole"/>
>> </data-map>
>>
>> Please advise,
>> Borut
>>
>> 2008/7/16 Andrus Adamchik <an...@objectstyle.org>
>>
>> Yes, auto increment flags are added to PK columns by Cayenne. Just
>> set "PK
>>> Generation Strategy" for each entity in question to "Database
>>> Generated".
>>>
>>> Andrus
>>>
>>>
>>> On Jul 16, 2008, at 9:53 AM, Borut Bolčina wrote:
>>>
>>> Hello,
>>>>
>>>> is it possible to generate AUTO_INCREMENT with MySQLDbAdapter when
>>>> running
>>>> DbGenerator via API?
>>>>
>>>> The goal is to use auto_increment instead of AUTO_PK_SUPPORT table.
>>>>
>>>> Regards,
>>>> Borut
>>>>
>>>
>>>
>>
Re: MySQL, DbGenerator and auto_increment
Posted by Borut Bolčina <bo...@gmail.com>.
Never mind - everything works "as advertised"! I was changing mapping files,
but not actualy using them (conf files were outside web app in another maven
module) - shame on me. :-(
2008/11/4 Borut Bolčina <bo...@gmail.com>
> Hello,
>
> I am doing something wrong as Cayenne is trying to use auto_pk_suport table
> although I specified PK Generation Strategy to Database generated on User
> and Role db entities. There is a join db entity (UserRole) with default
> strategy, but i guess this is supposed to be set to default as one can not
> select both foreign keys (userId, roleId). I am using mysql.
>
> This happens when first trying to insert a record:
> [INFO] access.QueryLogger LOCK TABLES AUTO_PK_SUPPORT WRITE
> [INFO] access.QueryLogger UNLOCK TABLES
> 2008-11-04 14:40:20.585::WARN: /iopenid-webapp/j_spring_security_check
> org.apache.cayenne.CayenneRuntimeException: [v.3.0M4 May 28 2008 23:57:15]
> Commit Exception
> at
> org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:1192)
> at
> org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:1066)
> Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
> 'iopenid.auto_pk_support' doesn't exist
>
> Mapping file:
> <?xml version="1.0" encoding="utf-8"?>
> <data-map project-version="3.0">
> <property name="defaultPackage" value="com.interseek.iopenid.model"/>
> <property name="defaultSchema" value="iopenid"/>
> <db-entity name="Role" schema="iopenid">
> <db-attribute name="authority" type="VARCHAR" isMandatory="true"
> length="200"/>
> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
> isGenerated="true" isMandatory="true"/>
> </db-entity>
> <db-entity name="User" schema="iopenid">
> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
> isGenerated="true" isMandatory="true"/>
> <db-attribute name="username" type="VARCHAR" isMandatory="true"
> length="32"/>
> </db-entity>
> <db-entity name="UserRole" schema="iopenid">
> <db-attribute name="roleId" type="INTEGER" isPrimaryKey="true"
> isMandatory="true"/>
> <db-attribute name="userId" type="INTEGER" isPrimaryKey="true"
> isMandatory="true"/>
> </db-entity>
> <obj-entity name="Role" className="com.interseek.iopenid.model.Role"
> dbEntityName="Role">
> <obj-attribute name="authority" type="java.lang.String"
> db-attribute-path="authority"/>
> </obj-entity>
> <obj-entity name="User" className="com.interseek.iopenid.model.User"
> dbEntityName="User">
> <obj-attribute name="username" type="java.lang.String"
> db-attribute-path="username"/>
> </obj-entity>
> <db-relationship name="toUserRole" source="Role" target="UserRole"
> toDependentPK="true" toMany="true">
> <db-attribute-pair source="id" target="roleId"/>
> </db-relationship>
> <db-relationship name="toUserRole" source="User" target="UserRole"
> toDependentPK="true" toMany="true">
> <db-attribute-pair source="id" target="userId"/>
> </db-relationship>
> <db-relationship name="toRole" source="UserRole" target="Role"
> toMany="false">
> <db-attribute-pair source="roleId" target="id"/>
> </db-relationship>
> <db-relationship name="toUser" source="UserRole" target="User"
> toMany="false">
> <db-attribute-pair source="userId" target="id"/>
> </db-relationship>
> <obj-relationship name="toUserRole" source="Role" target="User"
> db-relationship-path="toUserRole.toUser"/>
> <obj-relationship name="toUserRole" source="User" target="Role"
> db-relationship-path="toUserRole.toRole"/>
> </data-map>
>
> Please advise,
> Borut
>
> 2008/7/16 Andrus Adamchik <an...@objectstyle.org>
>
> Yes, auto increment flags are added to PK columns by Cayenne. Just set "PK
>> Generation Strategy" for each entity in question to "Database Generated".
>>
>> Andrus
>>
>>
>> On Jul 16, 2008, at 9:53 AM, Borut Bolčina wrote:
>>
>> Hello,
>>>
>>> is it possible to generate AUTO_INCREMENT with MySQLDbAdapter when
>>> running
>>> DbGenerator via API?
>>>
>>> The goal is to use auto_increment instead of AUTO_PK_SUPPORT table.
>>>
>>> Regards,
>>> Borut
>>>
>>
>>
>
Re: MySQL, DbGenerator and auto_increment
Posted by Borut Bolčina <bo...@gmail.com>.
Forgot to tell that I am using Cayenne 3.0M4 and jConnector mysql driver
5.1.6.
I read some more mails on this list about pk generation strategy. I am
guessing autoincrement is not working because Cayenne concludes that driver
is not supporting database generated pk values and therefore falls-back to
auto_pk_support table?
Regards,
Borut
2008/11/4 Borut Bolčina <bo...@gmail.com>
> Hello,
>
> I am doing something wrong as Cayenne is trying to use auto_pk_suport table
> although I specified PK Generation Strategy to Database generated on User
> and Role db entities. There is a join db entity (UserRole) with default
> strategy, but i guess this is supposed to be set to default as one can not
> select both foreign keys (userId, roleId). I am using mysql.
>
> This happens when first trying to insert a record:
> [INFO] access.QueryLogger LOCK TABLES AUTO_PK_SUPPORT WRITE
> [INFO] access.QueryLogger UNLOCK TABLES
> 2008-11-04 14:40:20.585::WARN: /iopenid-webapp/j_spring_security_check
> org.apache.cayenne.CayenneRuntimeException: [v.3.0M4 May 28 2008 23:57:15]
> Commit Exception
> at
> org.apache.cayenne.access.DataContext.flushToParent(DataContext.java:1192)
> at
> org.apache.cayenne.access.DataContext.commitChanges(DataContext.java:1066)
> Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
> 'iopenid.auto_pk_support' doesn't exist
>
> Mapping file:
> <?xml version="1.0" encoding="utf-8"?>
> <data-map project-version="3.0">
> <property name="defaultPackage" value="com.interseek.iopenid.model"/>
> <property name="defaultSchema" value="iopenid"/>
> <db-entity name="Role" schema="iopenid">
> <db-attribute name="authority" type="VARCHAR" isMandatory="true"
> length="200"/>
> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
> isGenerated="true" isMandatory="true"/>
> </db-entity>
> <db-entity name="User" schema="iopenid">
> <db-attribute name="id" type="INTEGER" isPrimaryKey="true"
> isGenerated="true" isMandatory="true"/>
> <db-attribute name="username" type="VARCHAR" isMandatory="true"
> length="32"/>
> </db-entity>
> <db-entity name="UserRole" schema="iopenid">
> <db-attribute name="roleId" type="INTEGER" isPrimaryKey="true"
> isMandatory="true"/>
> <db-attribute name="userId" type="INTEGER" isPrimaryKey="true"
> isMandatory="true"/>
> </db-entity>
> <obj-entity name="Role" className="com.interseek.iopenid.model.Role"
> dbEntityName="Role">
> <obj-attribute name="authority" type="java.lang.String"
> db-attribute-path="authority"/>
> </obj-entity>
> <obj-entity name="User" className="com.interseek.iopenid.model.User"
> dbEntityName="User">
> <obj-attribute name="username" type="java.lang.String"
> db-attribute-path="username"/>
> </obj-entity>
> <db-relationship name="toUserRole" source="Role" target="UserRole"
> toDependentPK="true" toMany="true">
> <db-attribute-pair source="id" target="roleId"/>
> </db-relationship>
> <db-relationship name="toUserRole" source="User" target="UserRole"
> toDependentPK="true" toMany="true">
> <db-attribute-pair source="id" target="userId"/>
> </db-relationship>
> <db-relationship name="toRole" source="UserRole" target="Role"
> toMany="false">
> <db-attribute-pair source="roleId" target="id"/>
> </db-relationship>
> <db-relationship name="toUser" source="UserRole" target="User"
> toMany="false">
> <db-attribute-pair source="userId" target="id"/>
> </db-relationship>
> <obj-relationship name="toUserRole" source="Role" target="User"
> db-relationship-path="toUserRole.toUser"/>
> <obj-relationship name="toUserRole" source="User" target="Role"
> db-relationship-path="toUserRole.toRole"/>
> </data-map>
>
> Please advise,
> Borut
>
> 2008/7/16 Andrus Adamchik <an...@objectstyle.org>
>
> Yes, auto increment flags are added to PK columns by Cayenne. Just set "PK
>> Generation Strategy" for each entity in question to "Database Generated".
>>
>> Andrus
>>
>>
>> On Jul 16, 2008, at 9:53 AM, Borut Bolčina wrote:
>>
>> Hello,
>>>
>>> is it possible to generate AUTO_INCREMENT with MySQLDbAdapter when
>>> running
>>> DbGenerator via API?
>>>
>>> The goal is to use auto_increment instead of AUTO_PK_SUPPORT table.
>>>
>>> Regards,
>>> Borut
>>>
>>
>>
>