Login throws SQL Exception on owncloud 9.1.1RC1 with activated file sharing plugin #25933

Closed
scroach opened this Issue Aug 24, 2016 · 20 comments

Projects

None yet

3 participants

@scroach
scroach commented Aug 24, 2016

@PVince81 As you requested my issue here in a separate ticket.
To give you a little more "history" - we migrated our old debian server to a new ubuntu server this week. Initial plan was to move from the old installlation to using owncloud in docker - unfortunately this caused problems with our apache reverse proxy (I think WebDAV calendar sync made problems in 9.0 something like "not in base url" and index.phpp) and there were problems when trying to update to 9.1 - but I cannot exactly tell you what now.
So we moved back to apt-get - migrated config and data folders and ran the update to 9.1 - which wouldn't work with file sharing. I think we first got the CSRF error when trying to login in, disabled the plugin and then couldnt enable it again reporting something like "plugin makes server unstable".
We found your update 9.1.1 #25557 and wanted to give it a try. And here we are - (after having lots and lots of bugs and troubles) we cannot login with some of our users getting this SQL exception.

I hope you could give us some advice on what we could try now. Thanks!

Steps to reproduce

  1. Dont know :(

Expected behaviour

File sharing works and login is possible

Actual behaviour

Login does not work with activated file sharing plugin

Server configuration

Operating system: Ubuntu 16.04

Web server: Apache2

Database: Mysql 5.6

PHP version: 7.0.8

ownCloud version: 9.1.1

Updated from an older ownCloud or fresh install: 8.2.1.4 > 9.0 > 9.1

Where did you install ownCloud from: apt

Signing status (ownCloud 9.0 and above):

No errors have been found.

List of activated apps:

Enabled:
  - activity: 2.3.2
  - calendar: 1.3.2
  - comments: 0.3.0
  - dav: 0.2.6
  - encryption: 1.3.0
  - federatedfilesharing: 0.3.0
  - federation: 0.1.0
  - files: 1.5.1
  - files_pdfviewer: 0.8.1
  - files_texteditor: 2.1
  - files_trashbin: 0.9.0
  - files_versions: 1.3.0
  - files_videoplayer: 0.9.8
  - firstrunwizard: 1.1
  - gallery: 15.0.0
  - notifications: 0.3.0
  - provisioning_api: 0.5.0
  - systemtags: 0.3.0
  - templateeditor: 0.1
  - updatenotification: 0.2.1
Disabled:
  - external
  - files_antivirus
  - files_external
  - files_sharing
  - user_external
  - user_ldap

The content of config/config.php:

{
    "system": {
        "instanceid": "oc1gg01dunez",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "xxx",
            "xxx"
        ],
        "overwrite.cli.url": "xxx",
        "dbtype": "mysql",
        "version": "9.1.1.0",
        "dbname": "owncloud",
        "dbhost": "127.0.0.1:3306",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "logtimezone": "UTC",
        "installed": true,
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_from_address": "xxx",
        "mail_domain": "xxx",
        "mail_smtphost": "xxx",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "theme": "",
        "maintenance": false,
        "loglevel": 2,
        "trashbin_retention_obligation": "auto",
        "updatechecker": false
    }
}

Are you using external storage, if yes which one: local

Are you using encryption: yes/no

Are you using an external user-backend, if yes which one: no

Client configuration

Browser: Firefox

Operating system: Windows 7

Logs

ownCloud log (data/owncloud.log)

