kallithea Changeset - 1ba3aeefe033

Changeset - 1ba3aeefe033

Parent rev.

Child rev.

[Not reviewed]

stable

0 2 1

Mads Kiilerich (kiilerix) - 5 years ago 2019-12-29 14:35:06
mads@kiilerich.com

Grafted from: a85a011f7750

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

3 files changed with 56 insertions and 8 deletions:

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

0 comments (0 inline, 0 general) First comment

kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py

➞

Show inline comments

@@ @@ -31,14 +31,20 @@ from alembic import op @@
 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')

kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py

➞

Show inline comments

@@ new file 100644 @@
 # 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

kallithea/model/db.py

➞

Show inline comments

@@ @@ -2518,7 +2518,6 @@ class Gist(Base, BaseDbModel): @@
 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

0 comments (0 inline, 0 general) First comment