Changing all collations to utf8mb4_unicode_ci was wrong. Tables with names starting with „#_finder“ should have utf8mb4_general_ci. And columns like e.g. diverse „alias“ columns in several tables have collation utf8mb4_bin so they can be used in joins to other tables with any other collation. If you have such column on a database table of your component, try if it works with utf8mb4_bin for that column. You can do a clean new installation of 3.9.19 into an empty database and export structure to see how collations should be in 3.9.19 core tables.

Beside this: Does „Extensions - Manage - Database“ show any database problems? If so, use the „Fix“ button to fix them.

Thanks for the quick answer and hints.

Beside this: Does „Extensions - Manage - Database“ show any database problems? If so, use the „Fix“ button to fix them.
I did the database-check and here is the output:

Hinweis
Die Datenbanktabellenstruktur ist aktuell!
Andere Informationen

Version des Datenbankschemas (in #__schemas): 3.9.19-2020-06-01
Aktualisierungsversion (in #__extensions): 3.9.19
Datenbanktreiber: mysqli
187 Datenbankänderungen wurden überprüft.
212 Datenbankänderungen hatten keinen Einfluss auf die Struktur der Datenbanktabellen und wurden deshalb übersprungen.

Tables with names starting with „#_finder“ should have utf8mb4_general_ci.
This tables have 'utf8mb4_general_ci'.

When reactivating the out-commented lines the errors still exists.
So I reset to the out-commented lines again.

And columns like e.g. diverse „alias“ columns in several tables have collation utf8mb4_bin ....

which tables and which aliases? Without any names it is very difficult.

Is there a tool to check the/this coalitions in the tables for their right values?

@hgh-esn

which tables and which aliases?

All Joomla Core tables having a column named alias.

As I've said: Make a new installation of the same Joomla version, do an SQL export of the structure only on both installations and compare these exports, then you will see the differences in character sets and collations caused by your global collations change. Then change back so it is like in a new installation.

@ALL, @richard67

That is a time consuming process - this time I don't have at the moment.
Is there no easier way to fix it?

Also I'm still wondering why the system still runs after I did the out-commenting as I reported in the opening issue.

@hgh-esn The update log looks ok and complete. I don't really know what has happened. Some extension may be doing an SQL statement containing UNION ALL and using own table and core table with different collations in that, or you have mixed collations for tables in your database which don't really fit together.

@hgh-esn Meanwhile I had an idea how you might save time with restoring the "normal" collations of Joomla 3.9.19 core databasye tables.

Just run the SQL statements from the following 2 files in e.g. phpMyAdmin or similar tool, replacing the #__ by the actual table prefix, and in oder of the files as mentioned here:

1. https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-02.sql
   But don't run the statements in the last section:
   https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-02.sql#L223-L242

2. https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-03.sql

But before of that, make a backup of your database in case if it fails.

And report back here the result after you have tried that.

Thanks in advance.

P.S.: And undo your changes in PHP code if not done yet.

@richard67 Thanks a lot for the files.
I'll try it this evening and report about it here.

Have a good day.

PS: Is the file ....01.sql not also necessary?

@hgh-esn No, 1st file is not needed, and from the 2nd file leave a way the last part. Another quick solution instead of using the files (or parts of their content) would be to update column "converted" or table "#__utf8_conversion" to value 1 and then go to "Extensions - Manage - Database". There should then be a database problem about missing Multibyte Characterset conversion (utf8mb4) shown. Use the Fix button to fix that. If that worked and no problem is shown afterwards, the core tables and their columns have all the right collations.

You should make a backup of the database structure and content before you do that, e.g. an SQL export with phpMyAdmin, so you later can fall back if it did not work.

@richard67

Hi Richard,
I've tested it as you wrote in the last post.

... to update column "converted" or table "#__utf8_conversion" to value 1 and then go to "Extensions - Manage - Database".
But I couldn't see any reaction, when entering the "Extensions - Manage - Database" site. There was no "fix" button visible. Only a button "Recover / Wiederherstellen" is visible and clickable. What does this button do, when there is nothing to fix?

I wonder if this site makes any check about the database, because there is no explicit "Check" button, or is the check done already in the background, when entering the site?

PS: During the test, I have recovered the original database driver .../mysqli.php. But the SQL-error was still there.
PPS: A special "Check"-button could be very useful to make the site-working more clear.

I recovered to my fix so the site is running.

I have not made any changes to the system between updating from J3.9.18 to J3.9.19. So I'm wondering about, what has been changed in J3.9.19 to run into this error?

But I couldn't see any reaction, when entering the "Extensions - Manage - Database" site. There was no "fix" button visible. Only a button "Recover / Wiederherstellen" is visible and clickable. What does this button do, when there is nothing to fix?

In the English Backend it is called "Fix", in the German backend it might be different, I never use it in German (except for testing maybe). When there is a problem found in "Extensions - Manage - Database", it is shown in a separate tab, then you can use that button to fix them.

I recovered to my fix so the site is running.

You recovered to status before the update, so it runs on 3.9.18 now? Or you made it work with 3.9.19 now?

I have not made any changes to the system between updating from J3.9.18 to J3.9.19. So I'm wondering about, what has been changed in J3.9.19 to run into this error?

What has been changed can be found in the release announcement: https://www.joomla.org/announcements/release-news/5812-joomla-3-9-19.html:

Bug fixes and Improvements

• Fix incomplete utf8mb4 conversion since 3.9.17 #29117
• Backport jQuery 3.5 security fixes #28948
• Frontend: Removal of the create/edit menu item buttons #29191
• Extend the checks to make sure only real user admins can create accounts #28948
• Mail: Support of dotless domains #28576
• Codemirror updated to its latest release #28691
• Improve translation system supporting better pluralization for languages like Welsh #28763

Your problem is possibly the first change, Fix incomplete utf8mb4 conversion since 3.9.17 #29117 .

As you wrote there was an error "Illegal mix of collations in UNION ALL statement". This doesn't happen with Joomla core. Either you had modified the collations of some tables in past (which has not caused any problem but was wrong, and the utf8mb4 conversion just unmasked that), or you have a 3rd party extension using a database query doing a UNION ALL between an own, component-specific database table and a core table, and the database table has a wrong collation.

There can be many different reasons.

But after that you have manipulated collations so that they are all utf8mb4_unicode_ci, and that's definitely wrong. In J3 the tables for smart search (table names starting with "finder_" after the prefix) need to have utf8mb4_general_ci.

Question: Before the site was 3.9.18, which versions did it have? Did you update from 3.9.16 to 3.9.17 or 3.9.18? If so: Do you have a backup from when it had 3.9.16? If so: Is that backup too old now for being restored because since that time too much content has changed? Or do you think the 3.9.16 backup could be restored and used?

If you can use a 3.9.16 backup (files and database), restore it on a local webserver and database, then updater it directly to 3.9.19, then if that works, transfer that to your live site e.g. with Akeeba tools.

P.S.: Your "fix" is definitely not a fix, it just surpresses the database error.

OK,
I better call it a workaround.

Is there a way to get deeper infos from the database SQL about this error?

Which tabels, columns are involved?

Gesendet mit der Telekom Mail App
https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer

--- Original-Nachricht ---
Von: Richard Fath
Betreff: Re: [joomla/joomla-cms] update J3.9.18 -> J3.9.19 -> SQL-error
1271 illegal mix of coalitions for operation 'UNION' (#29450)
Datum: 09.06.2020, 22:17 Uhr
An: joomla/joomla-cms
Cc: hgh-esn, Mention

P.S.: Your "fix" is definitely not a fix, it just surpresses the database
error.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#29450 (comment)
, or unsubscribe
https://github.com/notifications/unsubscribe-auth/ABIN4DXQSOH6TSJZKKSUSJLRV2KFZANCNFSM4NVZSVOA
.

Is there a way to get deeper infos from the database SQL about this error? Which tabels, columns are involved?

Sure. The database server's log file. On Linux for a MySQL server it is e.g. /var/log/mysql/error.log, for other databases or operating systems it is different of course. Please check your database server's (online) documentation or Google for it. Watch the log file while the error happens, and you should see the complete error message and the SQL statement which has caused it.

You recovered to status before the update, so it runs on 3.9.18 now? Or you made it work with 3.9.19 now?
With my workaround I made it work with 3.9.19.
In 3.9.18 I never had a Problem with the database.

Gesendet von Mail für Windows 10

Von: Richard Fath
Gesendet: Dienstag, 9. Juni 2020 22:17
An: joomla/joomla-cms
Cc: hgh-esn; Mention
Betreff: Re: [joomla/joomla-cms] update J3.9.18 -> J3.9.19 -> SQL-error 1271illegal mix of coalitions for operation 'UNION' (#29450)

P.S.: Your "fix" is definitely not a fix, it just surpresses the database error.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or unsubscribe.

In 3.9.18 I never had a Problem with the database.

Maybe you not had an SQL error, but maybe you had wrong collations and just did not notice. This is a database problem too, you just might not have noticed that.

I suggest you try my suggestions above instead of keeping your workaround.

The only queries in the J3 core with "UNION" are done by Smart Search.

@hgh-esn Do you use Smart Search on your site? What happens if you switch it off, i.e. disable the Smart Search plugins (plugins with names starting with "Smart Search - "? Does the error disappear then without your "workaround"?

@richard67

sorry richard, I have no plugins with "Smart Search" in it names.
There are only a lot of plugins with:
Suche - xxxxx Their type is "search".

Also there are some search-Components
called "Suche" and "SuchIndex"

And a site-modul called "Suchen".

I disabled them all, but the error hasn't gone.

sorry richard, I have no plugins with "Smart Search" in it names.

We use the English names here because language is English here.

In German they are all names with "Suchindex" in their name.

If disabling them doesn't help, then it might very likely come from a 3rd party extension.

@hgh-esn If you could export your database from phpMyAdmin (or similar) into an SQL file: Check the exports options if there is a way to export only the structure but not the data (there should such). If you found that, export your data structures into SQL. Then zip the sql file and send it as email attachment to "adminrichardfathde", replacing the and and by the corresponding special characters in the address. Use a subject like "Joomla issue 29450" so I know it's you ;-) You can write in German of course.

I could have a look on the structure to find out where the problem happens, and maybe I can create an SQL script to fix it for you.

Meanwhile an analysis of an Akeeba backup from before the update which I was given by the author of this issue and then a test with the uptate fo 3.9.19 has shown that the update was successful and all core tables have correct character sets and collations.

But the front page shows the database error mentioned in the issue.

With debug on in Global Config, it shows the stack trace:

So the culprit is a 3rd party module "Joes Word Cloud", which does SQL queries with UNION over several kinds of core and 3rd party extensions's database tables, and the query doesn't do a conversion to a common collation on the fly, so it can't handle database tables with particular mix of collations.

When unpublishing this module, the error goes away, and frontend works as well as before the update (like backend does all the time).

I haven't found the module on JED with a quick search, and on the manufacturer's web site https://joellipman.com I haven't seen any update.

But I am in doubt if an update would solve the problem, except if the author meanwhile has implemented handling of mixed collations.

The mix of collations seems to be caused by a past modification of table collations with a tool like e.g. phpMyAdmin to utf8mb4_0900_ai_ci. I think it was not the Akeeba Admin tools, because when using them in that version, the result would be utf8mb4_general_ci. In addition there are still old 3rd party extensions having utf8 character set and collations.

This was not a problem in past because core tables partly had that collation utf8mb4_0900_ai_ci. With the update from 3.9.18 to 3.9.19 that has been fixed for the core tables because with that update, the utf8mb4 conversion runs again, but only when updating a 3.9.17 or 3.9.18 due to fix a previous problem with it in these versions. Normally this doesn't cause such issues.

I've sent @hgh-esn and SQL script which he can use to clean up the mix of collations by changing the utf8mb4_0900_ai_ci to utf8mb4_unicode_ci, which is the standard for J core at the moment. The old utf8tables are not touched.

With that fix, the module mentioned above which causes the SQL error works again.

@hgh-esn Please check and apply the SQL script I've sent you, or remove that module if you don't need it anymore.

Closing as not a Joomla CMS core issue but caused by a 3rd party module.