Push notifications do not work (SQL syntax error)

[errotu]

Summary

Push notifications do not work on Android and iOS, probably due to a SQL syntax error.

Steps to reproduce

1. User A sends a message to user B.
2. User B expects push notification on the other end.
3. User B does not receive a push notification.

Operating System

CentOS7

Mattermost Server Version

10.2.0

Expected behavior

Push notifications should be sent to the mobile devices.

Observed behavior

Mobile devices do not receive push notifications (iOS and Android alike).

Log Output

Hundreds of entries like this:

{"timestamp":"2024-11-28 20:53:55.332 +01:00","level":"error","msg":"Failed to send mobile app sessions","caller":"app/notification_push.go:123","logSource":"notifications","type":"push","status":"error","reason":"fetch_error","user_id":"3875fqztif8exe4oo8c1enwd8o","error":"getMobileAppSessions: We encountered an error while finding user sessions., failed to find Sessions with userId=3875fqztif8exe4oo8c1enwd8o: Error 1064 (42000): 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 '>>'$.last_removed_device_id', '')' at line 9"}

Additional Information

The same behavior (with the same error messages) occurs on two different Mattermost instances I maintain. They are completely independent, but are both using the same web hosting service (uberspace). Push notifications worked until a couple of weeks ago.

[amyblais]

Hi @errotu, do you reproduce this on our community server here https://community.mattermost.com/?

[errotu]

No, on the community server I receive the notifications without any problem. Maybe it's related to using either PostgreSQL (what the community server probably does) or MariaDB (what the both instances where the error occurs do)?

Just fyi: I had created the issue in the Mattermost server-repository because my guess was (based on the error message in the server's log) that the cause of the problem lies there, and is not directly related to the app's code.

[ruhnet]

I have the same issue since upgrading to v10.2. I am running MariaDB 10.5 on Debian 11. I upgraded from MM 9.5 to 10.2 and push notifications stopped. In my notifications log I get the same thing as reported above.

[r1de]

Same issue here.
Same SQL Syntax error.
Also using MariaDB on Debian 11.
Just started a few days ago after upgrading.
Tried logging in and out, etc. nothing helps.

[enahum]

@larkox fyi

[errotu]

@amyblais, could you maybe move it back to the server repo? I'm pretty sure it's an issue with the server, not the app.

[r1de]

I'm pretty sure it's an issue with the server, not the app.

Agreed, it does seem to be a server issue and should be moved there.

[agnivade]

@errotu / @r1de - We don't support MariaDB. This is a syntax issue with MariaDB.

[errotu]

I thought that MySQL/MariaDB (as far as I know, MariaDB is completely compatible with the MySQL syntax) are supported until Mattermost v11?

We no longer support new installations using MySQL starting in v10. All new customers and/or deployments will only be supported with the minimum supported version of the PostgreSQL database. End of support for MySQL is targeted for Mattermost v11.

[errotu]

Just fyi: For the moment, I downgraded our server back to 9.11.5 and push notifications work again. However, if v10 does not support MySQL/MariaDB, this should be indicated clearly.

[agnivade]

We support MySQL. Not MariaDB. MariaDB is not compatible with MySQL. As you can see clearly in the error.

[r1de]

It is true that MariaDB and MySQL are 100% compatible only 99% of the time. 😄

That explains it, but the error does not show that clearly.

After reading this, I successfully migrated my MM instance to Postgres today, thereby solving this notification issue on my end (and anything else that might pop up later due to MariaDB).
Ran into a few issues during the migration process, which took several hours and a few failed attempts to figure it all out, but after working all that out it was successful.