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 <sla...@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 > sla...@kaplonski.pl > _______________________________________________ > OpenStack-operators mailing list > OpenStack-operators@lists.openstack.org > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-operators > >
_______________________________________________ OpenStack-operators mailing list OpenStack-operators@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-operators
