Re: [openstack-dev] [Group-based Policy] Database migration chain
On 10/7/14 6:36 PM, Ivar Lazzaro wrote: I posted a patch that implements the Different DB Different Chain approach [0]. That does not mean that this approach is the chosen one! It's just to have a grasp of what the change looks like. The Same DB different chain solution is much simpler to implement (basically you just specify a different version table in the alembic environment) so I haven't posted anything for that. One thing I'm particularly interested about is to hear packagers opinions about which approach would be the preferred one: Same DB or Different? It seems to me that deployment tools such as puppet scripts would also be simpler if the GBP service plugin used the neutron DB, as there would be no need to create a separate DB, set its permissions, put its URL into neutron's config file, etc.. All that would be needed at deployment time is to run the additional gbp-db-manage tool to perform the GBP DB migrations. Am I missing anything? With dependencies only in one direction, and the foreign keys GBP depends on (neutron resource IDs) unlikely to be changed by neutron migrations during Kilo, I don't think we need to worry about interleaving GBP migrations with neutron migrations. On initial deployments or version upgrades, it should be sufficient to run gbp-db-manage after neutron-db-manage. On downgrades, some situations might require running gbp-db-manage before neutron-db-manage. This seems not to be effected by whether the two migration chains are in the same DB/schema or different ones. Also, on the line of Bob's comment in my patch, is there any kind of compatibility or performance issue anyone is aware of about in using cross schema FKs? In addition to compatibility and performance, I'm also concerned about DB connection management when the same server is using multiple connection URLs. I'm not convinced the approach in the patch is sufficient. At least with some DBs, wouldn't we we need a separate sqlalchemy.create_engine() call with each DB's connection URL, which might require using separate context and session objects rather than the ones neutron uses? -Bob Thanks, Ivar. [0] https://review.openstack.org/#/c/126383/ On Mon, Oct 6, 2014 at 11:09 AM, Ivar Lazzaro ivarlazz...@gmail.com mailto:ivarlazz...@gmail.com wrote: I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp That's correct, that would be the likely approach if we go with the different schema route. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. I'm experimenting this approach with our code and it seems to be the case. ' I feel that having the constraint of pointing the same database connection with a different schema is pretty acceptable given how tight is GBP to Neutron. On Sat, Oct 4, 2014 at 8:54 AM, Henry Gessau ges...@cisco.com mailto:ges...@cisco.com wrote: Clint Byrum cl...@fewbar.com mailto:cl...@fewbar.com wrote: Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com mailto:blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron
Re: [openstack-dev] [Group-based Policy] Database migration chain
It seems to me that deployment tools such as puppet scripts would also be simpler if the GBP service plugin used the neutron DB, as there would be no need to create a separate DB, set its permissions, put its URL into neutron's config file, etc.. All that would be needed at deployment time is to run the additional gbp-db-manage tool to perform the GBP DB migrations. Am I missing anything? That's correct, being a new schema it needs to be created, the access granted, and the connection URL configured in neutron.conf (doable also with devstack in the extra-conf option). With dependencies only in one direction, and the foreign keys GBP depends on (neutron resource IDs) unlikely to be changed by neutron migrations during Kilo, I don't think we need to worry about interleaving GBP migrations with neutron migrations. On initial deployments or version upgrades, it should be sufficient to run gbp-db-manage after neutron-db-manage. On downgrades, some situations might require running gbp-db-manage before neutron-db-manage. This seems not to be effected by whether the two migration chains are in the same DB/schema or different ones. That's correct too. Also, this is true for the downgrade even with a different schema. In addition to compatibility and performance, I'm also concerned about DB connection management when the same server is using multiple connection URLs. I'm not convinced the approach in the patch is sufficient. At least with some DBs, wouldn't we we need a separate sqlalchemy.create_engine() call with each DB's connection URL, which might require using separate context and session objects rather than the ones neutron uses? That should not be the case. We are not using a separate DB connection. The connection is in fact the same, the only thing that changes is the schema. For the sql engine this should be only a matter of namespacing (different schema - different namespace), therefore I don't see any relevant performance/connection/greenthread issue. However I'm no DBMS expert, any additional feedback on this matter is welcome. Ivar. On Fri, Oct 10, 2014 at 2:59 PM, Robert Kukura kuk...@noironetworks.com wrote: On 10/7/14 6:36 PM, Ivar Lazzaro wrote: I posted a patch that implements the Different DB Different Chain approach [0]. That does not mean that this approach is the chosen one! It's just to have a grasp of what the change looks like. The Same DB different chain solution is much simpler to implement (basically you just specify a different version table in the alembic environment) so I haven't posted anything for that. One thing I'm particularly interested about is to hear packagers opinions about which approach would be the preferred one: Same DB or Different? It seems to me that deployment tools such as puppet scripts would also be simpler if the GBP service plugin used the neutron DB, as there would be no need to create a separate DB, set its permissions, put its URL into neutron's config file, etc.. All that would be needed at deployment time is to run the additional gbp-db-manage tool to perform the GBP DB migrations. Am I missing anything? With dependencies only in one direction, and the foreign keys GBP depends on (neutron resource IDs) unlikely to be changed by neutron migrations during Kilo, I don't think we need to worry about interleaving GBP migrations with neutron migrations. On initial deployments or version upgrades, it should be sufficient to run gbp-db-manage after neutron-db-manage. On downgrades, some situations might require running gbp-db-manage before neutron-db-manage. This seems not to be effected by whether the two migration chains are in the same DB/schema or different ones. Also, on the line of Bob's comment in my patch, is there any kind of compatibility or performance issue anyone is aware of about in using cross schema FKs? In addition to compatibility and performance, I'm also concerned about DB connection management when the same server is using multiple connection URLs. I'm not convinced the approach in the patch is sufficient. At least with some DBs, wouldn't we we need a separate sqlalchemy.create_engine() call with each DB's connection URL, which might require using separate context and session objects rather than the ones neutron uses? -Bob Thanks, Ivar. [0] https://review.openstack.org/#/c/126383/ On Mon, Oct 6, 2014 at 11:09 AM, Ivar Lazzaro ivarlazz...@gmail.com wrote: I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp That's correct, that would be the likely approach if we go with the different schema route. if you can get the “other database” to be accessible from the target database via
Re: [openstack-dev] [Group-based Policy] Database migration chain
I posted a patch that implements the Different DB Different Chain approach [0]. That does not mean that this approach is the chosen one! It's just to have a grasp of what the change looks like. The Same DB different chain solution is much simpler to implement (basically you just specify a different version table in the alembic environment) so I haven't posted anything for that. One thing I'm particularly interested about is to hear packagers opinions about which approach would be the preferred one: Same DB or Different? Also, on the line of Bob's comment in my patch, is there any kind of compatibility or performance issue anyone is aware of about in using cross schema FKs? Thanks, Ivar. [0] https://review.openstack.org/#/c/126383/ On Mon, Oct 6, 2014 at 11:09 AM, Ivar Lazzaro ivarlazz...@gmail.com wrote: I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp That's correct, that would be the likely approach if we go with the different schema route. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. I'm experimenting this approach with our code and it seems to be the case. ' I feel that having the constraint of pointing the same database connection with a different schema is pretty acceptable given how tight is GBP to Neutron. On Sat, Oct 4, 2014 at 8:54 AM, Henry Gessau ges...@cisco.com wrote: Clint Byrum cl...@fewbar.com wrote: Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp Typically we use the public API's when we want to access data in a different application. The database is a private implementation detail of each application. Currently GPB is very tightly coupled to Neutron. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp That's correct, that would be the likely approach if we go with the different schema route. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. I'm experimenting this approach with our code and it seems to be the case. ' I feel that having the constraint of pointing the same database connection with a different schema is pretty acceptable given how tight is GBP to Neutron. On Sat, Oct 4, 2014 at 8:54 AM, Henry Gessau ges...@cisco.com wrote: Clint Byrum cl...@fewbar.com wrote: Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp Typically we use the public API's when we want to access data in a different application. The database is a private implementation detail of each application. Currently GPB is very tightly coupled to Neutron. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. On Fri, Oct 3, 2014 at 5:25 PM, Ivar Lazzaro ivarlazz...@gmail.com wrote: Hi, Following up the latest GBP team meeting [0][1]: As we keep going with our Juno stackforge implementation [2], although the service is effectively a Neutron extension, we should avoid breaking Neutron's migration chain by adding our model on top of it (and subsequently changing Neutron's HEAD [3]). If we do that, upgrading from Juno to Kilo will be painful for those who have used GBP. There are roughly a couple of possibilities for reaching this goal: 1) Using a separate DBs with separate migration chain; 2) Using the same DB with separate chain (and different alembic version table). Personally I prefer the option 1, moving to a completely different database while leveraging cross database foreign keys. Please let me know your preference, or alternative solutions! :) Cheers, Ivar. [0] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-09-25-18.02.log.html [1] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-10-02-18.01.log.html [2] https://github.com/stackforge/group-based-policy [3] https://review.openstack.org/#/c/123617/ ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- Kevin Benton ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. Typically we use the public API's when we want to access data in a different application. The database is a private implementation detail of each application. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
Clint Byrum cl...@fewbar.com wrote: Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. I believe Group-based Policy (which this thread is about) will use the Neutron database configuration for its dependent database. If Neutron is configured for: connection = mysql://user:pass@locationX:3306/neutron then GBP would use: connection = mysql://user:pass@locationX:3306/neutron_gbp Typically we use the public API's when we want to access data in a different application. The database is a private implementation detail of each application. Currently GPB is very tightly coupled to Neutron. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
On Oct 4, 2014, at 11:24 AM, Clint Byrum cl...@fewbar.com wrote: Excerpts from Mike Bayer's message of 2014-10-04 08:10:38 -0700: On Oct 4, 2014, at 1:10 AM, Kevin Benton blak...@gmail.com wrote: Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. cross “database” is not typically portable, but cross “schema” is. different database vendors have different notions of “databases” or “schemas”. if you can get the “other database” to be accessible from the target database via “otherdatabase.sometable”, then you’re in. from SQLAlchemy’s perspective, it’s just a name with a dot. It’s the database itself that has to support the foreign key at the scope you are shooting for. All true, however, there are zero guarantees that databases will be hosted on the same server, and typically permissions are setup to prevent cross-schema joins. the impression I’ve gotten so far is that they are looking to have different sets of database tables isolated into groups that can be migrated independently, and rather than using multiple alembic version tables, they’d go with this approach. This to me means that on a postgresql backend these would just be individual sub-schemas, and on a MySQL backend would be other databases that are limited to being on the same host (which is MySQL’s version of “schemas”, in that the “CREATE SCHEMA” command is a synonym for “CREATE DATABASE). Typically we use the public API's when we want to access data in a different application. The database is a private implementation detail of each application. I was just having a discussion on IRC the other day with a Neutron dev stating that they’d like to break out several parts of Neutron into “plugins”, which would have their own database tables but still linked to the database tables of the core Neutron application. If this is the case, within an architecture like that you’d have different sub-applications cross-accessing databases. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
[openstack-dev] [Group-based Policy] Database migration chain
Hi, Following up the latest GBP team meeting [0][1]: As we keep going with our Juno stackforge implementation [2], although the service is effectively a Neutron extension, we should avoid breaking Neutron's migration chain by adding our model on top of it (and subsequently changing Neutron's HEAD [3]). If we do that, upgrading from Juno to Kilo will be painful for those who have used GBP. There are roughly a couple of possibilities for reaching this goal: 1) Using a separate DBs with separate migration chain; 2) Using the same DB with separate chain (and different alembic version table). Personally I prefer the option 1, moving to a completely different database while leveraging cross database foreign keys. Please let me know your preference, or alternative solutions! :) Cheers, Ivar. [0] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-09-25-18.02.log.html [1] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-10-02-18.01.log.html [2] https://github.com/stackforge/group-based-policy [3] https://review.openstack.org/#/c/123617/ ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] [Group-based Policy] Database migration chain
Does sqlalchemy have good support for cross-database foreign keys? I was under the impression that they cannot be implemented with the normal syntax and semantics of an intra-database foreign-key constraint. On Fri, Oct 3, 2014 at 5:25 PM, Ivar Lazzaro ivarlazz...@gmail.com wrote: Hi, Following up the latest GBP team meeting [0][1]: As we keep going with our Juno stackforge implementation [2], although the service is effectively a Neutron extension, we should avoid breaking Neutron's migration chain by adding our model on top of it (and subsequently changing Neutron's HEAD [3]). If we do that, upgrading from Juno to Kilo will be painful for those who have used GBP. There are roughly a couple of possibilities for reaching this goal: 1) Using a separate DBs with separate migration chain; 2) Using the same DB with separate chain (and different alembic version table). Personally I prefer the option 1, moving to a completely different database while leveraging cross database foreign keys. Please let me know your preference, or alternative solutions! :) Cheers, Ivar. [0] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-09-25-18.02.log.html [1] http://eavesdrop.openstack.org/meetings/networking_policy/2014/networking_policy.2014-10-02-18.01.log.html [2] https://github.com/stackforge/group-based-policy [3] https://review.openstack.org/#/c/123617/ ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- Kevin Benton ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
