You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Markus Reich <re...@gmail.com> on 2023/04/12 06:34:15 UTC
Problem with order when delete/inserting cascading objects
Hi,
I'm using Cayenne 4.2.RC2 and have a special problem when deleting or
creating objects that have a relation to others.
To solve the problem I have to delete the FK relation on database. I'm
using MariaDB 10.9. with the JDBC driver org.mariadb.jdbc 3.1.3.
My model
meals
meals_meals_items
meals_items
meals 1 : n meals_items
the relation is in meals_meals_items
so meals_meals_items has two fk one to meal_id and one to meal_item_id
I try to create a meal with two meals_items
the log looks like
2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
INSERT INTO `balecon`.`meals_meals_items`( `meal_id`, `meal_item_id`)
VALUES( ?, ?)
2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
[batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
2->meal_item_id:'d2b8082f-9318-4a71-b27d-c51ae7...']
2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
[batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
2->meal_item_id:'67f4bbfe-8837-43c3-b722-aedd29...']
2023-04-12 08:29:01,435 INFO [org.apache.cayenne.log.JdbcEventLogger]: ***
error.
java.sql.BatchUpdateException: (conn=19509) Cannot add or update a child
row: a foreign key constraint fails (`balecon`.`meals_meals_items`,
CONSTRAINT `fk_meals_items` FOREIGN KEY (`meal_item_id`) REFERENCES
`meals_items` (`id`))
so cayenne tries to insert the meals_meals_items before the inserts of
meals_items?
sometimes it works, but then the deleting has problem, also first he trys
to delete meals_meals_items then the fk has a problem?
regards
Meex
When I log the JDBC statements I see
Re: Problem with order when delete/inserting cascading objects
Posted by Markus Reich <re...@gmail.com>.
Thx Nikita, this solved my problem!!!
Am Fr., 14. Apr. 2023 um 11:08 Uhr schrieb Nikita Timofeev <
ntimofeev@objectstyle.com>:
> Hi,
>
> In general this type of relationship should be just fine.
> Could you please check if your DB relationships have the 'to Dep Pk'
> flag set properly.
> In your case it should be set on meals and meals_items parts and not
> set on the meals_meals_items part of the relationship.
>
> It's a pretty common source of the troubles, as it's not the most
> obvious checkbox.
> But it is required in order for Cayenne to decide what part of the PK
> to PK relationship is leading.
>
> On Wed, Apr 12, 2023 at 9:34 AM Markus Reich <re...@gmail.com>
> wrote:
> >
> > Hi,
> >
> > I'm using Cayenne 4.2.RC2 and have a special problem when deleting or
> > creating objects that have a relation to others.
> > To solve the problem I have to delete the FK relation on database. I'm
> > using MariaDB 10.9. with the JDBC driver org.mariadb.jdbc 3.1.3.
> >
> > My model
> >
> > meals
> > meals_meals_items
> > meals_items
> >
> > meals 1 : n meals_items
> > the relation is in meals_meals_items
> > so meals_meals_items has two fk one to meal_id and one to meal_item_id
> >
> > I try to create a meal with two meals_items
> > the log looks like
> >
> > 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> > INSERT INTO `balecon`.`meals_meals_items`( `meal_id`, `meal_item_id`)
> > VALUES( ?, ?)
> >
> > 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> > [batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
> > 2->meal_item_id:'d2b8082f-9318-4a71-b27d-c51ae7...']
> >
> > 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> > [batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
> > 2->meal_item_id:'67f4bbfe-8837-43c3-b722-aedd29...']
> >
> > 2023-04-12 08:29:01,435 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> ***
> > error.
> >
> > java.sql.BatchUpdateException: (conn=19509) Cannot add or update a child
> > row: a foreign key constraint fails (`balecon`.`meals_meals_items`,
> > CONSTRAINT `fk_meals_items` FOREIGN KEY (`meal_item_id`) REFERENCES
> > `meals_items` (`id`))
> >
> >
> > so cayenne tries to insert the meals_meals_items before the inserts of
> > meals_items?
> >
> >
> > sometimes it works, but then the deleting has problem, also first he trys
> > to delete meals_meals_items then the fk has a problem?
> >
> >
> > regards
> >
> > Meex
> >
> >
> >
> >
> >
> > When I log the JDBC statements I see
>
>
>
> --
> Best regards,
> Nikita Timofeev
>
--
*Markus Reich*
Waldweg 62
6393 St. Ulrich am Pillersee
reich.markus@gmail.com
Re: Problem with order when delete/inserting cascading objects
Posted by Nikita Timofeev <nt...@objectstyle.com>.
Hi,
In general this type of relationship should be just fine.
Could you please check if your DB relationships have the 'to Dep Pk'
flag set properly.
In your case it should be set on meals and meals_items parts and not
set on the meals_meals_items part of the relationship.
It's a pretty common source of the troubles, as it's not the most
obvious checkbox.
But it is required in order for Cayenne to decide what part of the PK
to PK relationship is leading.
On Wed, Apr 12, 2023 at 9:34 AM Markus Reich <re...@gmail.com> wrote:
>
> Hi,
>
> I'm using Cayenne 4.2.RC2 and have a special problem when deleting or
> creating objects that have a relation to others.
> To solve the problem I have to delete the FK relation on database. I'm
> using MariaDB 10.9. with the JDBC driver org.mariadb.jdbc 3.1.3.
>
> My model
>
> meals
> meals_meals_items
> meals_items
>
> meals 1 : n meals_items
> the relation is in meals_meals_items
> so meals_meals_items has two fk one to meal_id and one to meal_item_id
>
> I try to create a meal with two meals_items
> the log looks like
>
> 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> INSERT INTO `balecon`.`meals_meals_items`( `meal_id`, `meal_item_id`)
> VALUES( ?, ?)
>
> 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> [batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
> 2->meal_item_id:'d2b8082f-9318-4a71-b27d-c51ae7...']
>
> 2023-04-12 08:29:01,423 INFO [org.apache.cayenne.log.JdbcEventLogger]:
> [batch bind: 1->meal_id:'d845fda8-ad39-47f9-98dd-5062ce...',
> 2->meal_item_id:'67f4bbfe-8837-43c3-b722-aedd29...']
>
> 2023-04-12 08:29:01,435 INFO [org.apache.cayenne.log.JdbcEventLogger]: ***
> error.
>
> java.sql.BatchUpdateException: (conn=19509) Cannot add or update a child
> row: a foreign key constraint fails (`balecon`.`meals_meals_items`,
> CONSTRAINT `fk_meals_items` FOREIGN KEY (`meal_item_id`) REFERENCES
> `meals_items` (`id`))
>
>
> so cayenne tries to insert the meals_meals_items before the inserts of
> meals_items?
>
>
> sometimes it works, but then the deleting has problem, also first he trys
> to delete meals_meals_items then the fk has a problem?
>
>
> regards
>
> Meex
>
>
>
>
>
> When I log the JDBC statements I see
--
Best regards,
Nikita Timofeev