Install issue with SQL Error

[Davr3n]

Which version of recognize are you using?

9.0.0

Enabled Modes

Object recognition

TensorFlow mode

Normal mode

Downstream App

Files App

Which Nextcloud version do you have installed?

31.0.0

Which Operating system do you have installed?

Debian 11.11

Which database are you running Nextcloud on?

1:11.7.2+maria~ubu2404

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

N/A

How much RAM does your server have?

8Gib

What processor Architecture does your CPU have?

x86_64

Describe the Bug

I had the application installed until recently but I have been upgrading some things and wanted to re-enable it but it would appear I am unable to.

The upgrades I completed were to:

• upgrade PHP8.1-FPM to PHP8.2-FPM
• Upgrade NC 8.x to 31 in the various hops with the various indexes and one of the steps did require me to set "ROW_FORMAT=DYNAMIC" on most of my table

Expected Behavior

Application installs and is ready to be configured

To Reproduce

Login as administrator, open "Apps", Find "recognize", click "download and enable"

Debug log

{
    "reqId": "XXXXXXXXXXXXXX",
    "level": 3,
    "time": "2025-03-02T01:28:31+00:00",
    "remoteAddr": "X.X.X.X",
    "user": "XXXXXXXXXXX",
    "app": "settings",
    "method": "POST",
    "url": "/settings/apps/enable",
    "message": "could not enable apps",
    "userAgent": "XXXXXXXXXXXXXXXXXXX",
    "version": "31.0.0.18",
    "exception": {
        "Exception": "Doctrine\\DBAL\\Exception\\SyntaxErrorException",
        "Message": "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 'LONGTEXT NOT NULL, cluster_id BIGINT DEFAULT NULL, threshold DOUBLE PRECISION...' at line 1",
        "Code": 1064,
        "Trace": [
            {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                "line": 1976,
                "function": "convert",
                "class": "Doctrine\\DBAL\\Driver\\API\\MySQL\\ExceptionConverter",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                "line": 1918,
                "function": "handleDriverException",
                "class": "Doctrine\\DBAL\\Connection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                "line": 1218,
                "function": "convertExceptionDuringQuery",
                "class": "Doctrine\\DBAL\\Connection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
                "line": 292,
                "function": "executeStatement",
                "class": "Doctrine\\DBAL\\Connection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                "line": 462,
                "function": "executeStatement",
                "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                "line": 142,
                "function": "executeStatement",
                "class": "OC\\DB\\Connection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                "line": 47,
                "function": "applySchema",
                "class": "OC\\DB\\Migrator",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                "line": 791,
                "function": "migrate",
                "class": "OC\\DB\\Migrator",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                "line": 436,
                "function": "migrateToSchema",
                "class": "OC\\DB\\Connection",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                "line": 387,
                "function": "migrateSchemaOnly",
                "class": "OC\\DB\\MigrationService",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/Installer.php",
                "line": 105,
                "function": "migrate",
                "class": "OC\\DB\\MigrationService",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php",
                "line": 529,
                "function": "installApp",
                "class": "OC\\Installer",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                "line": 200,
                "function": "enableApps",
                "class": "OCA\\Settings\\Controller\\AppSettingsController",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                "line": 114,
                "function": "executeController",
                "class": "OC\\AppFramework\\Http\\Dispatcher",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/AppFramework/App.php",
                "line": 161,
                "function": "dispatch",
                "class": "OC\\AppFramework\\Http\\Dispatcher",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/lib/private/Route/Router.php",
                "line": 307,
                "function": "main",
                "class": "OC\\AppFramework\\App",
                "type": "::"
            },
            {
                "file": "/var/www/html/nextcloud/lib/base.php",
                "line": 1018,
                "function": "match",
                "class": "OC\\Route\\Router",
                "type": "->"
            },
            {
                "file": "/var/www/html/nextcloud/index.php",
                "line": 24,
                "function": "handleRequest",
                "class": "OC",
                "type": "::"
            }
        ],
        "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php",
        "Line": 86,
        "Previous": {
            "Exception": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
            "Message": "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 'LONGTEXT NOT NULL, cluster_id BIGINT DEFAULT NULL, threshold DOUBLE PRECISION...' at line 1",
            "Code": 1064,
            "Trace": [
                {
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php",
                    "line": 39,
                    "function": "new",
                    "class": "Doctrine\\DBAL\\Driver\\PDO\\Exception",
                    "type": "::"
                },
                {
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                    "line": 1216,
                    "function": "exec",
                    "class": "Doctrine\\DBAL\\Driver\\PDO\\Connection",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
                    "line": 292,
                    "function": "executeStatement",
                    "class": "Doctrine\\DBAL\\Connection",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                    "line": 462,
                    "function": "executeStatement",
                    "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                    "line": 142,
                    "function": "executeStatement",
                    "class": "OC\\DB\\Connection",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                    "line": 47,
                    "function": "applySchema",
                    "class": "OC\\DB\\Migrator",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                    "line": 791,
                    "function": "migrate",
                    "class": "OC\\DB\\Migrator",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                    "line": 436,
                    "function": "migrateToSchema",
                    "class": "OC\\DB\\Connection",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                    "line": 387,
                    "function": "migrateSchemaOnly",
                    "class": "OC\\DB\\MigrationService",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/Installer.php",
                    "line": 105,
                    "function": "migrate",
                    "class": "OC\\DB\\MigrationService",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php",
                    "line": 529,
                    "function": "installApp",
                    "class": "OC\\Installer",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                    "line": 200,
                    "function": "enableApps",
                    "class": "OCA\\Settings\\Controller\\AppSettingsController",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                    "line": 114,
                    "function": "executeController",
                    "class": "OC\\AppFramework\\Http\\Dispatcher",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/AppFramework/App.php",
                    "line": 161,
                    "function": "dispatch",
                    "class": "OC\\AppFramework\\Http\\Dispatcher",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/private/Route/Router.php",
                    "line": 307,
                    "function": "main",
                    "class": "OC\\AppFramework\\App",
                    "type": "::"
                },
                {
                    "file": "/var/www/html/nextcloud/lib/base.php",
                    "line": 1018,
                    "function": "match",
                    "class": "OC\\Route\\Router",
                    "type": "->"
                },
                {
                    "file": "/var/www/html/nextcloud/index.php",
                    "line": 24,
                    "function": "handleRequest",
                    "class": "OC",
                    "type": "::"
                }
            ],
            "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php",
            "Line": 24,
            "Previous": {
                "Exception": "PDOException",
                "Message": "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 'LONGTEXT NOT NULL, cluster_id BIGINT DEFAULT NULL, threshold DOUBLE PRECISION...' at line 1",
                "Code": "42000",
                "Trace": [
                    {
                        "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php",
                        "line": 33,
                        "function": "exec",
                        "class": "PDO",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connection.php",
                        "line": 1216,
                        "function": "exec",
                        "class": "Doctrine\\DBAL\\Driver\\PDO\\Connection",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php",
                        "line": 292,
                        "function": "executeStatement",
                        "class": "Doctrine\\DBAL\\Connection",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                        "line": 462,
                        "function": "executeStatement",
                        "class": "Doctrine\\DBAL\\Connections\\PrimaryReadReplicaConnection",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                        "line": 142,
                        "function": "executeStatement",
                        "class": "OC\\DB\\Connection",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/Migrator.php",
                        "line": 47,
                        "function": "applySchema",
                        "class": "OC\\DB\\Migrator",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/Connection.php",
                        "line": 791,
                        "function": "migrate",
                        "class": "OC\\DB\\Migrator",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                        "line": 436,
                        "function": "migrateToSchema",
                        "class": "OC\\DB\\Connection",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/DB/MigrationService.php",
                        "line": 387,
                        "function": "migrateSchemaOnly",
                        "class": "OC\\DB\\MigrationService",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/Installer.php",
                        "line": 105,
                        "function": "migrate",
                        "class": "OC\\DB\\MigrationService",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/apps/settings/lib/Controller/AppSettingsController.php",
                        "line": 529,
                        "function": "installApp",
                        "class": "OC\\Installer",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                        "line": 200,
                        "function": "enableApps",
                        "class": "OCA\\Settings\\Controller\\AppSettingsController",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
                        "line": 114,
                        "function": "executeController",
                        "class": "OC\\AppFramework\\Http\\Dispatcher",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/AppFramework/App.php",
                        "line": 161,
                        "function": "dispatch",
                        "class": "OC\\AppFramework\\Http\\Dispatcher",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/private/Route/Router.php",
                        "line": 307,
                        "function": "main",
                        "class": "OC\\AppFramework\\App",
                        "type": "::"
                    },
                    {
                        "file": "/var/www/html/nextcloud/lib/base.php",
                        "line": 1018,
                        "function": "match",
                        "class": "OC\\Route\\Router",
                        "type": "->"
                    },
                    {
                        "file": "/var/www/html/nextcloud/index.php",
                        "line": 24,
                        "function": "handleRequest",
                        "class": "OC",
                        "type": "::"
                    }
                ],
                "File": "/var/www/html/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Connection.php",
                "Line": 33
            }
        },
        "message": "could not enable apps",
        "exception": {
        },
        "CustomMessage": "could not enable apps"
    }
}