{"reqId":"AO1OAdlnpklUTvXF6Ui4","remoteAddr":"xxxxxx","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO oc_mounts (storage_id,root_id,user_id,mount_point) SELECT ?,?,?,? FROM oc_mounts WHERE root_id = ? AND user_id = ? HAVING COUNT(*) = 0' with params [false, 2147, \\\"winkelmayer\\\", \\\"\\\\\\\/winkelmayer\\\\\\\/files\\\\\\\/xxxxx\\\\\\\/\\\", 2147, \\\"winkelmayer\\\"]:\\n\\nSQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'storage_id' at row 1\",\"Code\":0,\"Trace\":\"#0 \\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(116): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(996): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO oc...', Array)\\n#2 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(209): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO oc...', Array, Array)\\n#3 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(113): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO oc...', Array)\\n#4 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(247): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n#5 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(144): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n#6 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(124): OC\\\\Files\\\\Config\\\\UserMountCache->addToCache(Object(OC\\\\Files\\\\Config\\\\LazyStorageMountInfo))\\n#7 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/MountProviderCollection.php(133): OC\\\\Files\\\\Config\\\\UserMountCache->registerMounts(Object(OC\\\\User\\\\User), Array)\\n#8 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(425): OC\\\\Files\\\\Config\\\\MountProviderCollection->registerMounts(Object(OC\\\\User\\\\User), Array)\\n#9 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(370): OC\\\\Files\\\\Filesystem::initMountPoints('winkelmayer')\\n#10 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(226): OC\\\\Files\\\\Filesystem::init('winkelmayer', '\\\/winkelmayer\\\/fi...')\\n#11 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(348): OC_Util::setupFS()\\n#12 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/View.php(1110): OC\\\\Files\\\\Filesystem::resolvePath('\\\/files_encrypti...')\\n#13 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/View.php(524): OC\\\\Files\\\\View->basicOperation('file_exists', '\\\/files_encrypti...')\\n#14 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Encryption\\\/Keys\\\/Storage.php(190): OC\\\\Files\\\\View->file_exists('\\\/files_encrypti...')\\n#15 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Encryption\\\/Keys\\\/Storage.php(101): OC\\\\Encryption\\\\Keys\\\\Storage->getKey('\\\/files_encrypti...')\\n#16 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/KeyManager.php(558): OC\\\\Encryption\\\\Keys\\\\Storage->getSystemUserKey('pubShare_15c297...', 'OC_DEFAULT_MODU...')\\n#17 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/KeyManager.php(157): OCA\\\\Encryption\\\\KeyManager->getPublicShareKey()\\n#18 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/Users\\\/Setup.php(85): OCA\\\\Encryption\\\\KeyManager->validateShareKey()\\n#19 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/AppInfo\\\/Application.php(72): OCA\\\\Encryption\\\\Users\\\\Setup->setupSystem()\\n#20 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/appinfo\\\/app.php(29): OCA\\\\Encryption\\\\AppInfo\\\\Application->__construct(Array, true)\\n#21 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(186): require_once('\\\/var\\\/www\\\/ownclo...')\\n#22 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(149): OC_App::requireAppFile('encryption')\\n#23 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(119): OC_App::loadApp('encryption')\\n#24 \\\/var\\\/www\\\/owncloud\\\/lib\\\/base.php(875): OC_App::loadApps()\\n#25 \\\/var\\\/www\\\/owncloud\\\/index.php(39): OC::handleRequest()\\n#26 {main}\",\"File\":\"\\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":115}","level":3,"time":"2016-08-24T10:58:45+00:00","method":"GET","url":"\/owncloud\/index.php","user":"winkelmayer"}
@PVince81 PVince81 added the bug label Aug 24, 2016
@PVince81
Collaborator

Okay, let's have a look.
First, reformatted the exception:

