[Bug]: SQLSTATE 1052 "Column 'file_id' in WHERE is ambiguous" in PreviewMapper::getPreviewForSpecification on MySQL/MariaDB
bug2. developingmediumfeature: previews and thumbnails34-feedback
### ⚠️ This issue respects the following points: ⚠️
- [x] This is a **bug**, not a question or a configuration/webserver/proxy issue.
- [x] This issue is **not** already reported on [Github](https://github.com/nextcloud/server/issues?q=is%3Aopen+is%3Aissue+label%3Abug) OR [Nextcloud Community Forum](https://help.nextcloud.com/) _(I've searched it)_.
- [x] Nextcloud Server **is** up to date. See [Maintenance and Release Schedule](https://github.com/nextcloud/server/wiki/Maintenance-and-Release-Schedule) for supported versions.
- [x] I agree to follow Nextcloud's [Code of Conduct](https://nextcloud.com/contribute/code-of-conduct/).
### Bug description
Preview generation fails on MySQL/MariaDB with:
```
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'file_id' in WHERE is ambiguous
```
The error is thrown from `OC\Preview\Db\PreviewMapper::getPreviewForSpecification()` every time a preview is saved (`Generator::savePreview()`). In my case it surfaces via the Preview Generator app background job (`OCA\PreviewGenerator\BackgroundJob\PreviewJob`), but the affected code is in **server core**, so on-the-fly preview generation is affected as well.
### Root cause analysis
`PreviewMapper::joinLocation()` joins three tables:
- `previews` (alias `p`)
- `preview_locations` (alias `l`)
- `preview_versions` (alias `v`)
Both `previews` (`p`) and `preview_versions` (`v`) contain a `file_id` column.
`getPreviewForSpecification()` builds WHERE conditions from parameter keys **without a table alias**:
```php
public function getPreviewForSpecification(array $parameters): ?Preview {
$qb = $this->db->getQueryBuilder();
$this->joinLocation($qb);
foreach ($parameters as $key => $value) {
$qb->andWhere($qb->expr()->eq($key, $qb->createNamedParameter($value)));
}
...
}
```
When `$parameters` contains `file_id`, MySQL/MariaDB rejects the query with error 1052 because the column reference is ambiguous.
The same problem exists in `getByFileId()`:
```php
public function getByFileId(int $fileId): \Generator {
$selectQb = $this->db->getQueryBuilder();
$this->joinLocation($selectQb)
->where($selectQb->expr()->eq('file_id', $selectQb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
...
}
```
Note that the neighboring method `getAvailablePreviewsForFile()` does it correctly (`'p.file_id'`), so this looks like an oversight.
### Suggested fix
Prefix the columns with the proper table alias, e.g.:
```php
foreach ($parameters as $key => $value) {
$alias = ($key === 'version') ? 'v.' : 'p.';
$qb->andWhere($qb->expr()->eq($alias . $key, $qb->createNamedParameter($value)));
}
```
and in `getByFileId()`:
```php
->where($selectQb->expr()->eq('p.file_id', $selectQb->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
```
I have applied this patch locally and can confirm the error disappears and previews are generated correctly.
### Steps to reproduce
1. Run Nextcloud 34.0.3/34.0.4 on MySQL or MariaDB.
2. Upload any image file (or install the Preview Generator app and wait for the background job / run `occ preview:generate-all`).
3. Preview generation triggers `Generator::savePreview()` → `PreviewMapper::getPreviewForSpecification()` and fails with SQL error 1052; check `nextcloud.log`.
### Expected behavior
Previews are generated and stored without SQL errors.
### Nextcloud Server version
34
### Operating system
Debian/Ubuntu
### PHP engine version
PHP 8.3
### Web server
Apache (supported)
### Database engine version
MariaDB
### Is this bug present after an update or on a fresh install?
Updated from a MINOR version (ex. 32.0.1 to 32.0.2)
### Are you using the Nextcloud Server Encryption module?
None
### What user-backends are you using?
- [x] Default user-backend _(database)_
- [ ] LDAP/ Active Directory
- [ ] SSO - SAML
- [ ] Other
### Configuration report
```json
{
"system": {
"serverid": "1",
"instanceid": "***REMOVED SENSITIVE VALUE***",
"passwordsalt": "***REMOVED SENSITIVE VALUE***",
"secret": "***REMOVED SENSITIVE VALUE***",
"allow_local_remote_servers": true,
"trusted_domains": [
"//example.com",
"//example.com",
"localhost",
"//example.com",
"localhost",
"//example.com"
],
"trusted_proxies": "***REMOVED SENSITIVE VALUE***",
"push_server_url": "https:\//example.com/push",
"datadirectory": "***REMOVED SENSITIVE VALUE***",
"dbtype": "mysql",
"version": "34.0.3.2",
"overwrite.cli.url": "https://example.com",
"dbname": "***REMOVED SENSITIVE VALUE***",
"dbhost": "***REMOVED SENSITIVE VALUE***",
"dbport": "",
"dbtableprefix": "oc_",
"mysql.utf8mb4": true,
"dbuser": "***REMOVED SENSITIVE VALUE***",
"dbpassword": "***REMOVED SENSITIVE VALUE***",
"installed": true,
"maintenance": false,
"memcache.local": "\\OC\\Memcache\\APCu",
"memcache.distributed": "\\OC\\Memcache\\Redis",
"redis": {
"host": "***REMOVED SENSITIVE VALUE***",
"port": 6379
},
"memcache.locking": "\\OC\\Memcache\\Redis",
"theme": "",
"loglevel": 2,
"overwriteprotocol": "https",
"mail_smtpmode": "smtp",
"mail_sendmailmode": "smtp",
"mail_from_address": "***REMOVED SENSITIVE VALUE***",
"mail_domain": "***REMOVED SENSITIVE VALUE***",
"mail_smtphost": "***REMOVED SENSITIVE VALUE***",
"mail_smtpport": "587",
"default_locale": "ru_RU",
"default_phone_region": "RU",
"app_install_overwrite": {
"0": "documentserver_community",
"2": "files_rightclick",
"3": "metadata",
"4": "files_archive"
},
"enable_previews": true,
"preview_libreoffice_path": "\/usr\/bin\/libreoffice",
"enabledPreviewProviders": [
"OC\\Preview\\TXT",
"OC\\Preview\\MarkDown",
"OC\\Preview\\OpenDocument",
"OC\\Preview\\PDF",
"OC\\Preview\\MSOffice2003",
"OC\\Preview\\MSOfficeDoc",
"OC\\Preview\\MSOffice2007",
"OC\\Preview\\Image",
"OC\\Preview\\Photoshop",
"OC\\Preview\\TIFF",
"OC\\Preview\\SVG",
"OC\\Preview\\Font",
"OC\\Preview\\MP3",
"OC\\Preview\\Movie",
"OC\\Preview\\MKV",
"OC\\Preview\\MP4",
"OC\\Preview\\AVI"
],
"apps_paths": [
{
"path": "\/var\/www\/html\/nextcloud\/apps",
"url": "\/apps",
"writable": false
},
{
"path": "\/var\/www\/html\/nextcloud\/custom_apps",
"url": "\/custom_apps",
"writable": true
}
],
"mail_smtpauth": true,
"mail_smtpname": "***REMOVED SENSITIVE VALUE***",
"mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
"maintenance_window_start": 22,
"collabora\/code": "https:\//example.com",
"fileuploadnew.chunk_size": "1G",
"fileuploadnew.max_filesize": "25G",
"memory_limit": "26G",
"timeout": 36000,
"enable_push": true,
"has_internet_connection": true,
"notify_push_base_endpoint": "https://example.com\/push",
"overwritewebroot": "",
"mail_smtpauthtype": "LOGIN",
"mail_smtpstreamoptions": {
"ssl": {
"allow_self_signed": false,
"verify_peer": true,
"verify_peer_name": true
}
},
"logtimezone": "Europe\/Moscow",
"mail_smtptimeout": 60
}
}
```
### List of activated Apps
```shell
Enabled:
- activity: 7.0.0
- admin_audit: 1.24.0
- admincockpit: 1.3.4
- analytics: 6.7.1
- announcementbanner: 2.4.3
- appstore: 1.0.0
- bruteforcesettings: 7.0.0
- calendar: 6.5.3
- cloud_federation_api: 1.18.0
- comments: 1.24.0
- contacts: 8.7.6
- contactsinteraction: 1.15.0
- dashboard: 7.14.0
- dav: 1.40.0
- deck: 1.18.3
- external: 9.0.1
- federatedfilesharing: 1.24.0
- federation: 1.24.0
- files: 2.6.0
- files_accesscontrol: 5.0.0
- files_automatedtagging: 5.0.0
- files_downloadlimit: 5.2.0
- files_external: 1.26.0
- files_lock: 34.0.1
- files_pdfviewer: 7.0.0-dev.0
- files_reminders: 1.7.0
- files_retention: 5.0.0
- files_sharing: 1.26.0
- files_trashbin: 1.24.0
- files_versions: 1.27.0
- flow_notifications: 5.0.0
- forms: 5.3.5
- guests: 4.9.0
- localpdf: 1.0.0
- logcleaner: 1.5.6
- logreader: 7.0.0
- lookup_server_connector: 1.22.0
- nextcloud_announcements: 6.0.0
- notes: 6.0.1
- notifications: 7.0.0-dev.1
- notify_push: 1.4.0
- oauth2: 1.22.0
- office: 1.0.0
- onlyoffice: 10.1.2
- password_policy: 6.0.0-dev.0
- previewgenerator: 5.14.0
- privacy: 6.0.0-dev.1
- profile: 1.3.0
- provisioning_api: 1.24.0
- recommendations: 7.0.0
- related_resources: 5.0.0-dev.0
- serverinfo: 6.0.0
- settings: 1.17.0
- sharebymail: 1.24.0
- spreed: 24.0.4
- support: 6.0.0
- survey_client: 6.0.0-dev.0
- systemtags: 1.24.0
- tables: 2.2.1
- tasks: 0.18.1
- text: 8.0.0
- theming: 2.9.0
- thesearchpage: 2.4.3
- twofactor_backupcodes: 1.23.0
- twofactor_totp: 16.0.0
- updatenotification: 1.24.0
- user_status: 1.14.0
- viewer: 7.0.0-dev.0
- weather_status: 1.14.0
- webhook_listeners: 1.6.0
- workflow_script: 5.0.0
- workflowengine: 2.16.0
Disabled:
- app_api: 34.0.0 (installed 32.0.0)
- circles: 34.0.0 (installed 28.0.0)
- encryption: 2.22.0
- files_archive: 1.2.8 (installed 1.2.8)
- files_zip: 3.0.0 (installed 3.0.0)
- firstrunwizard: 7.0.0-dev.0 (installed 2.18.0)
- metadata: 0.24.0 (installed 0.24.0)
- photos: 7.0.0 (installed 2.4.0)
- side_menu: 6.0.1 (installed 6.0.1)
- socialsharing_email: 4.1.0 (installed 4.1.0)
- socialsharing_telegram: 4.1.0 (installed 4.1.0)
- suspicious_login: 12.0.0-dev.0
- testing: 1.23.0
- twofactor_nextcloud_notification: 8.0.0
- user_ldap: 1.25.0
```
### Nextcloud Signing status
```shell
```
### Nextcloud Logs
```json
<details>
{
"reqId": "Nrd5F8ZlSxmeEMhpV3fM",
"level": 3,
"time": "2026-08-13T21:35:04+03:00",
"app": "core",
"message": "Error while running background job OCA\\PreviewGenerator\\BackgroundJob\\PreviewJob (id: 16785, arguments: null)",
"version": "34.0.3.2",
"exception": {
"Exception": "OC\\DB\\Exceptions\\DbalException",
"Message": "An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'file_id' in WHERE is ambiguous",
"Code": 1052,
"Trace": [
{ "file": ".../lib/private/Preview/Db/PreviewMapper.php", "line": 250, "function": "findEntity", "class": "OCP\\AppFramework\\Db\\QBMapper" },
{ "file": ".../lib/private/Preview/Generator.php", "line": 605, "function": "getPreviewForSpecification", "class": "OC\\Preview\\Db\\PreviewMapper" },
{ "file": ".../lib/private/Preview/Generator.php", "line": 566, "function": "savePreview", "class": "OC\\Preview\\Generator" },
{ "file": ".../lib/private/Preview/Generator.php", "line": 184, "function": "generatePreview", "class": "OC\\Preview\\Generator" },
{ "file": ".../lib/private/PreviewManager.php", "line": 203, "function": "generatePreviews", "class": "OC\\Preview\\Generator" },
{ "file": ".../apps/previewgenerator/lib/Service/PreGenerateService.php", "line": 160, "function": "generatePreviews", "class": "OC\\PreviewManager" }
],
"Previous": {
"Exception": "Doctrine\\DBAL\\Exception\\NonUniqueFieldNameException",
"Message": "An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'file_id' in WHERE is ambiguous",
"Code": 1052
}
}
}
</details>
```
### Additional info
The bug does not reproduce on PostgreSQL/SQLite in the same way only if those engines resolve the ambiguity differently; on MySQL/MariaDB it fails consistently on every preview save. Happy to submit a PR with the fix above if maintainers agree with the approach.
8 条评论