Constraint naming conventions and MySQL's 64 character name limit

[Giovanni Torres]

Hello All!

I'm facing the well known problem of maintaining constraints, when using sqlalchemy (v0.9.8), alembic (v0.7.4) and MySQL (v5.5.41) on Ubuntu (v14.04.2).

I'm talking specifically about these two links:

o http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#constraint-naming-conventions

o http://alembic.readthedocs.org/en/latest/naming.html

The suggested naming conventions, do not work for me with MySQL and I was hoping to hear how others are dealing with the issues. The main problem is the following:

o MySQL's 64 character limit on constrain names: https://bugs.mysql.com/bug.php?id=13942. I hit it when using the recommended foreign key naming convention: fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s

But, I also have these issues:

o The fact the primary constraint in MySQL *is always* called PRIMARY, regardless of what you specify.

o I also seem to be hitting a bug in sqlalchemy similar to this one: https://bitbucket.org/zzzeek/sqlalchemy/issue/3067/naming-convention-exception-for-boolean. I get this error: InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named. 

All this is compounded by the fact that we're also trying to support Postgres (which works very well by way), so we can't only cater to MySQL's nuisances.

The bug I'm trying to fix is in a future OpenStack component: https://bugs.launchpad.net/barbican/+bug/1415869

Any suggestion or shared experiences about how to deal with any of these issues is welcome!

-- 

Giovanni

[Mike Bayer]

On 4/9/15 3:38 PM, Giovanni Torres wrote:

Hello All!

I'm facing the well known problem of maintaining constraints, when using sqlalchemy (v0.9.8), alembic (v0.7.4) and MySQL (v5.5.41) on Ubuntu (v14.04.2).

I'm talking specifically about these two links:

o http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#constraint-naming-conventions

o http://alembic.readthedocs.org/en/latest/naming.html

The suggested naming conventions, do not work for me with MySQL and I was hoping to hear how others are dealing with the issues. The main problem is the following:

o MySQL's 64 character limit on constrain names: https://bugs.mysql.com/bug.php?id=13942. I hit it when using the recommended foreign key naming convention: fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s

For that issue, ultimately the naming convention feature should have some more pluggability in that special tokens can be provided for such as a truncated version of the naming convention.

However, if you have just specific ForeignKey or ForeignKeyConstraint objects that are hitting this limit, I recommend you give them a "name" explicitly that is the name you want them to have, not following the convention.

o The fact the primary constraint in MySQL *is always* called PRIMARY, regardless of what you specify.

I'm not familiar with that issue.  Given this test:

from sqlalchemy import *

convention = {
  "ix": 'ix_%(column_0_label)s',
  "uq": "uq_%(table_name)s_%(column_0_name)s",
  "ck": "ck_%(table_name)s_%(constraint_name)s",
  "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
  "pk": "pk_%(table_name)s"
}

metadata = MetaData(naming_convention=convention)

t = Table('foobar', metadata,
        Column('id', Integer, primary_key=True)
    )

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
metadata.create_all(e)

the output is:


CREATE TABLE foobar (
    id INTEGER NOT NULL AUTO_INCREMENT,
    CONSTRAINT pk_foobar PRIMARY KEY (id)
)

need a test case illustrating the failure.

o I also seem to be hitting a bug in sqlalchemy similar to this one: https://bitbucket.org/zzzeek/sqlalchemy/issue/3067/naming-convention-exception-for-boolean. I get this error: InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

Several approaches to this. 

The first is that you specify constraint_name for your Boolean type, using the "name" parameter.

Second, forego the use of a CHECK constraint with the Boolean type by using create_constraint=False.

Third, instead of using %(constraint_name)s in your CHECK constraint, you use %(column_0_name)s.   Barbican will have to upgrade to SQLAlchemy 1.0 for this, but the good news is that SQLA 1.0 will be released before the Vancouver summit and you can invite me to a Barbican session there in order to start selling this.

Fourth, essentially emulate 1.0's behavior by removing "ck_" from the naming convention and using a straight "after_parent_attach" event to set up the name; this is how naming conventions were done before the feature was added.  This is illustrated here: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions . This is also a potential approach to the foreign key issue as well.


I'm zzzeek on #openstack-dev if you want to chat further!

All this is compounded by the fact that we're also trying to support Postgres (which works very well by way), so we can't only cater to MySQL's nuisances.

The bug I'm trying to fix is in a future OpenStack component: https://bugs.launchpad.net/barbican/+bug/1415869

Any suggestion or shared experiences about how to deal with any of these issues is welcome!

-- 

Giovanni

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Giovanni Torres]

On 10 Apr 2015, at 00:56, Mike Bayer <mik...@zzzcomputing.com> wrote:

> On 4/9/15 3:38 PM, Giovanni Torres wrote:
>>
>> Hello All!
>>
>> I'm facing the well known problem of maintaining constraints, when using sqlalchemy (v0.9.8), alembic (v0.7.4) and MySQL (v5.5.41) on Ubuntu (v14.04.2).
>>
>> I'm talking specifically about these two links:
>>
>> o http://docs.sqlalchemy.org/en/rel_0_9/core/constraints.html#constraint-naming-conventions
>>
>> o http://alembic.readthedocs.org/en/latest/naming.html
>>
>> The suggested naming conventions, do not work for me with MySQL and I was hoping to hear how others are dealing with the issues. The main problem is the following:
>>
>> o MySQL's 64 character limit on constrain names: https://bugs.mysql.com/bug.php?id=13942. I hit it when using the recommended foreign key naming convention: fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s
>
> For that issue, ultimately the naming convention feature should have some more pluggability in that special tokens can be provided for such as a truncated version of the naming convention.
>
> However, if you have just specific ForeignKey or ForeignKeyConstraint objects that are hitting this limit, I recommend you give them a "name" explicitly that is the name you want them to have, not following the convention.

