Bug #4995
openMean Stack filter crash - "Got a packet bigger than 'max_allowed_packet' bytes"
0%
Description
Log from Priyamvada's 17may30c session:
... reading particle 88338 from stack start.hed into memory
... 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"
Updated by Neil Voss over 7 years ago
I think the problem is "Long MySQL query of 10962851 characters", we are trying to commit all particles at once...
Updated by Neil Voss over 7 years ago
Hi Carl,
edit myami/appion/appionlib/apStack.py line 548
# break up command into groups of 100K inserts # this is a workaround for the max_allowed_packet at 16MB n = 100000
I think we just need to lower the value of n
Updated by Carl Negro over 7 years ago
Still no good with n = 80000 and n = 30000.
Updated by Sargis Dallakyan over 7 years ago
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.
Updated by Neil Voss over 7 years ago
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