MySQL / MariaDB utf8mb4 support

Hello Zammad community,
Zammad currently supports MySQL and PostgreSQL as database backends.

When paired with MySQL or MariaDB, Zammad looses the ability to save or process uncommon UTF-8 icons due to the lack of utf8mb4 support, as documented here and here (#1824).

The only solution that was proposed till now was this one by adding a note to the documentation that proper UTF-8 suppoer by using utf8mb4 is broken in Zammad, which itself doesn’t solve the issue.

This issue serves more as a bug report then a feature request, because the expected behaviour of any application would be for proper utf8mb4 support, letting users make full use of UTF-8, leaving users with broken installs or incomplete UTF-8 capabilities, even leading to potential security issues.
That’s why I was sure that this is a bug, but the issue was closed on GitHub hinting that proper MySQL support was a feature request.

Installation Info:

  • Database version: MariaDB 10.7 (utf8mb4)
  • Zammad version: latest (5.1.1)

Expected behavior:

  • Use MySQL utf8mb4 as DB backend

Actual behavior:

  • SQL Errors (#1824)

Thanks for information.
Is there any possibility to migrate from mySQL to PostGre with active ans running installation of Zammad 5.1.1 ? A migration tool running on Ruby for example?

There is at least no official documentation for handling a database conversion.
The documentation tool of the Zammad backup tool mentions that you should contact Zammad support for this.

Some people online have successfully used PGLoader to transition from MySQL to PostgreSQL, you can read more about it here.

The problem is that Zammad advertises full MySQL support but doesn’t deliver in this aspect.

The documentation clearly states that UTF8 is required and utf8mb4 is not supported:
https://docs.zammad.org/en/latest/prerequisites/software.html#database-server

It also states (on the very same section) that administrators are encouraged to use pgsql instead.
Would you thus care to point me to the place where “full mysql support” is being declared?

It states it in the software requirements - in fact the first two supported database systems are MySQL and MariaDB.

If a Database system stated as supported, I think anyone would suspect that the software is running the same as with any of the other DBMS.

Furthermore you don’t encourage to use PostgreSQL instead, you just say that you tend recommend it - so if a customer has lots of experience with MariaDB but none with PostgreSQL, you wouldn’t recommend him to still use PostgreSQL over anything else even though it might be negative for the customer?

image

Sorry but if you say that a DBMS is not fully supported, don’t list it as a viable fully-supported option in the software requirements.

Good point.
I’ll encourage to drop MariaDB support in the next product management meetup then.

This will remove a lot of struggle, cluster fucks and decision decisions.
Good thign, you’re right.

2 Likes

Either that or adding proper MariaDB/MySQL support.

Dropping MySQL support would also mean dropping many users that are using Zammad in a MariaDB/MySQL setup.
If I’m not mistaken the problem here is that the individual fields are not adjusted for the higher size requirements of proper utf8mb4.

You know. I can help nobody that is not able to read.
We do warn about everything you’re complaining about.

So I’ll be opting for dropping the support completely as I’m tired of these useless discussions.
Use postgresql. It’s faster and more reliable than MariaDB and MySQL DB - that’s what we’ve learned in the past years.

We will also not offer a migration path, so bad luck I guess.

Wouldn’t it be better to keep the option to use MySQL/MariaDB (with today’s restrictions) but clearly indicate that PostgreSQL is THE choice and that there are mechanisms to migrate from one DB to another?

This is useful in the following scenario - migration from another ticket system to Zammad, where the old DB is MySQL/MariaDB. When a potential customer sees that none of the above databases are supported, he leaves without trying.

I have tested it in the last few days: Migration from OTRS to Zammad with MariaDB, then migration of MariaDB to PostgreSQL (the link from @akkari above was very helpful) via pgloader. Worked well.

3 Likes

Our documentation exactly does this since over 3 years. Maybe even 4. Not sure if it was noted in the docs before I started at Zammad or if I added it. Still, people either do not care about that fact, do not read the documentation or can’t understand the scop.e Above screenshoted warning is at least 2 years old.

If an administrator doesn’t understand what utf8mb4 is and it’s not supported and the suggestion of the DB is a different than the choice he’s going to make, I can’t help those. It’s not our job and also not task of the documentation to tell people technical differences in between database servers.

That might be a very hard lined opinion of mine but also allows us to concentrade on Zammad documentation wise. We have enough grey spots that are missing. How grey will the documentation get when we start completely covering all other dependencies as well.

Better stick with vendor documentations and get the input from there. Postgresql, MySQl and Elasticsearch all three have very very detailed and excellent documentations - why copy cat that.

I’ll gladly cover these things in my private time if someone sponsors it on a regular basis. However, as it’s time consuming it’s gonna be expensive.