Migration to 9.0.x fails with MariaDb 11.8

[dleidert]

Which version of recognize are you using?

9.0.3

Enabled Modes

Object recognition, Face recognition

TensorFlow mode

Normal mode

Downstream App

Photos App

Which Nextcloud version do you have installed?

31.0.7.1

Which Operating system do you have installed?

Debian 13

Which database are you running Nextcloud on?

11.8.3 (dockerized)

Which Docker container are you using to run Nextcloud? (if applicable)

31.0.7.1 (dockerized)

How much RAM does your server have?

128GB

What processor Architecture does your CPU have?

x86_64

Describe the Bug

I see the following error when attempting to upgrade the recognize app:

Setting log level to debug
Updating database schema
Updated database
Updating <recognize> ...
Exception: Database error when running migration 010000001Date20250727094821 for app recognize
An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'vector, CHANGE face_vector face_vector LONGTEXT NOT NULL' at line 1
Update failed
Maintenance mode is kept active
Resetting log level

I had to disable the app for now. In the app list, it shows as version 9.0.3. When disabling, it shows version 9.0.5. Thus, I assume this is the upgrade 9.03->9.0.5. Due to the error, it cannot be re-enabled at this point.

Expected Behavior

Run the upgrade and migration without error.

To Reproduce

I haven't set up a fresh instance to reproduce. This instance has been running for a while. Thus, the situation might be different between a fresh installation and this long-running one due to all the migrations that happened over time.

Debug log

Please let me know what I can provide to help.

[marcelklehr]

Hi @dleidert
You are using a version of MariaDB that is not supported by Nextcloud 31, which causes this failure.

[L041S]

Same problem here, mariaDB 11.8, which is the default version for Debian 13. Does it mean that every person with an up to date Debian will get that issue ?

[marcelklehr]

If you have mariaDB 11.8 and Nextcloud < 32 this will create issues, yes. There is nothing I can do about this as this is an incompatiblitiy between MariaDB and the DB abstraction library used by Nextcloud.

[L041S]

I see, thank you for the explanations !

[marcelklehr]

@nickvergessen Is there anything we can do?

[nickvergessen]

you can show a descriptive error at least.
I guess we can also try to patch doctrine in the stable branches or look if we can update it

[clorenz150]

The problem is, that starting with MariaDB 11.7.1, VECTOR is a new datatype.
It's currently used as a column name in oc_recognize_face_detections.
A query using vector without backticks is causing the syntax error.

[pelzvieh]

The problem in this specific case seems to arise from lib/Migration/Version010000001Date20250727094721.php, specifically the copy statement that gets executed if column vector and face_vector both exist.
In the separate migration script lib/Migration/Version010000001Date20250727094821.php you find the statement to delete column vector.
Therefore I suggest to execute these statements manually on the MariaDB database:

update oc_recognize_face_detections fdt set fdt.face_vector=fdt.vector;
alter table oc_recognize_face_detections drop column `vector`;
alter table oc_recognize_face_detections modify face_vector longtext NOT NULL;

Due to the defensive checks on previously done migration steps in the migration scripts, these are then able to complete:

occ app:enable recognize

[uberDoward]

Can I run arbitrary DB commands via occ, or is there another way I need to do this?

edit

I'm running a docker compose stack, I connected to my mariadb container via docker exec and then opened mariadb cmd using the same user/pwd as I set up in my nextcloud instance, and ran the comments.

Re-ran occ upgrade from docker exec, and all seems well now. Thanks!