search

nextcloud / user_migration

This app allows users to easily migrate from one instance to another using an export of their account
https://apps.nextcloud.com/apps/user_migration
GNU Affero General Public License v3.0
15 stars 7 forks source link

systemtags export fails with SQL error "number of parameters must be between 0 and 65535" #742

Closed Disassembler0 closed 1 month ago

Disassembler0 commented 1 month ago

Exporting a large user account from console via occ user:export ${USER} ${EXPORT_DIR} fails as follows:

Exporting user information in user.json…
Exporting settings in settings.json…
Exporting versions in versions.json…
Exporting calendars into dav/calendars/…
Skipping calendar "Osobní" containing invalid calendar data
No calendars to export…
Exporting contacts into dav/address_books/…
Exporting trashbin into files_trashbin…
Exporting trashbin files…
Exporting account information in settings/account.json…
Exporting files…
Exporting file versions…
Exporting file tags…
Exporting file systemtags…

 [ERROR] An exception occurred while executing a query: SQLSTATE[HY000]: General error: 7 number of parameters must be  
         between 0 and 65535

The relevant log messages are:

{
    "app": "core",
    "exception": {
        "Code": 0,
        "CustomMessage": "More than 1000 expressions in a list are not allowed on Oracle.",
        "exception": {},
        "Exception": "Doctrine\\DBAL\\Query\\QueryException",
        "File": "/usr/share/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "Line": 264,
        "message": "More than 1000 expressions in a list are not allowed on Oracle.",
        "Message": "More than 1000 expressions in a list are not allowed on Oracle.",
        "query": "SELECT `systemtagid`, `objectid` FROM `*PREFIX*systemtag_object_mapping` WHERE (`objectid` IN (:objectids)) AND (`objecttype` = :objecttype) ORDER BY `objectid` ASC, `systemtagid` ASC",
        "Trace": [
            {
                "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
                "file": "/usr/share/nextcloud/lib/private/SystemTag/SystemTagObjectMapper.php",
                "function": "execute",
                "line": 90,
                "type": "->"
            },
            {
                "class": "OC\\SystemTag\\SystemTagObjectMapper",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Migrator/FilesMigrator.php",
                "function": "getTagIdsForObjects",
                "line": 181,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Migrator\\FilesMigrator",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Service/UserMigrationService.php",
                "function": "export",
                "line": 216,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Service\\UserMigrationService",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Command/Export.php",
                "function": "export",
                "line": 208,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Command\\Export",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Command/Command.php",
                "function": "execute",
                "line": 255,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Command\\Command",
                "file": "/usr/share/nextcloud/core/Command/Base.php",
                "function": "run",
                "line": 168,
                "type": "->"
            },
            {
                "class": "OC\\Core\\Command\\Base",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "run",
                "line": 1009,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "doRunCommand",
                "line": 273,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "doRun",
                "line": 149,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/lib/private/Console/Application.php",
                "function": "run",
                "line": 211,
                "type": "->"
            },
            {
                "class": "OC\\Console\\Application",
                "file": "/usr/share/nextcloud/console.php",
                "function": "run",
                "line": 100,
                "type": "->"
            },
            {
                "args": [
                    "/usr/share/nextcloud/console.php"
                ],
                "file": "/usr/share/nextcloud/occ",
                "function": "require_once",
                "line": 11
            }
        ]
    },
    "level": 3,
    "message": "More than 1000 expressions in a list are not allowed on Oracle.",
    "method": "",
    "remoteAddr": "",
    "reqId": "pUHGuOcALMuVsRtsrDtw",
    "time": "2024-08-24T21:29:41+00:00",
    "url": "--",
    "user": "--",
    "userAgent": "--",
    "version": "24.0.12.1"
}

and