This will work, sounds good to me. I’ll try first using the “after_parent_attach” method and if I have issues with it, I’ll try this one then.

>> o The fact the primary constraint in MySQL *is always* called PRIMARY, regardless of what you specify.
> I'm not familiar with that issue. Given this test:
>
> from sqlalchemy import *
>
> convention = {
> "ix": 'ix_%(column_0_label)s',
> "uq": "uq_%(table_name)s_%(column_0_name)s",
> "ck": "ck_%(table_name)s_%(constraint_name)s",
> "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
> "pk": "pk_%(table_name)s"
> }
>
> metadata = MetaData(naming_convention=convention)
>
> t = Table('foobar', metadata,
> Column('id', Integer, primary_key=True)
> )
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
> metadata.create_all(e)
>
> the output is:
>
>
> CREATE TABLE foobar (
> id INTEGER NOT NULL AUTO_INCREMENT,
> CONSTRAINT pk_foobar PRIMARY KEY (id)
> )
>
> need a test case illustrating the failure.

The main problem with this is that I can’t see a way to modify primary keys with alembic in a way that works with MySQL and Postgres.

MySQL lets you create a table with a proper primary key name, but then you can’t use the name. See http://dev.mysql.com/doc/refman/5.5/en/create-table.html, specifically:

"A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.”

Therefore, if I have a table as follows, which I then try to modify the primary key:

t = Table('foobar', metadata,

Column('id', Integer, primary_key=True),
Column('foo', Integer)
)

An Alembic script would look like this *for MySQL*:

def upgrade():
op.drop_constraint('pk_foobar', ‘foobar’, type_=‘primary')
op.create_primary_key(None, 'foobar', ['id', 'foo'])

However, it doesn’t work. I get: (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key') 'ALTER TABLE foobar DROP PRIMARY KEY ' ()

alembic —sql upgrade 675e5c38a0b:head shows:

-- Running upgrade 675e5c38a0b -> c37885f5cff

ALTER TABLE foobar DROP PRIMARY KEY;

ALTER TABLE foobar ADD PRIMARY KEY (id, foo);

UPDATE alembic_version SET version_num='c37885f5cff' WHERE alembic_version.version_num = '675e5c38a0b’;

Then, I proceed to do it as follows:

def upgrade():
op.execute('ALTER TABLE foobar DROP PRIMARY KEY, ADD PRIMARY KEY (id, foo)’)

Which works OK, however it doesn’t work in Postgres, it’s invalid SQL, to make it work in Postgres I would do:

def upgrade():
op.drop_constraint('pk_foobar', 'foobar')
op.create_primary_key(None, 'foobar', ['id', 'foo'])

Which works very well, alembic —sql upgrade 675e5c38a0b:head shows:

-- Running upgrade 675e5c38a0b -> c37885f5cff

ALTER TABLE foobar DROP CONSTRAINT pk_foobar;

ALTER TABLE foobar ADD CONSTRAINT pk_foobar PRIMARY KEY (id, foo);

UPDATE alembic_version SET version_num='c37885f5cff' WHERE alembic_version.version_num = '675e5c38a0b';

COMMIT;

To conclude, don’t know how to make it work with MySQL and Postgres.

>> o I also seem to be hitting a bug in sqlalchemy similar to this one: https://bitbucket.org/zzzeek/sqlalchemy/issue/3067/naming-convention-exception-for-boolean. I get this error: InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.
> Several approaches to this.
>
> The first is that you specify constraint_name for your Boolean type, using the "name" parameter.
>
> Second, forego the use of a CHECK constraint with the Boolean type by using create_constraint=False.
>
> Third, instead of using %(constraint_name)s in your CHECK constraint, you use %(column_0_name)s. Barbican will have to upgrade to SQLAlchemy 1.0 for this, but the good news is that SQLA 1.0 will be released before the Vancouver summit and you can invite me to a Barbican session there in order to start selling this.

Thanks for the offer! However, this is a bit over my head at the moment. I’m just trying to get my first commit in. But, hopefully the code review generates some discussion and I could try to push it there. I also know one of the core members, which might help.

> Fourth, essentially emulate 1.0's behavior by removing "ck_" from the naming convention and using a straight "after_parent_attach" event to set up the name; this is how naming conventions were done before the feature was added. This is illustrated here: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions . This is also a potential approach to the foreign key issue as well.

Thanks, this seems to me the way to go. I could fix the constraint_name issue and the 64 character limit in one go.

> I'm zzzeek on #openstack-dev if you want to chat further!

Thanks for all the help!

[Mike Bayer]

well like so many openstack scripts I see you'd need to conditional this
on MySQL for now:

if op.get_bind().name == "mysql":
op.execute(" ... ")
else:
op.drop_constraint(....)
op.create_primary_key(...)

within Alembic we'd probably need a new op directive that does an
in-place alter of a PK in a backend-agnostic way.

[Giovanni Torres]

Thank you very much Mike. I think this pretty much covers all my doubts.