You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Christian Grobmeier <gr...@gmail.com> on 2011/11/18 13:42:37 UTC
Foreign keys whe migrating
Hi all,
sometimes my Modelere throws an error when I alter my database,
because of foreign key restrictions.
To avoid this, one must do this:
SET foreign_key_checks =0;
and afterwards this:
SET foreign_key_checks =1;
(on a mysql) to make the migration a success.
I wonder if there are sometimes problems with other databases too and
if there is a chance to disable these checks before making the
moderler migrating the alter scripts.
Cheers
Christian
--
http://www.grobmeier.de
RE: Foreign keys whe migrating
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
> > 1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just the currently running session, until the next COMMIT (at which point SET CONSTRAINTs is reset to whatever the database default is, usuall SET CONSTRAINTS ALL IMMEDIATE).
>
> I guess doing this inside Cayenne OracleAdapter on commit might be helpful for the Oracle users. And yeah, also wish other DB's would support deferred constraint checking.
Oh, the Oracle users can specify deferred checking on the constraints, so if they want deferred checking, they don't need support from Cayenne for that. It's probably best to leave these settings alone.
(I was a bit imprecise above, the "database default" is: revert to whatever was specified on each constraint. Which usually means CONSTRAINTS ALL IMMEDIATE because few application programmers are aware of deferred checking.)
> BTW the original issue that Christian described was happening during DDL operations (vs DML we are discussing here). DDL could be an entirely different beast.
Ah, sorry, I missed that.
And yes, that's a different beast; on most databases, DDL is non-transactional. (The only exception I'm aware of is Postgresql.)
For Oracle, as for most other databases, the solution for DDL would be this:
1) Remove all constraints that will become invalid, via ALTER TABLE
2) DROP/CREATE/ALTER TABLE to change the table structure
3) Create new constraints via ALTER TABLE
I just found a good comprehensive explanation, including Oracle syntax examples, on http://infolab.stanford.edu/~ullman/fcdb/oracle/or-triggers.html .
Regards,
Jo
Re: Foreign keys whe migrating
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Nov 21, 2011, at 12:48 AM, Durchholz, Joachim wrote:
> 1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just the currently running session, until the next COMMIT (at which point SET CONSTRAINTs is reset to whatever the database default is, usuall SET CONSTRAINTS ALL IMMEDIATE).
I guess doing this inside Cayenne OracleAdapter on commit might be helpful for the Oracle users. And yeah, also wish other DB's would support deferred constraint checking.
BTW the original issue that Christian described was happening during DDL operations (vs DML we are discussing here). DDL could be an entirely different beast.
Andrus
Re: Foreign keys whe migrating
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On Mon Nov 21 21:48:59 2011, Durchholz, Joachim wrote:
>>> For Oracle, the constraint needs to be configured for "deferrable" checking (available since 8i, don't know how well it works today). That postpones constraint checks to commit time, and I think it is not the default.
>>> In other words, in a legacy schema, Oracle's constraint checking will be on a per-DML-statement basis.
>>> SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint checking to the next commit (the Oracle docs claim this is ANSI SQL92 syntax and semantics).
>>> Oracle in general is bad at reporting what data exactly caused some constraint violation; deferring checks to commit time is going to make that worse if at all possible.
>>>
>>> Just enumerating the issues.
>>> I sure would like to see deferred checking in Cayenne. I have absolutely no idea whether it's worth the challenges though.
>
>> How is this a Cayenne issue? Isn't that just a property of the database as a whole which you set up at config time?
>
> 1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just the currently running session, until the next COMMIT (at which point SET CONSTRAINTs is reset to whatever the database default is, usuall SET CONSTRAINTS ALL IMMEDIATE).
> 2) It is a Cayenne issue in that Cayenne issues COMMIT commands. Also, in that Cayenne's operation is affected by the setting - if constraint checking is deferred, Cayenne could do INSERTs/UPDATEs/DELETEs in any order.
>
>> After having spent a long time struggling with this in mysql when upgrading schemas, I have to say it would be a very nice feature to have in my db of choice. Oh, I forgot to mention the mysql workaround:
>>
>> 1. Set the db constraints to OFF
>> 2. Do your schema stuff
>> 3. Set the db constraints back to ON
>> 4. Check that you didn't break anything:
>>
>> http://forge.mysql.com/tools/tool.php?id=11
>>
>> That procedure works really well.
>
> foreign_key_checks is a system variable. In other words: you switch it, you affect all sessions, not just your currently running transaction.
> The given script checks whether the data is inconsistent now, but it must check ALL data in the database. That's too much to run after each commit.
>
> The Oracle setting just checks those rows that were actually modified. That should be MUCH faster.
> Also, it does not allow a COMMIT to go through if any constraints are violated; things will go into automatic ROLLBACK instead.
>
> Regards,
> Jo
I agree that deferred constraint checks are better than the script I
posted (if you are running Oracle or MSSQL). But that is the only
workaround for mysql I found. Useful for doing major database
refactoring. I think it would be very difficult for Cayenne to use
deferred checks as part of the commit process, since so few databases
support it, and it is rarely a problem in normal everyday use of a
database.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
RE: Foreign keys whe migrating
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
>> For Oracle, the constraint needs to be configured for "deferrable" checking (available since 8i, don't know how well it works today). That postpones constraint checks to commit time, and I think it is not the default.
>> In other words, in a legacy schema, Oracle's constraint checking will be on a per-DML-statement basis.
>> SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint checking to the next commit (the Oracle docs claim this is ANSI SQL92 syntax and semantics).
>> Oracle in general is bad at reporting what data exactly caused some constraint violation; deferring checks to commit time is going to make that worse if at all possible.
>>
>> Just enumerating the issues.
>> I sure would like to see deferred checking in Cayenne. I have absolutely no idea whether it's worth the challenges though.
> How is this a Cayenne issue? Isn't that just a property of the database as a whole which you set up at config time?
1) Nope, SET CONSTRAINTS ALL DEFERRED is an in-session command. It affects just the currently running session, until the next COMMIT (at which point SET CONSTRAINTs is reset to whatever the database default is, usuall SET CONSTRAINTS ALL IMMEDIATE).
2) It is a Cayenne issue in that Cayenne issues COMMIT commands. Also, in that Cayenne's operation is affected by the setting - if constraint checking is deferred, Cayenne could do INSERTs/UPDATEs/DELETEs in any order.
> After having spent a long time struggling with this in mysql when upgrading schemas, I have to say it would be a very nice feature to have in my db of choice. Oh, I forgot to mention the mysql workaround:
>
> 1. Set the db constraints to OFF
> 2. Do your schema stuff
> 3. Set the db constraints back to ON
> 4. Check that you didn't break anything:
>
> http://forge.mysql.com/tools/tool.php?id=11
>
> That procedure works really well.
foreign_key_checks is a system variable. In other words: you switch it, you affect all sessions, not just your currently running transaction.
The given script checks whether the data is inconsistent now, but it must check ALL data in the database. That's too much to run after each commit.
The Oracle setting just checks those rows that were actually modified. That should be MUCH faster.
Also, it does not allow a COMMIT to go through if any constraints are violated; things will go into automatic ROLLBACK instead.
Regards,
Jo
Re: Foreign keys whe migrating
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On Mon Nov 21 20:37:06 2011, Durchholz, Joachim wrote:
>> Oracle and MS-SQL do not suffer from this limitation, but I've done no testing to verify this.
>
> For Oracle, the constraint needs to be configured for "deferrable" checking (available since 8i, don't know how well it works today). That postpones constraint checks to commit time, and I think it is not the default.
> In other words, in a legacy schema, Oracle's constraint checking will be on a per-DML-statement basis.
> SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint checking to the next commit (the Oracle docs claim this is ANSI SQL92 syntax and semantics).
> Oracle in general is bad at reporting what data exactly caused some constraint violation; deferring checks to commit time is going to make that worse if at all possible.
>
> Just enumerating the issues.
> I sure would like to see deferred checking in Cayenne. I have absolutely no idea whether it's worth the challenges though.
>
> Regards,
> Jo
How is this a Cayenne issue? Isn't that just a property of the database
as a whole which you set up at config time?
After having spent a long time struggling with this in mysql when
upgrading schemas, I have to say it would be a very nice feature to
have in my db of choice. Oh, I forgot to mention the mysql workaround:
1. Set the db constraints to OFF
2. Do your schema stuff
3. Set the db constraints back to ON
4. Check that you didn't break anything:
http://forge.mysql.com/tools/tool.php?id=11
That procedure works really well.
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
RE: Foreign keys whe migrating
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
> Oracle and MS-SQL do not suffer from this limitation, but I've done no testing to verify this.
For Oracle, the constraint needs to be configured for "deferrable" checking (available since 8i, don't know how well it works today). That postpones constraint checks to commit time, and I think it is not the default.
In other words, in a legacy schema, Oracle's constraint checking will be on a per-DML-statement basis.
SET CONSTRAINTS ALL DEFERRED will make Oracle postpone all constraint checking to the next commit (the Oracle docs claim this is ANSI SQL92 syntax and semantics).
Oracle in general is bad at reporting what data exactly caused some constraint violation; deferring checks to commit time is going to make that worse if at all possible.
Just enumerating the issues.
I sure would like to see deferred checking in Cayenne. I have absolutely no idea whether it's worth the challenges though.
Regards,
Jo
Re: Foreign keys whe migrating
Posted by Aristedes Maniatis <ar...@maniatis.org>.
On Fri Nov 18 23:42:37 2011, Christian Grobmeier wrote:
> Hi all,
>
> sometimes my Modelere throws an error when I alter my database,
> because of foreign key restrictions.
>
> To avoid this, one must do this:
> SET foreign_key_checks =0;
>
> and afterwards this:
> SET foreign_key_checks =1;
>
> (on a mysql) to make the migration a success.
>
> I wonder if there are sometimes problems with other databases too and
> if there is a chance to disable these checks before making the
> moderler migrating the alter scripts.
>
> Cheers
> Christian
>
This can sometimes happen because mysql performs constraint checks on a
row by row basis. So you might have 3 commands inside a transaction
which all together result in valid data. However the first row touched
by the first command will cause the innodb engine to validate the row.
Oracle and MS-SQL do not suffer from this limitation, but I've done no
testing to verify this. You need to be careful, because "SET
foreign_key_checks =1" does not cause the data to the validated. You
could end up with invalid rows of data.
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A