{"reqId":"AO1OAdlnpklUTvXF6Ui4","remoteAddr":"xxxxxx","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO oc_mounts (storage_id,root_id,user_id,mount_point) SELECT ?,?,?,? FROM oc_mounts WHERE root_id = ? AND user_id = ? HAVING COUNT(*) = 0' with params [false, 2147, \\\"winkelmayer\\\", \\\"\\\\\\\/winkelmayer\\\\\\\/files\\\\\\\/xxxxx\\\\\\\/\\\", 2147, \\\"winkelmayer\\\"]:\\n\\nSQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'storage_id' at row 1\",\"Code\":0,\"Trace\":\"
#0 \\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(116): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n
#1 \\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(996): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO oc...', Array)\\n
#2 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(209): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO oc...', Array, Array)\\n
#3 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(113): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO oc...', Array)\\n
#4 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(247): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n
#5 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(144): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n
#6 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(124): OC\\\\Files\\\\Config\\\\UserMountCache->addToCache(Object(OC\\\\Files\\\\Config\\\\LazyStorageMountInfo))\\n
#7 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/MountProviderCollection.php(133): OC\\\\Files\\\\Config\\\\UserMountCache->registerMounts(Object(OC\\\\User\\\\User), Array)\\n
#8 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(425): OC\\\\Files\\\\Config\\\\MountProviderCollection->registerMounts(Object(OC\\\\User\\\\User), Array)\\n
#9 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(370): OC\\\\Files\\\\Filesystem::initMountPoints('winkelmayer')\\n
#10 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(226): OC\\\\Files\\\\Filesystem::init('winkelmayer', '\\\/winkelmayer\\\/fi...')\\n
#11 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(348): OC_Util::setupFS()\\n
#12 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/View.php(1110): OC\\\\Files\\\\Filesystem::resolvePath('\\\/files_encrypti...')\\n
#13 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Files\\\/View.php(524): OC\\\\Files\\\\View->basicOperation('file_exists', '\\\/files_encrypti...')\\n
#14 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Encryption\\\/Keys\\\/Storage.php(190): OC\\\\Files\\\\View->file_exists('\\\/files_encrypti...')\\n
#15 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/Encryption\\\/Keys\\\/Storage.php(101): OC\\\\Encryption\\\\Keys\\\\Storage->getKey('\\\/files_encrypti...')\\n
#16 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/KeyManager.php(558): OC\\\\Encryption\\\\Keys\\\\Storage->getSystemUserKey('pubShare_15c297...', 'OC_DEFAULT_MODU...')\\n
#17 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/KeyManager.php(157): OCA\\\\Encryption\\\\KeyManager->getPublicShareKey()\\n
#18 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/Users\\\/Setup.php(85): OCA\\\\Encryption\\\\KeyManager->validateShareKey()\\n
#19 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/lib\\\/AppInfo\\\/Application.php(72): OCA\\\\Encryption\\\\Users\\\\Setup->setupSystem()\\n
#20 \\\/var\\\/www\\\/owncloud\\\/apps\\\/encryption\\\/appinfo\\\/app.php(29): OCA\\\\Encryption\\\\AppInfo\\\\Application->__construct(Array, true)\\n
#21 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(186): require_once('\\\/var\\\/www\\\/ownclo...')\\n
#22 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(149): OC_App::requireAppFile('encryption')\\n
#23 \\\/var\\\/www\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/app.php(119): OC_App::loadApp('encryption')\\n
#24 \\\/var\\\/www\\\/owncloud\\\/lib\\\/base.php(875): OC_App::loadApps()\\n
#25 \\\/var\\\/www\\\/owncloud\\\/index.php(39): OC::handleRequest()\\n
#26 {main}\",\"File\":\"\\\/var\\\/www\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":115}","level":3,"time":"2016-08-24T10:58:45+00:00","method":"GET","url":"\/owncloud\/index.php","user":"winkelmayer"}
@PVince81 PVince81 added this to the 9.1.1 milestone Aug 24, 2016
@PVince81
Collaborator
PVince81 commented Aug 24, 2016 edited

Could you provide the output for the following queries:

  • select * from oc_storages where id like '%winkelmayer%'
  • select * from oc_mounts where user_id='winkelmayer'
  • select * from oc_share where share_with='winkelmayer'
  • if this user is also in any group receiving share, also try select * from oc_share where share_with in ('group1', 'group2', ...) (replace with the group names).

From what I see in the error above, the user "winkelmayer" is receiving a mount point, possibly a share, but the storage id was resolved to false instead of a numeric value for some yet unknown reason. So when trying to register the mount into oc_mounts it fails.

Goal now is trying to find out what storage the user is receiving. Could be from a broken share.

@scroach
scroach commented Aug 25, 2016

Data from the queries below. How do I find out which share is broken or not accessible by owncloud?

select * from oc_storages where id like '%winkelmayer%';

id numeric_id available last_checked
home::winkelmayer 1 1 \N

select * from oc_mounts where user_id='winkelmayer';

id storage_id root_id user_id mount_point
94 1 1 winkelmayer /winkelmayer/

select * from oc_share where share_with='winkelmayer';

id share_type share_with uid_owner parent item_type item_source item_target file_source file_target permissions stime accepted expiration token mail_send uid_initiator
8 0 winkelmayer niederl \N folder 2147 /2147 2147 /akaryon-development 15 1437572420 0 \N \N 0 niederl
12 0 winkelmayer niederl \N folder 2148 /2148 2148 /akaryon_backup 15 1437572586 0 \N \N 0 niederl
131 0 winkelmayer finker \N folder 464999 /464999 464999 /treevolution_test_27072016 31 1469648850 0 \N \N 0 finker
160 0 winkelmayer niederl \N file 214606 \N 214606 /akaryon-backup-log.txt 3 1472076776 0 \N \N 0 niederl
161 0 winkelmayer niederl \N folder 214575 \N 214575 /akaryon-development (2) 15 1472076800 0 \N \N 0 niederl

select * from oc_share where share_with in (select gid from oc_group_user where uid like 'winkelmayer');

id share_type share_with uid_owner parent item_type item_source item_target file_source file_target permissions stime accepted expiration token mail_send uid_initiator
156 1 mitarbeiterInnen niederl \N folder 214607 \N 214607 /alle_templates_logo_etc 15 1472076688 0 \N \N 0 niederl
163 1 mitarbeiterInnen niederl \N folder 95983 \N 95983 /default_projekt 15 1472076875 0 \N \N 0 niederl
164 1 treevolution niederl \N folder 151358 \N 151358 /treevolution 15 1472076906 0 \N \N 0 niederl
166 1 swf niederl \N folder 294938 \N 294938 /swf 15 1472077213 0 \N \N 0 niederl
169 1 ksp niederl \N folder 95294 \N 95294 /ksp 15 1472077286 0 \N \N 0 niederl
170 1 mona niederl \N folder 95304 \N 95304 /more_ex_mona 15 1472077314 0 \N \N 0 niederl
171 1 stadt_graz niederl \N folder 383588 \N 383588 /stadt_graz 15 1472077468 0 \N \N 0 niederl
@PVince81
Collaborator

Now try the following:

  • select * from oc_filecache where fileid in (select file_source from oc_share where share_with='winkelmayer')
  • select * from oc_filecache where fileid in (select * from oc_share where share_with in (select gid from oc_group_user where uid like 'winkelmayer'))

and, extended to the storages, just in case:

  • select * from oc_storages where numeric_id in (select storage from oc_filecache where fileid in (select file_source from oc_share where share_with='winkelmayer'))
  • select * from oc_storages where (select storage from oc_filecache where fileid in (select * from oc_share where share_with in (select gid from oc_group_user where uid like 'winkelmayer')))

Maybe the list of linked files will show a clue about a deleted or missing file of some sorts.
Sometimes the file is visible in oc_filecache but missing on disk.
Normally there is code to fail gracefully.

@PVince81
Collaborator

One thing I find strange is when you said that reenabling the app as admin says that the app would make OC unstable. The stability check is done by opening the files app behind the scenes after enabling the app. This would mean that even the admin user is receiving broken shares.

@scroach
scroach commented Aug 25, 2016

Couldn't find anynthing special there.. i checked that all files/folders exist in the user folders of niederl/finker.
Just shortened file names and paths in the output below.

select * from oc_filecache where fileid in (select file_source from oc_share where share_with='winkelmayer');

fileid storage path path_hash parent name mimetype mimepart size mtime storage_mtime encrypted unencrypted_size etag permissions checksum
464999 42 x 0eb91e53a647517b612175fe397d62ca 400641 x 2 1 4151024 1469648819 1469648819 0 0 57990fb3b1e93 31 \N
214606 3 x 2b9ce3b792ea744ec6bb37845797b952 214576 x 12 11 3731 1471870389 1471870389 1 0 4b298b6c07ece65d1ee7191df18a4263 27 \N
214575 3 x d1c845d9f5a93472094955e3e5e7499a 2146 x 2 1 49163 1470643729 1438635533 0 0 57a83e1107009 31 \N

select * from oc_filecache where fileid in (select file_source from oc_share where share_with in (select gid from oc_group_user where uid like 'winkelmayer'));

fileid storage path path_hash parent name mimetype mimepart size mtime storage_mtime encrypted unencrypted_size etag permissions checksum
214607 3 x 6c90ad6f8d6050cc24c915067f5131be 2146 x 2 1 16113481 1470402170 1467883422 0 0 57a48e7a3655c 31 \N
95983 3 x a0bdb0bf8bf0738afe99520cccd1c470 95293 x 2 1 948 1437892856 1437892856 0 0 55b480f851698 31 \N
151358 3 x f34dfd91d30029c591707fd39db9ccf4 95293 x 2 1 608042338 1469805000 1438637770 0 0 579b71c83e3db 31 \N
294938 3 x f4508d3386787d29fcc99acdf09b6a25 95293 x 2 1 60991206 1456152896 1448815203 0 0 56cb21405abd3 31 \N
95294 3 x 9fa45fccf29d773b0b0982d122002155 95293 x 2 1 116836719 1450166274 1438348862 0 0 566fc8024d07d 31 \N
95304 3 x 64547625e5f6a155b465459559981004 95293 x 2 1 297484734 1469691815 1453385176 0 0 5799b7a77f3cf 31 \N
383588 3 x 8349c21e831b7b16b2a98bb52ef28e79 95293 x 2 1 36496399 1447252508 1446677531 0 0 5643521d0e7c0 31 \N

select * from oc_storages where numeric_id in (select storage from oc_filecache where fileid in (select file_source from oc_share where share_with='winkelmayer'));

id numeric_id available last_checked
home::niederl 3 1 \N
home::finker 42 1 \N

select * from oc_storages where numeric_id in (select storage from oc_filecache where fileid in (select file_source from oc_share where share_with in (select gid from oc_group_user where uid like 'winkelmayer')));

id numeric_id available last_checked
home::niederl 3 1 \N
@PVince81
Collaborator

Does any of the paths you censored contained files inside "files_trashbin" instead of "files" ?

@scroach
scroach commented Aug 25, 2016

Nope, all of them were files/folders in the files directory of niederl/finker.
Anything else I may look for?

I just tried to log in with another admin account - remove my main account winkelmayer from all groups and tried to login again. Still does not work. This removes of course all the shares from the 2nd query - does this mean one of the directly shared filed/folders from the 1st query should be the problem?

@scroach
scroach commented Aug 25, 2016

Ok @PVince81, coming in with some good (at least I think so :D) news - I found old shares which referenced file_sourceswhich were not present in oc_filecache.

select fc.fileid, s.* from oc_share s
left join oc_filecache fc on s.file_source = fc.fileid
where fc.fileid is null;

After I removed those invalid references I could login again.

Some questions regarding this problem:

  • Is there any particular reason why no foreign keys are set on these columns?
  • Do you have any idea on how this invalid state might have happened? The invalid shares were quite old, from arround July 2015 - were we might just have upgraded to a new owncloud version?!
  • Why did this error only arrise in owncloud 9? In our old owncloud 8.2 thoses shares didn't seem to cause any problems.
  • Would it be possible for the owncloud server to provide better error messages or analysis tools to find those invalid configurations?
@PVince81
Collaborator

Is there any particular reason why no foreign keys are set on these columns?
Very very old design decisions from the early days of ownCloud. Something that should change at some point: #13143

Do you have any idea on how this invalid state might have happened? The invalid shares were quite old, from arround July 2015 - were we might just have upgraded to a new owncloud version?!

This can happen if you have a folder "folder/sub" and "sub" is shared with someone and you delete "folder". It doesn't cascade delete shares, which is ok and by design for performance reasons. Such shares are then cleant up later when running cron. The thing is that ownCloud is supposed to work fine and properly ignore these shares. I'll double check this logic again as many things changed with sharing lately.

Why did this error only arrise in owncloud 9? In our old owncloud 8.2 thoses shares didn't seem to cause any problems.

Yes, am also wondering... Such shares should not cause any issues.

Would it be possible for the owncloud server to provide better error messages or analysis tools to find those invalid configurations?

As explained above, such configuration isn't considered as invalid, so in general there is nothing to analyze or debug. Running cron will clear orphaned entries at some point.

You mention "owncloud 9", do you mean ownCloud 9.1.0 ? (9.0 and 9.1 are different major versions)

Thanks for the details, I'll try and reproduce a similar case locally.

@scroach
scroach commented Aug 25, 2016

Ok thanks for the info :D

By owncloud 9 I mainly mean our current 9.1(.1) version. But since we had so many problems during this migration and we upgraded directly from 9.0 to 9.1 (without using 9.0) I can't guarantee that the error isn't present there too. I just know that those references seem to be quite old and owncloud 8.2 worked just fine.

@PVince81
Collaborator

I tried reproducing this locally on v9.1.1RC1 and had oc_share entries where file_source pointed in the void (direct user links). However that user was still able to log in. So there's more to it.

Next up: try with shares inherited from before an update.

@PVince81
Collaborator

Okay, seems I got something reproduced:

{"reqId":"Dln5iHzHltKkp06JWMAW","remoteAddr":"127.0.0.1","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO `oc_mounts` (`storage_id`,`root_id`,`user_id`,`mount_point`) SELECT ?,?,?,? FROM `oc_mounts` WHERE `root_id` = ? AND `user_id` = ? HAVING COUNT(*) = 0' with params [false, 10, \\\"user1\\\", \\\"\\\\\\\/user1\\\\\\\/files\\\\\\\/sub\\\\\\\/\\\", 10, \\\"user1\\\"]:\\n\\nSQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column 'storage_id' at row 1\",\"Code\":0,\"Trace\":\"#0 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(116): Doctrine\\\\DBAL\\\\Driver\\\\AbstractMySQLDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(996): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOMySql\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO `oc...', Array)\\n#2 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(209): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO `oc...', Array, Array)\\n#3 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Adapter.php(113): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO `*P...', Array)\\n#4 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/DB\\\/Connection.php(247): OC\\\\DB\\\\Adapter->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n#5 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(144): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*mounts', Array, Array)\\n#6 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/UserMountCache.php(124): OC\\\\Files\\\\Config\\\\UserMountCache->addToCache(Object(OC\\\\Files\\\\Config\\\\LazyStorageMountInfo))\\n#7 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Config\\\/MountProviderCollection.php(133): OC\\\\Files\\\\Config\\\\UserMountCache->registerMounts(Object(OC\\\\User\\\\User), Array)\\n#8 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(425): OC\\\\Files\\\\Config\\\\MountProviderCollection->registerMounts(Object(OC\\\\User\\\\User), Array)\\n#9 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/Files\\\/Filesystem.php(370): OC\\\\Files\\\\Filesystem::initMountPoints('user1')\\n#10 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/private\\\/legacy\\\/util.php(226): OC\\\\Files\\\\Filesystem::init('user1', '\\\/user1\\\/files')\\n#11 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/lib\\\/base.php(890): OC_Util::setupFS()\\n#12 \\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/index.php(39): OC::handleRequest()\\n#13 {main}\",\"File\":\"\\\/srv\\\/www\\\/htdocs\\\/owncloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractMySQLDriver.php\",\"Line\":115}","level":3,"time":"2016-08-25T14:57:30+00:00","method":"GET","url":"\/owncloud\/index.php\/apps\/files\/","user":"user1"}

It involved starting with 8.2.1, creating the shares, upgrading to 9.0.4 then 9.1.0 then 9.1.1RC1.
I saw on v8.2.1 that there were already bogus "shared::" storages in the DB, could be related.

So far it seems it's a quite specific combination.

I'll try and narrow down the steps.

@PVince81
Collaborator
PVince81 commented Aug 25, 2016 edited

Steps

  1. Setup OC v8.2.1 or v8.2.7 (both create bogus "shared::" oc_storages entries!)
  2. Create a user "user1"
  3. Create a folder "test/sub"
  4. Upload a file "test.jpg" into "test"
  5. Upload a file "testsub.jpg" into "sub" (looks like we need at least 2 shares!)
  6. Share "test/test.jpg" with "user1"
  7. Share "test/sub" with "user1"
  8. Login as "user1" and see the shares are there
  9. Log out
  10. Upgrade to v9.0.4
  11. Login back as admin
  12. Delete "test"
  13. Empty trashbin
  14. Upgrade to v9.1.1RC1
  15. Login as "user1"

Was able to reproduce it twice with these steps.
Going to narrow down a bit more.

@PVince81
Collaborator
PVince81 commented Aug 25, 2016 edited

Ok, that's the most I have been able to reduce it to. Every further item I remove stops the issue from happening.

It seems this bug requires the following preconditions:

  • a user "user1" is recipient of at least TWO shares (having only one doesn't trigger the issue)
  • the share must be created in v8.2.x and seems to create a bogus "shared::" oc_storage entry
  • the share must point to a non-existing file_source at the time the user logs in
@PVince81
Collaborator

Wrong, even without the bogus "shared::" entry, the problem appears.

@PVince81
Collaborator

If I create the share directly on v9.0.4 then delete the "test" folder, all works well.

So maybe those share entries are different, let's compare.

@PVince81
Collaborator

Okay, turns out that the difference is that shares from v8.2.7 don't come with preloaded oc_mounts entries because that table didn't exist yet. In v9.0.4, the shares directly add those entries. However if I delete them when arriving at v9.1.1RC1, OC will try to recreate them and it will fail.

Easier steps:

  1. Setup v9.1.1RC1
  2. Login as "admin"
  3. Create a user "user1"
  4. Create "test/sub/test.jpg"
  5. Create "test/test2.jpg"
  6. Share "sub" and "test2.jpg" with "user1"
  7. Delete "test" and empty trash
  8. delete from oc_mounts where user_id='user1';
  9. Login as "user1"
  10. 💣

There is clearly another bug in 9.1.1RC1 but doesn't appear that easily because most of the time the oc_mounts entries are already there. But as that is some kind of cache, the risk of it appearing again is rather high.

@PVince81
Collaborator

Fix is here: #25948

@scroach can you test the patch https://github.com/owncloud/core/pull/25948.patch ?
It should now properly ignore these orphaned shares.

@PVince81 PVince81 referenced this issue in owncloud/QA Aug 26, 2016
Closed

Orphaned shares situations #298

@scroach
scroach commented Aug 30, 2016

Sry for the late response, I wasn't working the last few days.

I just tried out the patch and it seems to work fine.

  1. Got oc_share from an old 8.2 backup (manually added the new uid_initiator column)
  2. Tried to login -> SQL Exception
  3. Applied the patch
  4. Tried to login again -> works
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment