We can reach DBDuplicateEntry error in Galera cluster when node enters or leaves a cluster

[Denis Klepikov]

During a high number of API requests to provision/terminate the instances MYSQL will sometime reply with 1062 due to primary key being already occupied.
ERROR nova.api.openstack.extensions DBDuplicateEntry: (_mysql_exceptions.IntegrityError) (1062, "Duplicate entry 'XXXXX' for key 'PRIMARY'")

Collision of auto generated integer PKs can happen, when a Galera node enters or leaves a cluster.

System impact:
OpenStack commands will fail or get stuck in execution. All stucked instances should be resetted and deleted manually.

[Jay Pipes (jaypipes)]

Can someone please show me the evidence that "OpenStack commands will ... get stuck in execution"? I can see that potentially a single API call might return a failure, but that's not the same as things getting stuck in execution.

[Denis Klepikov (dklepikov)]

I have additional information.
MySQL-wsrep 5.6.35 released.
Release nodes contain information about fixing:
INSERT operations on a table with an auto_increment key could result in a duplicate key error (Bug #76872) https://bugs.mysql.com/bug.php?id=76872

http://releases.galeracluster.com/release-notes-mysql-wsrep-5.6.35-25.19.txt

[Oleksiy Molchanov (omolchanov)]

Linux team, it seems you should update mysql version to at least 5.6.35

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/428723
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=f1de309d583ced7b4dfd36c0ac67ef5802edff90
Submitter: Jenkins
Branch: stable/mitaka

commit f1de309d583ced7b4dfd36c0ac67ef5802edff90
Author: Ivan Suzdal <email address hidden>
Date: Fri Feb 3 12:26:06 2017 +0300

    Remove log-error mysqld_safe option

    According to [1], if file name has no extension,
    the server adds an extension of ".err".

    It causes two different issues.

    The first one is impossibility to create /dev/stdout.err file
    in newer versions of mysql (5.6.35 at least).

    The second one is a potential OOM in case of intensive stderr flow from mysql.
    Because the /dev mount point is a devtmpfs which is in-memory pseudo file system.

    [1] https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-error

    Related-Bug: #1657707

    Change-Id: I430d8dd085433e8cb84fdf0595b8357758d9ea75

[Fuel Devops McRobotson (fuel-devops-robot)]

Fix proposed to branch: 10.0/newton
Change author: Ivan Suzdal <email address hidden>
Review: https://review.fuel-infra.org/30617

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/428708
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=d72354db6a7ec145fe933b9077034705f30e75bb
Submitter: Jenkins
Branch: stable/newton

commit d72354db6a7ec145fe933b9077034705f30e75bb
Author: Ivan Suzdal <email address hidden>
Date: Fri Feb 3 12:26:06 2017 +0300

    Remove log-error mysqld_safe option

    According to [1], if file name has no extension,
    the server adds an extension of ".err".

    It causes two different issues.

    The first one is impossibility to create /dev/stdout.err file
    in newer versions of mysql (5.6.35 at least).

    The second one is a potential OOM in case of intensive stderr flow from mysql.
    Because the /dev mount point is a devtmpfs which is in-memory pseudo file system.

    [1] https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-error

    Related-Bug: #1657707

    Change-Id: I430d8dd085433e8cb84fdf0595b8357758d9ea75

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/30169
Submitter: Pkgs Jenkins <email address hidden>
Branch: 9.0

Commit: f9f69935f73237815b920a5495b528fcc93d902e
Author: Ivan Suzdal <email address hidden>
Date: Wed Feb 8 13:06:28 2017

New version of mysql-wsrep (5.6.35)

Closes-Bug: #1657707

Change-Id: Ib3b160ff533203bd70bb883254568ea3546a7746

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/30170
Submitter: Pkgs Jenkins <email address hidden>
Branch: master

Commit: 52f0aa958369441649725b8308af7db1af47c068
Author: Ivan Suzdal <email address hidden>
Date: Tue Feb 7 13:04:28 2017

New version of mysql-wsrep (5.6.35)

Closes-Bug: #1657707

Change-Id: Ia4c84106de15fb406cc460cda645d905f214b9ab

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/30617
Submitter: Pkgs Jenkins <email address hidden>
Branch: 10.0/newton

Commit: 0cb9c858994cf0098284a3e0e5d0436c1c12e61d
Author: Ivan Suzdal <email address hidden>
Date: Wed Feb 8 13:14:55 2017

New version of mysql-wsrep (5.6.35)

Closes-Bug: #1657707

Change-Id: Ia4c84106de15fb406cc460cda645d905f214b9ab

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/7.0
Review: https://review.openstack.org/436461

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/436461
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=10bb7df324a15a7fa1ee23239c024cd0c04292e1
Submitter: Jenkins
Branch: stable/7.0

commit 10bb7df324a15a7fa1ee23239c024cd0c04292e1
Author: Ivan Suzdal <email address hidden>
Date: Fri Feb 3 12:26:06 2017 +0300

    Remove log-error mysqld_safe option

    According to [1], if file name has no extension,
    the server adds an extension of ".err".

    It causes two different issues.

    The first one is impossibility to create /dev/stdout.err file
    in newer versions of mysql (5.6.35 at least).

    The second one is a potential OOM in case of intensive stderr
    flow from mysql. Because the /dev mount point is a devtmpfs which
    is in-memory pseudo file system.

    [1] https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-error

    Related-Bug: #1657707

    Change-Id: I430d8dd085433e8cb84fdf0595b8357758d9ea75
    (cherry picked from commit d72354db6a7ec145fe933b9077034705f30e75bb)

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/30997
Submitter: Pkgs Jenkins <email address hidden>
Branch: 7.0

Commit: b4756532829c8d8f8f47e87dd16c172920609327
Author: Denis V. Meltsaykin <email address hidden>
Date: Tue Feb 21 15:22:23 2017

MySQL wsrep updated to 5.6.35

cherry-picked from: f9f69935f73237815b920a5495b528fcc93d902e

Change-Id: I2fe3d6fb8fb1c1fd6392e509e298a1a9eb4772cd
Closes-Bug: #1648738
Closes-Bug: #1657707

[TatyanaGladysheva (tgladysheva)]

Verified on MOS 7.0 + MU7 updates.

Actual results:
OSTF tests are passed for cluster 3 controllers + 2 compute+cinder nodes.

root@node-1:~# dpkg -l | grep mysql-
ii mysql-client-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database client binaries
ii mysql-server-wsrep-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database wsrep server binaries and system database setup
ii mysql-wsrep-common-5.6 5.6.35-0~u14.04+mos1 all MySQL database common files, e.g. /etc/mysql/conf.d/my-5.6.cnf
ii mysql-wsrep-libmysqlclient18:amd64 5.6.35-0~u14.04+mos1 amd64 MySQL database client library

[TatyanaGladysheva (tgladysheva)]

Verified on MOS 9.2 + MU1 updates.

Actual results:
OSTF tests are passed for cluster 3 controllers + 2 compute+cinder nodes.

root@node-1:~# dpkg -l | grep mysql-
ii mysql-client-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database client binaries
ii mysql-server-wsrep-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database wsrep server binaries and system database setup
ii mysql-wsrep-common-5.6 5.6.35-0~u14.04+mos1 all MySQL database common files, e.g. /etc/mysql/conf.d/my-5.6.cnf
ii mysql-wsrep-libmysqlclient18:amd64 5.6.35-0~u14.04+mos1 amd64 MySQL database client library

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/8.0
Review: https://review.openstack.org/451380

[Fuel Devops McRobotson (fuel-devops-robot)]

Fix proposed to branch: 8.0
Change author: Ivan Suzdal <email address hidden>
Review: https://review.fuel-infra.org/32632

[TatyanaGladysheva (tgladysheva)]

Verified on 10.0 build #1569.

Actual results:
OSTF tests are passed for cluster 3 controllers + 2 compute+cinder nodes.

root@node-1:~# dpkg -l | grep mysql-wsrep
ii mysql-wsrep-client-5.6 5.6.35-0~u16.04+mos1 amd64 MySQL database client binaries
ii mysql-wsrep-common-5.6 5.6.35-0~u16.04+mos1 all MySQL database common files, e.g. /etc/mysql/conf.d/my-5.6.cnf
ii mysql-wsrep-libmysqlclient18:amd64 5.6.35-0~u16.04+mos1 amd64 MySQL database client library
ii mysql-wsrep-server-5.6 5.6.35-0~u16.04+mos1 amd64 MySQL database wsrep server binaries and system database setup

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/451380
Committed: https://git.openstack.org/cgit/openstack/fuel-library/commit/?id=1185924083451cc7a47101d3e8bc07f6a9128587
Submitter: Jenkins
Branch: stable/8.0

commit 1185924083451cc7a47101d3e8bc07f6a9128587
Author: Ivan Suzdal <email address hidden>
Date: Fri Feb 3 12:26:06 2017 +0300

    Remove log-error mysqld_safe option

    According to [1], if file name has no extension,
    the server adds an extension of ".err".

    It causes two different issues.

    The first one is impossibility to create /dev/stdout.err file
    in newer versions of mysql (5.6.35 at least).

    The second one is a potential OOM in case of intensive stderr flow from mysql.
    Because the /dev mount point is a devtmpfs which is in-memory pseudo file system.

    [1] https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_log-error

    Related-Bug: #1657707

    Change-Id: I430d8dd085433e8cb84fdf0595b8357758d9ea75
    (cherry picked from commit f1de309d583ced7b4dfd36c0ac67ef5802edff90)

[Fuel Devops McRobotson (fuel-devops-robot)]

Reviewed: https://review.fuel-infra.org/32632
Submitter: Pkgs Jenkins <email address hidden>
Branch: 8.0

Commit: fd146e277c77a3e077d61bca14abeec5d7c66b88
Author: Sergii Rizvan <email address hidden>
Date: Wed Mar 29 16:46:03 2017

MySQL wsrep updated to 5.6.35

Change-Id: I9573f5f182c89b7d70278ddcba636c1f4f800af7
Closes-Bug: #1657707

[TatyanaGladysheva (tgladysheva)]

Verified on 8.0 + MU4 updates.

Actual results:
OSTF tests are passed for cluster 3 controllers + 2 compute+cinder nodes.

root@node-1:~# dpkg -l | grep mysql | grep 5.6.35
ii mysql-client-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database client binaries
ii mysql-server-wsrep-5.6 5.6.35-0~u14.04+mos1 amd64 MySQL database wsrep server binaries and system database setup
ii mysql-wsrep-common-5.6 5.6.35-0~u14.04+mos1 all MySQL database common files, e.g. /etc/mysql/conf.d/my-5.6.cnf
ii mysql-wsrep-libmysqlclient18:amd64 5.6.35-0~u14.04+mos1 amd64 MySQL database client library