Foreign key constraint failure leads to subsequent DELETION of the documents in OneDrive

[SmartLayer]

Bug Report Details

Describe the bug

This look like a serious bug. It's a bit long to describe, kindly read on.

Overview

First, the initial synchronisation failed, with "Foreign key constraint failure" when 0.1% of the files are downloaded to an empty target. This is not a big deal itself, software breaks all the time. The following is the real problem:

Then, the user - out of habit - rerun the same commandline to see if any sporatic issue caused the failure, which lead every file not yet synced deleted from the OneDrive server side, that is, 99.9% of the files are gone. It seems onedrive thought the previous sync was 100% successful and the fact that 99.9% are missing is that someone deleted them, and ordered the deletion from the server. These can be recovered from Recycle Bin but we can't rely on the intelligence of the user to recover from such a failure.

The output and log will be provided below. A right solution to this problem should consider not marking the directory as previously synced if any error occurred.

• One can argue that the user should use "download" instead of "sync" but we can't assume users, even seasoned sysop users, understand that sync-once and seeing error redoing it might cause deletion, which is unexpected unless you know the inside work of this software.

The initial error

Running onedrive synchronise for the first time with an empty directory as target. Got this error:

A database statement execution error occurred: FOREIGN KEY constraint failed

This may be related to the content of the specific OneDrive account, so for testing please let me know how to reach you to provide the authorisation code / config to support@mynas.com.au if you find useful.

Note that by using 2.4.13, PR #856 is already applied, which was addressing #851

Application and Operating System Details:

• Provide your OS & version (CentOS 6.x, Ubuntu 18.x etc) and the output of: uname -a

Ubuntu 20.04

uname:

Linux thinkpad 5.4.0-42-generic #46-Ubuntu SMP Fri Jul 10 00:24:02 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

• Are you using a headless system (no gui) or with a gui installed?

no gui.

• OneDrive Account Type

Personal. Free.

• Did you build from source or install from a package?

OpenSuSE's build service for Ubuntu.

• OneDrive Application Version: Output of onedrive --version

onedrive v2.4.13-1~np1

• OneDrive Application Configuration: Output of onedrive --display-config

~$ onedrive --display-config
Configuration file successfully loaded
onedrive version                       = v2.4.13-1~np1
Config path                            = /home/a/.config/onedrive
Config file found in config path       = true
Config option 'check_nosync'           = false
Config option 'sync_dir'               = /var/local/OneDrive
Config option 'skip_dir'               = 
Config option 'skip_file'              = ~*|.~*|*.tmp
Config option 'skip_dotfiles'          = false
Config option 'skip_symlinks'          = false
Config option 'monitor_interval'       = 300
Config option 'min_notify_changes'     = 5
Config option 'log_dir'                = /var/log/onedrive/
Config option 'classify_as_big_delete' = 1000
Config option 'upload_only'            = false
Config option 'no_remote_delete'       = false
Config option 'remove_source_files'    = false
Config option 'sync_root_files'        = false
Selective sync 'sync_list' configured  = false
Business Shared Folders configured     = false

• Provide the version of curl you are using: Output of curl --version

curl 7.68.0 (x86_64-pc-linux-gnu) libcurl/7.68.0 OpenSSL/1.1.1f zlib/1.2.11 brotli/1.0.7 libidn2/2.3.0 libpsl/0.21.0 (+libidn2/2.2.0) libssh/0.9.3/openssl/zlib nghttp2/1.40.0 librtmp/2.3

• Is your configured 'sync_dir' a local directory or a network mount point?

local.

• What partition format type does your configured 'sync_dir' reside on? Output of: lsblk -f

ext4

• Explain your entire configuration setup - is the OneDrive folder shared with any other system, shared with any other platform at the same time, is the OneDrive account you use shared across multiple systems / platforms / Operating Systems and in use at the same time

OneDrive folder is accessible by mobile devices through their app and another OS X computer but they are not in use when this error occur. Also the error occurred while syncing a folder that is disused for years.

Note: Please generate a full debug log whilst reproducing the issue as per https://github.com/abraunegg/onedrive/wiki/Generate-debug-log-for-support and email to support@mynas.com.au

To Reproduce
Steps to reproduce the behavior if not causing an application crash:

See application log output

Debug Log Output

debug_output_of_the_2nd_run_which_lead_to_99.9%_content_deletion.log