{
    "app": "core",
    "exception": {
        "Code": 0,
        "CustomMessage": "The number of parameters must not exceed 65535. Restriction by PostgreSQL.",
        "exception": {},
        "Exception": "Doctrine\\DBAL\\Query\\QueryException",
        "File": "/usr/share/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "Line": 273,
        "message": "The number of parameters must not exceed 65535. Restriction by PostgreSQL.",
        "Message": "The number of parameters must not exceed 65535. Restriction by PostgreSQL.",
        "query": "SELECT `systemtagid`, `objectid` FROM `*PREFIX*systemtag_object_mapping` WHERE (`objectid` IN (:objectids)) AND (`objecttype` = :objecttype) ORDER BY `objectid` ASC, `systemtagid` ASC",
        "Trace": [
            {
                "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
                "file": "/usr/share/nextcloud/lib/private/SystemTag/SystemTagObjectMapper.php",
                "function": "execute",
                "line": 90,
                "type": "->"
            },
            {
                "class": "OC\\SystemTag\\SystemTagObjectMapper",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Migrator/FilesMigrator.php",
                "function": "getTagIdsForObjects",
                "line": 181,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Migrator\\FilesMigrator",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Service/UserMigrationService.php",
                "function": "export",
                "line": 216,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Service\\UserMigrationService",
                "file": "/usr/share/nextcloud/apps/user_migration/lib/Command/Export.php",
                "function": "export",
                "line": 208,
                "type": "->"
            },
            {
                "class": "OCA\\UserMigration\\Command\\Export",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Command/Command.php",
                "function": "execute",
                "line": 255,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Command\\Command",
                "file": "/usr/share/nextcloud/core/Command/Base.php",
                "function": "run",
                "line": 168,
                "type": "->"
            },
            {
                "class": "OC\\Core\\Command\\Base",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "run",
                "line": 1009,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "doRunCommand",
                "line": 273,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/3rdparty/symfony/console/Application.php",
                "function": "doRun",
                "line": 149,
                "type": "->"
            },
            {
                "class": "Symfony\\Component\\Console\\Application",
                "file": "/usr/share/nextcloud/lib/private/Console/Application.php",
                "function": "run",
                "line": 211,
                "type": "->"
            },
            {
                "class": "OC\\Console\\Application",
                "file": "/usr/share/nextcloud/console.php",
                "function": "run",
                "line": 100,
                "type": "->"
            },
            {
                "args": [
                    "/usr/share/nextcloud/console.php"
                ],
                "file": "/usr/share/nextcloud/occ",
                "function": "require_once",
                "line": 11
            }
        ]
    },
    "level": 3,
    "message": "The number of parameters must not exceed 65535. Restriction by PostgreSQL.",
    "method": "",
    "remoteAddr": "",
    "reqId": "pUHGuOcALMuVsRtsrDtw",
    "time": "2024-08-24T21:29:41+00:00",
    "url": "--",
    "user": "--",
    "userAgent": "--",
    "version": "24.0.12.1"
}

I've replaced the following line in FilesMigrator.php

$systemTags = $this->systemTagMapper->getTagIdsForObjects(array_values($objectIds), 'files');

with

$systemTags = [];
for ($i = 0; $i <= intval(count($objectIds) / 1000); $i++) {
    $objectIds_batch = array_slice($objectIDs, 1000 * $i, 1000);
    $systemTags_batch = $this->systemTagMapper->getTagIdsForObjects(array_values($objectIds_batch), 'files');
    $systemTags = array_merge($systemTags, $systemTags_batch);
}

and the export then completed successfully, however I'm not a PHP programmer and I don't find this solution elegant enough to submit a PR.

come-nc commented 1 month ago

Thank you for the report. Indeed this should be fixed in the system tag mapper itself I think

come-nc commented 1 month ago

Hum, actually we might as well fix it here, since having thousands of tags object at the same time in memory is not a good idea anyway

come-nc commented 1 month ago

Actually this is already fixed in 27 and above: https://github.com/nextcloud/server/pull/37029 Duplicate of https://github.com/nextcloud/user_migration/issues/368