[Davr3n]

Hi Folks,

Just to add more detail, I enable mariadb query logging, threw the query into phpmyadmin and see that this version has an issue with the type "DOUBLE PRECISION".

The query matched from the partial error is:
CREATE TABLE oc_recognize_face_detections (id BIGINT AUTO_INCREMENT NOT NULL, user_id VARCHAR(64) DEFAULT NULL, file_id BIGINT DEFAULT NULL, x DOUBLE PRECISION DEFAULT NULL, y DOUBLE PRECISION DEFAULT NULL, height DOUBLE PRECISION DEFAULT NULL, width DOUBLE PRECISION DEFAULT NULL, vector LONGTEXT NOT NULL, cluster_id BIGINT DEFAULT NULL, threshold DOUBLE PRECISION DEFAULT '0' NOT NULL, INDEX recognize_facedet_cluster (cluster_id), INDEX recognize_facedet_user (user_id), INDEX recognize_facedet_file (file_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB

[jankuca]

I'm getting the same error likely:

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 'LONGTEXT NOT NULL, cluster_id BIGINT DEFAULT NULL, threshold DOUBLE PRECISION...' at line 1

Same specs:

• Recognize 9.0.0
• Nextcloud 31.0.0
• MariaDB 11.7.2-MariaDB-ubu2404

[Monokel777]

Same Error here
(Docker-Compose)

Recognize 9.0.0
Nextcloud 31.0.0
MariaDB 11.7.2-MariaDB-ubu2404