Support

In the database restoration page set "Normalise character set" to Disabled.

This happens because the restoration script normalises all collations, including binary collations such as utf8mb4_bin, to the server's default linguistic collation. Under utf8mb4_unicode_ci and similar collations ß and ss compare as equal, so außer and ausser collide in the unique index of the Smart Search table. With normalisation disabled the binary collation is kept and both terms can be restored.

Disabled is only safe if the new server is the same kind (MySQL or MariaDB) and version family as the old one. If it isn't, the restoration may fail with an "unknown collation" error. 

Normalisation exists because different MySQL / MariaDB versions support different character sets and collations, so a backup from one server may not restore on another. We can't simply preserve binary collations during normalisation. A column's collation has to agree with its character set, so keeping them would mean parsing the SQL dialect instead of simple replacement. That's so abstract, convoluted, and brittle that it makes no sense discussing it even as an academic exercise.

If for some reason you do need to enable Normalise character set and keep the binary collation the best I could possibly do is maybe add another option to convert binary collations to their new character set equivalent IF it's a pair I can definitely know would work. I am not sure whether it would work or introduce more problems, though, as the valid combinations differ wildly between database servers and even the same server make's versions.

After sending this reply it occurred to me that I can try to carve out a very specific exception for core tables' columns which have a binary collation. I will be working on that the next couple of days. If it works I will include that in the next published version.

I appreciate your eagerness to help, but I will have to respectfully decline. I do not want to come across as condescending or haughty. I know what ChatGPT did (as it will soon become apparent), which is why I am declining. What it did most definitely works in your use case, but not in the mass-distributed application use case. My job is to write software for everyone, not just for one use case.

Let me provide some much-needed context. I am not anti-AI; just watch my Joomla World Conference 2026 presentation from two weeks ago talking about how I use AI mindfully and efficiently. It is exactly because I do use AI and know how it works that I know why it failed to create a valid solution.

A couple of years ago, the restoration script used to simply replace the charset part of the collation regardless of which collation it was. This can lead to problems. If a database server has the default collation latin1 (common in Scandinavian countries) this would lead to the invalid, non-existent collation latin1_bin. While addressing this bug I found out that the binary collations are quite convoluted in MySQL and MariaDB. The only universally applicable rule I could find was to replace them explicitly with non-binary collations. The code you quoted was that bug fix. I am the first to admit that the fix wasn't fully right – it was just far less problematic than the original bug which led to many more sites being impossible to restore.

The only idea I've had about how to "properly" fix it was to parse the SQL and use a very involved and convoluted way of figuring out if the resulting collation is valid. However, this hits the wall of how each table and column will be used in a query. If you try to concatenate or compare two columns with incompatible collations you get a SQL error on the restored site. That's why I had done nothing. It was brittle, and it would work materially worse than the bandaid solution I had already implemented.

I asked Claude Code before answering your ticket – it had full access to the backup engine, the restoration script, Joomla's source code (versions 3.10, 4.4, 5.4, and 6.2), and the full documentation of my code. Quite a lot of available context. It did discover the same solution I did, disable collation normalization. I asked it for ideas to address the root cause. Its first suggestion was pretty much the same your model did: convert any X_bin collation to Y_bin, substituting just the charset part of the collation, reintroducing the bug from two odd years ago. When I pointed out that was the problem I was solving in the first place it told me that there is no way to address it without parsing the SQL which, as I said, was already what I had considered and found not to be worth the trouble. Hence my original reply.

However, after I hit send I had an epiphany. I've had the experience of recently working on a number of features which parsed the Joomla installation SQL files to determine which core tables and which core extensions exist in each Joomla version. Naturally, I thought that going from there to a list of tables and/or columns with a binary collation is not a stretch. If I have that, I can carve out exceptions for core tables during the conversion. These exceptions need to be conditional upon the Joomla version, whether the column has an explicitly set charset, whether the table has an explicitly set charset, whether the table has an explicitly set collation, whether the resulting collation would be legal, and whether the resulting collation would be compatible with the indices and the way Joomla uses that column. Of that, only the latter is practically impossible to know – but I've discovered through experience that if the collation is compatible with the indices then Joomla's queries should also work.

Giving that context to Claude Code helped arrive at a mostly correct code solution and tests within 45 minutes – and another 45 polishing the solution, plus some necessary manual edits. It did catch a mistake I made in my description of the logic. It did find that there are only four possible column collisions, which is much better than my guess and it confirmed it in two minutes instead of me taking a day. That helped a lot.

That said, even with all that context you couldn't possible have and me driving its decisions, it still got several things wrong along the way. It did get confused by Joomla 3, but I gave it the backstory of why 3.10 is so different to 3.9 and earlier and it stopped throwing a hissy fit about it (it considered the difference odd and it was convinced it must be a mistake; it was focused on the code, not on the Joomla lore which explains the decision to have 3.10 be a "bridge" version with some choices that look very odd in isolation). It did insist that Joomla 3.1 needs special handling before I pointed out that Joomla 3's PHP version ceiling is way lower than BRS' PHP floor, therefore out of scope (it did have access to that information, but it did not read it). It also came back with a disastrous idea to downgrade utf8mb4_bin to utf8_bin unconditionally which I shot down because while technically compatible, it would make any four-byte Unicode character collapse into U+FFFD – which it did consider, but failed to understand why it's unacceptable for software used with a global CMS; we settled that as something to do only when the user explicitly asks for a UTF8 downgrade, as documented. It did kinda caught the problem of what happens if someone was upgrading through Joomla 3, 4, and 5 without managing to fully convert their site to utf8mb4, but it failed to consider that Joomla 4 itself would have marked the database upgrade as failed and the site wouldn't work upon upgrading to Joomla 5; well, I told it to check the code to make sure what I told it was true, and it corrected itself.

As you can see, there is quite a difference between fixing a problem for one use case and trying to find a solution which hopefully works for everyone. I say "hopefully" because I am keenly aware of the possibility I have missed something. I can't know what, because I can't know what I don't know. Neither can any AI coding assistant help me since their capabilities never reached the point I did. How will I know if and what I am missing? The good, old-fashioned way: I will test as much as I can, I will release it when I am content it should be released, and someone will file a bug report about a use case I cannot think of, let alone test for. I will reproduce it, write the test for it, and try to address it.