MySQL server has gone away

[Alinux]

Dear Sirs
I have a 3 node Galera cluster running "latest version", and when I
connect to the server using mysql locally I occasionally get the
following error when trying to execute a query

ERROR 2006 (HY000): MySQL server has gone away No connection. Trying
to reconnect...

The error log shows nothing "this happened just now, and the latest
entry to the error log was yesterday"

Thanks

[Alinux]

Please see mysql> SHOW VARIABLES LIKE '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+----------------------------+----------+

Another thing to note that on the HAProxy interface "I am getting the
error without HAProxy as well" I am getting the following values for
uptime column
Node1 8m56s UP
Node2 36m51s UP
Node3 16m24s UP

Is this normal shouldn't those values be much higher ? HAProxy
monitors DB availability by executing mysql queries. So this might be
related.

Regards

[seppo....@codership.com]

Probably HAProxy gets similar connectivity issues and the UP time
values are calculated from latest successful connection. However,
HAProxy will use connections over network and network issues can also
explain at least part of the problem. But it looks like the client
connectivity has some problems in your setup, either local or network
related.

Galera should not interfere with client connection establishing. But I
need to run some connectivity scenario tests to rule it out. For this,
some characteristics of your cluster and SQL load could help e.g.:
* do you run SQL load for to master only or to all nodes?
* is CPU load high?
* Do you have DDL statetments (CREATE/DROP/ALTER...) in the load ?

-seppo

[Ali Jawad]

Hi
Just to clarify, HAproxy was a later on conclusion, I first discovered it on Node1 of the cluster while on the node and running msyql -uroot -p ..and then use database and then

 SELECT count( `cc_card` ) , SUM( `sessiontime` ) , SUM( `sessionbill` ) , SUM( `buycost` ) , SUM( `buyrate` ) , SUM( `calledRate` ) FROM `cdrs_2011-8` GROUP BY `cc_card` ORDER BY `trunkcode` DESC LIMIT 0 , 30;

So I am not sure that this is a network connectivity problem, since I face the issue on localhost. Neverless the answer to your questions is :

1- All nodes are SQL loaded
2- Not at all quadro core - 16GB with low load
3- No, basically the statement for HAproxy is show databases,and the user of the only active application in use right now only has select,delete,update,insert GRANTs.

Thanks

--
You received this message because you are subscribed to the Google Groups "codership" group.
To post to this group, send email to codership-team@googlegroups.com.
To unsubscribe from this group, send email to codership-team+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/codership-team?hl=en.

[Alex Yurchenko]

On 2012-01-17 13:04, Ali Jawad wrote:
> Hi
> Just to clarify, HAproxy was a later on conclusion, I first
> discovered it
> on Node1 of the cluster while on the node and running msyql -uroot -p
> ..and
> then use database and then
>
> SELECT count( `cc_card` ) , SUM( `sessiontime` ) , SUM(
> `sessionbill` ) ,
> SUM( `buycost` ) , SUM( `buyrate` ) , SUM( `calledRate` ) FROM
> `cdrs_2011-8` GROUP BY `cc_card` ORDER BY `trunkcode` DESC LIMIT 0 ,
> 30;
>
> So I am not sure that this is a network connectivity problem, since I
> face
> the issue on localhost. Neverless the answer to your questions is :
>
> 1- All nodes are SQL loaded
> 2- Not at all quadro core - 16GB with low load
> 3- No, basically the statement for HAproxy is show databases,and the
> user
> of the only active application in use right now only has
> select,delete,update,insert GRANTs.

How often does HAproxy query the server?

Is it possible to change HAproxy query to something not accessing the
database, like SELECT 0; or something? Could you try it with that?

Regards,
Alex