file_id column reference but not in schema of oc_group_folders_trash

[bernd-wechner]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

1. Unknown

Expected behaviour

No errors.

Actual behaviour

Sync errors directing me to the log and log errors complaining of:

"Message": "SQLSTATE[42703]: Undefined column: 7 ERROR: column \"file_id\" of relation \"oc_group_folders_trash\" does not exist\nLINE 1: ...id\", \"name\", \"deleted_time\", \"original_location\", \"file_id\")...\n ^",

Conclusion: file_id column missing from table oc_group_folders_trash.
Confirmed by inspecting schema.

Reference to it exists in apps/groupfolders/lib/Trash/TrashManager.php wherein is written:

public function addTrashItem(int $folderId, string $name, int $deletedTime, string $originalLocation, int $fileId): void {
		$query = $this->connection->getQueryBuilder();
		$query->insert('group_folders_trash')
			->values([
				'folder_id' => $query->createNamedParameter($folderId, IQueryBuilder::PARAM_INT),
				'name' => $query->createNamedParameter($name),
				'deleted_time' => $query->createNamedParameter($deletedTime, IQueryBuilder::PARAM_INT),
				'original_location' => $query->createNamedParameter($originalLocation),
				'file_id' => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)
			]);
		$query->execute();
	}

My attempted local fix:

ALTER TABLE public.oc_group_folders_trash
ADD COLUMN file_id bigint;

Server configuration

Operating system: Linuc Mint 20.2

Web server: Lighttpd 1.4.55 (as if it matters)

Database: Postgresql 12.8

PHP version: 7.4.3

Nextcloud version: 21.0.4

Group folders version: 9.0.3

Updated from an older Nextcloud/ownCloud or fresh install: Irrelevant, longstanding issue. Reported on forums over a year ago with no response and so took to deeper diagnosis myself this year. Only symptom is annoying errors reported by the desktop ync app.

Where did you install Nextcloud from: Lost to memory alas. It's an old mature install been kept up to date with every release.

Are you using external storage, if yes which one: local/s3/smb/sftp/... No.

Are you using encryption: No

Are you using an external user-backend, if yes which one: Not even sure what that means, so probably no.

Client configuration

Browser: Irrelevant. Firefox or Chromium or Chrome usually

Operating system: Whatever I'm on, my Mint machines or Windoze 10.

Logs

An extract of one of the repors. This happens a fair bit.

