Mean Stack filter crash - "Got a packet bigger than 'max_allowed_packet' bytes"

[leginonbot]

---

Author Name: Carl Negro (@carl9384) Original Redmine Issue: 4995, https://emg.nysbc.org/redmine/issues/4995 Original Date: 2017-05-31

---

Log from Priyamvada's 17may30c session:

... reading particle 88355 from stack start.hed into memory 
... reading particle 88389 from stack start.hed into memory 
... reading particle 90302 from stack start.hed into memory 
... reading particle 92166 from stack start.hed into memory 
... reading particle 92704 from stack start.hed into memory 
... reading particle 93887 from stack start.hed into memory 
... finished reading 282 particles of boxsize 160 x 160 from file 
... finished readParticlesFromFile() in 11.81 sec 
... writing stack to disk from memory: /gpfs/appion/pacharya/17may30c/stacks/meanfilt836_2/bad.hed 
... wrote 282 particles to header file 
... finished in 479.03 msec 
... finished appendParticlesToFile() in 479.26 msec 
... ProcessStack2: finished processing stack in 12.29 sec 
got old stackdata in 1.68 msec 
... created new stackdata in 10.05 msec 
... Getting list of particles to include 
... Completed in 243.71 msec 
... Retrieving original stack information 
... Completed in 163.37 msec 
... Assembling database insertion command 
... Inserting particle information into database 
appending 94787 particles to file 
before flip 1.59927 0.622391 
after flip 0.622391 1.59927 
appending 282 particles to file 
before flip -0.402555 -1.25287 
after flip -1.25287 -0.402555 
Long MySQL query of 10962851 characters 
connecting 
Traceback (most recent call last): 
File "/opt/myamisnap/bin/stackFilter.py", line 102, in  
subStack.start() 
File "/opt/myamisnap/bin/stackFilter.py", line 91, in start 
apStack.commitSubStack(self.params, newname, oldstackparts=stackparts) 
File "/opt/myamisnap/lib/appionlib/apStack.py", line 557, in commitSubStack 
sinedon.directq.complexMysqlQuery('appiondata',sqlcmd) 
File "/opt/myamisnap/lib/sinedon/directq.py", line 26, in complexMysqlQuery 
results = cur.selectall(query) 
File "/opt/myamisnap/lib/sinedon/sqldb.py", line 42, in selectall 
self.c.execute(strSQL, param) 
File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 173, in execute 
self.errorhandler(self, exc, value) 
File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler 
raise errorclass, errorvalue 
_mysql_exceptions.OperationalError: (1153, "Got a packet bigger than 'max_allowed_packet' bytes"

[leginonbot]

---

Original Redmine Comment Author Name: Neil Voss (@vosslab) Original Date: 2017-05-31T18:32:58Z

---

I think the problem is "Long MySQL query of 10962851 characters", we are trying to commit all particles at once...

[leginonbot]

---

Original Redmine Comment Author Name: Neil Voss (@vosslab) Original Date: 2017-05-31T18:39:36Z

---

Hi Carl,

edit myami/appion/appionlib/apStack.py line 548

1. break up command into groups of 100K inserts
1. this is a workaround for the max_allowed_packet at 16MB
n = 100000

I think we just need to lower the value of n

[leginonbot]

---

Original Redmine Comment Author Name: Carl Negro (@carl9384) Original Date: 2017-05-31T20:46:16Z

---

Still no good with n = 80000 and n = 30000.

[leginonbot]

---

Original Redmine Comment Author Name: Sargis Dallakyan (@dallakyan) Original Date: 2017-05-31T21:16:22Z

---

I've changed max allowed packet from 1048576 to 104857600 using @SET GLOBAL max_allowed_packet =104857600;@. I've also copied the following variables from old my.cnf to the new server. key_buffer = 1512M max_allowed_packet = 16M table_cache = 11212 sort_buffer_size=16M read_buffer_size = 18M read_rnd_buffer_size = 18M myisam_sort_buffer_size = 164M thread_cache_size = 8 query_cache_size=132M query_cache_limit=116M max_connections = 1350 interactive_timeout = 864000 wait_timeout = 864000 max_heap_table_size=132M tmp_table_size=1032M join_buffer_size=1256K

This will not take affect until mysqld restart.

[leginonbot]

---

Original Redmine Comment Author Name: Neil Voss (@vosslab) Original Date: 2017-05-31T21:20:50Z

---

If you setup a new database server the default my.cnf would be 1M, but the my-huge.cnf has a better setting.

In Docker, I run the following commands to setup CentOS 6 and 7:

cp -fv /usr/share/mysql/my-huge.cnf /etc/my.cnf
sed -i.bak 's/max_allowed_packet = [0-9]*M/max_allowed_packet = 24M/' /etc/my.cnf