[Bug 1889752] Re: devstack fails for duplicated check constraint name in gnocchi
Yoshito Ito
1889752 at bugs.launchpad.net
Tue Aug 18 00:10:37 UTC 2020
I'm also facing the same issue.
It seems a bug in gnocchi related to compatibility with MySQL 8. In my
environment, MySQL version is "5.7.31-0ubuntu0.18.04.1" in Ubuntu1804
and "8.0.21-0ubuntu0.20.04.4" in Ubuntu2004.
The duplicated name of check constraint causes the error in the
following code.
*
https://github.com/gnocchixyz/gnocchi/blob/master/gnocchi/indexer/alembic/versions/40c6aae14c3f_ck_started_before_ended.py
In the MySQL 8 document, it's clearly noted that CHECK constraint names
must be unique per schema; no two tables in the same schema can share a
CHECK constraint name.
* https://dev.mysql.com/doc/refman/8.0/en/create-table-check-
constraints.html
To fix this issue, I believe we need to change the name of check
constraint to be unique as the following change. It also tries to fix
this issue but it's not merged in the github repository (not sure why).
* https://launchpad.net/ubuntu/+source/gnocchi/+changelog
* http://launchpadlibrarian.net/444917380/gnocchi_4.3.4-0ubuntu2_4.3.4-0ubuntu3.diff.gz
--
You received this bug notification because you are a member of Ubuntu
OpenStack, which is subscribed to ceilometer in Ubuntu.
https://bugs.launchpad.net/bugs/1889752
Title:
devstack fails for duplicated check constraint name in gnocchi
Status in ceilometer package in Ubuntu:
New
Bug description:
I've tried to install openstack/tacker on Ubuntu20.04, but failed
because for gnocchi possibly. Tacker's monitoring depends on
ceilometer and it uses gnocchi. On Ubuntu20.04, devstack installation
fails for duplicated check constraint name in `gnocchi-upgrade` which
is an initialization command for gnocchi. It doesn't happen on
Ubuntu18.04.
In my install log below, it says "Duplicate check constraint name
'ck_started_before_ended'.". Although I don't know how `gnocchi-
upgrade` works so much, it seems the schema is defined in [2]. The
same symbol `ck_started_before_ended` is used for two tables,
`resource` and `resource_history`, and failed to create second one.
I used the latest devstack (commit
647fef0b405deea635a710c124d508a59e6d1119) and tacker(commit
896d03804def46b1365c92a8040995ca571983f2). `local.conf` is for all-in-
one mode with Kubernetes described in [3].
Although I'm not sure which of ceilometer or gnocchi should be fixed
actually, I appreciate if you give me some advice. Thanks.
[1] https://wiki.openstack.org/wiki/Gnocchi
[2] https://github.com/gnocchixyz/gnocchi/blob/master/gnocchi/indexer/alembic/versions/40c6aae14c3f_ck_started_before_ended.py
[3] https://docs.openstack.org/tacker/latest/install/devstack.html
---------------------------------------------- Here is my log ----------------------------------------
2020-07-30 14:27:10,341 [143612] INFO gnocchi.cli.manage: Upgrading indexer SQLAlchemyIndexer: mysql+pymysql://***:***@127.0.0.1/gnocchi?charset=utf8
2020-07-30 14:27:10,351 [143612] WARNING py.warnings: /usr/local/lib/python3.8/dist-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
result = self._query(query)
2020-07-30 14:27:10,385 [143612] WARNING oslo_db.sqlalchemy.exc_filters: DBAPIError exception wrapped.
Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 684, in _read_packet
packet.check_error()
File "/usr/local/lib/python3.8/dist-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.8/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (3822, "Duplicate check constraint name 'ck_started_before_ended'.")
2020-07-30 14:27:10,392 [143612] CRITICAL root: Traceback (most recent call last):
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 684, in _read_packet
packet.check_error()
File "/usr/local/lib/python3.8/dist-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.8/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.InternalError: (3822, "Duplicate check constraint name 'ck_started_before_ended'.")
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/gnocchi-upgrade", line 11, in <module>
load_entry_point('gnocchi', 'console_scripts', 'gnocchi-upgrade')()
File "/opt/stack/devstack/src/gnocchi/gnocchi/cli/manage.py", line 69, in upgrade
index.upgrade()
File "/opt/stack/devstack/src/gnocchi/gnocchi/indexer/sqlalchemy.py", line 334, in upgrade
Base.metadata.create_all(connection)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/schema.py", line 4546, in create_all
bind._run_visitor(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1657, in _run_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 783, in visit_metadata
self.traverse_single(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/visitors.py", line 144, in traverse_single
return meth(obj, **kw)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 827, in visit_table
self.connection.execute(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1014, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1071, in _execute_ddl
ret = self._execute_context(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
self._handle_dbapi_exception(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1509, in _handle_dbapi_exception
util.raise_(newraise, with_traceback=exc_info[2], from_=e)
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
self.dialect.do_execute(
File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 593, in do_execute
cursor.execute(statement, parameters)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 732, in _read_query_result
result.read()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 684, in _read_packet
packet.check_error()
File "/usr/local/lib/python3.8/dist-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.8/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
oslo_db.exception.DBError: (pymysql.err.InternalError) (3822, "Duplicate check constraint name 'ck_started_before_ended'.")
[SQL:
CREATE TABLE resource_history (
creator VARCHAR(255),
started_at DATETIME(6) NOT NULL,
revision_start DATETIME(6) NOT NULL,
ended_at DATETIME(6),
user_id VARCHAR(255),
project_id VARCHAR(255),
original_resource_id VARCHAR(255) NOT NULL,
revision INTEGER NOT NULL AUTO_INCREMENT,
id BINARY(16) NOT NULL,
revision_end DATETIME(6) NOT NULL,
type VARCHAR(255) NOT NULL,
PRIMARY KEY (revision),
CONSTRAINT ck_started_before_ended CHECK (started_at <= ended_at),
CONSTRAINT fk_rh_id_resource_id FOREIGN KEY(id) REFERENCES resource (id) ON DELETE CASCADE,
CONSTRAINT fk_resource_history_resource_type_name FOREIGN KEY(type) REFERENCES resource_type (name) ON DELETE RESTRICT
)CHARSET=utf8 ENGINE=InnoDB
]
(Background on this error at: http://sqlalche.me/e/13/2j85)
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/ceilometer/+bug/1889752/+subscriptions
More information about the Ubuntu-openstack-bugs
mailing list