{
    "reqId": "8CdSeqcXNtXmlQqte5wa",
    "level": 4,
    "time": "2021-09-02T20:16:45+10:00",
    "remoteAddr": "192.168.0.11",
    "user": "bernd",
    "app": "webdav",
    "method": "DELETE",
    "url": "/remote.php/dav/files/bernd/HoGS/Treasury/Bank%20Statements/Bendigo/2020%20-09%20September.pdf",
    "message": {
        "Exception": "Doctrine\\DBAL\\Exception\\InvalidFieldNameException",
        "Message": "An exception occurred while executing a query: SQLSTATE[42703]: Undefined column: 7 ERROR:  column \"file_id\" of relation \"oc_group_folders_trash\" does not exist\nLINE 1: ...id\", \"name\", \"deleted_time\", \"original_location\", \"file_id\")...\n                                                             ^",
        "Code": 7,
        "Trace": [{
            "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1728,
            "function": "convert",
            "class": "Doctrine\\DBAL\\Driver\\API\\PostgreSQL\\ExceptionConverter",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1667,
            "function": "handleDriverException",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
            "line": 1146,
            "function": "convertExceptionDuringQuery",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
            "line": 257,
            "function": "executeStatement",
            "class": "Doctrine\\DBAL\\Connection",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
            "line": 213,
            "function": "executeStatement",
            "class": "OC\\DB\\Connection",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
            "line": 287,
            "function": "execute",
            "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashManager.php",
            "line": 57,
            "function": "execute",
            "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashBackend.php",
            "line": 184,
            "function": "addTrashItem",
            "class": "OCA\\GroupFolders\\Trash\\TrashManager",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Trash/TrashManager.php",
            "line": 103,
            "function": "moveToTrash",
            "class": "OCA\\GroupFolders\\Trash\\TrashBackend",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
            "line": 203,
            "function": "moveToTrash",
            "class": "OCA\\Files_Trashbin\\Trash\\TrashManager",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
            "line": 103,
            "function": "doDelete",
            "class": "OCA\\Files_Trashbin\\Storage",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
            "line": 1172,
            "function": "unlink",
            "class": "OCA\\Files_Trashbin\\Storage",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
            "line": 729,
            "function": "basicOperation",
            "class": "OC\\Files\\View",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/dav/lib/Connector/Sabre/File.php",
            "line": 466,
            "function": "unlink",
            "class": "OC\\Files\\View",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Tree.php",
            "line": 179,
            "function": "delete",
            "class": "OCA\\DAV\\Connector\\Sabre\\File",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
            "line": 281,
            "function": "delete",
            "class": "Sabre\\DAV\\Tree",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
            "line": 89,
            "function": "httpDelete",
            "class": "Sabre\\DAV\\CorePlugin",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 472,
            "function": "emit",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 253,
            "function": "invokeMethod",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
            "line": 321,
            "function": "start",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/dav/lib/Server.php",
            "line": 332,
            "function": "exec",
            "class": "Sabre\\DAV\\Server",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php",
            "line": 35,
            "function": "exec",
            "class": "OCA\\DAV\\Server",
            "type": "->"
        }, {
            "file": "/var/www/html/nextcloud/remote.php",
            "line": 167,
            "args": ["/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php"],
            "function": "require_once"
        }],
        "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php",
        "Line": 64,
        "Previous": {
            "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
            "Message": "SQLSTATE[42703]: Undefined column: 7 ERROR:  column \"file_id\" of relation \"oc_group_folders_trash\" does not exist\nLINE 1: ...id\", \"name\", \"deleted_time\", \"original_location\", \"file_id\")...\n                                                             ^",
            "Code": 7,
            "Trace": [{
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
                "line": 84,
                "function": "new",
                "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
                "type": "::"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                "line": 1136,
                "function": "execute",
                "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                "line": 257,
                "function": "executeStatement",
                "class": "Doctrine\\DBAL\\Connection",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
                "line": 213,
                "function": "executeStatement",
                "class": "OC\\DB\\Connection",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
                "line": 287,
                "function": "execute",
                "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashManager.php",
                "line": 57,
                "function": "execute",
                "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashBackend.php",
                "line": 184,
                "function": "addTrashItem",
                "class": "OCA\\GroupFolders\\Trash\\TrashManager",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Trash/TrashManager.php",
                "line": 103,
                "function": "moveToTrash",
                "class": "OCA\\GroupFolders\\Trash\\TrashBackend",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
                "line": 203,
                "function": "moveToTrash",
                "class": "OCA\\Files_Trashbin\\Trash\\TrashManager",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
                "line": 103,
                "function": "doDelete",
                "class": "OCA\\Files_Trashbin\\Storage",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
                "line": 1172,
                "function": "unlink",
                "class": "OCA\\Files_Trashbin\\Storage",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
                "line": 729,
                "function": "basicOperation",
                "class": "OC\\Files\\View",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/dav/lib/Connector/Sabre/File.php",
                "line": 466,
                "function": "unlink",
                "class": "OC\\Files\\View",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Tree.php",
                "line": 179,
                "function": "delete",
                "class": "OCA\\DAV\\Connector\\Sabre\\File",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
                "line": 281,
                "function": "delete",
                "class": "Sabre\\DAV\\Tree",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
                "line": 89,
                "function": "httpDelete",
                "class": "Sabre\\DAV\\CorePlugin",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                "line": 472,
                "function": "emit",
                "class": "Sabre\\DAV\\Server",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                "line": 253,
                "function": "invokeMethod",
                "class": "Sabre\\DAV\\Server",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                "line": 321,
                "function": "start",
                "class": "Sabre\\DAV\\Server",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/dav/lib/Server.php",
                "line": 332,
                "function": "exec",
                "class": "Sabre\\DAV\\Server",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php",
                "line": 35,
                "function": "exec",
                "class": "OCA\\DAV\\Server",
                "type": "->"
            }, {
                "file": "/var/www/html/nextcloud/remote.php",
                "line": 167,
                "args": ["/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php"],
                "function": "require_once"
            }],
            "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
            "Line": 26,
            "Previous": {
                "Exception": "PDOException",
                "Message": "SQLSTATE[42703]: Undefined column: 7 ERROR:  column \"file_id\" of relation \"oc_group_folders_trash\" does not exist\nLINE 1: ...id\", \"name\", \"deleted_time\", \"original_location\", \"file_id\")...\n                                                             ^",
                "Code": "42703",
                "Trace": [{
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
                    "line": 82,
                    "function": "execute",
                    "class": "PDOStatement",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                    "line": 1136,
                    "function": "execute",
                    "class": "Doctrine\\DBAL\\Driver\\PDO\\Statement",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                    "line": 257,
                    "function": "executeStatement",
                    "class": "Doctrine\\DBAL\\Connection",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php",
                    "line": 213,
                    "function": "executeStatement",
                    "class": "OC\\DB\\Connection",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
                    "line": 287,
                    "function": "execute",
                    "class": "Doctrine\\DBAL\\Query\\QueryBuilder",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashManager.php",
                    "line": 57,
                    "function": "execute",
                    "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/groupfolders/lib/Trash/TrashBackend.php",
                    "line": 184,
                    "function": "addTrashItem",
                    "class": "OCA\\GroupFolders\\Trash\\TrashManager",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Trash/TrashManager.php",
                    "line": 103,
                    "function": "moveToTrash",
                    "class": "OCA\\GroupFolders\\Trash\\TrashBackend",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
                    "line": 203,
                    "function": "moveToTrash",
                    "class": "OCA\\Files_Trashbin\\Trash\\TrashManager",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/files_trashbin/lib/Storage.php",
                    "line": 103,
                    "function": "doDelete",
                    "class": "OCA\\Files_Trashbin\\Storage",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
                    "line": 1172,
                    "function": "unlink",
                    "class": "OCA\\Files_Trashbin\\Storage",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/lib/private/Files/View.php",
                    "line": 729,
                    "function": "basicOperation",
                    "class": "OC\\Files\\View",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/dav/lib/Connector/Sabre/File.php",
                    "line": 466,
                    "function": "unlink",
                    "class": "OC\\Files\\View",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Tree.php",
                    "line": 179,
                    "function": "delete",
                    "class": "OCA\\DAV\\Connector\\Sabre\\File",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/CorePlugin.php",
                    "line": 281,
                    "function": "delete",
                    "class": "Sabre\\DAV\\Tree",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/event/lib/WildcardEmitterTrait.php",
                    "line": 89,
                    "function": "httpDelete",
                    "class": "Sabre\\DAV\\CorePlugin",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                    "line": 472,
                    "function": "emit",
                    "class": "Sabre\\DAV\\Server",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                    "line": 253,
                    "function": "invokeMethod",
                    "class": "Sabre\\DAV\\Server",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/3rdparty/sabre/dav/lib/DAV/Server.php",
                    "line": 321,
                    "function": "start",
                    "class": "Sabre\\DAV\\Server",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/dav/lib/Server.php",
                    "line": 332,
                    "function": "exec",
                    "class": "Sabre\\DAV\\Server",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php",
                    "line": 35,
                    "function": "exec",
                    "class": "OCA\\DAV\\Server",
                    "type": "->"
                }, {
                    "file": "/var/www/html/nextcloud/remote.php",
                    "line": 167,
                    "args": ["/var/www/html/nextcloud/apps/dav/appinfo/v2/remote.php"],
                    "function": "require_once"
                }],
                "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php",
                "Line": 82
            }
        },
        "CustomMessage": "--"
    },
    "userAgent": "Mozilla/5.0 (Linux) mirall/3.3.1git (build 6888) (Nextcloud, linuxmint-5.4.0-81-generic ClientArchitecture: x86_64 OsArchitecture: x86_64)",
    "version": "21.0.4.1",
    "id": "6130a81fdf4d4"
}

