Upgrading from 10.0.9 to 10.9.1 failed with "Foreign key constraint is incorrectly formed"

[Badb0y]

Steps to reproduce

Stop basic stuffs
sudo -u apache php /var/www/html/occ maintenance:mode --on
systemctl stop crond
systemctl stop httpd

backup db
mkdir -p /var/tmp/owncloudbackupfiles/db
DATE=date +%Y%m%d%H%M%S
/bin/mysqldump -u owncloud -pxyz owncloud > /var/tmp/owncloudbackupfiles/db/owncloud-$DATE.dump

backup htaccess and user ini and download the update file
cp /var/www/html/.htaccess /var/tmp/owncloudbackupfiles/
cp /var/www/html/.user.ini /var/tmp/owncloudbackupfiles/
cd /var/www
wget --no-check-certificate https://download.owncloud.org/community/owncloud-complete-latest.tar.bz2

Move Current ownCloud Directory
mv /var/www/html /var/www/backup_of_html_before_update

extract the new owncloud
tar -xf owncloud-complete-latest.tar.bz2
mv owncloud html

copy config.php to the new folder
cp -pr /var/www/backup_of_html_before_update/config/config.php /var/www/html/config/

check the apps are available in the new one that was available in the old one, if not available something has to copy over
ll html/apps/
ll backup_of_html_before_update/apps/

set permissions
find -L /var/www/html ( -path ./data -o -path ./config ) -prune -o -type d -exec chown root:apache {} +
find -L /var/www/html ( -path ./data -o -path ./config ) -prune -o -type f -exec chown root:apache {} +
chown -R apache:apache /var/www/html/config
chown -R apache:apache /var/www/html/data
chown -R apache:apache /var/www/html/apps
find /var/www/html -type f -exec chmod 640 {} ;
find /var/www/html -type d -exec chmod 750 {} ;

before update has to change to php 7.3 first because it is not compatible with my current version php56w (56 common removed)
yum-config-manager --disable webtatic
yum-config-manager --enable remi-php73
yum install php php-cli php-fpm php-mysqlnd php-zip php-devel php-gd php-mcrypt php-mbstring php-curl php-xml php-pear php-bcmath php-json php-intl

sudo -u apache php /var/www/html/occ app:disable files_videoplayer
sudo -u apache php /var/www/html/occ upgrade

Expected behaviour

It should update the owncloud

Actual behaviour

Failed with DB error

10:53:01 May 20, 2022 Set log level to debug
10:53:01 May 20, 2022 Repair step: Upgrade app code from the marketplace
10:53:01 May 20, 2022 Repair warning: Market app is unavailable for updating of apps. Please update manually, see Redirect Notice
10:53:01 May 20, 2022 Repair step: Repair MySQL database engine
10:53:01 May 20, 2022 Repair step: Repair MySQL collation
10:53:01 May 20, 2022 Repair info: All tables already have the correct collation → nothing to do
10:53:01 May 20, 2022 Repair step: Repair SQLite autoincrement
10:53:01 May 20, 2022 Repair step: Repair orphaned reshare
10:53:01 May 20, 2022 Repair step: Repair duplicate entries in oc_lucene_status
10:53:01 May 20, 2022 Repair info: lucene_status table does not exist → nothing to do
10:53:01 May 20, 2022 Updating database schema
10:53:02 May 20, 2022 Doctrine\DBAL\Exception\DriverException: An exception occurred while executing ‘ALTER TABLE oc_persistent_locks ADD CONSTRAINT FK_F0C3D55B93CB796C FOREIGN KEY (file_id) REFERENCES oc_filecache (fileid) ON DELETE CASCADE’:
SQLSTATE[HY000]: General error: 1005 Can’t create table owncloud.#sql-4ce_bcde2a2 (errno: 150 “Foreign key constraint is incorrectly formed”)
10:53:02 May 20, 2022 Update failed
10:53:02 May 20, 2022 Maintenance mode is kept active
10:53:02 May 20, 2022 Reset log level

Server configuration

Operating system: centos7 - 3.10.0-693.5.2.el7.x86_64

Web server: apache httpd

httpd-tools-2.4.6-93.el7.centos.x86_64
httpd24-libnghttp2-1.7.1-6.el7.x86_64
httpd24-httpd-tools-2.4.27-8.el7.x86_64
httpd-2.4.6-93.el7.centos.x86_64
httpd24-runtime-1.1-18.el7.x86_64
httpd24-httpd-2.4.27-8.el7.x86_64

