inserting longblobs raises errors

[stephenholtz]

When including matrices in my insert() commands longblobs of doubles, singles, int64 etc., that exceeds 4Mb, I get an mym error. I'm not sure if this is the correct place to raise the issue but I figured someone here has run into this before.

For example if v is a longblob attribute and I set it to be 4.2Mb, I will get an error:

v = rand(1,5.25E5);
whos_v = whos('v'); 
mb = whos_v.bytes/(1000^2); 

>> disp(mb)
    4.2000

>> insert(...)

Error using mym
MySQL server has gone away

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

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

However the following works every time without errors:

v = rand(1,5E5);
whos_v = whos('v'); 
mb = whos_v.bytes/(1000^2); 

>> disp(mb)
    4.2000
>> insert(...)

The datajoint datatypes documentation and MySQL documentation suggests longblobs should be able to support up to 4 GB. In the mym documentation there doesn't appear to be any restriction on this datatype. Am I missing some other limitation? Any ideas?

[stephenholtz]

I see now that another issue was already raised by @mjlm below. I will take a look and see if I can implement a similar solution

[eywalker]

Hey @stephenholtz I have just tested and verified that I can replicate your issue on a freshly installed MySQL server using its default configuration, and also verified that if you would update the MySQL server configuration according to our recommended spec as can be found here then the LONGBLOB insert issue disappears.

Given this, I recommend that you try using our recommended MySQL configuration by updating the my.cnf on the MySQL database server, restart the server, and retry the insertion. Let me know if that works!

[eywalker]

If you were to look into your MySQL log, you should be able to see that connection was aborted with error message similar to this

 Aborted connection 0 to db: 'unconnected' user: '***' host: '*.*.*.*'
 (Got a packet bigger than 'max_allowed_packet' bytes)

And this issue can be remedied by increasing the max_allowed_packet to something bigger. In our experience, increasing this to 512M works well with even very big BLOBs.

According to the documentation, the largest transferable packet size that can be generated in MySQL client/server is 1GB, so if you want to truly avoid this issue, you can even set this value to 1G. There is no real demerit to using large max_allowed_packet value in your database aside from the fact that this means that each connection may set aside up to 1GB of memory to handle packet. However, memory allocation is done only as needed, so as long as you ensure that your database server has sufficient memory to handle such large packet size at the time of its highest demand, you should be good to go.

[stephenholtz]

@eywalker This is great! Thanks for the information, I should be able to test this just using a flag (e.g. mysql --max_allowed_packet=512M or similar). The locations of the files referenced in the ~/.my.cnf file using a brew installation are a little opaque to me, and I have everything running on a macbook currently but I'll give this a shot and see how it goes.

[eywalker]

@stephenholtz Great! If I recall correctly, there is no my.cnf by default for mysql installed by homebrew and thus the system uses default values for all options. You can create a new my.cnf and place it in places like /etc/my.cnf or ~/.my.cnf (and few other locations), and mysql will start using the config the next start you (re)start the server. For brew installed mysql, you should be able to easily restart the server with command mysql.server restart, applying the new config.

I'm not sure if passing the flag into mysql would do the trick as what you would want is for the MySQL server to be started with the correct configuration, and mysql is just a MySQL client software. You can set max_allowed_packet for both the client and the server, but your settings on the client will not affect the settings on the server.

Keep us posted on how things work out!

[stephenholtz]

@eywalker I had a chance to test this. You were right about the lack of a default my.cnf, and as you suggested the flag didn't work. But, a very simple ~/.my.cnf file was able to solve this after fixing some permissions issues:

# ~/.my.cnf
[mysqld]
max_allowed_packet=512M

Ultimately it would be nice to use the docker image you have kindly provided for flexibility, so I appreciate the link. And will probably set that up some time in the future.

In reference to the other issue thread, and in further support of this solution, recompiling the mym file for my platform did not solve this issue. The SQL settings worked like a charm though. So far no further issues.

[stephenholtz]

I will close the issue since as far as I can tell there are no further problems with insert()