5

MySQL throwing Broken Pipe and MySQL Server has gone away errors. My database table contains text fields where I insert large HTML in every 1 hour periodically.

Which MySQL variable should i consider to set.

Here is current configuration:

[mysqld]
datadir=/home/monk/mysql
socket=/home/monk/mysql/mysql.sock
user=mysql
default_time_zone='+06:00'
innodb_buffer_pool_size=35G
innodb_buffer_pool_instances=15
query_cache_type=ON
query_cache_size=64M
join_buffer_size=524288
tmp_table_size=32M
max_heap_table_size=32M  


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=1G
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

wait_timeout = 2000
interactive_timeout = 2000



#bind-address=127.0.0.1
##skip-networking~
pogibas
  • 27,303
  • 19
  • 84
  • 117
Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
  • Interesting, I had a similar problem and set max_allowed_packet to 64M to fix it but you have it set to 1G! How big is that HTML anyway? You can double check the value of max_allowed_packet in your mysql session with `SHOW VARIABLES LIKE 'max_allowed_packet';` – Clay Jan 01 '16 at 05:39
  • HTML is not more than 25Kb in size. – Abhishek Ginani Jan 01 '16 at 05:42
  • Every time I have to restart MySQL to resolve this error but it comes after 2-3 days again. – Abhishek Ginani Jan 01 '16 at 05:42
  • I would verify the max_allowed_packet in your connection's session, and also do a `ps aux | grep mysql` to verify you are looking at the right my.cnf file (I believe mysqld checks the directory defined in the `--basedir` parameter). – Clay Jan 01 '16 at 05:50
  • @Clayton: Show variable returns this output `1073741824` for max_allowed_packet. – Abhishek Ginani Jan 01 '16 at 05:52
  • ps aux | grep mysql output : `--basedir=/usr --user=mysql` – Abhishek Ginani Jan 01 '16 at 05:54
  • the my.cnf you are looking at is at `/usr/my.cnf` ?.. or where is the location of the my.cnf you posted? – Clay Jan 01 '16 at 06:01
  • Location of config I posted : /etc/my.cnf – Abhishek Ginani Jan 01 '16 at 06:03
  • is there a my.cnf anywhere in /usr?? – Clay Jan 01 '16 at 06:05
  • actually, check this link to get an idea of where my.cnf could be located: http://stackoverflow.com/a/2485758/747678 – Clay Jan 01 '16 at 06:06
  • I also could be totally wrong about it reading the incorrect my.cnf file but just trying to understand how max_allowed_packet is set to a high number yet you mysql is going away. – Clay Jan 01 '16 at 06:07
  • mysql config only located at :`/etc/my.cnf` – Abhishek Ginani Jan 01 '16 at 06:09
  • crap, well, I'm out of ideas! Have you tried to adjust the timeout values as suggested here? http://stackoverflow.com/a/7942309/747678 – Clay Jan 01 '16 at 06:12
  • Already set this value to 2000 . – Abhishek Ginani Jan 01 '16 at 06:13
  • are you using extended inserts or simple inserts? – Clay Jan 01 '16 at 06:20
  • I am using extended insert statements, which may contains upto 20 value sets . – Abhishek Ginani Jan 01 '16 at 06:22
  • if you change to simple inserts does it fix it? yeah, it'll be slightly slower but since max_allowed_packet looks okay looking for a viable workaround. Are you using the latest version of mysql? – Clay Jan 01 '16 at 06:23
  • Yeah... It may resolve the issue but this thing not limited to the inserts , I am also receiving errors in selects statements of this data and some times MySQL dynamic SQL queries also fails in which I am using session variables to store temporary query – Abhishek Ginani Jan 01 '16 at 06:28
  • What version of mysql are you using? – Clay Jan 01 '16 at 06:35
  • 5.6.x? ..? Perhaps do an upgrade? What is in your mysql error log? This is the closest I found in MySQL's bug list to your problem http://bugs.mysql.com/bug.php?id=74400 - it suggests to change the thread_stack setting (it is for 5.6.21). – Clay Jan 01 '16 at 06:39
  • MySQL Version 5.6.25 – Abhishek Ginani Jan 01 '16 at 06:41
  • Well, besides doing an upgrade or finding other potentially useful error messages in the error log I'm out of ideas and I have to get going.. good luck! – Clay Jan 01 '16 at 06:42
  • Thanks for your help:) – Abhishek Ginani Jan 01 '16 at 06:43

1 Answers1

4

I think, max_allowed_packet should be 256MB is enough and you may have a slow network connection. try the below variables also.

set net_write_timeout to double and increase net_buffer_length also in a rally.

Also How much ram you have in your box, are you taking all to innodb_buffer_pool, set this upto 70 to 80% of your box ram.

One more advice is to turn off query cache as it is a web stuff.

Adarsh Gangadharan
  • 653
  • 4
  • 10