SQL Error 1064 (Syntax error) when posting/replying on Flarum 2.0.0-rc.2

MrLaviana1

Hi everyone,

I am experiencing a critical issue after updating my Flarum installation from v2.0.0-rc.1 to v2.0.0-rc.2. Whenever I try to post a new comment or reply to an existing one, I get a 500 Internal Server Error.

This happens both on my local XAMPP environment and on my web hosting . The error appears to be related to a database syntax violation involving the CAST(... AS JSON) command.

Error Log:

PDOException: 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 'JSON)' at line 1...

SQL: select * from notifications where (type = newPostInTag and from_user_id = 1 and subject_id = 27) and data = CAST({"postNumber":4} AS JSON)
Environment Details:

Flarum Version: 2.0.0-rc.2

Database: MariaDB 11.4

Server: Localhost (XAMPP) and shared hosting.

I have verified that running SELECT CAST('{"postNumber":12}' AS JSON); directly in phpMyAdmin returns the same #1064 syntax error, suggesting that the database environment is rejecting the CAST(... AS JSON) syntax used by the new RC2 release.

Has anyone encountered this incompatibility with MariaDB 11.x in the RC2 version? Is there a known workaround or a specific sql_mode configuration that I should be using to make this compatible?

Any help would be greatly appreciated!

MrLaviana1

Flarum core: 2.0.0-rc.2
PHP version: CLI: 8.3.31, Web: unable to detect
PHP memory limit: CLI: 512M, Web: unable to detect
MySQL version: 10.4.32
Loaded extensions: Core, bcmath, calendar, ctype, date, filter, hash, iconv, json, SPL, pcre, random, readline, Reflection, session, standard, mysqlnd, tokenizer, zlib, libxml, dom, PDO, openssl, SimpleXML, xml, xmlreader, xmlwriter, apache2handler, fileinfo, gd, mbstring, mysqli, Phar, pdo_mysql, zip
+--------------------------+-----------------+--------+-------+
| Flarum Extensions        |                 |        |       |
+--------------------------+-----------------+--------+-------+
| ID                       | Version         | Commit | Notes |
+--------------------------+-----------------+--------+-------+
| flarum-flags             | v2.0.0-rc.2     |        |       |
| flarum-tags              | v2.0.0-rc.2     |        |       |
| flarum-approval          | v2.0.0-rc.2     |        |       |
| flarum-suspend           | v2.0.0-rc.2     |        |       |
| flarum-gdpr              | v2.0.0-rc.2     |        |       |
| flarum-mentions          | v2.0.0-rc.2     |        |       |
| flarum-subscriptions     | v2.0.0-rc.2     |        |       |
| fof-user-directory       | 2.0.0-beta.5    |        |       |
| fof-follow-tags          | 2.0.0-beta.6    |        |       |
| fof-pages                | 2.0.0-beta.2    |        |       |
| flarum-markdown          | v2.0.0-rc.2     |        |       |
| resofire-mobile-search   | v0.1.9          |        |       |
| resofire-menu-control    | v2.2.7          |        |       |
| ianm-follow-users        | 2.0.0-beta.7    |        |       |
| huseyinfiliz-leaderboard | 2.0.0-beta.1    |        |       |
| gitzaai-v2mobileui       | v2.0.0          |        |       |
| forumaker-magicslider    | 2.4.0           |        |       |
| fof-user-bio             | 2.0.0-beta.3    |        |       |
| fof-upload               | 2.0.0-beta.5    |        |       |
| fof-sitemap              | 3.0.0-beta.6    |        |       |
| fof-rich-text            | 2.0.0-beta.5    |        |       |
| fof-profile-image-crop   | 2.0.0-beta.1    |        |       |
| fof-moderator-warnings   | 2.0.0-beta.4    |        |       |
| fof-links                | 2.0.0-beta.3    |        |       |
| fof-linguist             | 2.0.0-beta.1    |        |       |
| fof-html-errors          | 2.0.0-beta.1    |        |       |
| fof-formatting           | 2.0.0-beta.2    |        |       |
| fof-drafts               | 2.0.0-beta.7    |        |       |
| fof-discussion-views     | 2.0.0-beta.1    |        |       |
| fof-discussion-thumbnail | 2.0.0-beta.1    |        |       |
| fof-best-answer          | 2.0.0-beta.5    |        |       |
| fof-badges               | 2.0.0-beta.1    |        |       |
| flarum-sticky            | v2.0.0-rc.2     |        |       |
| flarum-statistics        | v2.0.0-rc.2     |        |       |
| flarum-nicknames         | v2.0.0-rc.2     |        |       |
| flarum-messages          | v2.0.0-rc.2     |        |       |
| flarum-lock              | v2.0.0-rc.2     |        |       |
| flarum-likes             | v2.0.0-rc.2     |        |       |
| flarum-lang-spanish      | dev-release/2.x |        |       |
| flarum-emoji             | v2.0.0-rc.2     |        |       |
| flarum-bbcode            | v2.0.0-rc.2     |        |       |
| ernestdefoe-theme-toggle | 3.0.10          |        |       |
| antoinefr-bbcode-fa      | v2.0.0          |        |       |
+--------------------------+-----------------+--------+-------+
Base URL: http://localhost
Installation path: C:\xampp\htdocs
Queue driver: sync
Session driver: file
Scheduler status: Nunca se ejecuta
Mail driver: log
Debug mode: ON

huoxin

MrLaviana1 I encountered this also but havent really take a look into it, please raise this on github issue
AI told me to disable Subscriptions, which works, but I think there is something wrong in the core or the extension itself

IanM

MrLaviana1 you are using MariaDB but have the MySQL driver set in config.php

On Flarum 2.0 it is vital to set the db configuration correctly, else you will see exactly this type of error

IanM

huoxin unfortunately, this is one of the many shortfalls of AI. It’s simply incorrect here, and nothing to do with the code!

MrLaviana1

IanM "Hi IanM,

Thank you so much! You were absolutely right. I changed the database driver from mysql to mariadb in my config.php and the error is completely gone. I can now post and reply with all my extensions enabled, including mentions and subscriptions.

I’ve been struggling with this issue for a long time, so I really appreciate your help in pointing out the configuration error. Problem solved!"

AyseDogan

Thanks for sharing the solution. This definitely looks like a configuration issue rather than a bug in Flarum itself. The error message around CAST(... AS JSON) can be quite misleading and makes it seem like a MariaDB compatibility problem at first glance.

It's a good reminder to double-check the database driver settings after upgrading, especially when moving to a new release candidate. If the MySQL driver is configured while running MariaDB, unexpected SQL syntax errors can appear even when the database version itself supports the required features.

Hopefully this helps anyone else encountering a similar 500 error after upgrading to Flarum 2.0 RC2. Thanks to everyone who helped identify the root cause and shared the fix!

huoxin

Changing the config.php fix the issue, didnt thought of that. Thanks for sharing.

'driver' => 'mariadb',

Karl

huoxin it's been in the docs for Flarum 2.x for a while 😛

MrLaviana1

Karl I didn't know, since it came from rc1 I didn't have that problem and when I updated I went crazy for days looking for solutions and that was it...XD

MrLaviana1

huoxin That's it, thanks to @Ian M, he solved the problem for me, I had been going crazy looking for the problem for days, I thought it was the PHP or MariaDB version, it was as simple as changing that hahaha

Karl

MrLaviana1 Flarum's move to being more database agnostic is the root for this. Some people want SQLite, others MySQL, others MariaDB, and some want Postgres. One source of truth could complicate things, therefor splitting it up and pointing to the correct architecture allows this to work seamlessly as possible.