[Bug 1889752] [NEW] devstack fails for duplicated check constraint name in gnocchi

Yasufumi Ogawa 1889752 at bugs.launchpad.net
Fri Jul 31 09:55:59 UTC 2020 

Public bug reported:

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)

** Affects: ceilometer (Ubuntu)
     Importance: Undecided
         Status: New

-- 
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