** Application Output** (first run, the one that resulted on foreign-key failure, not the one that deleted 99.9% files):

$ onedrive --synchronize 
Configuration file successfully loaded
Configuring Global Azure AD Endpoints
Initializing the Synchronization Engine ...
Syncing changes from OneDrive ...
Creating local directory: Documents
Creating local directory: Documents/My Games
Creating local directory: Documents/Battlefield 3
Creating local directory: Documents/Respawn
Creating local directory: Documents/Rockstar Games
Creating local directory: Documents/My Games/Fallout4
Creating local directory: Documents/Battlefield 3/Screenshots
Creating local directory: Documents/Battlefield 3/settings
Creating local directory: Documents/Rockstar Games/Launcher
Creating local directory: Documents/Rockstar Games/Red Dead Redemption 2
Creating local directory: Documents/Respawn/Titanfall2
Creating local directory: Documents/Rockstar Games/Social Club
Creating local directory: Documents/My Games/Fallout4/F4SE
Creating local directory: Documents/My Games/Fallout4/Saves
Downloading file Documents/Battlefield 3/settings/PROF_SAVE_header ... done.
Downloading file Documents/Battlefield 3/settings/PROF_SAVE_body ... done.
Downloading file Documents/Battlefield 3/settings/meta.xml ... done.
Downloading file Documents/Battlefield 3/settings/PROF_SAVE_profile ... done.
Downloading file Documents/Rockstar Games/Launcher/launcher.01.log ... done.
Downloading file Documents/Rockstar Games/Social Club/socialclub.log ... done.
Downloading file Documents/Rockstar Games/Launcher/launcher.log ... done.
Downloading file Documents/Rockstar Games/Social Club/socialclub_launcher.log ... done.
Creating local directory: Documents/Respawn/Titanfall2/local
Creating local directory: Documents/Rockstar Games/Social Club/Cache
Creating local directory: Documents/Rockstar Games/Launcher/Profiles
Creating local directory: Documents/Rockstar Games/Red Dead Redemption 2/Profiles
Creating local directory: Documents/Rockstar Games/Red Dead Redemption 2/Settings
Creating local directory: Documents/Rockstar Games/Social Club/Titles
Creating local directory: Documents/Respawn/Titanfall2/profile
Creating local directory: Documents/Rockstar Games/Social Club/Profiles
Creating local directory: Documents/Rockstar Games/Social Club/Renderer
Creating local directory: Documents/Rockstar Games/Social Club/Launcher
Downloading file Documents/My Games/Fallout4/F4SE/f4se_steam_loader.log ... done.
Downloading file Documents/My Games/Fallout4/F4SE/LL_fourPlay.log0 ... done.
Downloading file Documents/My Games/Fallout4/F4SE/LL_fourPlay.log ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_Commonwealth_010720_20200613080535_17_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_Commonwealth_020847_20200623171506_23_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_Commonwealth_021255_20200627200859_25_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_Goodneighbor_011556_20200615163102_19_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_DLC04ColaCars01_031525_20200718221502_31_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_RailroadHQ01_021713_20200630214221_26_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave1_07F3926CM4C697361_DLC01Lair01_020235_20200619210042_22_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave2_07F3926CM4C697361_Commonwealth_020838_20200623170639_23_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave2_07F3926CM4C697361_Commonwealth_010722_20200613080709_17_2.f4se ... done.
Downloading file Documents/My Games/Fallout4/Saves/Autosave2_07F3926CM4C697361_Commonwealth_021301_20200627202018_25_2.f4se ... done.

A database statement execution error occurred: FOREIGN KEY constraint failed

Please retry your command with --resync to fix any local database corruption issues.

