Core: Small wait timeout of MySQL kills many file operations - General error: 2006 MySQL server has gone away

[MartinNowack]

Steps to reproduce

1. Set 'wait_timeout' to a _small_ value e.g. 60 seconds (default is 28800 seconds)
2. Try to upload a moderate sized file (>5 MB) with a slow upstream connection
   

   Expected behaviour

The file should be uploaded successfully.

Actual behaviour

The file is not uploaded

Server configuration

Operating system: Linux

Web server: Apache

Database: Mysql 5.1

PHP version: 5.5

ownCloud version: (see ownCloud admin page): 8.0.2

Updated from an older ownCloud or fresh install:
updated

Are you using external storage, if yes which one: local/smb/sftp/...
No

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
No

Logs

ownCloud log (data/owncloud.log)

SELECT `fileid` FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ?' with params [\"2\", \"0fea6a13c52b4d4725368f24b045ca84\"]:\n\nSQLSTATE[HY000]: General error: 2006 MySQL server has gone away

More Detailed Description

I use a hosted web service where I can't change the MySQL timeout. The value wait_timeout of 60 seconds sounds quite reasonable for me.
I'm using the DesktopClient version 1.8, I have a very small upstream ~50KiB/s and I couldn't upload bigger files (>2MB).

I guess the following happens:

