Open Stack

Hello,

I'm using MySQL (Percona) and Galera with HAProxy and have previously run
into the same issues you describe.

The first thing to mention is "SELECT ... FOR UPDATE" queries do not work
well with multi-master environments. This was discovered during discussion
at the OpenStack Atlanta Summit. A recent write-up about it can be found
here
<http://www.percona.com/blog/2014/09/11/openstack-users-shed-light-on-percona-xtradb-cluster-deadlock-issues/>
.

To resolve this issue, you'll need to configure HAProxy to only send writes
to one MySQL server. I do this by having all but one MySQL node configured
as a "backup".

For the "gone away" errors, I have been able to resolve this by removing
any sort of timeout option from the MySQL nodes in HAProxy.

Here's what my MySQL config looks like in HAProxy:

listen mysql-3306
  bind 192.168.1.1:3306
  mode  tcp
  balance  leastconn
  option  httpchk
  server mysql-1 192.168.1.2:3306  check port 9200 inter 12000 rise 3 fall 3
  server mysql-2 192.168.1.3:3306  check port 9200 inter 12000 rise 3 fall
3 backup

Note that port 9200 is configured with this script
<https://github.com/olafz/percona-clustercheck> to check the cluster status.

Hope that helps,
Joe

On Thu, Sep 18, 2014 at 10:29 AM, Sławek Kapłoński <slawek at kaplonski.pl>
wrote:

> Hello,
>
> Is anyone here using openstack with mysql galera and haproxy? Have You got
> any
> problems with that?
> I was today installed such ha infra for database (two mysql servers in
> galera
> cluster and haproxy on controller and neutron node, this haproxy is
> connecting
> to one of galera servers with round robin algorithm). Generally all is
> working
> fine but I have few problems:
> 1. I have a lot of messages like:
> WARNING neutron.openstack.common.db.sqlalchemy.session [-] Got mysql server
> has gone away: (2006, 'MySQL server has gone away')
> 2. I have (most on neutron) many errors like:
> OperationalError: (OperationalError) (2013, 'Lost connection to MySQL
> server
> during query') 'UPDATE ml2_port_bindings SET vif_type=%s, driver=%s,
> segment=%s WHERE ml2_port_bindings.port_id =
> 3. Also errors:
> StaleDataError: UPDATE statement on table 'ports' expected to update 1
> row(s);
> 0 were matched.
> 4. and errors:
> DBDeadlock: (OperationalError) (1213, 'Deadlock found when trying to get
> lock;
> try restarting transaction') 'UPDATE ipavailabilityranges SET first_ip=%s
> WHERE
> ipavailabilityranges.allocation_pool_id =
>
> Sql queries in examples are "accidental" and same problem is with other
> queries also (like deleting ports).
> Strange think is that those problems are not happend when I have one mysql
> server and all was connecting to that one server. Do You have maybe same
> problems? Do You know what can be a reason and solution for it?
>
> ---
> Best regards
> Sławek Kapłoński
> slawek at kaplonski.pl
> _______________________________________________
> OpenStack-operators mailing list
> OpenStack-operators at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-operators
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-operators/attachments/20140918/2cc106a4/attachment.html>