I had that on updating and used Akeeba Admin Tools to reset the collation to change the database collation to UTF-8 (I think I had it set to UTF-8 multibyte. This fixed the issue. I suspect this can be done through PhpMyAdmin too.

Yes, I agree that changing the collation should fix the problem.
But I think this must be improved or prevented.

I see that we have both collations in our SQL files but I don't know why. Someone knows?

It has to be unicode and not general, with exception of com_finder where we had problems when implementing utf8mb4 support, and so we fell back for those tables. In those times there were no critical joins or comparisons, and problems mainly came up with 3rd party extensions, but this might have changed meanwhile.

Changing collation for everything is not really a solution. It fixes the problem for now, but it may appear again when in future with some Joomla schema update some new table or column is added which is joined to one of the tables from com_menu. I will see if I find time in the next 4 days to make a PR.

In addition, I assume that all the new tables added in recent times have not been added to the utf8mb4 conversion scripts, which causes problems when someone migrates his/her database from an old mysql version not supporting utf8mb4 to a newer version supporting it. If I can, I will also make PR for that.

@sshcli @DavidBoggitt Could you see somewhere in the error message or - if you log PHP errors - in the PHP error log file any information on the SQL statement which caused the error message? If so, it would help me a lot to find out which of the table columns possibly need to have binary collation so they can be joined with or compared to both columns with unicode and with general collation. If not: Do you have a backup which you can use on a test installation to reproduce the error message, having error loging for PHP switched on in php.ini and errors logged into a log file? Then you can let me know about the SQL statement.

As I promised in my 2nd last comment before this one here, I have just made a PR for the missing utf8mb4 conversion of new Joomla stuff, which turned out to be just com_fields.

I will not link to it here to avoid that some quick maintainer closes this issue because having a PR, because the PR does not fix the problem of this issue here.

For fixing this, having the SQL statement which failed would help me a lot, as I noted in my previous comment.

There is some interaction between the fields plugin and com_finder here: https://github.com/joomla/joomla-cms/blob/staging/plugins/system/fields/fields.php#L398
I don't know how com_finder works internally, but since finder is the only extension using that collation I'd assume something there.

@Bakual Yes. There must be a join or comparison of table columns in some SQL statement causing the error. It is necessary to change that particular column of the particular com_fields database table to binary (utf8mb4_bin). At the moment I think it could be columns context of the fields and fields groups tables, but I have not found the SQL statement yet when debug on. It needs some data for that, but I neither use com_fields nor com_finder on my Joomla! site, so I haven't found it yet.

During my testing I never saw that message either, and I'm sure I created fields in every core extension.
@sshcli Can you give more details?

@sshcli @DavidBoggitt Do you have some backup of data (database export) which you can restore to reproduce the problem? Or a backup of Joomla! before update so you can update again to reproduce the problem? Of course on a test installation and not your life site.

If so, can you just convert columns context of tables #__fields and #__fields_groups to utf8mb4_bin collation by issuing following two SQL statements:
ALTER TABLE #__fields MODIFY context varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__fields_groups MODIFY context varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

Then refresh the page, and the SQL error should have gone.

Can you check that?

@richard67 working on it, please give me a couple of minutes.

@sshcli @DavidBoggitt If the 2 SQL statements I provided with my previous post do not help, then try in addition this one:
ALTER TABLE #__fields_values MODIFY item_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.';

If that helps, fall back to backup before testing the 2 statements from my post above, and then apply only the one from this post.

Then report back here which helped: The 2 before, or the one here, or all 3.

@sshcli @DavidBoggitt And if all SQL statements I provided above do not help, please try this:
ALTER TABLE #__fields MODIFY name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
and report back if it helped.

If so, can you just convert columns context of tables #__fields and #__fields_groups to utf8mb4_bin collation by issuing following two SQL statements:
ALTER TABLE #__fields MODIFY context varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__fields_groups MODIFY context varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
Then refresh the page, and the SQL error should have gone.

It Doesn't work.
I also try these with no success:
ALTER TABLE #_fields CHANGE context context VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #_fields_groups CHANGE context context VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

Could you see somewhere in the error message or - if you log PHP errors - in the PHP error log file any information on the SQL statement which caused the error message?

Nothing in my PHP error_log file, only deprecated PasswordHash (Not related to this issue)

Do you have some backup of data (database export) which you can restore to reproduce the problem? Or a backup of Joomla! before update so you can update again to reproduce the problem? Of course on a test installation and not your life site.

Unfortunately is my production site, for that reason I cannot upload the database here.

If the 2 SQL statements I provided with my previous post do not help, then try in addition this one:
ALTER TABLE #__fields_values MODIFY item_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.';

I haven't try it yet, I need to make a full backup first before start making some tests.
and my free time is very limited right now (8:33 am right now, rush hour)
So, I hope you can understand if I take too much time to respond

@sshcli Can you try the one I posted just before?
ALTER TABLE #__fields MODIFY name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

@sshcli No need to fall back to a backup after a test failed. If the 2 statements did not help, you can just try the 3rd one, and if that does not help the 4th one.

Can you try the one I posted just before?
ALTER TABLE #__fields MODIFY name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

It doesn't work.
Also I tried this:
ALTER TABLE #_fields CHANGE name name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

I still can see the error in the backend (After clear cache, logout, login, Ctrl+F5)

@sshcli
And the
ALTER TABLE #__fields_values MODIFY item_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.';
Did that help?

let me test it, hold on

@sshcli Sure, thanks for your help so far. I am optimistic we can find the culprit database column(s).

And the
ALTER TABLE #__fields_values MODIFY item_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Allow references to items which have strings as ids, eg. none db systems.';
Did that help?

No.

What do you think about change the collation to the whole table?

@chrisdavenport Do you have any idea which columns of com_fields database tables could be joined with com_finder table columns in some SQL statements?

@sshcli That could help to identify which table it is, which could save us time to find the particular column. If you want, test it by just changing them in PHPmyAdmin and reporting back the result here. Then if it is not all of the tables, we could try to find the columns.

We should not have the complete tables having bindary collation at the end. We should have binary collation only for the columns where it is necessary.

@sshcli Can you finally try following:
ALTER TABLE #__fields_values MODIFY value text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

@sshcli If the statement I posted with my previous post does not help, can you provide more details about what you wrote in the issue description?
You wrote:

I notice this error after upgrade to nightly builds and switch back to stable version.
Which nightly build was it? The one for 3.7, or 3.8 or 4.0?
Which method did you use to update? Jooma update with custom URL, or with file upload?
And what means swich back to stable version? You restored dabatase and files from before the update to the nightly build?
Sorry for my silly questions.
But I've tried everything here to reproduce the error. I created some custom field, enabled advanced search (com_finder), made some index, updated to recent nightly build for 3.7 ... no success. I cannot reproduce the problem.
What is missing in my test is the switch back to stable version because I don't know how you did that, see my question above.
Can test if the same happens again with the most recent nightly build?
Maybe there has been something fixed already?

@sshcli Can you finally try following:
ALTER TABLE #__fields_values MODIFY value text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

Didn't work

Which nightly build was it? The one for 3.7, or 3.8 or 4.0?

3.7

Which method did you use to update? Jooma update with custom URL, or with file upload?

Both, I don't remember the exact order

And what means swich back to stable version? You restored dabatase and files from before the update to the nightly build?

No. I use the wrong way for this procedure "Unzip 3.6.5 Full package in my root directory containing a 3.7 nightly build

Sorry for my silly questions.

Your questions are not silly

But I've tried everything here to reproduce the error. I created some custom field, enabled advanced search (com_finder), made some index, updated to recent nightly build for 3.7 ... no success. I cannot reproduce the problem.

I also can't reproduce the issue in my test site

What is missing in my test is the switch back to stable version because I don't know how you did that,

Even I can't reproduce it, I start thinking in other possible reasons (Disk full a few weeks ago crashed my site, also I use admin tools in 3.6.5 to change collations a few months ago)

Can test if the same happens again with the most recent nightly build?
Maybe there has been something fixed already?

I you want I can keep trying to reproduce the error, and we can close this issue for now and I will create a new one with full details. What do you think?

Additionally,
Since yesterday, I've set all table collations to its default value (No success, problem persist)

What means defautl value? And can you check if you have some tables which have "...general_ci" collation beside those with names starting with dbprefix+"finder_"?

What means defautl value?
It means, that I have installed a new website with 3.7.2, and I take those tables collations as reference.
Now in my production site (where the problem is) I have changed the collation for all tables (only tables, not fields) to match the same collations as the new website running 3.7.2
But sadly, the problem persist.

During the process, I also notice some components missing in the backend (It could be another indicator that something in my Joomla installation is wrong)

And can you check if you have some tables which have "...general_ci" collation beside those with names starting with dbprefix+"finder_"?

Yesterday, yes.
But today no, I have change it all.
Only finder tables are using general_ci and tables from 3rd party components (Kunena and schuweb_sitemap)

Some of the table columns in Joomla have utf8mb4_bin collation.
You can find them here:
https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_admin/sql/others/mysql/utf8mb4-conversion-02.sql#L132
Can you check if running the SQL statements in this section of the script solve the problem?
I.e. run:
ALTER TABLE #__banners MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__categories MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__contact_details MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__content MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__languages MODIFY lang_code char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
ALTER TABLE #__menu MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'The SEF alias of the menu item.';
ALTER TABLE #__newsfeeds MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__tags MODIFY alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE #__ucm_content MODIFY core_alias varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '';

You are the best.
This one solve the problem:
ALTER TABLE #__languages MODIFY lang_code char(7) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

Many thanks

Ok, then this issue could be really closed.

I am not sure if I am the best, but I am sure I try my best not to be too bad :-)

Do you need to make a PR for prevent this in the future?

Why so? The problem comes from using admin tools to change collation for complete tables.
Joomla! already does it in the right way, nothing I could do a PR for.

You can make a PR on the admin tools repository that they shall keep binary collations for table columns which have such when changing database and table collations.

Go it.
Thanks again

I hope you did run also the other four SQL statements below that one which helped so you are safe for the future because 100% aligned with Joomla standard.

Yes, I did.

I am not so smart with a database. I had the issue with opening "fields" below Joomla article menu.

I solfed the issue with akeeba admin tools, and the option "Change Database Collation" and changed it to UTF-8 Multibyte what the tool recommended. After that change everything workt great again.

I thought you wanted to know this.