[Bug]: MariaDB 11.7.2 causing SQL error with not null longtext column (recognize app)

[marcelklehr]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

when using recognize app with MariaDB 11.7.2 in Nextcloud 31 you get an SQL error.

Steps to reproduce

1. Install latest MariaDB (not supported yet, but will eventually become supported I believe)
2. Install Nextcloud 31
3. Install recognize App
4. 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

In recognize (I'm the author and maintainer), I'm using the following model code to access the db: https://github.com/nextcloud/recognize/blob/main/lib/Db/FaceDetection.php#L65

And the following code for the migration that seems to offend: https://github.com/nextcloud/recognize/blob/main/lib/Migration/Version003001000Date20221017094721.php#L31

Expected behavior

It should just work as it did in previous MariaDB versions.

Nextcloud Server version

31

Operating system

None

PHP engine version

None

Web server

None

Database engine version

None

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

List of activated Apps

Nextcloud Signing status

Nextcloud Logs

Additional info

No response

[susnux]

(MariaDB 11.7 will never be supported as it is EOL in ~2 month, but 11.8 will be the next LTS so I guess we will face the same issue with 11.8)

[marcelklehr]

cc @nickvergessen as dbal expert

[nickvergessen]

Normally the error messages start with the problematic part, so it's not float, but:

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

the LONGTEXT column that is the issue

[nickvergessen]

And regarding support, if there is an issue it's upstream and as long as doctrine/dbal is not supporting the version we can not claim to support it:
https://github.com/doctrine/dbal/blob/4.2.x/.github/workflows/continuous-integration.yml#L295-L299

[jaylinski]

I created a pull request for doctrine/dbal to test on MariaDB 11.8: doctrine/dbal#6991