Issue #374: 0.6.0 create MySQL database fails in SQLAlchemy
| Reported by: | Austin Collier |
| State: | resolved |
| Created on: | 2020-05-20 15:38 |
| Updated on: | 2020-06-05 07:38 |
Description
Upgrading to Kallithea version 0.6.0 I wanted to migrate my db to MySQL from SQLite, but the db wouldn’t create properly (and I ran into issues with scripts perl and bash to convert the db over. MySQL kept finding random issues).
When I’d use kallithea-cli db-create -c my.ini it would prompt me to remove the database, I’d hit y and then the following error would happen.
(kallithea-venv) [kallithea@kallithea kallithea-0.6.0]$ kallithea-cli db-create -c my.ini
Are you sure to destroy old database ? [y/n]y
Traceback (most recent call last):
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/mysql/connector/connection_cext.py", line 489, in cmd_query
raw_as_string=raw_as_string)
_mysql_connector.MySQLInterfaceError: Duplicate key name 'uq_user_ssh_keys_fingerprint'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
cursor, statement, parameters, context
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
cursor.execute(statement, parameters)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/mysql/connector/connection_cext.py", line 492, in cmd_query
sqlstate=exc.sqlstate)
mysql.connector.errors.ProgrammingError: 1061 (42000): Duplicate key name 'uq_user_ssh_keys_fingerprint'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/opt/kallithea-0.6.0/kallithea-venv/bin/kallithea-cli", line 11, in <module>
load_entry_point('Kallithea==0.6.0', 'console_scripts', 'kallithea-cli')()
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/click/core.py", line 829, in __call__
return self.main(*args, **kwargs)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/click/core.py", line 610, in invoke return callback(*args, **kwargs) File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/kallithea/bin/kallithea_cli_base.py", line 81, in runtime_wrapper return annotated(*args, **kwargs) File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/kallithea/bin/kallithea_cli_db.py", line 60, in db_create
dbmanage.create_tables(override=True)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/kallithea/lib/db_manage.py", line 116, in create_tables
Base.metadata.create_all(checkfirst=checkfirst)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/schema.py", line 4465, in create_all
ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 2104, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1663, in _run_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single
return meth(obj, **kw)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 787, in visit_metadata
_is_metadata_operation=True,
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single [520/1439]
return meth(obj, **kw)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
include_foreign_key_constraints, # noqa
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1020, in execute
return meth(self, multiparams, params)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1082, in _execute_ddl
compiled,
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1324, in _execute_context
e, statement, parameters, cursor, context
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1518, in _handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from_=e
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
cursor, statement, parameters, context
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
cursor.execute(statement, parameters)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/mysql/connector/cursor_cext.py", line 266, in execute
raw_as_string=self._raw_as_string)
File "/opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/mysql/connector/connection_cext.py", line 492, in cmd_query
sqlstate=exc.sqlstate)
sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1061 (42000): Duplicate key name 'uq_user_ssh_keys_fingerprint'
[SQL:
CREATE TABLE user_ssh_keys (
user_ssh_key_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
public_key TEXT NOT NULL,
description TEXT NOT NULL,
fingerprint VARCHAR(255) NOT NULL,
created_on DATETIME NOT NULL,
last_seen DATETIME,
CONSTRAINT pk_user_ssh_keys PRIMARY KEY (user_ssh_key_id),
CONSTRAINT uq_user_ssh_keys_fingerprint UNIQUE (fingerprint),
CONSTRAINT fk_user_ssh_keys_user_id FOREIGN KEY(user_id) REFERENCES users (user_id),
CONSTRAINT uq_user_ssh_keys_fingerprint UNIQUE (fingerprint)
)ENGINE=InnoDB CHARSET=utf8
]
(Background on this error at: http://sqlalche.me/e/f405)
You can notice in the query that CONSTRAINT uq_user_ssh_keys_fingerprint UNIQUE (fingerprint)` is duplicated twice. In looking at the affected file kallithea-venv/lib64/python3.6/site-packages/kallithea/alembic/versions/b74907136bc1_create_table_for_ssh_keys.py I didn’t see anything obvious that would trigger that key creation twice.
It’s worth noting that I tried PyMySQL and also python-mysql-connection (both with the same results). Also doing this same process specifying an SQLite DB worked flawlessly.
Attachments
Comments
Comment by Thomas De Schampheleire, on 2020-05-20 17:46
The file you referred to from ‘alembic’ is not actually used in this context of creating a database, only when migrating an existing one. You will not find a direct reference to the full string elsewhere because it is composed out of two or more parts.
The actual source seems to be a double unique constraint in `kallithea/model/db.py`, once as UniqueConstraint, and once as unique=True .
I think following may solve your problem, could you apply this patch then retry the db-create step?
diff --git a/kallithea/model/db.py b/kallithea/model/db.py --- a/kallithea/model/db.py +++ b/kallithea/model/db.py @@ -2304,7 +2304,7 @@ class UserSshKeys(Base, BaseDbModel): user_id = Column(Integer(), ForeignKey('users.user_id'), nullable=False) _public_key = Column('public_key', UnicodeText(), nullable=False) description = Column(UnicodeText(), nullable=False) - fingerprint = Column(String(255), nullable=False, unique=True) + fingerprint = Column(String(255), nullable=False) created_on = Column(DateTime(timezone=False), nullable=False, default=datetime.datetime.now) last_seen = Column(DateTime(timezone=False), nullable=True)
It seems Postgres and SQLite don’t mind this, but MySQL does.
Comment by Austin Collier, on 2020-05-20 21:19
Yes! It does fix it, however here are the errors that are generated (it seems to be working from the mysql db regardless though)
Are you sure to destroy old database ? [y/n]y /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_settings' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_ui' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_permissions' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_api_keys' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_email_map' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_ip_map' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users_groups' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_groups' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_to_perm' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_gists' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_ssh_keys' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users_groups_members' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_repositories' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_user_group_to_perm' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_group_user_group_to_perm' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users_group_to_perm' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_repo_group_to_perm' ignored for PRIMA RY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users_group_repo_group_to_perm' ignored fo r PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_logs' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_repositories_fields' ignored for PRIMARY k ey.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_repo_to_perm' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_users_group_repo_to_perm' ignored for PRIM ARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_statistics' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_user_followings' ignored for PRIMARY key." ) result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_pull_requests' ignored for PRIMARY key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_changeset_comments' ignored for PRIMARY ke y.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_pull_request_reviewers' ignored for PRIMAR Y key.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'pk_changeset_statuses' ignored for PRIMARY ke y.") result = self._query(query) /opt/kallithea-0.6.0/kallithea-venv/lib64/python3.6/site-packages/pymysql/cursors.py:170: Warning: (1280, "Name 'alembic_version_pkc' ignored for PRIMARY key. ") result = self._query(query)
Comment by Mads Kiilerich, on 2020-05-22 12:01
These message are just warnings and can be ignored.
Kallithea define a naming in convention in https://kallithea-scm.org/repos/kallithea/changeset/1a080d4e926e#kallitheamodelmetapy_n45 . Naming is convenient for other databases but mysql do apparently not allow it. I don’t know if it is nice or annoying that sqlalchemy warns in this way.