WordPress JSON fields corrupt

[Sakamoto Ryōma]

I transferred two client sites which are using WordPress and some page builders which save data in JSON format and after import JSON data gets broken. I use Maria DB 10.6.15.

 

[Sakamoto Ryōma]

It seems it is a known problem as MariaDB and MySql are using different ways of handling JSON fields. MariaDB has a tool https://mariadb.org/making-mariadb-understand-mysql-json/ but it is manual work to update every table on the database.

 

[Richard G]

Yes it seems MariaDB is not a Mysql-drop in anymore or at least planning to go another way.

https://blogs.oracle.com/mysql/post/how-to-migrate-from-mariadb-to-mysql-80

In a comparison I've seen loads of differences now between the both and it seems people are starting to go backup to mysql again.
Seems this can already be happening since MariaDB 10.5.x

Good you discovered this issue as for sure more people will encounter this problem as Wordpress is widely used.

@fln since this is MariaDB 10.5 will there be some automated import fix created or can the mysql_json plugin be installed by default in MariaDB?

 

[Sakamoto Ryōma]

I have some client sites which were on Cpanel, and as I use DirectAdmin, the default database servers are different. I had JSON problems on some client sites, and now I set my DirectAdmin to use MySQL server instead of MariaDB because I don't want to spend time on bugs and problems as there are too many plugins on WordPress sites, and some of them use JSON to save data to DB and some of them has their own custom tables. I know MariaDB is performing better but in my situation using MySQL is more error-free migration as Cpanel is using MySQL by default.

There is also one more main difference between Maria and MySQL, it is the default charset. MySQL is using utf8mb4 but Maria is not.

I got a suggestion to add some extra config to my.cnf file but I preferred to move to MySQL as there can be other unseen problems in the future.

 

[Ohm J]

For anyone want to make json-data work in mariadb in future.
just create with longtext data-type ( utf8mb4_bin ) table column.

if it small data "TEXT" should be fine.

 

[Richard G]

just create with longtext data-type ( utf8mb4_bin ) table column.

Maybe you can tell us the command or how this is done? I'm sure a lot of people (like me to) with little sql knowledge will know how this is done.

 

[Richard G]

I got a suggestion to add some extra config to my.cnf file

For those interested, I will place it here so one does not have to visit everywhere:

[mariadb]
plugin-load-add=type_mysql_json

 

[Ohm J]

@Richard G
it not completely work with mysql_json, but it can store data as mysql_json with plaintext.

this will same as when you create json table from phpmyadmin, it will force to use longtext data-type ( utf8mb4_bin ). Besure you must store as ( *_bin ).

 

[Richard G]

Well... not sure if I understand it but I will try if/when I encounter the issue.