Webdav SEARCH: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

[tobiasKaminsky]

While doing this:

<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:oc="http://nextcloud.com/ns">
    <d:basicsearch>
        <d:select>
            <d:prop>
                <d:displayname />
                <d:getcontenttype />
                <d:resourcetype />
                <d:getcontentlength />
                <d:getlastmodified />
                <d:creationdate />
                <d:getetag />
                <d:quota-used-bytes />
                <d:quota-available-bytes />
                <oc:permissions xmlns:oc="http://owncloud.org/ns" />
                <oc:id xmlns:oc="http://owncloud.org/ns" />
                <oc:size xmlns:oc="http://owncloud.org/ns" />
                <oc:favorite xmlns:oc="http://owncloud.org/ns" />
            </d:prop>
        </d:select>
        <d:from>
            <d:scope>
                <d:href>/files/test</d:href>
                <d:depth>infinity</d:depth>
            </d:scope>
        </d:from>
        <d:where>
            <d:like>
                <d:prop>
                    <d:getcontenttype />
                </d:prop>
                <d:literal>application/pdf</d:literal>
            </d:like>
        </d:where>
        <d:orderby />
    </d:basicsearch>
</d:searchrequest>

I do get this:

<?xml version="1.0" encoding="utf-8"?>
<d:error xmlns:d="DAV:" xmlns:s="http://sabredav.org/ns">
    <s:exception>Doctrine\DBAL\Exception\DriverException</s:exception>
    <s:message>An exception occurred while executing 'SELECT `fileid`, `storage`, `path`, `parent`, `name`, `mimetype`,
        `mimepart`, `size`, `mtime`, `storage_mtime`, `encrypted`, `etag`, `permissions`, `checksum` FROM `oc_filecache`
        `file` WHERE (`storage` = ?) AND (`mimetype` COLLATE UTF8_general_ci LIKE ?)' with params [9,
        "application\/pdf"]:

        SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION 'utf8_general_ci' is not valid for CHARACTER
        SET 'latin1'
    </s:message>
</d:error>

If I change
<d:literal>application/pdf</d:literal>
to
<d:literal>application/%</d:literal>
I do get a result.

[tobiasKaminsky]

@nextcloud/server-triage can anyone help me?

[nickvergessen]

Please run occ db:convert-mysql-charset

[tobiasKaminsky]

I ran it and ran it again to verify, after it changed a lot of tables.
Now I get: All tables already have the correct collation -> nothing to do

But still I do have the same problem.
What is unknown to me is why it fails when using a real mimetype (e.g. pdf), but it is not failing if there is a placeholder…

[tobiasKaminsky]

@nextcloud/server-triage can anyone help me?

[blizzz]

@tobiasKaminsky could you provide a curl command for reproduction?

[tobiasKaminsky]

curl --request SEARCH \
  --url http://localhost/nc/remote.php/dav \
  --header 'authorization: Basic dG9iaTplOGVrei1SbVpaVC1DMkdDTi01clk2My1BeTl5QQ==' \
  --header 'content-type: text/xml' \
  --header 'ocs-apirequest: true' \
  --data '<?xml version="1.0" encoding="UTF-8"?>
<d:searchrequest xmlns:d="DAV:" xmlns:oc="http://nextcloud.com/ns">
    <d:basicsearch>
        <d:select>
            <d:prop>
                <d:displayname />
                <d:getcontenttype />
                <d:resourcetype />
                <d:getcontentlength />
                <d:getlastmodified />
                <d:creationdate />
                <d:getetag />
                <d:quota-used-bytes />
                <d:quota-available-bytes />
                <oc:permissions xmlns:oc="http://owncloud.org/ns" />
                <oc:id xmlns:oc="http://owncloud.org/ns" />
                <oc:size xmlns:oc="http://owncloud.org/ns" />
                <oc:favorite xmlns:oc="http://owncloud.org/ns" />
            </d:prop>
        </d:select>
        <d:from>
            <d:scope>
                <d:href>/files/tobi</d:href>
                <d:depth>infinity</d:depth>
            </d:scope>
        </d:from>
        <d:where>
            <d:like>
                <d:prop>
                    <d:getcontenttype />
                </d:prop>
                <d:literal>image/jpeg</d:literal>
            </d:like>
        </d:where>
        <d:orderby>
            <d:order>
                <d:prop>
                    <d:getlastmodified />
                </d:prop>
                <d:descending />
            </d:order>
        </d:orderby>
    </d:basicsearch>
</d:searchrequest>'

[blizzz]

Confirmed on postgresql with a different message, but apparently the same problem:

<?xml version="1.0" encoding="utf-8"?>
<d:error xmlns:d="DAV:" xmlns:s="http://sabredav.org/ns">
  <s:exception>Doctrine\DBAL\Exception\DriverException</s:exception>
  <s:message>An exception occurred while executing 'SELECT "fileid", "storage", "path", "parent", "name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted", "etag", "permissions", "checksum" FROM "oc_filecache" "file" WHERE ("storage" = ?) AND ("mimetype" ILIKE ?) ORDER BY "mtime" desc' with params [1713, "image\/jpeg"]:

SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: bigint ~~* unknown
LINE 1: ...he" "file" WHERE ("storage" = $1) AND ("mimetype" ILIKE $2) ...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.</s:message>
</d:error>

Works also when using the placeholder.

[tobiasKaminsky]

Somehow I remember that mimetype is not a string, but an int in database…

[tobiasKaminsky]

database table: oc_filecache

[blizzz]

Ǹow that makes sense. The mimetypes are in oc_mimetypes.