1. Files are uploaded into chunks on client side
2. The server opens an SQL connection
3. a) it waits for one chunk, and
4. b) updates the cache information in the data base (SELECT fileid FROM oc_filecache WHERE storage = ? AND path_hash = ?')

The problem is now, if the time it takes to receive a chunk is bigger than the timeout for the database connection, it fails.

According to https://github.com/owncloud/client/issues/766 the default chunk size is 5MB. If I set the OWNCLOUD_CHUNK_SIZE to 1MB on the client side, it works, as the timeout is not triggered.

But this solution is cumbersome, the server should suggest which size it can work with or which time it has maximum. At least the error message should be changed to be more reasonable.

[nickvergessen]

The error message is from the MySQL server, not from owncloud.
I'm not sure whether it is possible to send messages to the client, because in this case I think telling the user of the client to change the OWNCLOUD_CHUNK_SIZE is what should be done, (or even the server telling that to the client)

[MartinNowack]

I'm aware that the error comes from the MySQL server. Still, the issue arises if uploading a chunk takes longer than the timeout of the SQL server (which is part of the OwnCloud backend). This is not handled correctly by the owncloud server itself. There is no activity by OwnCloud with the opened connection to the SQL server.

Imagine the following scenarios:

• you have a shared internet connection (e.g. family) and somebody else is occupying major parts of upstream (e.g. video chat) - that time your owncloud updates will fail
• you are traveling and you are somewhere with an unstable internet connection (public access point) - at some point your owncloud client might fail

I'm not convinced that readjusting OWNCLOUD_CHUNK_SIZE on the client side is the solution for user Joe.
Furthermore, currently readjusting chunk sizes for ongoing transmissions is not supported.

Possible fixes for OwnCloud could be:

• try to reconnect to the MySQL server instead of bailing out with an exception, or
• send a keep-connection-alive ping to the MySQL server

[deKay01]

I posted this on the ownCloud forum but it was suggested I ask here as it seems to be the same error:

I (clean) installed 8.0.2 on my web host, and v1.8.0 of the Windows client. I've tried syncing, but am getting a lot of errors. Small files (under a few MB) upload fine, but large files all fail. I can see they partially upload as the cache folder on the server has loads of "chunking" files, but I'm getting these errors in the client:

Error downloading http://[mydomain]/cloud/remote.php/webdav/[path]/[filename].pdf-chunking-248386926-8-0 - server replied: Internal Server Error

and

Continue blacklisting: Error downloading http://[mydomain]/cloud/remote.php/webdav/[path]/[filename].pdf-chunking-2741399126-13-0 - server replied: Internal Server Error

On the service, I'm getting:

An exception occurred while executing 'SELECT fileid FROM oc_filecache WHERE storage = ? AND path_hash = ?' with params ["1", "0fea6a13c52b4d4725368f24b045ca84"]: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

(the MySQL server hasn't gone away)

Any ideas? It was all working under ownCloud 5 on the same host. I thought it might be an upload file limit somewhere but I've a php.ini file that reads:

max_execution_time = 3600
max_input_time = 3600
memory_limit = 512M
post_max_size = 2000M
upload_max_filesize = 2000M
max_file_uploads = 20
default_charset = UTF-8

Which I assume would cover it?

Uploading the same failed files through the web interface works fine.

I've also tried:

Changing the owncloud.cfg file to include

OWNCLOUD_CHUNK_SIZE=1000000

(but this doesn't change anything, so perhaps it's in the wrong place?)

And I've also noticed that every single chunk on the server is exactly 5242880 bytes.

[DeepDiver1975]

we should use docrine's ping method to reconnect - https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connection.php#L1570

[djibux]

I an also experimenting that issue on a shared server (mutualized hosting). I am not allowed to change any PHP or MySQL setting.

[DeepDiver1975]

I an also experimenting that issue on a shared server (mutualized hosting). I am not allowed to change any PHP or MySQL setting.

I'm sorry to hear that this is causing that much of a problem - we will address this with owncloud 8.2

[djibux]

Would is be possible to specify a maximum upload time rather than a chuck size?

I know my owncloud server will timeout after a minute, but depending on the network I am on that could mean 1MB or 10MB or anything else. If I could ask the owncloud client to renew its connection to the server every minute it would solve my issue.

[rolf303]

Hi Martin, I can also confirm ( a couple months later) the issue has NOT gone away. ;-)

The server installation here is on OpenSUSE 13.1
Apache/2.4.6 (Linux/SUSE)
PHP 5.4.20 (cli)
mysql 5.5.44-MariaDB

Although the upload speed to the server is quiet slow (about 200 kByte/s or less depends on client) it is still fast enough to handle with chunks of 5MB, if I calculate this right.

Therefore I played a bit with OWNCLOUD_CHUNK_SIZE. Set it to 1MB and so on. I have a wait_timeout of 1 minute here. But the server don't care about the changed chunk size. It will still chunk files into 5MB. I'm sure there is no difference if I would increase the wait_timeout.
Anyway, as I can see in my case 5MB chunks work fine.

So I wondering which is why the chunk file timestamp points always 1 day in future. Look at this e.g.

stat ZW3D2013SPDeu_TM_32.zip-chunking-2073301663-64

File: „ZW3D2013SPDeu_TM_32.zip-chunking-2073301663-64“
Size: 5242880 Inode: 10240 IO Block: 4096 regular File
Device: 903h/2307d Inode: 110628488 Link: 1
Access: (0644/-rw-r--r--) Uid: ( 30/ wwwrun) Gid: ( 8/ www)
Access : 2015-08-26 23:05:16.364718265 +0200
Modify : 2015-08-27 22:07:01.000000000 +0200
Change : 2015-08-26 22:07:01.586622455 +0200

As you see, the mtime points nearly 1day ahead.

Maybe this could be a reason the timeout is (partially ) calculate wrong on server side. Possibility an underflow, overflow or fraction fault ... I think this has to be considered. Perhaps that can test some of the programmer.

[DeepDiver1975]

Mtime +1 day is used to cleanup the chunks. Totally unrelated.

[rolf303]

Well DeepDiver you're fast...
I really like to solve this.

I didn't mentioned yet I test owncloud 8.1.1 + clamav at client to server connection.

IMHO in my case it should not a timeout problem. As I can see there arrive a few chunk parts (2 or 3) before the 1 minute timeout was reached (and mysql.allow_persistent=On). And the
physical cable connection should be fine and stable (nothing logged in router). I also tried it with clients from different routes to the server (eg. TV or optic fiber cable).

It seems the server doesn't or missed retrigger the mysql connection. Is that possible?

On bigger files transfer the server stopped uploading a part and start an other transfer. It will start with 3 different chunk parts uploading at same time.

e.g. the transfer cache look like this

5242880 27. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-10
5242880 27. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-3
5242880 27. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-4
5242880 28. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-5
5242880 27. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-6
3981312 27. Aug 10:03 FLEISCHK─SE.H-chunking-1296721829-6.0TQ7LB5aDuNPZImS.part
5242880 27. Aug 12:01 FLEISCHK─SE.H-chunking-1296721829-6.N9MpOz3Ke6xT5hCc.part
5242880 27. Aug 12:01 FLEISCHK─SE.H-chunking-1296721829-7.A5O4P42JPXXuSv66.part
5242880 27. Aug 10:03 FLEISCHK─SE.H-chunking-1296721829-7.vKIrwGgyCOwzuGhx.part
5210112 27. Aug 12:01 FLEISCHK─SE.H-chunking-1296721829-8.2MOr3L1G2lzaSLpE.part
5242880 27. Aug 2015 FLEISCHK─SE.H-chunking-1296721829-9

As you will see the chunk FLEISCHK─SE.H-chunking-1296721829-6 upload started more than once and last from 26. Aug 10:03 to 26. Aug 16:22 (UTC 27.08.2015 14:22:35) Just to upload 5MB!

In addition, it seems finished chunk older than 24 hours will be dropped. Is this correct? What happen on huge directory, one full directory scan with many files could take a very long time, especially on slow connection?
Finally, I suppose this can not be a wire problem.

As my log entry is slightly different that Martin reported. e.g. my log is

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away)141 MiB

