Issue #324: MySQL Setup Error - "Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `group_id` (conservancy/kallithea)

Wed Jun 20 07:55:49 UTC 2018

New issue 324: MySQL Setup Error - "Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `group_id`
https://bitbucket.org/conservancy/kallithea/issues/324/mysql-setup-error-function-or-expression

Renato Rosa:

Hi Everybody.

I just installed `Kallithea` a few days ago and I have finally ended my crusade for a fully fledged git web server for personal projects. Thanks everybody for the effort in (totally) open sourcing this piece of software.

However, I am currently using SQLite and it is hanging up when under a high load, so I am trying to setup a fresh install with MySQL backend, and I am getting the following error when I run `$paster setub-db`:

```
sqlalchemy.exc.InternalError: (InternalError) (1901, u"Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `group_id`") '
CREATE TABLE groups (
    group_id INTEGER NOT NULL AUTO_INCREMENT, 
    group_name VARCHAR(255) NOT NULL, 
    group_parent_id INTEGER, 
    group_description VARCHAR(10000), 
    enable_locking BOOL NOT NULL, 
    user_id INTEGER NOT NULL, 
    created_on DATETIME NOT NULL, 
    PRIMARY KEY (group_id), 
    UNIQUE (group_name, group_parent_id), 
    CHECK (group_id != group_parent_id), 
    UNIQUE (group_id), 
    UNIQUE (group_name), 
    FOREIGN KEY(group_parent_id) REFERENCES groups (group_id), 
    CHECK (enable_locking IN (0, 1)), 
    FOREIGN KEY(user_id) REFERENCES users (user_id)
)ENGINE=InnoDB CHARSET=utf8
' {}
```

Actually, before the above error, I get the following Traceback:

```

2018-06-20 04:37:09,368 INFO sqlalchemy.engine.base.Engine {}
2018-06-20 04:37:09,392 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "/usr/local/bin/paster", line 11, in <module>
    sys.exit(run())
  File "/usr/local/lib/python2.7/dist-packages/paste/script/command.py", line 102, in run
    invoke(command, command_name, options, args[1:])
  File "/usr/local/lib/python2.7/dist-packages/paste/script/command.py", line 141, in invoke
    exit_code = runner.run(args)
  File "/usr/local/lib/python2.7/dist-packages/paste/script/appinstall.py", line 66, in run
    return super(AbstractInstallCommand, self).run(new_args)
  File "/usr/local/lib/python2.7/dist-packages/paste/script/command.py", line 236, in run
    result = self.command()
  File "/usr/local/lib/python2.7/dist-packages/kallithea/lib/paster_commands/setup_db.py", line 108, in command
    self, config_file, section, self.sysconfig_install_vars(installer))
  File "/usr/local/lib/python2.7/dist-packages/paste/script/appinstall.py", line 595, in setup_config
    mod.setup_app, command, filename, section, vars)
  File "/usr/local/lib/python2.7/dist-packages/paste/script/appinstall.py", line 611, in _call_setup_app
    func(command, conf, vars)
  File "/usr/local/lib/python2.7/dist-packages/kallithea/websetup.py", line 43, in setup_app
    dbmanage.create_tables(override=True)
  File "/usr/local/lib/python2.7/dist-packages/kallithea/lib/db_manage.py", line 105, in create_tables
    Base.metadata.create_all(checkfirst=checkfirst)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 2590, in create_all
    tables=tables)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 2302, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1972, in _run_visitor
    **kwargs).traverse_single(element)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 67, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 106, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/ddl.py", line 86, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1542, in _execute_ddl
    compiled
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 893, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1103, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1396, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 1059, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python2.7/dist-packages/pymysql/connections.py", line 384, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python2.7/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
```

I installed `Kallithea 0.3.5` with `pip2 install kallithea` on Ubuntu 16.04 LTS (xenial), x86_64 and am trying to run it with `MariaDB 10.2.15`.

I have little experience with Python and I even tried to understand the DB schema, having located the groups table function under `kallithea/lib/dbmigrate/schema`, but it seems to be ok, as it runs just fine with SQLite.

I also tried running `grate migrate kallithea.model.meta:Base.metadata "sqlite:///kallithea.db" "mysql+pymysql://localhost/kallithea"`, to no avail. 

I consider this a bug, as MySQL is supposed to be supported, but apart from setup instructions missing crucial information (there is already an issue filed for that), it seems that setup-db is trying an illegal SQL statement.

I would really appreciate if anybody could point me in the right direction here.

Kind regards,

Renato.