Re: [Openstack] Using Foreign Keys
On Apr 26, 2012, at 12:03 PM, Monty Taylor wrote: On 04/26/2012 10:14 AM, Sean Dague wrote: On 04/25/2012 05:17 PM, Vishvananda Ishaya wrote: The main issue is when the relevant tables are moved into a separate service a la quantum or cinder. We can't keep referential integrity across multiple databases, so the foreign keys in this case need to be removed. It leads to an odd situation when there is still an internal implementation in addition to the external implementation because the internal implementation no longer has foreign keys. As an example, we used to have foreign key relationships between instances and networks. We can no longer have these because we support networks declared externally. The internal network management now has no referential integrity, but this is the price we pay for separation of concerns. We are going through a similar set of relationship-breaking with the volume code. There are definitely the practical aspects of where this can't be done because the services have split out, and I think that's fine. But enforcing the ref constraints where possible just provides another level of safety in the data. A policy where we break FK relationships if the preferred core model is 2 services (i.e. Nova / Quantum), but we add FK constraints within a service might be a good idea. I think the real key is to have a config option to tell sqlalchemy to not, even if we're running innodb, add the foreign keys to the DDL sent to the database. If sqlalchemy doesn't have that ability, we should write it and contribute it, because anyone using MySQL at scale via sqlalchemy actually wants the feature, whether they recognize it yet or not. I registered a blueprint for this: https://blueprints.launchpad.net/nova/+spec/disable-fkeys-by-config Take care, Lorin -- Lorin Hochstein Lead Architect - Cloud Services Nimbis Services, Inc. www.nimbisservices.com smime.p7s Description: S/MIME cryptographic signature ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On 25/04/12 19:02, Doug Hellmann wrote: From a MySQL prospective that is probably more of an argument to use transactions, not foreign keys. Transactions and referential integrity are related, but not equivalent. Without referential integrity it's quite easy to commit a transaction that leaves the database in a logically inconsistent state (it sounds like that's what was happening in the case described by the OP). From the way I read it the example given wasn't a referential integrity check but a delete across multiple tables. Is there a technical reason to disable strict referential integrity checking with MySQL? Technically it can make upgrades/downgrades harder, no engines other than InnoDB don't support them whereas many engines support transactions, MySQL doesn't actually support them (they are passed down to the InnoDB engine even at the parser layer). There are several other reasons (bugs and performance) why I don't like the MySQL implementation I won't go into here. Kind Regards -- Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/ ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On 04/25/2012 05:17 PM, Vishvananda Ishaya wrote: The main issue is when the relevant tables are moved into a separate service a la quantum or cinder. We can't keep referential integrity across multiple databases, so the foreign keys in this case need to be removed. It leads to an odd situation when there is still an internal implementation in addition to the external implementation because the internal implementation no longer has foreign keys. As an example, we used to have foreign key relationships between instances and networks. We can no longer have these because we support networks declared externally. The internal network management now has no referential integrity, but this is the price we pay for separation of concerns. We are going through a similar set of relationship-breaking with the volume code. There are definitely the practical aspects of where this can't be done because the services have split out, and I think that's fine. But enforcing the ref constraints where possible just provides another level of safety in the data. A policy where we break FK relationships if the preferred core model is 2 services (i.e. Nova / Quantum), but we add FK constraints within a service might be a good idea. -Sean -- Sean Dague IBM Linux Technology Center email: slda...@us.ibm.com alt-email: sda...@linux.vnet.ibm.com ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On 04/26/2012 10:14 AM, Sean Dague wrote: On 04/25/2012 05:17 PM, Vishvananda Ishaya wrote: The main issue is when the relevant tables are moved into a separate service a la quantum or cinder. We can't keep referential integrity across multiple databases, so the foreign keys in this case need to be removed. It leads to an odd situation when there is still an internal implementation in addition to the external implementation because the internal implementation no longer has foreign keys. As an example, we used to have foreign key relationships between instances and networks. We can no longer have these because we support networks declared externally. The internal network management now has no referential integrity, but this is the price we pay for separation of concerns. We are going through a similar set of relationship-breaking with the volume code. There are definitely the practical aspects of where this can't be done because the services have split out, and I think that's fine. But enforcing the ref constraints where possible just provides another level of safety in the data. A policy where we break FK relationships if the preferred core model is 2 services (i.e. Nova / Quantum), but we add FK constraints within a service might be a good idea. SO ... in a production MySQL service in this situation, under no circumstances should foreign keys actually be applied to the database. Specifying them as part of the SqlAlchemy model is fine, and I believe conveys the informational relationships that are important. But it turns out that in practice, especially with an ORM running things, the performance hit of adding them is pretty bad (generates tons of unneeded index scans, for one thing) If all of your db access is via the ORM layer, there is absolutely zero actual benefit. I think the real key is to have a config option to tell sqlalchemy to not, even if we're running innodb, add the foreign keys to the DDL sent to the database. If sqlalchemy doesn't have that ability, we should write it and contribute it, because anyone using MySQL at scale via sqlalchemy actually wants the feature, whether they recognize it yet or not. Monty ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On 12/04/12 13:35, J. Daniel Schmidt wrote: While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. From a MySQL prospective that is probably more of an argument to use transactions, not foreign keys. Kind Regards -- Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/ ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On Wed, Apr 25, 2012 at 7:38 AM, Andrew Hutchings and...@linuxjedi.co.ukwrote: On 12/04/12 13:35, J. Daniel Schmidt wrote: While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. From a MySQL prospective that is probably more of an argument to use transactions, not foreign keys. Transactions and referential integrity are related, but not equivalent. Without referential integrity it's quite easy to commit a transaction that leaves the database in a logically inconsistent state (it sounds like that's what was happening in the case described by the OP). Is there a technical reason to disable strict referential integrity checking with MySQL? Doug ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
The main issue is when the relevant tables are moved into a separate service a la quantum or cinder. We can't keep referential integrity across multiple databases, so the foreign keys in this case need to be removed. It leads to an odd situation when there is still an internal implementation in addition to the external implementation because the internal implementation no longer has foreign keys. As an example, we used to have foreign key relationships between instances and networks. We can no longer have these because we support networks declared externally. The internal network management now has no referential integrity, but this is the price we pay for separation of concerns. We are going through a similar set of relationship-breaking with the volume code. Vish On Apr 25, 2012, at 11:02 AM, Doug Hellmann wrote: On Wed, Apr 25, 2012 at 7:38 AM, Andrew Hutchings and...@linuxjedi.co.uk wrote: On 12/04/12 13:35, J. Daniel Schmidt wrote: While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. From a MySQL prospective that is probably more of an argument to use transactions, not foreign keys. Transactions and referential integrity are related, but not equivalent. Without referential integrity it's quite easy to commit a transaction that leaves the database in a logically inconsistent state (it sounds like that's what was happening in the case described by the OP). Is there a technical reason to disable strict referential integrity checking with MySQL? Doug ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On Wed, Apr 25, 2012 at 5:17 PM, Vishvananda Ishaya vishvana...@gmail.comwrote: The main issue is when the relevant tables are moved into a separate service a la quantum or cinder. We can't keep referential integrity across multiple databases, so the foreign keys in this case need to be removed. It leads to an odd situation when there is still an internal implementation in addition to the external implementation because the internal implementation no longer has foreign keys. Sure, there's little you can do in that case. I thought the OP was talking about 2 tables in the same database that were already set up with a foreign key relationship, with the referential integrity checks disabled. As an example, we used to have foreign key relationships between instances and networks. We can no longer have these because we support networks declared externally. The internal network management now has no referential integrity, but this is the price we pay for separation of concerns. We are going through a similar set of relationship-breaking with the volume code. Right, those references should be via UUID rather than the integer keys (if the reference is still needed at all). Vish On Apr 25, 2012, at 11:02 AM, Doug Hellmann wrote: On Wed, Apr 25, 2012 at 7:38 AM, Andrew Hutchings and...@linuxjedi.co.ukwrote: On 12/04/12 13:35, J. Daniel Schmidt wrote: While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. From a MySQL prospective that is probably more of an argument to use transactions, not foreign keys. Transactions and referential integrity are related, but not equivalent. Without referential integrity it's quite easy to commit a transaction that leaves the database in a logically inconsistent state (it sounds like that's what was happening in the case described by the OP). Is there a technical reason to disable strict referential integrity checking with MySQL? Doug ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On Apr 19, 2012, at 8:59 PM, Vaze, Mandar wrote: +1 for data integrity ... Here is an example that could use data integrity check : tenant information is managed in keystone DB ovs_quantum DB has tenant_id column for networks table. When I use stack.sh - it puts a string default in tenant_id column - when it creates network via nova-manage network create and it WORKS I see two problems here : 1. tenant_id are uuid - so string default should be rejected with check _is_like_uuid - but that is only partial solution. tenant_ids are strings. It is an implementation detail that keystone uses uuids. 2. tenant_id should be valid ID from keystone.tenants This would require nova-manage having logic to be able to connect to keystone which it doesn't have. One of the drawbacks of having decoupled services is everything isn't in one database where you can support foreign keys. We could in theory add logic to nova to allow it to verify things inside of keystone, but I'm not sure this makes sense from a security perspective. It would require nova to have administrative access to keystone to find out what tenants exist. Alternatively we could force administrative commands like network create to be done through the api using the context of the intended network. This has a drawback as well of making things administratively more difficult. An admin would have to get an administrative token for the intended tenant somehow before making the call. Vish ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
Vish, Thanks for the detailed explanation. I didn't mean to imply that fixing it is trivial. ( I did realize that since keystone could be using different DB server, it might not be trivial for nova-manage to validate the tenant_id) I was just supporting the argument in favor of data integrity :) -Mandar -Original Message- From: Vishvananda Ishaya [mailto:vishvana...@gmail.com] Sent: Friday, April 20, 2012 11:47 AM To: Vaze, Mandar Cc: Philipp Wollermann; J. Daniel Schmidt; openstack Subject: Re: [Openstack] Using Foreign Keys On Apr 19, 2012, at 8:59 PM, Vaze, Mandar wrote: +1 for data integrity ... Here is an example that could use data integrity check : tenant information is managed in keystone DB ovs_quantum DB has tenant_id column for networks table. When I use stack.sh - it puts a string default in tenant_id column - when it creates network via nova-manage network create and it WORKS I see two problems here : 1. tenant_id are uuid - so string default should be rejected with check _is_like_uuid - but that is only partial solution. tenant_ids are strings. It is an implementation detail that keystone uses uuids. 2. tenant_id should be valid ID from keystone.tenants This would require nova-manage having logic to be able to connect to keystone which it doesn't have. One of the drawbacks of having decoupled services is everything isn't in one database where you can support foreign keys. We could in theory add logic to nova to allow it to verify things inside of keystone, but I'm not sure this makes sense from a security perspective. It would require nova to have administrative access to keystone to find out what tenants exist. Alternatively we could force administrative commands like network create to be done through the api using the context of the intended network. This has a drawback as well of making things administratively more difficult. An admin would have to get an administrative token for the intended tenant somehow before making the call. Vish __ Disclaimer:This email and any attachments are sent in strictest confidence for the sole use of the addressee and may contain legally privileged, confidential, and proprietary data. If you are not the intended recipient, please advise the sender by replying promptly to this email and then delete and destroy this email and any attachments without any further use, copying or forwarding ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On Thu, Apr 19, 2012 at 07:32:15PM +0200, Bernhard M. Wiedemann wrote: On 04/12/2012 02:35 PM, J. Daniel Schmidt wrote: Dear Developers, While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. In order to prevent further inconsistencies we created a patch that added more foreign keys: https://review.openstack.org/6216 One reviewer commented: i don't approve of adding foreign keys, and we should probably remove the existing ones (in UserTenantMembership and in Endpoint) and on https://review.openstack.org/#patch,sidebyside,6216,3,keystone/identity/backends/sql.py we shouldn't be using foreign keys at all, they are a crutch that are not available everywhere This was a surprising answer to us as the usage of the foreign keys revealed the inconsistency in the first place. So removing them elsewhere does in no way improve the situation, it even does not help for mysql and sqlite, as SQLAlchemy abstracts them away. We also found similar bugs elsewhere: * https://bugs.launchpad.net/keystone/+bug/959294 * https://bugs.launchpad.net/keystone/+bug/973243 * https://bugs.launchpad.net/keystone/+bug/974199 In our point of view foreign keys should be used in all possible places. This would not harm any database that does not support them but helps all of us to find data inconsistencies and related bugs, which leads to faster development with fewer bugs. What is your take on these things? How would you take care of data consistency otherwise? Thank you for your feedback, Berhard M. Wiedemann J. Daniel Schmidt [1]: http://en.wikipedia.org/wiki/Referential_integrity Just saw another bug, that would have been caught earlier with foreign keys: https://bugs.launchpad.net/nova/+bug/754900 So was this issue discussed on the OpenStack Summit? Yes, I raised this in the Standardizing database management across projects[1] session and everybody attending that sessions agreed that it's a good idea. However, https://review.openstack.org/#/c/6216/ is still open ;( Best Christoph [1] http://folsomdesignsummit2012.sched.org/event/40adf6c240f7ed1fab82e19e8e55dc24 -- Christoph Thiel, Project Manager, SUSE Cloud Infrastructure SUSE LINUX Products GmbH, Maxfeldstraße 5, 90409 Nürnberg, Germany GF: Jeff Hawn, Jennifer Guild, Felix Imendörffer, HRB 16746 (AG Nürnberg) ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/12/2012 02:35 PM, J. Daniel Schmidt wrote: Dear Developers, While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. In order to prevent further inconsistencies we created a patch that added more foreign keys: https://review.openstack.org/6216 One reviewer commented: i don't approve of adding foreign keys, and we should probably remove the existing ones (in UserTenantMembership and in Endpoint) and on https://review.openstack.org/#patch,sidebyside,6216,3,keystone/identity/backends/sql.py we shouldn't be using foreign keys at all, they are a crutch that are not available everywhere This was a surprising answer to us as the usage of the foreign keys revealed the inconsistency in the first place. So removing them elsewhere does in no way improve the situation, it even does not help for mysql and sqlite, as SQLAlchemy abstracts them away. We also found similar bugs elsewhere: * https://bugs.launchpad.net/keystone/+bug/959294 * https://bugs.launchpad.net/keystone/+bug/973243 * https://bugs.launchpad.net/keystone/+bug/974199 In our point of view foreign keys should be used in all possible places. This would not harm any database that does not support them but helps all of us to find data inconsistencies and related bugs, which leads to faster development with fewer bugs. What is your take on these things? How would you take care of data consistency otherwise? Thank you for your feedback, Berhard M. Wiedemann J. Daniel Schmidt [1]: http://en.wikipedia.org/wiki/Referential_integrity Just saw another bug, that would have been caught earlier with foreign keys: https://bugs.launchpad.net/nova/+bug/754900 So was this issue discussed on the OpenStack Summit? Ciao Bernhard M. -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk+QTB8ACgkQSTYLOx37oWRf5gCdE9jLHdRY2vGKU6CZy3hVxtVe sn0Anjg94EkYBeg2RnBSTTGKovnVLveI =CS+y -END PGP SIGNATURE- ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
Hi, On Apr 12, 2012, at 21:35, J. Daniel Schmidt wrote: One reviewer commented: i don't approve of adding foreign keys, and we should probably remove the existing ones (in UserTenantMembership and in Endpoint) and on we shouldn't be using foreign keys at all, they are a crutch that are not available everywhere what the … !? I'm not actively participating in OpenStack development yet, so my opinion may carry no weight - but this is ridiculous. IMHO OpenStack is not some who needs data integrity anyway-web2.0 project, it's meant to provide a stable, robust platform where people run mission-critical production stuff. Calling our test cluster in the office BrokenStack is fun for a while and the 0 days since last exception sign wasn't bad either, but the fun stops when you actually need to depend on this software running. We should implement as many checks for integrity and validity of data as possible and if a foreign key actually catches something, that means that your last line of defense just stopped the worst and you should have failed much earlier. And someone is actually thinking about *removing* that? I'm all for a switch to the add as much foreign keys as we can policy. Actually, why are they not created automatically? If one uses an ORM correctly, there should be no way to actually *not* create them. -- Philipp Wollermann Infrastructure Engineer CyberAgent, Inc. (Tokyo) https://github.com/philwo ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
+1 for data integrity ... Here is an example that could use data integrity check : tenant information is managed in keystone DB ovs_quantum DB has tenant_id column for networks table. When I use stack.sh - it puts a string default in tenant_id column - when it creates network via nova-manage network create and it WORKS I see two problems here : 1. tenant_id are uuid - so string default should be rejected with check _is_like_uuid - but that is only partial solution. 2. tenant_id should be valid ID from keystone.tenants -Mandar -Original Message- From: openstack-bounces+mandar.vaze=nttdata@lists.launchpad.net [mailto:openstack-bounces+mandar.vaze=nttdata@lists.launchpad.net] On Behalf Of Philipp Wollermann Sent: Friday, April 20, 2012 7:36 AM To: J. Daniel Schmidt Cc: openstack Subject: Re: [Openstack] Using Foreign Keys Hi, On Apr 12, 2012, at 21:35, J. Daniel Schmidt wrote: One reviewer commented: i don't approve of adding foreign keys, and we should probably remove the existing ones (in UserTenantMembership and in Endpoint) and on we shouldn't be using foreign keys at all, they are a crutch that are not available everywhere what the ... !? I'm not actively participating in OpenStack development yet, so my opinion may carry no weight - but this is ridiculous. IMHO OpenStack is not some who needs data integrity anyway-web2.0 project, it's meant to provide a stable, robust platform where people run mission-critical production stuff. Calling our test cluster in the office BrokenStack is fun for a while and the 0 days since last exception sign wasn't bad either, but the fun stops when you actually need to depend on this software running. We should implement as many checks for integrity and validity of data as possible and if a foreign key actually catches something, that means that your last line of defense just stopped the worst and you should have failed much earlier. And someone is actually thinking about *removing* that? I'm all for a switch to the add as much foreign keys as we can policy. Actually, why are they not created automatically? If one uses an ORM correctly, there should be no way to actually *not* create them. -- Philipp Wollermann Infrastructure Engineer CyberAgent, Inc. (Tokyo) https://github.com/philwo ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp __ Disclaimer:This email and any attachments are sent in strictest confidence for the sole use of the addressee and may contain legally privileged, confidential, and proprietary data. If you are not the intended recipient, please advise the sender by replying promptly to this email and then delete and destroy this email and any attachments without any further use, copying or forwarding ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
[Openstack] Using Foreign Keys
Dear Developers, While testing our SUSE OpenStack packages we hit a nasty bug and reported it as: https://bugs.launchpad.net/keystone/+bug/972502 We found out that the underlying cause was a lack of referential integrity[1] using sqlite or mysql. When we tried to reproduce this issue on postgresql the usage of foreign keys greatly helped to find the cause. In order to prevent further inconsistencies we created a patch that added more foreign keys: https://review.openstack.org/6216 One reviewer commented: i don't approve of adding foreign keys, and we should probably remove the existing ones (in UserTenantMembership and in Endpoint) and on https://review.openstack.org/#patch,sidebyside,6216,3,keystone/identity/backends/sql.py we shouldn't be using foreign keys at all, they are a crutch that are not available everywhere This was a surprising answer to us as the usage of the foreign keys revealed the inconsistency in the first place. So removing them elsewhere does in no way improve the situation, it even does not help for mysql and sqlite, as SQLAlchemy abstracts them away. We also found similar bugs elsewhere: * https://bugs.launchpad.net/keystone/+bug/959294 * https://bugs.launchpad.net/keystone/+bug/973243 * https://bugs.launchpad.net/keystone/+bug/974199 In our point of view foreign keys should be used in all possible places. This would not harm any database that does not support them but helps all of us to find data inconsistencies and related bugs, which leads to faster development with fewer bugs. What is your take on these things? How would you take care of data consistency otherwise? Thank you for your feedback, Berhard M. Wiedemann J. Daniel Schmidt [1]: http://en.wikipedia.org/wiki/Referential_integrity -- J. Daniel Schmidt j...@suse.de SUSE LINUX Products GmbH Research Development Maxfeldstr. 5 HRB 16746 (AG Nürnberg) D-90409 Nürnberg GF: Jeff Hawn, Jennifer Guild, Felix Imendörffer ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
Re: [Openstack] Using Foreign Keys
On Thu, Apr 12, 2012 at 7:35 AM, J. Daniel Schmidt j...@suse.de wrote: In our point of view foreign keys should be used in all possible places. This would not harm any database that does not support them but helps all of us to find data inconsistencies and related bugs, which leads to faster development with fewer bugs. FWIW, I recently learned that you can enforce foreign keys in sqlite 3.6.19 with a pragma by setting up and event/listener on session create. http://www.sqlite.org/foreignkeys.html We've had some good success with it. -clayg ___ Mailing list: https://launchpad.net/~openstack Post to : openstack@lists.launchpad.net Unsubscribe : https://launchpad.net/~openstack More help : https://help.launchpad.net/ListHelp