server replied: Internal Server Error (An exception occurred while executing 'SELECT * FROM oc_files_avir_status WHERE status_type=? and result=?' with params [1, 0]:

Because, I use clamav-milter to protect data, I tink.

Can I do some further investigation?
Is there a var, timestamp or flag I can watch the transfer of a chunk has been fully captured or has mysql triggered (mybe set some trigger in phpMyAdmin or so)?
Pherhaps I should test on a server to server connection as well...

[DeepDiver1975]

We do not reconnect to the DB and as a result the upload is broken.a possible solution to use ping() - but this was not yet implemented up to now.

Contributions are welcome ...

[rolf303]

I don't think a plain ping or reconnect to the database is a best solution, because it will not cover the fact the input speed could drop down and otherwise after starting a transfer. In drop down case a wait_timeout may happen before the first bytes arrive in php. You can not do anything. What I would prefer is a kind of dynamic input calculation (non static OWNCLOUD_CHUNK_SIZE) depends on current speed and the current wait_timeout value of the website (ownCloud). Perhaps this may help + a ping/reconnect to minimize lost connections (Best would be to interrupt the process before wait_timeout). It will reduce repeats, save time and cover resources. This would also minimize the impact to other webspace running on the same server. Because afaik wait_timeout is global to a running mysql instance.

Anyway, I just forgot, most important, it will keep away annoying people, so you may have plenty time left for all the other nice things... :honeybee:

Well, I've not enough time for development any more (because somehow you have to earn money), but I will do some tests if you need it. Just let me know.

[Duwe]

Hi,
i have the same problem. I cant change the wait_timeout at my provider. This value ist set to 60 seconds. (Yea, it is a cheap webplace)
If i upload a file, the upload will be broken after more than one minute. So i hope docrine's ping method will be used in the next time.
I cant use owncloud complete since the beginning of the year. Why you pushing the solution far in the future?

[clementmoulin]

As an "hot" fix, adding this to the configuration file could help:
'dbdriveroptions' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET wait_timeout = 28800'),

[peschuster]

Thanks @ClementSimpleRezo
This config line "fixed" it for me.

[sirlensen]

@ClementSimpleRezo
Thanks also from me!

[tzshng]

Thanks @ClementSimpleRezo , your method works! But if the internet is even slower, then then some files is still having trouble to upload. But your method fixed 80% of my problem. :)

[DeepDiver1975]

As an "hot" fix, adding this to the configuration file could help:
'dbdriveroptions' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET wait_timeout = 28800'),

I consider this a quite valid fix overall - we should adopt the docs and config sample

[DeepDiver1975]

FUCK - push to master :angry:

[djibux]

I am still experimenting the issue, even with that wait_timeout fix.
The issue seems to happen only on big files (in the hundreds of bytes).

I have configure chunking of files to 1MB.

[Duwe]

The issue depends on the upload time. At a wait_timeout = 60 seconds, after 1.5 minutes the upload broke. But the file is saved complete at the server as a *.part file. At my home, i can upload about 100MB files. At my parents, 3MB are possible.

The Hotfix from ClementSimpleRezo changes the wait_timeout value for the connection. But i dont have the rights to change it, so this solution have no effect for me.

The best way is, to reconnect the MySQL server. In Doctrine/DBAL there are 2 drivers for MySQL.
The PDO driver is used by owncloud and can't reconncet. If the connection have a timeout, the driver sould connect a second time to continue the transfer. But this is a job of Doctrine, i think.
The mysqli driver can reconnect (i hope). If the php value mysqli.reconnect=1 is set and ownclud use the ping() function.
Is there a way to change the driver to mysqli easily?

Well, maybe the Hotfix and the chunking work in the very most cases.
But thank you for all the work at owncloud. With a chageble wait_timeout value at my hoster, everything would work well.

[Rpnpif]

Hi,

I have still this issue with 10.0.2 release.
I had :

 'dbdriveroptions' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET wait_timeout = 28800'),
);

But not fixed.

Would you have another solutions ?

Regards,

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.