But when I try to reveal their email address, It wont show anything to me. And also their public profile gives a 404 error.
As far as I see, all of these users are almost inactive (they are active in terms of activation but inactive in terms of activity in forum). So I think maybe its been caused by a broken automatic inactive user removal in the very long past.
If a user is inactive for a certain length of time (730 days by default) they are automatically inactivated. The setting is in your dashboard under Settings/Users and scroll almost down to the bottom. That is where you’ll see that. But, it’s not necessary to make any changes for the sake of making a change. If those users haven’t logged in for 2 years, it makes no sense to reactivate them unless they start showing up again.
Seems similar to the issue I’m having. Could you check if the affected names have duplicates or ‘very near matches’ in your database? For example user.user and useruser.
Yes affected users are among those usernames that are very popular. so discourse suggest a near match username. I signup users with api. so I get a username from user. then I check it against discourse api and if its already taken, I would automatically use whatever discourse suggest.
To add to that, the issue @hosna is having is clearly an issue on the database level. It seems like there is some corruption in the users table. Copying the contents to a new table solves these issues.
That said, I did see two occurrences of @bartv’s issue in @hosna’s database (those were the two duplicates before Sept 22 and they both had a dot in their username) but I am not sure if these two issues are related. They just have the same symptoms.
Except that that is impossible since the index is broken.
This does the trick:
# create a temporary table without constraints and copy the contents into it
create table users_test (like users);
insert into users_test select * from users;
# remove duplicate usernames case sensitive, the dupes are after Sept 22
delete from users_test where username in (
select username
from users_test
group by username
having count(username)>1
) and created_at>'2019-09-22' ;
# remove duplicate usernames case insensitive, the dupes are after Sept 22
delete from users_test where lower(username) in (
select lower(username)
from users_test
group by lower(username)
having count(lower(username))>1
) and created_at > '2019-09-22' ;
# two more problems left, delete them individually
delete from users_test where id in (184534,130826);
# create a new table with constraints and copy the users
create table users_clean (like users including indexes);
insert into users_clean select * from users_test;
and then rename users to users_old and users_clean to users.
I’d like to drop in here and say this might break your database even more than the broken users will!
We now are stuck halfway between upgrades as a lot of constraints are still relying on users_old since we renamed the table, and this was only shown as an issue a few days after applying this apparently incomplete fix, also like users including indexesis not enough (it’ll ignore the id sequence for example).