Question: Before I tried to change my local preference (yesterday): Which notification settings were really in effect on the local wiki; the settings that appeared to be set locally, or those that appeared after I clicked "Save"?

(I'm not asking about my own particular settings (which may have been the same locally and globally), but about how the preferences currently work; can there be misleading display of noneffective local settings?)

In T201340#4483641, @Pipetricker wrote:

Question: Before I tried to change my local preference (yesterday): Which notification settings were really in effect on the local wiki; the settings that appeared to be set locally, or those that appeared after I clicked "Save"?

(I'm not asking about my own particular settings (which may have been the same locally and globally), but about how the preferences currently work; can there be misleading display of noneffective local settings?)

You get what the interface tells you: if global preference is enabled, global is used, otherwise a local one. The problem here is that setting a global preference overwrites local one.

In T201340#4486102, @MaxSem wrote:

You get what the interface tells you

OK. (When I wrote the above question (and when I wrote the original bug report, which I have now rewritten), I for some reason had the impression that the settings from the previously set-and-unset global preference didn't appear on the local preference page until I tried to change something on it and clicked "Save", but now when I check, that isn't the case.)

The problem here is that setting a global preference overwrites local one.

And that the local preference can't be changed.

@MaxSem We discussed this briefly yesterday but it'd be good to have it on record here - could you list out what exactly happens on the backend when a user enables and disables the Checkmatrix global preference with a different setting? On all wikis.
That'll enable me to be able to better help users who face this issue. Thanks.

Change 452026 had a related patch set uploaded (by MaxSem; owner: MaxSem):
[mediawiki/extensions/GlobalPreferences@master] Fix global settings overriding local ones

https://gerrit.wikimedia.org/r/452026

Change 452026 merged by jenkins-bot:
[mediawiki/extensions/GlobalPreferences@master] Fix global settings overriding local ones

https://gerrit.wikimedia.org/r/452026

I ran an investigation to try and understand the impact of this, and what exactly happened. Here's the results (findings and recommendations at the end)

The situation before

To make the distinction between local and global better, I first enabled all of my CheckMatrix (Echo notifications) so that all checkboxes are true.

The database query reflects that:

19:00) vagrant@localhost:[wiki]> SELECT * FROM user_properties WHERE up_user=1 AND up_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| up_user | up_property                              | up_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions-email-article-linked  | 1        |
|       1 | echo-subscriptions-email-edit-thank      | 1        |
|       1 | echo-subscriptions-email-flow-discussion | 1        |
|       1 | echo-subscriptions-email-mention         | 1        |
|       1 | echo-subscriptions-email-page-review     | 1        |
|       1 | echo-subscriptions-email-reverted        | 1        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
+---------+------------------------------------------+----------+
7 rows in set (0.00 sec)

Then, I went to GlobalPreferences, and enabled the CheckMatrix (Echo notifications) but made sure to uncheck all of the 'web' notifications, so we can see if it overrides any data.

GlobalPreferences table

(19:05) vagrant@localhost:[centralauth]> SELECT * FROM global_preferences WHERE gp_user=1 AND gp_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| gp_user | gp_property                              | gp_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions                       | 1        |
|       1 | echo-subscriptions-email-article-linked  | 0        |
|       1 | echo-subscriptions-email-edit-thank      | 0        |
|       1 | echo-subscriptions-email-edit-user-talk  | 0        |
|       1 | echo-subscriptions-email-emailuser       | 0        |
|       1 | echo-subscriptions-email-flow-discussion | 0        |
|       1 | echo-subscriptions-email-mention         | 0        |
|       1 | echo-subscriptions-email-page-review     | 0        |
|       1 | echo-subscriptions-email-reverted        | 0        |
|       1 | echo-subscriptions-email-user-rights     | 0        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
|       1 | echo-subscriptions-web-edit-thank        | 1        |
|       1 | echo-subscriptions-web-edit-user-talk    | 1        |
|       1 | echo-subscriptions-web-emailuser         | 1        |
|       1 | echo-subscriptions-web-flow-discussion   | 1        |
|       1 | echo-subscriptions-web-mention           | 1        |
|       1 | echo-subscriptions-web-page-review       | 1        |
|       1 | echo-subscriptions-web-reverted          | 1        |
|       1 | echo-subscriptions-web-user-rights       | 1        |
+---------+------------------------------------------+----------+
19 rows in set (0.00 sec)

• Local preferences table **

(19:00) vagrant@localhost:[wiki]> SELECT * FROM user_properties WHERE up_user=1 AND up_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| up_user | up_property                              | up_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions-email-article-linked  | 1        |
|       1 | echo-subscriptions-email-edit-thank      | 1        |
|       1 | echo-subscriptions-email-flow-discussion | 1        |
|       1 | echo-subscriptions-email-mention         | 1        |
|       1 | echo-subscriptions-email-page-review     | 1        |
|       1 | echo-subscriptions-email-reverted        | 1        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
+---------+------------------------------------------+----------+
7 rows in set (0.00 sec)

Now I disable the global preference. Here's the database result:

GlobalPreferences table:

19:05) vagrant@localhost:[centralauth]> SELECT * FROM global_preferences WHERE gp_user=1 AND gp_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| gp_user | gp_property                              | gp_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions-email-article-linked  | 0        |
|       1 | echo-subscriptions-email-edit-thank      | 0        |
|       1 | echo-subscriptions-email-edit-user-talk  | 0        |
|       1 | echo-subscriptions-email-emailuser       | 0        |
|       1 | echo-subscriptions-email-flow-discussion | 0        |
|       1 | echo-subscriptions-email-mention         | 0        |
|       1 | echo-subscriptions-email-page-review     | 0        |
|       1 | echo-subscriptions-email-reverted        | 0        |
|       1 | echo-subscriptions-email-user-rights     | 0        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
|       1 | echo-subscriptions-web-edit-thank        | 1        |
|       1 | echo-subscriptions-web-edit-user-talk    | 1        |
|       1 | echo-subscriptions-web-emailuser         | 1        |
|       1 | echo-subscriptions-web-flow-discussion   | 1        |
|       1 | echo-subscriptions-web-mention           | 1        |
|       1 | echo-subscriptions-web-page-review       | 1        |
|       1 | echo-subscriptions-web-reverted          | 1        |
|       1 | echo-subscriptions-web-user-rights       | 1        |
+---------+------------------------------------------+----------+
18 rows in set (0.00 sec)

