Issue #374: 0.6.0 create MySQL database fails in SQLAlchemy (conservancy/kallithea)
austin_collier
issues-reply at bitbucket.org
Wed May 20 15:38:43 UTC 2020
New issue 374: 0.6.0 create MySQL database fails in SQLAlchemy
https://bitbucket.org/conservancy/kallithea/issues/374/060-create-mysql-database-fails-in
Austin Collier:
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 at 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.
More information about the kallithea-general
mailing list