I'm trying to download and restore a very large mySQL dump (>40GB) which also contains large binary blobs in tables. I'm using a command line like this:
curl 'https://url_from_doogle_dirve' -H ... --compressed | mysql --user=root --password=my-secret
The operation starts but after about 57MB I get the following error:
ERROR 2013 (HY000) at line 388: Lost connection to MySQL server during query
I think I've read almost all threads about this error and tried many solutions but unfortunately none of them works for me
here is my mysqld.cnf file (relevant part only):
key_buffer_size = 16M
max_allowed_packet = 32M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
net_write_timeout = 36000
net_read_timeout = 36000
innodb_buffer_pool_size = 32M
wait_timeout = 36000
I had to increase max_allowed_packet to 32MB because with 16MBit previously failed with another error, but now I have this new ERROR 2013 that seems related to large query / timeout. Actually, as stated above, tables contains binary blobs that may be even larger than 20 / 30 MB each.
EDIT
I've also tried downloading the sql file locally but I get the same result, so it has nothing to do with curl
MySQL 5.7.29 on Ubuntu Server 18.04.4
Ok I found the problem thanks to maria-db.
I run the same command on mariadb server instead of mySQL server and I got the same issue but mariadb gave me a more meaningful error message:
ERROR 1118 (42000) at line 388: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.
So I set
innodb_log_file_size=256M
and it's now working (not finished yet but at least this issue has gone) I guess it will work on mySQL server too.