Local preferences table:

(19:07) vagrant@localhost:[wiki]> SELECT * FROM user_properties WHERE up_user=1 AND up_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| up_user | up_property                              | up_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions-email-article-linked  | 1        |
|       1 | echo-subscriptions-email-edit-thank      | 1        |
|       1 | echo-subscriptions-email-flow-discussion | 1        |
|       1 | echo-subscriptions-email-mention         | 1        |
|       1 | echo-subscriptions-email-page-review     | 1        |
|       1 | echo-subscriptions-email-reverted        | 1        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
+---------+------------------------------------------+----------+
7 rows in set (0.00 sec)

Conclusion #1: Rows were not overridden in the local database, with a caveat

We can see that disabling GlobalPreferences, our global table removed the echo-subscriptions row (which enables the global override) but retained the internal values of the checkboxes for global values. The local table was untouched.

However, this created a mismatch in the UI (which is the reason for this bug)

In this state, if a user opened their local preferences page, the CheckMatrix would display the values of the global setting even though the general check matrix global row was not there. This means that if a user changed anything in their preferences and hit "Save" the global values (that are now in the UI, wrongly) overrode the previous local settings.

If the user did not save their local preferences, their previous local preferences are still there.

With the fix

With the fix, the situation is corrected to only load into the UI the state of the global preferences only if the global preference check is there, and if it's not, we clean up the individual value rows.
The local preferences remains untouched, so I skip it in the outputs; we're looking at GlobalPreferences table through the changes:

GlobalPreferences table for users affected (global is disabled, but rows still appear)

