Re: Prepared Statement in Galera Cluster and haproxy

[Ilias Bertsimas]

Hello,

That seems to happen is the statement is prepared in one node and then you try to execute it on another possibly as you balance by least connections on HAProxy.

I would recommend to change: balance leastconn -> balance source which will make the load split between nodes more sticky and persistent.

Kind Regards,

Ilias.

On Tuesday, November 20, 2012 8:29:51 AM UTC, jack wrote:

Hello everyone,

I have following problem wit haproxy and Galera Cluster. The problem is with Prepared Statements,  I  think the
reference of the connections  is wrong which goes to the nodes.

Error is like  Unknown prepared statement handler (1163087433) given to mysqld_stmt_execute in
/opt/online/classes/db/dbTemplate/impl/mysqli/MysqliTemplate.php (205)

Is there a solution?
Use everyone Galera load balancer (GLB)  with Prepared Statement is there the same problem?

my haproxy configuration

global
        log 127.0.0.1   local0
        log 127.0.0.1   local1 notice
        maxconn 1024
        user haproxy
        group haproxy
        daemon
        stats socket /var/lib/haproxy/stats
defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 1024
        timeout connect 5000ms
        timeout client  50000ms
        timeout server  50000ms
        balance leastconn
listen galera_cluster 192.168.13.141:3306
        mode tcp
        option tcpka
        option httpchk
        server production.cluster-log-1 192.168.13.144:3306
        server production.cluster-log-2 192.168.13.145:3306
        server production.cluster-log-3 192.168.13.146:3306

Thanks

[Henrik Ingo]

"A prepared statement is specific to the session in which it was
created. If you terminate a session without deallocating a previously
prepared statement, the server deallocates it automatically. "

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

It's not just that you need to remain on the same node, HAProxy should
not disconnect you at all. But it is also surprising to claim that it
would do so, rather I would suspect your application code is
disconnecting in between. Are you sure this has worked for you without
HAProxy? Have you tested by connecting directly to one of the MySQL
nodes?

Anyway, if it is some weird HAProxy issue or something else you can't
figure out, some MySQL client libraries also support client side
prepared statements. Essentially this mean you can use the same
code/syntax, but under the hood the client will just do normal sql
statements anyway. For example in PHP, the PDO driver does this:
http://stackoverflow.com/questions/10146733/what-are-client-side-prepared-statements

henrik

henrik

> --
>
>



--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[mfres]

Hello Henrik,

thank you for your reply.
Yes, application works well on only one MySQL node without Haproxy or with Haproxy but only with one target server.

I think, I have the solution for my Problem but not sure.(Need more time to test it)
I add to configuration file of my Haproxy persist option:

 
global
        log 127.0.0.1   local0
        log 127.0.0.1   local1 notice
        maxconn 1024
        user haproxy
        group haproxy
        daemon
        stats socket /var/lib/haproxy/stats
defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        option redispatch
        maxconn 1024
        timeout connect 5000ms
        timeout client  50000ms
        timeout server  50000ms
        balance leastconn
listen galera_cluster 192.168.13.141:3306
        mode tcp
        option tcpka
        option httpchk

        option persist

        server production.cluster-log-1 192.168.13.144:3306
        server production.cluster-log-2 192.168.13.145:3306
        server production.cluster-log-3 192.168.13.146:3306

Thanks,

[Henrik Ingo]

That explains it.

Makes sense. It of course takes some resources for HAProxy to maintain
state for peristed connections. But it's really needed so worth
turning it on!

Those of you maintaining HAProxy scripts for Galera usage (Percona!)
may want to make this a default option in your configs.

henrik

[Jay Janssen]

On Nov 21, 2012, at 7:33 AM, Henrik Ingo <henri...@avoinelama.fi> wrote:

That explains it.

Makes sense. It of course takes some resources for HAProxy to maintain
state for peristed connections. But it's really needed so worth
turning it on!

Those of you maintaining HAProxy scripts for Galera usage (Percona!)
may want to make this a default option in your configs.

  I'm not convinced.  The haproxy doc refers to the 'persist' option as an HTTP option (i.e., layer 7, not layer 4).  (http://cbonte.github.com/haproxy-dconv/configuration-1.4.html#4-option persist) However, the haproxy manual also (in my reading) tends to assume you are using it for HTTP, so it's possible that persist applies somehow to 'mode TCP' and it's just not documented. 

   In TCP mode I don't believe HAproxy has any type of connection multiplexing feature set, and even if it did, the MySQL protocol does not support a Layer 4 load balancer moving existing sessions between nodes.  HAproxy is a dumb (for our purposes) Layer 4 load balancer, therefore the application needs to be smart enough to reconnect to get a currently "up" node.  

  In the case above, the prepared statement handle and/or the underlying db connection should probably be tested (and re-established) before re-using it.  That being said, I have seen weirdness with MySQL connections using HAproxy that I can't fully explain -- however, TCP tunnels should be TCP tunnels and at least in theory there should be no difference except dealing properly with the failure of that TCP connection.  

Jay Janssen, Senior MySQL Consultant, Percona Inc.

http://about.me/jay.janssen

Percona Live in London, UK Dec 3-4th: http://www.percona.com/live/london-2012/

[Ilias Bertsimas]

I do not use HAProxy so I cannot comment on the functionality and manual but in my OpenBSD's relayd I just make sure to have a source ip hash balancing of connections to make sure the same source always gets redirected to the same node.

Of course handling prepared statements in the code by checking if they exist is always essential otherwise in case of failover it will fail. 

Kind Regards,

Ilias.

[mfres]

Hello,

after few days testing in production I can say, this configuration of haproxy works well.

Thanks