Issue #324: MySQL Setup Error - "Function or expression 'AUTO_INCREMENT' cannot be used in the CHECK clause of `group_id`
| Reported by: | Renato Rosa |
| State: | resolved |
| Created on: | 2018-06-20 07:55 |
| Updated on: | 2020-06-18 20:14 |
Description
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.
Attachments
Comments
Comment by Thomas De Schampheleire, on 2018-06-20 09:56
Hello and welcome to Kallithea!
Are you running 'paster setup-db' on a clean empty database?
The schema definition is actually in kallithea/model/db.py, see class RepoGroup.
You may want to try using the 'default' branch instead of the latest release 0.3.5. There have been many changes, and it seems that this schema definition is also different. I'm not sure though if your problem will be solved, but it is worth trying.
We are planning on making an official release with that 'soon' but have some blocking points to resolve first. Nevertheless, that version of Kallithea is stable too and can be used in production without issue. Instead of using 'pip install Kallithea', you would clone the repository and work from that. See https://kallithea.readthedocs.io/en/default/index.html .
Comment by Renato Rosa, on 2018-06-22 22:40
I downloaded the master branch and I followed the installation and the setup instructions and I still got the same error.
Maybe it has something to do with AUTO_INCREMENT behavior in SQL Alchemy.
Comment by Mads Kiilerich, on 2018-06-24 11:58
I guess this is related to the "my"sql version you are using. They might have "fixed" some "undefined behaviour" we relied on.
I agree CHECK (group_id != group_parent_id) is odd when group_id is assigned values automatically. The check is probably mainly intended to apply when group_parent_id is changed, to avoid loops. The check will however still at the db level allow loops that use an intermediate group. We thus have to rely on more high level checks in application code.
As a workaround, can you try removing the db.py line with the group_parent_id check, also to see if you will encounter other problems later?
Comment by Renato Rosa, on 2018-06-27 00:12
The workaround did work.
There were two lines that had a CheckConstraint call. I commented out both of them and it did work:
diff -r e71216a16853 kallithea/model/db.py --- a/kallithea/model/db.py Wed May 16 21:20:40 2018 +0200 +++ b/kallithea/model/db.py Tue Jun 26 21:13:37 2018 -0300 @@ -1525,7 +1525,7 @@ class RepoGroup(Base, BaseDbModel): __tablename__ = 'groups' __table_args__ = ( - CheckConstraint('group_id != group_parent_id', name='ck_groups_no_self_parent'), + #CheckConstraint('group_id != group_parent_id', name='ck_groups_no_self_parent'), _table_args_default_dict, ) __mapper_args__ = {'order_by': 'group_name'} # TODO: Deprecated as of SQLAlchemy 1.1. @@ -1949,7 +1949,7 @@ __tablename__ = 'user_group_user_group_to_perm' __table_args__ = ( UniqueConstraint('target_user_group_id', 'user_group_id', 'permission_id'), - CheckConstraint('target_user_group_id != user_group_id', name='ck_user_group_user_group_to_perm_no_self_target'), +# CheckConstraint('target_user_group_id != user_group_id', name='ck_user_group_user_group_to_perm_no_self_target'), _table_args_default_dict, )
As I don't need a production-like environment, I will continue with this solution.
Thank you.
Comment by Mads Kiilerich, on 2018-07-01 23:58
https://jira.mariadb.org/browse/MDEV-13320 and https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html suggest that it is a "regression" in MariaDB 10.2 / MySQL 5.7.