(19:08) vagrant@localhost:[centralauth]> SELECT * FROM global_preferences WHERE gp_user=1 AND gp_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| gp_user | gp_property                              | gp_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions-email-article-linked  | 0        |
|       1 | echo-subscriptions-email-edit-thank      | 0        |
|       1 | echo-subscriptions-email-edit-user-talk  | 0        |
|       1 | echo-subscriptions-email-emailuser       | 0        |
|       1 | echo-subscriptions-email-flow-discussion | 0        |
|       1 | echo-subscriptions-email-mention         | 0        |
|       1 | echo-subscriptions-email-page-review     | 0        |
|       1 | echo-subscriptions-email-reverted        | 0        |
|       1 | echo-subscriptions-email-user-rights     | 0        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
|       1 | echo-subscriptions-web-edit-thank        | 1        |
|       1 | echo-subscriptions-web-edit-user-talk    | 1        |
|       1 | echo-subscriptions-web-emailuser         | 1        |
|       1 | echo-subscriptions-web-flow-discussion   | 1        |
|       1 | echo-subscriptions-web-mention           | 1        |
|       1 | echo-subscriptions-web-page-review       | 1        |
|       1 | echo-subscriptions-web-reverted          | 1        |
|       1 | echo-subscriptions-web-user-rights       | 1        |
+---------+------------------------------------------+----------+

GlobalPreferences table after enabling the option in the UI

(19:09) vagrant@localhost:[centralauth]> SELECT * FROM global_preferences WHERE gp_user=1 AND gp_property LIKE 'echo-subscriptions%';
+---------+------------------------------------------+----------+
| gp_user | gp_property                              | gp_value |
+---------+------------------------------------------+----------+
|       1 | echo-subscriptions                       | 1        |
|       1 | echo-subscriptions-email-article-linked  | 0        |
|       1 | echo-subscriptions-email-edit-thank      | 0        |
|       1 | echo-subscriptions-email-edit-user-talk  | 0        |
|       1 | echo-subscriptions-email-emailuser       | 0        |
|       1 | echo-subscriptions-email-flow-discussion | 0        |
|       1 | echo-subscriptions-email-mention         | 0        |
|       1 | echo-subscriptions-email-page-review     | 0        |
|       1 | echo-subscriptions-email-reverted        | 0        |
|       1 | echo-subscriptions-email-user-rights     | 0        |
|       1 | echo-subscriptions-web-article-linked    | 1        |
|       1 | echo-subscriptions-web-edit-thank        | 1        |
|       1 | echo-subscriptions-web-edit-user-talk    | 1        |
|       1 | echo-subscriptions-web-emailuser         | 1        |
|       1 | echo-subscriptions-web-flow-discussion   | 1        |
|       1 | echo-subscriptions-web-mention           | 1        |
|       1 | echo-subscriptions-web-page-review       | 1        |
|       1 | echo-subscriptions-web-reverted          | 1        |
|       1 | echo-subscriptions-web-user-rights       | 1        |
+---------+------------------------------------------+----------+
19 rows in set (0.00 sec)

GlobalPreferences table after disabling the option in the UI

(19:36) vagrant@localhost:[centralauth]> SELECT * FROM global_preferences WHERE gp_user=1 AND gp_property LIKE 'echo-subscriptions%';
Empty set (0.00 sec)

which is exactly what we want to see.

Conclusion #2: The fix corrects the issue, with a caveat

With the fix, we're correctly removing the redundant rows and correctly loading the right information into local and global settings.

However while the loading of the UI is fixed and while the unnecessary rows will be removed when the user does anything to the global prefrences (like saves a setting) -- it won't be cleaned up on its own. We will still have redundant rows left-over from the bug.

