Description:
Not sure if this is the correct place to log this, but it may be of some concern if there is interest in OpenStack compatiblity, which in turn requires SQLAlchemy compatibility....
Trying to use MySQL Cluster with OpenStack (per O3L's approach). OpenStack services come with a set of DB migration scripts that use SQLAlchemy for defining / updating tables for a given service's database. As part of the workarounds implemented by the O3L team for MySQL Cluster compatibility, we sometimes comment out the creation of unique constraints to avoid problems in other migration scripts where those same constraints can't be deleted due to the FK. I've noticed that if we comment out the unique constraints in the SQLAlchemy script defining a table, the resulting table doesn't contain the FK, which of course causes problems in later migration scripts. I cannot repeat this when creating the table manually.
How to repeat:
MySQL Cluster / Python / SqlAlchemy versions used:
# python --version
Python 2.7.5
# pip freeze | grep -i sqlalchemy
SQLAlchemy==1.0.12
sqlalchemy-migrate==0.10.0
mysql> select @@version;
+-------------------------------+
| @@version |
+-------------------------------+
| 5.6.28-ndb-7.4.10-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)
Example:
The SQLAlchmemy block for metadef_objects table creation (from Glance DB migration):
def define_metadef_objects_table(meta):
_constr_kwargs = {}
if meta.bind.name == 'ibm_db_sa':
_constr_kwargs['name'] = 'ix_objects_namespace_id_name'
objects = Table('metadef_objects',
meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('namespace_id', Integer(),
ForeignKey('metadef_namespaces.id'),
nullable=False),
Column('name', String(80), nullable=False),
Column('description', Text()),
Column('required', Text()),
Column('schema', Text(), nullable=False),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
UniqueConstraint('namespace_id', 'name',
**_constr_kwargs),
mysql_engine='NDBCLUSTER',
mysql_charset='utf8',
extend_existing=True)
if meta.bind.name != 'ibm_db_sa':
Index('ix_objects_namespace_id_name',
objects.c.namespace_id,
objects.c.name)
return objects
The above code results in the following table as described by the “show create table” SQL statement:
CREATE TABLE `metadef_objects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`namespace_id` int(11) NOT NULL,
`name` varchar(80) NOT NULL,
`description` text,
`required` text,
`schema` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `namespace_id` (`namespace_id`,`name`),
KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`),
CONSTRAINT `FK_218_245` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
Notice the FK and unique constraint. One of the workarounds employed by Oracle OpenStack team in the event a DB migration script failed to delete a unique constraint was to comment out the creation of the constraint. For example:
objects = Table('metadef_objects',
meta,
Column('id', Integer(), primary_key=True, nullable=False),
Column('namespace_id', Integer(),
ForeignKey('metadef_namespaces.id'),
nullable=False),
Column('name', String(80), nullable=False),
Column('description', Text()),
Column('required', Text()),
Column('schema', Text(), nullable=False),
Column('created_at', DateTime(), nullable=False),
Column('updated_at', DateTime()),
# Don’t create for NDB
# UniqueConstraint('namespace_id', 'name',
# **_constr_kwargs),
mysql_engine='NDBCLUSTER',
mysql_charset='utf8',
extend_existing=True)
if meta.bind.name != 'ibm_db_sa':
Index('ix_objects_namespace_id_name',
objects.c.namespace_id,
objects.c.name)
return objects
Running this version of the script results in the following table (as described by the ’show create’ SQL command):
CREATE TABLE `metadef_objects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`namespace_id` int(11) NOT NULL,
`name` varchar(80) NOT NULL,
`description` text,
`required` text,
`schema` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
Notice there is no FK now! But, if I run the above create table SQL statements manually, the FK is created regardless of whether or not the unique constraint is defined:
mysql> CREATE TABLE `metadef_objects` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `namespace_id` int(11) NOT NULL,
-> `name` varchar(80) NOT NULL,
-> `description` text,
-> `required` text,
-> `schema` text NOT NULL,
-> `created_at` datetime NOT NULL,
-> `updated_at` datetime DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`),
-> CONSTRAINT `metadef_objects_ibfk_1` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
-> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)
mysql> show create table metadef_objects;
| metadef_objects | CREATE TABLE `metadef_objects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`namespace_id` int(11) NOT NULL,
`name` varchar(80) NOT NULL,
`description` text,
`required` text,
`schema` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`),
CONSTRAINT `metadef_objects_ibfk_1` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 |
© 2024, Oracle Corporation and/or its affiliates
Description: Not sure if this is the correct place to log this, but it may be of some concern if there is interest in OpenStack compatiblity, which in turn requires SQLAlchemy compatibility.... Trying to use MySQL Cluster with OpenStack (per O3L's approach). OpenStack services come with a set of DB migration scripts that use SQLAlchemy for defining / updating tables for a given service's database. As part of the workarounds implemented by the O3L team for MySQL Cluster compatibility, we sometimes comment out the creation of unique constraints to avoid problems in other migration scripts where those same constraints can't be deleted due to the FK. I've noticed that if we comment out the unique constraints in the SQLAlchemy script defining a table, the resulting table doesn't contain the FK, which of course causes problems in later migration scripts. I cannot repeat this when creating the table manually. How to repeat: MySQL Cluster / Python / SqlAlchemy versions used: # python --version Python 2.7.5 # pip freeze | grep -i sqlalchemy SQLAlchemy==1.0.12 sqlalchemy-migrate==0.10.0 mysql> select @@version; +-------------------------------+ | @@version | +-------------------------------+ | 5.6.28-ndb-7.4.10-cluster-gpl | +-------------------------------+ 1 row in set (0.00 sec) Example: The SQLAlchmemy block for metadef_objects table creation (from Glance DB migration): def define_metadef_objects_table(meta): _constr_kwargs = {} if meta.bind.name == 'ibm_db_sa': _constr_kwargs['name'] = 'ix_objects_namespace_id_name' objects = Table('metadef_objects', meta, Column('id', Integer(), primary_key=True, nullable=False), Column('namespace_id', Integer(), ForeignKey('metadef_namespaces.id'), nullable=False), Column('name', String(80), nullable=False), Column('description', Text()), Column('required', Text()), Column('schema', Text(), nullable=False), Column('created_at', DateTime(), nullable=False), Column('updated_at', DateTime()), UniqueConstraint('namespace_id', 'name', **_constr_kwargs), mysql_engine='NDBCLUSTER', mysql_charset='utf8', extend_existing=True) if meta.bind.name != 'ibm_db_sa': Index('ix_objects_namespace_id_name', objects.c.namespace_id, objects.c.name) return objects The above code results in the following table as described by the “show create table” SQL statement: CREATE TABLE `metadef_objects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `namespace_id` int(11) NOT NULL, `name` varchar(80) NOT NULL, `description` text, `required` text, `schema` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `namespace_id` (`namespace_id`,`name`), KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`), CONSTRAINT `FK_218_245` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 Notice the FK and unique constraint. One of the workarounds employed by Oracle OpenStack team in the event a DB migration script failed to delete a unique constraint was to comment out the creation of the constraint. For example: objects = Table('metadef_objects', meta, Column('id', Integer(), primary_key=True, nullable=False), Column('namespace_id', Integer(), ForeignKey('metadef_namespaces.id'), nullable=False), Column('name', String(80), nullable=False), Column('description', Text()), Column('required', Text()), Column('schema', Text(), nullable=False), Column('created_at', DateTime(), nullable=False), Column('updated_at', DateTime()), # Don’t create for NDB # UniqueConstraint('namespace_id', 'name', # **_constr_kwargs), mysql_engine='NDBCLUSTER', mysql_charset='utf8', extend_existing=True) if meta.bind.name != 'ibm_db_sa': Index('ix_objects_namespace_id_name', objects.c.namespace_id, objects.c.name) return objects Running this version of the script results in the following table (as described by the ’show create’ SQL command): CREATE TABLE `metadef_objects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `namespace_id` int(11) NOT NULL, `name` varchar(80) NOT NULL, `description` text, `required` text, `schema` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`) ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 Notice there is no FK now! But, if I run the above create table SQL statements manually, the FK is created regardless of whether or not the unique constraint is defined: mysql> CREATE TABLE `metadef_objects` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `namespace_id` int(11) NOT NULL, -> `name` varchar(80) NOT NULL, -> `description` text, -> `required` text, -> `schema` text NOT NULL, -> `created_at` datetime NOT NULL, -> `updated_at` datetime DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`), -> CONSTRAINT `metadef_objects_ibfk_1` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION -> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.10 sec) mysql> show create table metadef_objects; | metadef_objects | CREATE TABLE `metadef_objects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `namespace_id` int(11) NOT NULL, `name` varchar(80) NOT NULL, `description` text, `required` text, `schema` text NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `ix_objects_namespace_id_name` (`namespace_id`,`name`), CONSTRAINT `metadef_objects_ibfk_1` FOREIGN KEY (`namespace_id`) REFERENCES `metadef_namespaces` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 |