[Bug 1826875] Re: [stein] 'Percona-XtraDB-Cluster prohibits use of DML command on a table (octavia.spares_pool) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER')

[Dongwon Cho]

Hi,

I had this issue on my test Env as well, applied the fix with following
option and confirmed it fixes the issue.

openstack-origin: cloud:bionic-stein/proposed

-- 
You received this bug notification because you are a member of Ubuntu
OpenStack, which is subscribed to Ubuntu Cloud Archive.
https://bugs.launchpad.net/bugs/1826875

Title:
  [stein] 'Percona-XtraDB-Cluster prohibits use of DML command on a
  table (octavia.spares_pool) without an explicit primary key with
  pxc_strict_mode = ENFORCING or MASTER')

Status in Ubuntu Cloud Archive:
  Fix Committed
Status in Ubuntu Cloud Archive stein series:
  Fix Committed
Status in octavia:
  New
Status in octavia package in Ubuntu:
  Fix Released
Status in octavia source package in Disco:
  Fix Committed
Status in octavia source package in Eoan:
  Fix Released

Bug description:
  Installed Octavia via charm-octavia using bionic-stein.

  When the charm tried to execute a database migration

  sudo octavia-db-manage upgrade head

  it failed with the following error:

  2019-04-29 11:49:20.096 22569 ERROR octavia-db-manage
  sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1105,
  'Percona-XtraDB-Cluster prohibits use of DML command on a table
  (octavia.spares_pool) without an explicit primary key with
  pxc_strict_mode = ENFORCING or MASTER') [SQL: 'INSERT INTO spares_pool
  (updated_at) VALUES (%(updated_at)s)'] [parameters: {'updated_at':
  datetime.datetime(2019, 4, 29, 11, 49, 20, 94405)}] (Background on
  this error at: http://sqlalche.me/e/2j85)

  It looks like the spares_pool table doesn't have a primary key.

  mysql> DESCRIBE spares_pool;
  +------------+----------+------+-----+-------------------+-------+
  | Field      | Type     | Null | Key | Default           | Extra |
  +------------+----------+------+-----+-------------------+-------+
  | updated_at | datetime | YES  |     | CURRENT_TIMESTAMP |       |
  +------------+----------+------+-----+-------------------+-------+
  1 row in set (0.00 sec)

  mysql> show variables like 'pxc_strict_mode';
  +-----------------+-----------+
  | Variable_name   | Value     |
  +-----------------+-----------+
  | pxc_strict_mode | ENFORCING |
  +-----------------+-----------+
  1 row in set (0.00 sec)

  This spares_pool table was added quite recently (in Stein):

  git --no-pager log -S spares_pool | grep -v Author
  commit 0b468090e6eef1483f6f7c53708f500802a57d51
  Date:   Tue Apr 2 18:05:52 2019 +0200

      Fix spare amphora check and creation

      This patch fixes an issue when the SparesPool table is empty that blocks
      spare amphorae creation. It creates a new spares pool entry if the table
      is empty.

      Story 2005352
      Task 30306

      Change-Id: I0ce2778277640ee9e509c709bf8621b8b025d6d3

  commit a205ab3ebe4ae14668f25328c062f9383d6c69cb
  Date:   Mon Mar 11 17:06:15 2019 -0700

      Limit spares pool to the spare_amphora_pool_size

      This patch fixes a bug where the housekeeping controllers may launch
      more spare amphora than the spare_amphora_pool_size setting.

      Story: 2003094
      Task 23186

      Change-Id: I4c98b3442d2471662488184fa7e91ac64ec33279

  ➜  octavia git:(master) git --no-pager branch --contains=a205ab3ebe4ae14668f25328c062f9383d6c69cb
  * master
    stable/stein

  
  Upstream bug: https://storyboard.openstack.org/#!/story/2005531

To manage notifications about this bug go to:
https://bugs.launchpad.net/cloud-archive/+bug/1826875/+subscriptions