The effect of this isn't huge, but it does have a subtle one:
Even after this fix, if the user had GlobalPreferences for CheckMatrix set up in the past (even if they're disabled) and the user now goes to Global Preferences and enables CheckMatrix, the values that the user will see is not the 'base' values from the current local wiki (Which is what usually happens to all other preferences) but rather whatever was previously saved (the redundant rows) .

This isn't horrendous, but it's worth noting.
(After retesting, the above seems to not be true; this means that the redundant lines should be fixed as part of good tech-debt maintenance, but should have no effect on user experience)

Recommendation: We might want to run an SQL query to clean up redundant rows for users that have 'echo-subscriptions-xxx' without 'echo-subscriptions'=1

@Mooeypoo Thanks for the in-depth investigation and explanation. This makes it much clearer to me as to what was happening.

Yes, thanks @Mooeypoo for the thorough investigation! It's very helpful.

Do we want to do the row cleanup as part of this ticket? If not, let's get another ticket in.

Okay, so @Mooeypoo and I tried to test this on beta cluster and the results were inconsistent. Sometimes it overrode the local preferences only but at another point it overrode all other wikis preferences but the local ones. My hunch is that this is something fishy with beta and we should give this another round of testing when it's on Group0 wikis tomorrow morning.

Okay. Good news and bad news. Let's begin with the bad news:
If a user has global preferences enabled for the check matrix, their local preferences everywhere have been overridden at this point and if the user changes the global, saves them and turns off their global preference, that will overwrite their local preferences everywhere.

Now for the good news:
After the user has disabled the global preference once, this behavior gets fixed. If they change their global preferences again, that will not overwrite what's in the local preferences.
Second bit of good news: Users who haven't yet enabled global preferences for their notification checkmatrix are not going to be affected by this bug at any point.

We've spent a decent amount of time on this bug and while it's still not fully fixed (see bad news), I'm inclined to close this task because we still don't have a good idea of what's going on and it's becoming a time sink. Leaving this to @Samwilson and @Mooeypoo to close if they agree.

In T201340#4536626, @Niharika wrote:

Okay. Good news and bad news.

Two things.
(1) I just understood that this problem can't be fixed by user. I fixed the local preferences, saved them, and they broken again by thereselves.
(2) I can see from the previous answer that there is a way to fix this, but I can't understand what I should do from the text. Can you explain it, please?
Thank you.

In T201340#4536656, @IKhitron wrote:

In T201340#4536626, @Niharika wrote:

Okay. Good news and bad news.

Two things.
(1) I just understood that this problem can't be fixed by user. I fixed the local preferences, saved them, and they broken again by thereselves.
(2) I can see from the previous answer that there is a way to fix this, but I can't understand what I should do from the text. Can you explain it, please?
Thank you.

@IKhitron Are you looking to stop using global preference for notifications? If you are using global preference, you should not be affected by this bug. Local exceptions should work as expected.

In T201340#4536659, @Niharika wrote:

@IKhitron Are you looking to stop using global preference for notifications? If you are using global preference, you should not be affected by this bug. Local exceptions should work as expected.

Day A: I opened Special:GlobalPrefs on my bot account. I changed one non-notification preference and saved. I opened Special:Preferences and saw it really works. I reopened Special:GlobalPrefs and removed the only change I did. I do not use the global preferences on that account any more.

Day C: I payed attention that one notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked. I checked it and saved.

Day E: I payed attention that the same notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked.

In T201340#4536678, @IKhitron wrote:

In T201340#4536659, @Niharika wrote:

@IKhitron Are you looking to stop using global preference for notifications? If you are using global preference, you should not be affected by this bug. Local exceptions should work as expected.

Day A: I opened Special:GlobalPrefs on my bot account. I changed one non-notification preference and saved. I opened Special:Preferences and saw it really works. I reopened Special:GlobalPrefs and removed the only change I did. I do not use the global preferences on that account any more.

That sounds like it works. Your local preferences are the ones that are active, global preferences are not affecting anything.

However, when you disabled GlobalPreferences, please check your local preferences and see what they are saved at. The bug potentially overrode those settings, so you might expect to have a certain checkbox on when it is actually off in local. Can you verify what local prefs you have for this bot?

Day C: I payed attention that one notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked. I checked it and saved.

Day E: I payed attention that the same notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked.

Which preference? The local? The global? If your global preferences are off, your local preferences are the ones that are working... is that what happened? It's a little confusing, I'm trying to make sense of what's wrong.

I'd like to see if I can understand what doesn't work, but I also don't want to destroy any of your preferences. Can you tell me wht is the bottom line of what you want to have, as preferences, at the end? Do you want to use the global preferences or the local ones?

In T201340#4539376, @Mooeypoo wrote:

In T201340#4536678, @IKhitron wrote:

In T201340#4536659, @Niharika wrote:

@IKhitron Are you looking to stop using global preference for notifications? If you are using global preference, you should not be affected by this bug. Local exceptions should work as expected.

Day A: I opened Special:GlobalPrefs on my bot account. I changed one non-notification preference and saved. I opened Special:Preferences and saw it really works. I reopened Special:GlobalPrefs and removed the only change I did. I do not use the global preferences on that account any more.

That sounds like it works. Your local preferences are the ones that are active, global preferences are not affecting anything.

However, when you disabled GlobalPreferences, please check your local preferences and see what they are saved at. The bug potentially overrode those settings, so you might expect to have a certain checkbox on when it is actually off in local. Can you verify what local prefs you have for this bot?

Day C: I payed attention that one notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked. I checked it and saved.

Day E: I payed attention that the same notification preference does not work. I opened Special:Preferences and saw that the checkbox is surprisingly not checked.

Which preference? The local? The global? If your global preferences are off, your local preferences are the ones that are working... is that what happened? It's a little confusing, I'm trying to make sense of what's wrong.

I'd like to see if I can understand what doesn't work, but I also don't want to destroy any of your preferences. Can you tell me wht is the bottom line of what you want to have, as preferences, at the end? Do you want to use the global preferences or the local ones?

The bottom line: The global preferences show X and off. The local preferences show Y. I expect Y to work. Instead, X overrides Y in local preferences. And when I open local preferences, fix them and save, after some time they return back to the global values.

In T201340#4539425, @IKhitron wrote:

The bottom line: The global preferences show X and off. The local preferences show Y. I expect Y to work. Instead, X overrides Y in local preferences. And when I open local preferences, fix them and save, after some time they return back to the global values.

The reason I'm asking for pointed questions, is that we can't manage to reproduce what you're saying off hand, so I am wondering if there's an issue that we miscommunicate here. The first step into us looking into what to fix is understanding what is going on, and what is happening, with what you expect to happen.

After this fix, the Global and Local preferences are working as expected; if you enable global, it acts on all your locals (except ones that have local exceptions) and if you remove it, your previous local settings return to act on each of the wikis.

The only caveat to this is that if you changed global before this fix was set then your local preferences may have been affected. Unfortunately, we can't fix that, but what we can do is make sure it doesn't happen again -- which is what this fix did.

The first time you toggle global/local may contain a mismatch between what you expect (local may display the global settings) but if you change your locals, and/or enable your globals, from that point on, things work as expected.

Is that not the case for you? Are you changing any of the local or globals and not see them as what they should be?

The step-by-step reproduction steps are crucial here, because since we can't reproduce this behavior anymore, we have to try and see if there's any behavior that we are missing that causes things to be out of synch.

In T201340#4557039, @Mooeypoo wrote:

Are you changing any of the local or globals and not see them as what they should be?

Yes, it is so. I change local preferences after the fix, and then see them with wrong value.

In T201340#4557600, @IKhitron wrote:

In T201340#4557039, @Mooeypoo wrote:

Are you changing any of the local or globals and not see them as what they should be?

Yes, it is so. I change local preferences after the fix, and then see them with wrong value.

@IKhitron I haven't been able to reproduce the problem after the fix. Can you tell us the exact steps you are doing which shows this problem?

I do not sure it is possible. I can tell that if I open now local prefs and change this, I'll see it back in a while. But I do not know how to reproduce the start situation, so it begin to happen.

In T201340#4508339, @Niharika wrote:

Do we want to do the row cleanup as part of this ticket? If not, let's get another ticket in.

I'm guessing this was never done. I want to clean up the hack in Hooks.php.

MariaDB [centralauth]> SELECT  DISTINCT(gp1.gp_user) FROM global_preferences gp1
    -> LEFT JOIN global_preferences gp2 ON gp1.gp_user=gp2.gp_user AND gp2.gp_property='echo-subscriptions'
    -> WHERE gp1.gp_property  LIKE 'echo-subscriptions-%' AND gp2.gp_user IS NULL;
+----------+
| gp_user  |
+----------+
| 54224289 |
| 45196561 |
+----------+

If this query is correct, there are two users who have global_preferences rows needing cleanup.

MariaDB [centralauth]> delete from global_preferences where gp_user in (54224289,45196561) and gp_property like 'echo-subscriptions-%';
Query OK, 36 rows affected (0.011 sec)

So, that's all done now and the hack can be removed.