Mercurial > kallithea
changeset 8084:1ba3aeefe033 stable
ssh: drop usk_public_key_idx again
Essentially a backout of d2a97f73fa1f and the
4851d15bc437_db_migration_step_after_95c01895c006_ alembic step.
We can't reliably have full index on fields with unbounded length. The
upgrade step has been reported to fail on MySQL [1]:
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
(1170, "BLOB/TEXT column 'public_key' used in key specification without
a key length") [SQL: u'CREATE INDEX usk_public_key_idx ON user_ssh_keys
(public_key)'] (Background on this error at: http://sqlalche.me/e/e3q8)
And we really don't need this index ... especially now when we use
fingerprints for key deletion instead of looking up by the full public key.
[1] https://lists.sfconservancy.org/pipermail/kallithea-general/2019q4/003068.html
author | Mads Kiilerich <mads@kiilerich.com> |
---|---|
date | Sun, 29 Dec 2019 15:35:06 +0100 |
parents | 44e18bd4c3b2 |
children | f9c55f700ad9 |
files | kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py kallithea/model/db.py |
diffstat | 3 files changed, 56 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py Mon Dec 30 01:38:59 2019 +0100 +++ b/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py Sun Dec 29 15:35:06 2019 +0100 @@ -31,14 +31,20 @@ def upgrade(): - meta = sa.MetaData() - meta.reflect(bind=op.get_bind()) + pass + # The following upgrade step turned out to be a bad idea. A later step + # "d7ec25b66e47_ssh_drop_usk_public_key_idx_again" will remove the index + # again if it exists ... but we shouldn't even try to create it. - if not any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes): - with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op: - batch_op.create_index('usk_public_key_idx', ['public_key'], unique=False) + #meta = sa.MetaData() + #meta.reflect(bind=op.get_bind()) + + #if not any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes): + # with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op: + # batch_op.create_index('usk_public_key_idx', ['public_key'], unique=False) def downgrade(): - with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op: - batch_op.drop_index('usk_public_key_idx') + if any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes): + with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op: + batch_op.drop_index('usk_public_key_idx')
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py Sun Dec 29 15:35:06 2019 +0100 @@ -0,0 +1,43 @@ +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program. If not, see <http://www.gnu.org/licenses/>. + +"""ssh: drop usk_public_key_idx again + +Revision ID: d7ec25b66e47 +Revises: 4851d15bc437 +Create Date: 2019-12-29 15:33:10.982003 + +""" + +# The following opaque hexadecimal identifiers ("revisions") are used +# by Alembic to track this migration script and its relations to others. +revision = 'd7ec25b66e47' +down_revision = '4851d15bc437' +branch_labels = None +depends_on = None + +import sqlalchemy as sa +from alembic import op + + +def upgrade(): + meta = sa.MetaData() + meta.reflect(bind=op.get_bind()) + + if any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes): + with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op: + batch_op.drop_index('usk_public_key_idx') + + +def downgrade(): + pass
--- a/kallithea/model/db.py Mon Dec 30 01:38:59 2019 +0100 +++ b/kallithea/model/db.py Sun Dec 29 15:35:06 2019 +0100 @@ -2518,7 +2518,6 @@ class UserSshKeys(Base, BaseDbModel): __tablename__ = 'user_ssh_keys' __table_args__ = ( - Index('usk_public_key_idx', 'public_key'), Index('usk_fingerprint_idx', 'fingerprint'), UniqueConstraint('fingerprint'), _table_args_default_dict