"MySQL server has gone away" when inserting many or large rows

[mjlm]

I often get the mYm error MySQL server has gone away when I try to insert multiple tuples at once. It appears to be related to the overall amount of data (e.g. >100 MB per call to insert causes error) or number of rows (e.g. >1000 rows per call to insert causes error).

Can I perhaps fix this with the right settings for my MySQL server?

These are a few settings I have already played with. I thought these settings should allow inserts of many hundreds of MB, at least, but I get errors for 100 MB-inserts. What are other important settings?

[mysqld]
max_allowed_packet = 2048M
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 1024M
innodb_log_file_size = 2G

Thanks!
Matthias

EDIT: Slightly related: I get even worse errors (Matlab segfault/crash) when trying to make many small inserts in rapid succession (e.g. in a for loop that executes quickly).

[dimitri-yatsenko]

MySQL does not inform in the error message which setting caused the connection to be terminated. Need to check the error log. It may be because of timeout settings, packet size limits, buffer size limits, etc. Please experiment and post what was the problem in your case. We have been running the same configuration for a few years and have not had these problems. I can post our my.cnf here if you like.

We have not seen MATLAB segfault because of inserts. Perhaps recompiling mym.mex for your platform might fix the problem.

[eywalker]

@mjlm Any updates on this?

[mjlm]

No updates...I've just been chunking large inserts manually and wrap them in a for loop.

[eywalker]

Hm, the issue really appears to be MySQL related and not so much DataJoint related. I'll see if I can reproduce this error, but if there is not, I'll close this issue. If a method to reliably reproduce this issue comes up, then we can always reopen this issue.

[eywalker]

@mjlm Could you perhaps use our my.cnf below and see if that resolves the issue? If it happens to help, it would be great if you could work out what changes you made was exactly necessary to address the issue. Some of the system specific information has been commented out:

[mysqld_safe]
nice                                    = 0
socket                                  = /var/run/mysqld/mysqld.sock

[mysqld]
basedir                                 = /usr
bind_address                            = 0.0.0.0
datadir                                 = # DATA DIRECTORY
expire_logs_days                        = 10
lc_messages_dir                         = /usr/share/mysql
log_bin                                 = # LOG BIN DIRECTORY
log_error                               = # ERROR LOG DIRECTORY
max_allowed_packet                      = 2048M
max_binlog_size                         = 100M
pid-file                                = /var/run/mysqld/mysqld.pid
port                                    = 3306
query_cache_limit                       = 1024M
query_cache_size                        = 16000M
skip_external_locking
skip_name_resolve
socket                                  = /var/run/mysqld/mysqld.sock
thread_cache_size                       = 8
thread_stack                            = 192K
tmpdir                                  = /tmp
user                                    = # BASE USER

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
key_buffer_size                         = 2048M
myisam-recover         = BACKUP
server-id               = 1

#The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it$
innodb_buffer_pool_size=30000M
innodb_log_file_size=4096M
innodb_log_buffer_size=128M
innodb_stats_on_metadata=0
innodb_file_per_table=1
connect_timeout=120
net_read_timeout=180
net_write_timeout=180


[mysqldump]
quick
quote_names
max_allowed_packet      = 1024M

[mysql]
no_auto_rehash

[isamchk]
key_buffer              = 16M

[client]
port                                    = 3306
socket                                  = /var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

[eywalker]

@mjlm Also as @dimitri-yatsenko suggested, I'd recommend that you recompile the mYm MEX if you haven't already tried that.

[eywalker]

Any updates on this @mjlm? If there is no further indication that this is a DataJoint MATLAB specific issue, I will close this issue shortly.

[mjlm]

Some updates, in case they are useful:

Recompiling mYm did not help.

When my inserts are much too large, I get the "MySQL server has gone away" error. But when they are just slightly too large, I get:

Error using mym
Out of memory (Needed 76288160 bytes)

Error in dj.Connection/query (line 174)
                mym(self.connId, queryStr, v{:});

Error in dj.Relvar/insert (line 278)
            self.schema.conn.query(command, blobs{:});

That's just ~76 MB. This is almost exactly the size of the Matlab structure that contains the data to be inserted.

I am using all the same settings as your my.cnf.

I should say that I am using XAMPP with 32 bit MySQL. Perhaps this causes memory limitations, but 76 MB seems very low even for 32 bit. If the issue is indeed due to 32 bit, it may be good to specify that the MySQL installation must be 64 bit in the documentation.

[dimitri-yatsenko]

Looks like the memory shortage is on the mym side, not the server side. I wonder why it would run out of memory requesting only 76MB of RAM. Do you monitor your memory usage?

[mjlm]

The Windows Task Manager shows tens of GB of available RAM.

I have now migrated the entire database from the XAMPP server to a pure MySQL sever (64bit). The problem still persists :(

[dimitri-yatsenko]

Yes, the problem appears to be on the mym/matlab side not the server. For some reason it has a hard time getting free RAM. Have you worked through this? https://www.mathworks.com/help/matlab/matlab_prog/resolving-out-of-memory-errors.html