How to fix differencies between onlyoffice files and nextcloud files

[ljf]

The problem

Since November, we have sometimes on this forum people explaining they have differencies between a file they edit inside onlyoffice and the file they can download from nextcloud interface OR they synchronize thanks to nextcloud client.

This bug is quite serious cause it seems to work but in the background you have 2 files becoming different. Due to this bug, data loss could easily appears.

In order to fix the issue for ReflexLibre customer, i find a solution. This topic speaks about the issue, and offer you a way to fix this behaviour But be careful and make backups of your nextcloud files.

Note: i speak about setup with onlyoffice installed by yunohost (not from the nextcloud apps) and bind to nextcloud 21 or 22 with the nextcloud-onlyoffice app (from netxtcloud app list). If you use an external resolver in /etc/resolv.conf instead of dnsmasq, you are not concerned.

First of all, you need to understand you probably have some files unregistered in Nextcloud waiting in /var/lib/onlyoffice/documentserver/App_Data/cache/

The solution

Step 1. Backup

Made backup of nextcloud files, onlyoffice app and a manual copy of /var/lib/onlyoffice/documentserver/App_Data/cache/

Step 2. Check onlyoffice files

Be sure the files in the onlyoffice cache are the versions you want. If yes you can do the other step.

If you want to keep nextcloud files (use the download features and not the open feature that open in onlyoffice), in this case you should reinstall onlyoffice to clean the cache before.

If it’s mixed, it’s a mess… Those instructions can help you in this case to make conflict appears inside nextcloud

app=nextcloud
pushd /var/lib/onlyoffice/documentserver/App_Data/cache/files/forgotten
for cache_dir in $(ls); do
    oo_file_id="${cache_dir%_*}"
    nc_file_id="$(sudo -u postgres psql -d onlyoffice -AXqtc "SELECT callback FROM task_result WHERE id = '${oo_file_id}';" | sed "s@\x05@@g" | jq --raw-output .callback | head -n1 | grep -o "\.ey.*\." | sed "s@\.@@g" | base64 -d 2> /dev/null | jq --raw-output .fileId)"
    if [[ "$nc_file_id" == "" ]] ; then
        continue
    fi
    file_path="$(echo "SELECT CONCAT(s.id, '/', f.path) FROM oc_filecache as f, oc_storages as s  WHERE f.fileid = ${nc_file_id} AND f.storage = s.numeric_id;" | mysql -N $app | sed "s@home::@/home/yunohost.app/$app/data/@" | sed "s@local::@@")"
    cp -a "$file_path" "$(echo $file_path | sed s@\.docx@_backup.docx@ | sed s@\.xlsx@_backup.xlsx@ | sed s@\.pptx@_backup.pptx@ | sed s@\.odt@_backup.odt@ | sed s@\.ods@_backup.ods@)"
done

pushd /var/lib/onlyoffice/documentserver/App_Data/cache/files/

for cache_dir in $(ls *_*); do
    oo_file_id="${cache_dir%_*}"
    nc_file_id="$(sudo -u postgres psql -d onlyoffice -AXqtc "SELECT callback FROM task_result WHERE id = '${oo_file_id}';" | sed "s@\x05@@g" | jq --raw-output .callback | head -n1 | grep -o "\.ey.*\." | sed "s@\.@@g" | base64 -d 2> /dev/null | jq --raw-output .fileId)"
    if [[ "$nc_file_id" == "" ]] ; then
        continue
    fi
    file_path="$(echo "SELECT CONCAT(s.id, '/', f.path) FROM oc_filecache as f, oc_storages as s  WHERE f.fileid = ${nc_file_id} AND f.storage = s.numeric_id;" | mysql -N $app | sed "s@home::@/home/yunohost.app/$app/data/@" | sed "s@local::@@")"
    cp -a "$file_path" "$(echo $file_path | sed s@\.docx@_backup.docx@ | sed s@\.xlsx@_backup.xlsx@ | sed s@\.pptx@_backup.pptx@ | sed s@\.odt@_backup.odt@ | sed s@\.ods@_backup.ods@)"
done
sudo -u $app /usr/bin/php7.3 --define apc.enable_cli=1 /var/www/$app/occ files:scan --all

Step 3. Upgrade the nextcloud-onlyoffice connector to 7.3.2

A fix has been added recently to nextcloud-onlyoffice 7.3.2.
Note you need Nextcloud 21+ to be able to do that. If you are up to date on YunoHost nextcloud app your in Nextcloud 22+ at this date.
=> So, now, you need to upgrade to the connector inside nextcloud.
See, for details: Cached documents get no updates from file changes in Nextcloud because the app Hooks does not work · Issue #562 · ONLYOFFICE/onlyoffice-nextcloud · GitHub

Step 4. Fix the “remote domain local” issue

Nextcloud has security (i guess) rules that prevent to Nextcloud to communicate with remote domains that return a local ip (like 127.0.0.1). If you are affected, you can see this message in the log “Host violates local access rules”.

In details, this message appears due to a conflict between this security rules from Nextcloud and a Yunohost behaviour using DNSmasq to return 127.0.0.1 for each domains managed by YunoHost. This behaviour helps YunoHost in installing apps even if DNS are not well configured or if there is no full internet connection (and in other use cases).

On YunoHost 4.x, remove DNSMasq file for the onlyoffice domain

rm /etc/dnsmasq.d/office.DOMAIN.TLD
systemctl restart dnsmasq

Be sure this domain has not been added in /etc/hosts too.

Note: if you have hairpining on your network, it won’t work instead you should patch Nextcloud 22 code by adding between line 111 and 112 of /var/www/nextcloud/lib/private/Http/Client/DnsPinMiddleware.php:

$options['nextcloud']['allow_local_address'] = true;

This patch should disappears after each upgrade of the app.

On YunoHost 11, install nextcloud 23

Nextcloud 23 has a settings to manage this protection rules, but not Nextcloud 22 (the version deployed in YunoHost 4.x). It will be added by the testing package (a fix will be produced soon)

So wait or apply the fix for YunoHost 4.x

Step 5. Restart PHP

systemctl restart php7.3-fpm

Step 6. Test

To make a test,

1. go in nextcloud web
2. create an xlsx
3. Edit it with onlyoffice
4. Return on the nextcloud files web view
5. Download the file from nextcloud (not just open it via onlyoffice)

You can also check the date of change of your files:

ls -l /home/yunohost.app/nextcloud/data/USER/files/test.xlsx

Note: on my side the file is saved in the 3 minutes after the change in onlyoffice. SO the bug is fixed. If the bug is fixed the file is never changed.

[ljf]

@adepetigny ^