0.6.0 create MySQL database fails in SQLAlchemy

Issue #374 resolved
Austin Collier created an issue

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.

Comments (4)

  1. Thomas De Schampheleire

    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.

  2. Austin Collier reporter

    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)
    

  3. Log in to comment