Upgrade failed SQL 7 ERROR: invalid input syntax for integer: "5,179"

[wolkenschieber]

Hi,

I just tried to upgrade to current version 5.0.13.
After applying the maintenance mode isn't turned off.

If I turn it off manually and refresh the main page I get the message:
Failed to upgrade "files_sharing". Exception="SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "5,179""

Does anyone know how to solve the issue?

Thanks
wolkenschieber

[wolkenschieber]

After some poking around, the problem is caused by
owncloud/apps/files_sharing/appinfo/update.php

There the in statement doesn't work with an array that has more than one item:
$removeShares = \OC_DB::prepare('DELETE FROM *PREFIX*share WHERE file_source IN (?)');

This is propably postgres related.

[DeepDiver1975]

https://github.com/owncloud/core/blob/master/apps/files_sharing/appinfo/update.php#L76

binding an array to one single query parameter will most probably work nowhere

[zmi1]

I had a similar error on two of three sites:
owncloud Failed to upgrade "files_sharing". Invalid text representation: 7 ERROR: invalid input syntax for integer: (then lots of numbers)

One site didn't say anything, just turned maintainance mode on and stopped.

The fix was to run the SQL statement manually:
delete from oc_share where file_source in (SELECT file_source FROM oc_share LEFT JOIN oc_filecache ON file_source = oc_filecache.fileid WHERE oc_filecache.fileid IS NULL AND oc_share.item_type IN ('file', 'folder'));

Afterwards the ugprade ran smooth. We use PostgreSQL.
Sigh. There are still too many upgrade problems in OwnCloud!

[DeepDiver1975]

delete from oc_share where file_source in (SELECT file_source FROM oc_share LEFT JOIN oc_filecache ON file_source = oc_filecache.fileid WHERE oc_filecache.fileid IS NULL AND oc_share.item_type IN ('file', 'folder'));

@zmi1 this is how the query should have looked like instead of that crazy code we have today.
Interested in fixing the code pieces and submit a pull request? THX

[satarsa]

Thank you @zmi1, it works!

[zmi1]

Hi Thomas, heres the very much simplified and WORKING (yes, tested) solution. I have no idea how youd like the pull request to look like. So I attach it as a file and inline. Have fun.

PULL REQUEST:

[zmi1]

Here's the simple diff that's working.

75,77c75,77
<       // delete all shares where the original file no longer exists
<       $removeShares = \OC_DB::prepare('DELETE FROM `*PREFIX*share` WHERE `file_source` IN (SELECT `file_source` FROM `*PREFIX*share` LEFT JOIN `*PREFIX*filecache` ON `file_source` = `*PREFIX*filecache`.`fileid` WHERE `*PREFIX*filecache`.`fileid` IS NULL AND `*PREFIX*share`.`item_type` IN (\'file\', \'folder\'))');
<       $result = $removeShares->execute(array(implode(',', $delArray)));
---
>       // get all shares where the original file no longer exists
>       $findShares = \OC_DB::prepare('SELECT `file_source` FROM `*PREFIX*share` LEFT JOIN `*PREFIX*filecache` ON `file_source` = `*PREFIX*filecache`.`fileid` WHERE `*PREFIX*filecache`.`fileid` IS NULL AND `*PREFIX*share`.`item_type` IN (\'file\', \'folder\')');
>       $sharesFound = $findShares->execute(array())->fetchAll();
78a79,87
>       // delete those shares from the oc_share table
>       if (is_array($sharesFound) && !empty($sharesFound)) {
>               $delArray = array();
>               foreach ($sharesFound as $share) {
>                       $delArray[] = $share['file_source'];
>               }
>               $removeShares = \OC_DB::prepare('DELETE FROM `*PREFIX*share` WHERE `file_source` IN (?)');
>               $result = $removeShares->execute(array(implode(',', $delArray)));
>       }

I've tested it, it's working.

[DeepDiver1975]

@zmi1 Thanks a lot!
Pull requests are the GitHub way of collaborative/social coding - if you are interested here is a quick tutorial on how to submit a pull request https://www.openshift.com/wiki/github-workflow-for-submitting-pull-requests

Feel free to get back to me in case of questions - THX

[zmi1]

Looked into it, too much effort for me. You can take the code above and create your own pull request. Whatever license you need I transfer it to you.

[DeepDiver1975]

Looked into it, too much effort for me.

That's sad - but understandable.

You can take the code above and create your own pull request. Whatever license you need I transfer it to you.

It will require MIT license - thanks a lot for your contribution!

[zmi1]

Hm, looking at the code I wonder why it's working:

< $result = $removeShares->execute(array(implode(',', $delArray)));

shouldn't that be

< $result = $removeShares->execute();

?? Not sure what the parameter to execute is. It was working four our site, but I guess a cleanup would be good. I guess it worked because delArray is empty as it's not used anymore.

[jmcclelland]

I had the same problem upgrading from 5.0.15 to 5.0.25 (via Debian jessie packages).

I've prepared a pull request (that is tested against 5.25/Debian Jessie package):

#6016

[jmcclelland]

I think you are right about the parameter being cruft. I just removed it in my pull request (but sadly I've already upgraded my installation so it's not tested).

[PVince81]

I believe this has been fixed now in #7293 which superseded the original PR #6016
Closing.

[rysiekpl]

Not fixed, the same error upgrading 4.5.x to 5.0.17 here. Probably related to these lines:

$removeShares = \OC_DB::prepare('DELETE FROM `*PREFIX*share` WHERE `file_source` IN (?)');
$result = $removeShares->execute(array(implode(',', $delArray)));

As @DeepDiver1975 pointed out, "binding an array to one single query parameter will most probably work nowhere".

Running the query by hand:

DELETE FROM "oc_share" WHERE "file_source" IN (4,8,11,26,37,38,41,70,71,51,95,99)

solves the problem for me - so the issue is placing an array of integers (imploded with ',') as a text param.

[VicDeo]

@rysiekpl Fix above has ownCloud 7 milestone. Surely it is not fixed for 5.x