Violación de segmento (`core' generado)

Bug Report Checklist

• Detailed description
• Application and Operating System Details provided in full
• []x Reproduction steps (if applicable)
• Verbose Log Output from your error
• [] Debug Log generated and submitted

[abraunegg]

@colourful-land
Given your comments in #1603 and #1604 it can safely be assumed that you have installed the older version of the client, and potentially still have this installed somewhere. You then have upgraded the client, and using the SuSE Build Service packages - but this process, because of packaging differences does not remove / upgrade the old client.

The problem here is - the original package is probably also still installed. It is still using the authentication tokens that the newer package created (as they as still valid), and if you are running any service, you might also be hit by #1274

When the application generates this message, it is because the local DB schema has been changed / modified - this is the key piece of information that leads me to the assumption you have an old version of code running in the background as the newer client versions have a DB schema change which was needed to implement certain features.

Steps for you to perform:

1. Ensure that you remove all 'onedrive' packages - this will not remove your data / config
2. Ensure that you cleanup your platform of old packages correctly:

#!/bin/bash
rm -rf /var/lib/dpkg/lock-frontend
rm -rf /var/lib/dpkg/lock
sudo apt-get update
sudo apt-get upgrade -y
sudo apt-get dist-upgrade -y
sudo apt-get autoremove -y
sudo apt-get autoclean -y

3. Ensure that 'onedrive' binary is no where on your system
4. Install the package again from the SuSE Build Service so that you can be sure that there is only this version installed.
5. Ensure you fix the systemd service by manually removing the link that is created that should not be created - this is a packaging problem that I cannot fix

Once you have done these steps to ensure you do not have an old version installed, please try again.

[abraunegg]

@colourful-land
Further to this, which also confirms my assumption, your debug log file shows zero deletion events of any data - this reinforces that another binary version is running, in the background, which modified your cache database, thus leading to this error, but that binary, also deleted the data ....

[SmartLayer]

1. Check if double installation happened

@abraunegg dpkg -l shows the latest

$ dpkg -l | grep onedrive
ii  onedrive                                   2.4.13-1~np1                                amd64        folder synchronization with OneDrive

I don't remember if I deleted the Ubuntu universal edition so:

2. Make sure only one version onedrive is installed

I removed the package, tested that /usr/bin/onedrive disappeared, and installed it again per @abraunegg 's suspicion. This time it should have only 1 onedrive:

$ LANG=en_US.UTF-8 sudo apt install onedrive
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages were automatically installed and are no longer required:
  epiphany-browser-data fonts-open-sans gnucash-common gnucash-docs gradle-ppa libclass-inspector-perl libclass-singleton-perl
  libdate-manip-perl libdatetime-format-strptime-perl libdatetime-locale-perl libdatetime-perl libdatetime-timezone-perl
  libfile-sharedir-perl libfinance-quote-perl libhtml-tableextract-perl libjson-parse-perl libjson-perl libllvm11 libllvm11:i386
  libminizip1 libqrcodegencpp1 librlottie0-1 libtext-template-perl libxdamage1:i386 python3-gnucash
Use 'sudo apt autoremove' to remove them.
The following NEW packages will be installed:
  onedrive
0 upgraded, 1 newly installed, 0 to remove and 74 not upgraded.
Need to get 0 B/526 kB of archives.
After this operation, 2,168 kB of additional disk space will be used.
Selecting previously unselected package onedrive.
(Reading database ... 398190 files and directories currently installed.)
Preparing to unpack .../onedrive_2.4.13-1~np1_amd64.deb ...
Unpacking onedrive (2.4.13-1~np1) ...
Setting up onedrive (2.4.13-1~np1) ...
Created symlink /etc/systemd/user/default.target.wants/onedrive.service → /usr/lib/systemd/user/onedrive.service.
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for systemd (245.4-4ubuntu3.11) ...

3. Redo the test

Then initialise the synchronise:

$ onedrive --synchronize --verbose --verbose > debug_output.log 2>&1

Output: debug_output.log

finding all files in OneDrive deleted. I hurriedly recovered them from OneDrive's online recycle bin.

4. Redo the test without database

So I think maybe it's because the database was dirty. Remove:

a@thinkpad:~$ mv .config/onedrive/config  .config/onedrive/refresh_token  /tmp/
a@thinkpad:~$ mv .config/onedrive/ /tmp/
a@thinkpad:~$ mkdir .config/onedrive
a@thinkpad:~$ mv /tmp/config /tmp/refresh_token  .config/onedrive/
a@thinkpad:~$ onedrive --synchronize --verbose --verbose > debug_output.log 2>&1

Then waited 5 hours but still hasn't finished #1608 , check the log there seem to be an unrelated problem (something look like a timeout). Check the Recycle Bin online and there is no new deletion. means the problem failed to reproduce and I'll close this issue for now.

[abraunegg]

@colourful-land
You have also not followed the directions correctly:

Created symlink /etc/systemd/user/default.target.wants/onedrive.service → /usr/lib/systemd/user/onedrive.service.

Make sure you manually remove this symbolic link which is created in error by the package .... this should not be created - see: #1274