nextcloud
[Bug]: MariaDB 11.7.2 causing SQL error with not null longtext column (recognize app)
⚠️ This issue respects the following points: ⚠️
- [x] This is a bug, not a question or a configuration/webserver/proxy issue.
- [x] This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
- [x] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
- [x] 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
- Install latest MariaDB (not supported yet, but will eventually become supported I believe)
- Install Nextcloud 31
- Install recognize App
- 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
(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)
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
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
I created a pull request for doctrine/dbal to test on MariaDB 11.8: https://github.com/doctrine/dbal/pull/6991
Pull request in doctrine/dbal is merged and released. Next step is to update the library. There is an open PR for this, although I think it would be better to update to doctrine/dbal 3.9.x instead of the latest version:
https://github.com/nextcloud/3rdparty/pull/1877
We will update to a newer 3.9.x as we are not ready for 4.0.x yet.
I also added 11.8 to CI and it's failing in the app https://github.com/nextcloud/recognize/pull/1295
Trying to isolate the issue this or next week
Want to mention this here as well: Recognize uses a column name vector, but since 11.7, vector is a reserved keyword in MariaDB, thus causing this error. https://github.com/nextcloud/recognize/issues/1252#issuecomment-3123498464
App bug: https://github.com/nextcloud/recognize/issues/1252#issuecomment-3123498464 Upstream bug: https://github.com/doctrine/dbal/issues/7060