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@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.
_______________________________________________
kallithea-general mailing list
[email protected]
https://lists.sfconservancy.org/mailman/listinfo/kallithea-general