Database: mariadb

mariadb-10.1.20-1.el7.x86_64
mariadb-common-10.1.20-1.el7.x86_64
mariadb-config-10.1.20-1.el7.x86_64
mariadb-galera-common-5.5.40-3.el7.x86_64
mariadb-server-galera-10.1.20-1.el7.x86_64
mariadb-libs-10.1.20-1.el7.x86_64
mariadb-errmsg-10.1.20-1.el7.x86_64
mariadb-server-10.1.20-1.el7.x86_64
php56w-mysql-5.6.40-1.w7.x86_64
perl-DBD-MySQL-4.023-5.el7.x86_64
rh-php56-php-mysqlnd-5.6.25-1.el7.x86_64

PHP version: PHP 5.6.40 (cli) (built: Jan 12 2019 13:11:15)

ownCloud version: 10.0.9 (stable)

Updated from an older ownCloud or fresh install: update from older

Where did you install ownCloud from: https://download.owncloud.org/community/owncloud-complete-latest.tar.bz2

Signing status (ownCloud 9.0 and above):
No errors have been found.

The content of config/config.php:

{
“system”: {
“instanceid”: “ockfcvhe73ml”,
“passwordsalt”: “REMOVED SENSITIVE VALUE”,
“secret”: “REMOVED SENSITIVE VALUE”,
“trusted_domains”: [
“1.2.3.4”,
“1.2.3.4”
],
“datadirectory”: “/data/owncloud”,
“overwrite.cli.url”: “https://file.mycompany.com”,
“overwritehost”: “file.mycompany.com”,
“dbtype”: “mysql”,
“version”: “10.0.9.5”,
“dbname”: “owncloud”,
“dbhost”: “1.2.3.4:3306”,
“dbtableprefix”: “oc_”,
“dbuser”: “REMOVED SENSITIVE VALUE”,
“dbpassword”: “REMOVED SENSITIVE VALUE”,
“installed”: true,
“updater.secret”: “REMOVED SENSITIVE VALUE”,
“mail_smtpmode”: “smtp”,
“mail_from_address”: “REMOVED SENSITIVE VALUE”,
“mail_domain”: “REMOVED SENSITIVE VALUE”,
“mail_smtphost”: “REMOVED SENSITIVE VALUE”,
“mail_smtpport”: “25”,
“ldapIgnoreNamingRules”: false,
“log_type”: “owncloud”,
“logfile”: “/var/log/owncloud/owncloud.log”,
“loglevel”: 2,
“logdateformat”: “H:i:s F d, Y”,
“logtimezone”: “Asia/Bangkok”,
“log_authfailip”: true,
“theme”: “”,
“trashbin_retention_obligation”: “auto, 30”,
“maintenance”: false,
“dbdriveroptions”: {
“1002”: “SET wait_timeout = 288000”
},
“integrity.ignore.missing.app.signature”: [
“theme-mycompany”
],
“integrity.excluded.files”: [
“core/templates/login.php”,
“core/templates/layout.user.php”,
“.htaccess”,
“.user.ini”,
“core/skeleton/Documents/Example.odt”,
“core/skeleton/Photos/Paris.jpg”,
“core/skeleton/Photos/San Francisco.jpg”,
“core/skeleton/Photos/Squirrel.jpg”
]
}
}

List of activated apps:

• activity: 2.3.7
• comments: 0.3.0
• dav: 0.3.2
• encryption: 1.3.1
• federatedfilesharing: 0.3.1
• files: 1.5.1
• files_antivirus: 0.13.0
• files_external: 0.7.1
• files_pdfviewer: 0.9.0
• files_sharing: 0.10.1
• files_texteditor: 2.2.1
• files_trashbin: 0.9.1
• notifications: 0.3.4
• provisioning_api: 0.5.0
• systemtags: 0.3.0
• user_ldap: 0.11.0

Are you using external storage, if yes which one:
glusterfs, I have 2 owncloud instance with balanced mariadb and glusterfs on an fs in the /data directory.

Are you using encryption: yes

Are you using an external user-backend, if yes which one: LDAP

LDAP configuration (delete this part if not used)

limited to 32000 characters the paste here

[Badb0y]

I had a look in the DB:
oc_persistent_lock bigint lenght is 20, this should be int 11 maybe because the oc_filecache is int length 11??

If I change it it should fix the issue but not sure this is the right way to fix it, hopefully someone smarter than me can advise.