Web server error log

Irrelevant

Nextcloud log (data/nextcloud.log)

Irrelevant

Browser log

Irrelevant

Conclusion

I have added the file_id column for now, though guessed at the definition.

My best guess is that some upgrade or other did not update the schema correctly, and that this may not be a bug with groupfolders present, but one lost to time as I've not acted on it for a long time and it's not a new issue so it could have been any Nexcloud or app update in the last couple of years that caused it.

To wit, I'm guessing best approach is if I could be advised on how to ensure the scheme is correct. Is there a command to run that can just do that?

[solracsf]

I can't reproduce this; in any (4) installation I've inspected (NC 21 or 22) file_id columns exists. Those installations have been upgraded since Nextcloud 16. 🤔

[joshtrichards]

That column was added back in GF v4.1.6 via #688 (and, specifically, here). For whatever reason it seems the migration didn't run maybe on your installation, but it's probably going to be near impossible to determine why at this point.

There is a command to check which migrations have supposedly ran and also to trigger a re-run of certain ones. You can adapt my instructions from the below two comments to your situation (which were for the dav app but can be swapped for the groupfolders app):

• [Bug]: After upgrade to NC v27.0.1, cannot sync to server. SQLSTATE[42S22]: Column not found: 1054 Unknown column 'valuetype' in 'field list' server#39548 (comment)
• [Bug]: After upgrade to NC v27.0.1, cannot sync to server. SQLSTATE[42S22]: Column not found: 1054 Unknown column 'valuetype' in 'field list' server#39548 (comment)

I'm going to close this out for now, but if a pattern emerges where db migrations seem to be failing to run after updating apps feel free to open an issue in the server repo since it's likely an app management matter rather than app specific. I've see similar issues arise, albeit rarely and so far without a known cause.