I will see if I can find the reason, but if someone is faster he/she is welcome 😄

@infograf768 Can you reproduce this?

Issue confirmed, but not only on multilingual websites... on every website!

I guess this needs an immediate patch to push a 3.9.10 just now... probably it has something to do with an SQL JOIN query

I am almost sure it has to do with a join, some recent "optimization". Not sure if it needs an immediate patch, I can live with it so far with my small little, rarely edited web site. Only thing is I don't have language specific template style default anymore and so no language specific site motto. Not nice, but frontend is still ok.

Yes but having the same style listed multiple times and poiting to the same record is not acceptable for a production version. I think it would be better to fix it immediately.

Hmm, it is not that easy, the join here has not changed since 3 years: https://github.com/joomla/joomla-cms/blame/staging/administrator/components/com_templates/models/styles.php#L126.

So maybe it is related to data type changes of database columns in recent times, e.g. things like home from string, e.g. '1', to numeric, e.g. 1.

The data type of column home of table #__template_styles, which is used in the join I've linked above, has been recently changed from varchar(7) DEFAULT '0' NOT NULL to tinyint(1) unsigned NOT NULL DEFAULT 0 with PR #24595 .

=> Ping @alikon

Yes i think it is https://github.com/joomla/joomla-cms/pull/24595/files

@alikon check what you have done here

Yes reverting to char(7) fixes the issue

Yes, the problem is that the column home of template style table can either hold things like ' 0' or '1' or - when made default for a language, the language code for that language, e.g. 'de-DE'.

So issue #23188 was wrong, and therefore also PR #24595 was wrong.

@alikon Just for info, not to blame.

@infograf768 Regarding your comment in issue 23188: now you know the answer ;-)

I mean if even Jean-Marie did not know about that, then it was really a very hidden thing to have also language codes in that column.

@alikon @wilsonge now we might have a problem: When we make PR to change data type of that column back to before, we have 2 schema updates, one with the wrong change from PR #24595 , and a new one with changing data type back. This will make the database schema checker toggle with complaining that the column with this and this format is missing: First it will complain new column definition is missing, then after fix it will complain old column definition is missing, after fix again new is missing and so on and so on. I will check that, but I am almost sure this will happen. Only way out would be either to rename that column, which means change code, too, or to betray the schema check somehow (no idea yet, how).

Yes @richard67 i just checked the code of the com_templates model, you are right this is the problem and need to be reverted.

@joeforjoomla As I wrote in previous comment, it could be a problem to simply revert with schema update script.
To be honest, it is also my fault: I did not test 3.9.9 RC well enough. If I had done that, I could have reported this error here before release. (blush)

I will prepare PR to change back and will test here if it will be a problem with schema update like I suppose, or if we are lucky and it will work.

Not to blame anyone @richard67 i've even tested the RC for 1 week but didn't notice this issue.

Sure, but I could bite myself in my ass now, as we say here in Germany.

Ahahah... however i think that it's very confusing for users so probably needs to be fixed now

We have a problem because there is an index on columns client_id, home.

Due to how the database schema checker (Extensions -> Manage -> Database) works, we can't just drop the index and add it back. That problem was discussed between me and @alikon in PR #24595 for old index idx_home, too, and result was that we decided not to add back the old index but the new combined one instead. But the new one makes sense. Either we add it back with a new name which does not fit to our index naming scheme, or we don't add it back and it can never be added an index on that table with that index name unless the databa schema checker is at some future day enabled to handle such stuff. @alikon Please advise what to do.

Regarding column data type we can handle by making new schema update for chaning it back plus removing the SQL in the old schema update from PR #24595 which changed it in the wrong way, so the schema schecker does not find that old SQL.

But with the index it is not that easy, we can only add it back with a new name, e.g. idx_client_id_home_2 or idx_client_id_home_new.

@richard67 new name is not a problem. I already removed Joomla! 3.9.9 from the update server and I'm waiting for you PR.

I still managed to do an update 2 minutes ago

Your "remove from update server" pull request isn't merged, or deployed, unless you're manually making changes on the server (in which case I know who to blame for one of the multilingual .org sites having an incorrectly restored 3.7 database snapshot 😈)

Sorry didn't merged it.... now it's merged...

its ok now you can't update

PR is #25484 . Haven't tested it myself yet. Will do soon.

Closing as having PR.

@infograf768 Regarding your comment in issue 23188: now you know the answer ;-)

Indeed. And, unhappily I never tested the following PR and fixed database after this was merged. Sorry.
#24595