OC 5.0.10 - SQLSTATE[08004] [1040] Too many connections

[mightycoco]

Expected behaviour

Availability of DB connection to mysql after upgrading from 5.0.9 to 5.0.10

Actual behaviour

After a couple of minutes the owncloud.log shows following error message and doesn't recover until the db-server is restarted:

{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392813}

Steps to reproduce

1. Upgrade from 5.0..9 to 5.0.10
2. Don't change any settings leaving the working 5.0.9 settings
   a) my.cnf
   b) php.ini
   c) oc

Server configuration

Operating system:

Linux xxx 3.6.11+ #474 PREEMPT Thu Jun 13 17:14:42 BST 2013 armv6l GNU/Linux

Web server:

Server version: Apache/2.2.22 (Debian)
Server built:   Mar 10 2013 05:34:48
Server's Module Magic Number: 20051115:30
Server loaded:  APR 1.4.6, APR-Util 1.4.1
Compiled using: APR 1.4.6, APR-Util 1.4.1
Architecture:   32-bit
Server MPM:     Prefork
  threaded:     no
    forked:     yes (variable process count)
Server compiled with....
 -D APACHE_MPM_DIR="server/mpm/prefork"
 -D APR_HAS_SENDFILE
 -D APR_HAS_MMAP
 -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
 -D APR_USE_SYSVSEM_SERIALIZE
 -D APR_USE_PTHREAD_SERIALIZE
 -D APR_HAS_OTHER_CHILD
 -D AP_HAVE_RELIABLE_PIPED_LOGS
 -D DYNAMIC_MODULE_LIMIT=128
 -D HTTPD_ROOT="/etc/apache2"
 -D SUEXEC_BIN="/usr/lib/apache2/suexec"
 -D DEFAULT_PIDLOG="/var/run/apache2.pid"
 -D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
 -D DEFAULT_LOCKFILE="/var/run/apache2/accept.lock"
 -D DEFAULT_ERRORLOG="logs/error_log"
 -D AP_TYPES_CONFIG_FILE="mime.types"
 -D SERVER_CONFIG_FILE="apache2.conf"

Database:

mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (armv7l) using readline 6.2

PHP version:

PHP 5.4.4-14+deb7u3 (cli) (built: Jul 18 2013 01:01:56)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
    with XCache v2.0.0, Copyright (c) 2005-2012, by mOo

ownCloud version:
5.0.10

Client configuration

Browser:
reporudable in all browsers and sync client 1.3.0 for windows

Operating system:
irrelevant

Logs

Web server error log

No related information found

ownCloud log (data/owncloud.log)

~# tail /var/www/owncloud/data/owncloud.log
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392813}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392841}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392841}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392842}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392870}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392870}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392871}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392899}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392899}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392899}
{"app":"core","message":"SQLSTATE[HY000] [1040] Too many connections","level":4,"time":1376392928}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392928}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392928}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392956}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392957}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392957}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392985}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392985}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376392986}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376393014}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376393014}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376393014}
{"app":"core","message":"SQLSTATE[HY000] [1040] Too many connections","level":4,"time":1376393043}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376393043}
{"app":"core","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":1376393043}

Browser log

[mightycoco]

changing the php.ini setting from -1 to 10 might have solved the issue for now - need to verify this over time:

mysql.max_persistent = 10

[Niduroki]

@bartv2 @icewind1991 @DeepDiver1975

Edit: nvm the second statement

[karlitschek]

Your database should always accept at least as many connections as the configured maximum number of apache/php process.

[nazar-pc]

Again getting this second time during 24 hours on 7.0.4
Even worse, that this causes removing some local files! (thankfully files where under Git)
Looks like when getting files list fails ownCloud client just removes them.
After first time this happens, I've set mysql.max_persistent = 10, but that didn't help as you can see.
Few last lines from log on server:

Error   PHP     Session has been closed - no further changes to the session as allowed at /var/www/owncloud/lib/private/session/memory.php#80   2014-12-25T11:53:07+00:00
Fatal   remote  An exception occurred while executing 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions` FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away     2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Search_Lucene\Hooks::deleteFile): An exception occurred while executing 'SELECT `id` FROM `oc_jobs` WHERE `class` = ? AND `argument` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Files_Versions\Hooks::pre_remove_hook): An exception occurred while executing 'SELECT `numeric_id` FROM `oc_storages` WHERE `id` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00
Error   PHP Session has been closed - no further changes to the session as allowed at /var/www/owncloud/lib/private/session/memory.php#80   2014-12-25T11:53:07+00:00
Fatal   remote  An exception occurred while executing 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions` FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away 2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Search_Lucene\Hooks::deleteFile): An exception occurred while executing 'SELECT `id` FROM `oc_jobs` WHERE `class` = ? AND `argument` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Files_Versions\Hooks::pre_remove_hook): An exception occurred while executing 'SELECT `numeric_id` FROM `oc_storages` WHERE `id` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Files_Trashbin\Hooks::remove_hook): An exception occurred while executing 'SELECT `id`, `mimetype` FROM `oc_mimetypes`': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away  2014-12-25T11:53:07+00:00
Error   PHP Session has been closed - no further changes to the session as allowed at /var/www/owncloud/lib/private/session/memory.php#80   2014-12-25T11:53:07+00:00
Fatal   remote  An exception occurred while executing 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`, `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions` FROM `oc_filecache` WHERE `storage` = ? AND `path_hash` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away 2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Search_Lucene\Hooks::deleteFile): An exception occurred while executing 'SELECT `id` FROM `oc_jobs` WHERE `class` = ? AND `argument` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00
Error   hook    error while running hook (OCA\Files_Versions\Hooks::pre_remove_hook): An exception occurred while executing 'SELECT `numeric_id` FROM `oc_storages` WHERE `id` = ?': SQLSTATE[HY000]: General error: 2006 MySQL server has gone away    2014-12-25T11:53:07+00:00

• Ubuntu 14.04 x64 (Docker container)
• Nginx 1.4.6
• PHP-FPM 5.5.9
• MariaDB 10.0.15
• ownCloud 7.0.4
• ownCloud client 1.7.1

[DeepDiver1975]

General error: 2006 MySQL server has gone away

looks like your MySQL db has been shutdown???

[nazar-pc]

Sure, I took logs right after mysql restart, because otherwise I wasn't able to get into ownCloud.
Well, looks like logs are not representative.
It is not funny, just happened third time.
Here are fresh logs before mysql restart:

{"app":"PHP","message":"filesize(): stat failed for \/var\/www\/owncloud\/data\/nazar-pc\/files_trashbin\/files\/pmd_display_field.2.php.*********** at \/var\/www\/owncloud\/lib\/private\/files\/storage\/local.php#117","level":3,"time":"2014-12-25T13:53:49+00:00"}
{"app":"PHP","message":"filesize(): stat failed for \/var\/www\/owncloud\/data\/nazar-pc\/files_trashbin\/files\/pmd_display_field.2.php.*********** at \/var\/www\/owncloud\/lib\/private\/files\/storage\/local.php#117","level":3,"time":"2014-12-25T13:53:54+00:00"}
{"app":"PHP","message":"filesize(): stat failed for \/var\/www\/owncloud\/data\/nazar-pc\/files_trashbin\/files\/pmd_display_field.2.php.*********** at \/var\/www\/owncloud\/lib\/private\/files\/storage\/local.php#117","level":3,"time":"2014-12-25T13:53:56+00:00"}
{"app":"PHP","message":"filesize(): stat failed for \/var\/www\/owncloud\/data\/nazar-pc\/files_trashbin\/files\/pmd_display_field.2.php.*********** at \/var\/www\/owncloud\/lib\/private\/files\/storage\/local.php#117","level":3,"time":"2014-12-25T13:53:56+00:00"}
{"app":"PHP","message":"filesize(): stat failed for \/var\/www\/owncloud\/data\/nazar-pc\/files_trashbin\/files\/pmd_display_field.2.php.*********** at \/var\/www\/owncloud\/lib\/private\/files\/storage\/local.php#117","level":3,"time":"2014-12-25T13:53:56+00:00"}
{"app":"remote","message":"SQLSTATE[08004] [1040] Too many connections","level":4,"time":"2014-12-25T13:54:20+00:00"}

[DeepDiver1975]

Too many connections

hmmm - can you increase the connection limit? - http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

[nazar-pc]

Do you think 100 (current value) is not enough for container where DB is used exclusively by ownCloud with single active user with single (Desktop) client?
I think that if it is so - there is definitely something wrong with ownCloud itself. Well, it may be a temporary workaround, but definitely not a solution of the root problem.

[DeepDiver1975]

Well, it may be a temporary workaround

all I can help you out with at the moment .. and yes - this requires deeper analysis

[nazar-pc]

I found weird thing. Stat failed on pmd_display_field.2.php.d1416559107 file, which is true, because there is no such file, indeed there is file pmd_display_field.2.php.trashinfo.d1416559107. Not sure whether this relates to current issue, but I have A LOT of such warnings in log file.

[DeepDiver1975]

@nazar-pc you can try to run a rescan of the file system from the command line (using the web server user)

./oc files:scan --all

[nazar-pc]

I did, files where green, I'll see how it will behave.
ownCloud works without Too many connections error for few days already. That is why I can make a conclusion that it was caused by some scheduled process (like removal of old files).

The root of this problem according to what I found is either unfinished DB transaction, or just not closed DB connection. Where is connection closing in ownCloud? Will it be closed if ownCloud crashed with exception (I mean register_shutdown_function handler for such case).

[MorrisJobke]

The root of this problem according to what I found is either unfinished DB transaction, or just not closed DB connection. Where is connection closing in ownCloud? Will it be closed if ownCloud crashed with exception (I mean register_shutdown_function handler for such case).

@icewind1991 Can you answer this?

[icewind1991]

Will it be closed if ownCloud crashed with exception

Connections should automatically be closed if php error's out

[nazar-pc]

@icewind1991, I thought so also, but since error happened it is not always true. I mean that in some cases connection left unclosed, or some process lives too long, or something else, but something wastes all available connections from time to time.

[gramels]

I had the same in 7.0.4.
Tried to set my.cnf to
max_connections = 1500
with no help.

Issue seems pop up, once I mount a webdav share with lost of files.

[PVince81]

Are you still seeing this in 7.0.7 or 8.0.5 ?

I remember that some queries were missing "closeCursor()", there is a chance that these resources are now properly freed which could hopefully fix your problem.

[MorrisJobke]

I’m closing this issue because it has been inactive for a few months. This probably means that the issue has been fixed in a newer version.

Please reopen if you still encounter this issue with the latest stable version.

Thank you!

[ghost]

Still hapenning in 9.0 when scanning 10.000.000+ files with mysl 5.7, also memory goes up to 1.5 GB for php.. (i had to change it in php.ini :-/. it seems the system leaks memory, and/or does not close/reuse connections... after rebooting the webserver i seen no problems, seems to relate to file scanning... large set of files...

[PVince81]

@Tristan79 are you able to check whether database connections are leaking ?

Regarding memory, this could be similar to #23019

[ghost]

It started working! using Link and instead of 16k I used 64k...

; Determines the size of the realpath cache to be used by PHP. This value should
; be increased on systems where PHP opens many files to reflect the quantity of
; the file operations performed.
; http://php.net/realpath-cache-size
realpath_cache_size = 64k

; Duration of time, in seconds for which to cache realpath information for a given
; file or directory. For systems with rarely changing files, consider increasing this
; value.
; http://php.net/realpath-cache-ttl
realpath_cache_ttl = 120

hope that it helps anyone...

still not added all my files (i reduced it so 3.500.000, that also did not work without these settings) so i am hopefull... no idea how to check for leaks at the moment...

Also it always tell me it is out of disk space..,when i enter my external storage, even when i go back to the root,... seems a bug. The external folder are mounted read only, still syncing works, still have to test that better...
And installing apps does not work under osx server 5 (might be the internal proxy?). For the most part runs perfect now :-) But then time will tell.

[CristianCardosoA]

This is still happening on version

Exception occurred while logging exception: Failed to connect to the database: An exception occured in driver: SQLSTATE[08004] [1040] Too many connections
#0 /var/www/myWebsite.com/public_html/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\DB\Connection->connect()
#1 /var/www/myWebsite.com/public_html/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\DBAL\Connection->getDatabasePlatformVersion()
#2 /var/www/myWebsite.com/public_html/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\DBAL\Connection->detectDatabasePlatform()
#3 /var/www/myWebsite.com/public_html/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\DBAL\Connection->getDatabasePlatform()
#4 /var/www/myWebsite.com/public_html/lib/private/DB/Connection.php(145): Doctrine\DBAL\Connection->setTransactionIsolation(2)
#5 /var/www/myWebsite.com/public_html/lib/composer/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\DB\Connection->__construct(Array, Object(Doctrine\DBAL\Driver\PDOMySql\Driver), Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#6 /var/www/myWebsite.com/public_html/lib/private/DB/ConnectionFactory.php(145): Doctrine\DBAL\DriverManager::getConnection(Array, Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#7 /var/www/myWebsite.com/public_html/lib/private/Server.php(493): OC\DB\ConnectionFactory->getConnection('mysql', Array)
#8 /var/www/myWebsite.com/public_html/lib/composer/pimple/pimple/src/Pimple/Container.php(113): OC\Server->OC{closure}(Object(OC\Server))
#9 /var/www/myWebsite.com/public_html/lib/private/AppFramework/Utility/SimpleContainer.php(111): Pimple\Container->offsetGet('DatabaseConnect...')
#10 /var/www/myWebsite.com/public_html/lib/private/ServerContainer.php(87): OC\AppFramework\Utility\SimpleContainer->query('DatabaseConnect...')
#11 /var/www/myWebsite.com/public_html/lib/private/Server.php(1160): OC\ServerContainer->query('DatabaseConnect...')
#12 /var/www/myWebsite.com/public_html/lib/private/Server.php(370): OC\Server->getDatabaseConnection()
#13 /var/www/myWebsite.com/public_html/lib/composer/pimple/pimple/src/Pimple/Container.php(113): OC\Server->OC{closure}(Object(OC\Server))
#14 /var/www/myWebsite.com/public_html/lib/private/AppFramework/Utility/SimpleContainer.php(111): Pimple\Container->offsetGet('AppConfig')
#15 /var/www/myWebsite.com/public_html/lib/private/ServerContainer.php(87): OC\AppFramework\Utility\SimpleContainer->query('AppConfig')
#16 /var/www/myWebsite.com/public_html/lib/private/Server.php(1089): OC\ServerContainer->query('AppConfig')
#17 /var/www/myWebsite.com/public_html/lib/private/Server.php(547): OC\Server->getAppConfig()
#18 /var/www/myWebsite.com/public_html/lib/composer/pimple/pimple/src/Pimple/Container.php(113): OC\Server->OC{closure}(Object(OC\Server))
#19 /var/www/myWebsite.com/public_html/lib/private/AppFramework/Utility/SimpleContainer.php(111): Pimple\Container->offsetGet('AppManager')
#20 /var/www/myWebsite.com/public_html/lib/private/ServerContainer.php(87): OC\AppFramework\Utility\SimpleContainer->query('AppManager')
#21 /var/www/myWebsite.com/public_html/lib/private/Server.php(1359): OC\ServerContainer->query('AppManager')
#22 /var/www/myWebsite.com/public_html/lib/private/legacy/app.php(346): OC\Server->getAppManager()
#23 /var/www/myWebsite.com/public_html/lib/private/legacy/app.php(110): OC_App::getEnabledApps()
#24 /var/www/myWebsite.com/public_html/lib/base.php(579): OC_App::loadApps(Array)
#25 /var/www/myWebsite.com/public_html/lib/base.php(998): OC::init()
#26 /var/www/myWebsite.com/public_html/index.php(54): require_once('/var/www/client...')
#27 {main}