# So many broken users due to corrupt database
**URL:** https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779
**Category:** Support
**Created:** [12 december 2019 om 16:32 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779 "2019-12-12T16:32:12Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [12 december 2019 om 16:32 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/1 "2019-12-12T16:32:12Z")
I have a real issue with many of my users. The users can be seen in admin panel like this:
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.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [12 december 2019 om 16:43 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/2 "2019-12-12T16:43:40Z")
Another thing I notice:
If I inactivate, and then activate them, they get fixed!
---
### Author:  [@JimPas](https://meta.discourse.org/u/JimPas)
#### Post date: [13 december 2019 om 23:02 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/3 "2019-12-13T23:02:11Z")
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. 😉
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [14 december 2019 om 05:37 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/4 "2019-12-14T05:37:10Z")
No. My problem is not this. The dashboard says the users are activated.
---
### Author:  [@bartv](https://meta.discourse.org/u/bartv)
#### Post date: [14 december 2019 om 08:24 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/5 "2019-12-14T08:24:36Z")
Seems similar to [the issue I’m having](https://meta.discourse.org/t/cant-access-profile-of-new-user/135893/). Could you check if the affected names have duplicates or ‘very near matches’ in your database? For example `user.user` and `useruser`.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [14 december 2019 om 08:33 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/6 "2019-12-14T08:33:20Z")
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.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [14 december 2019 om 08:41 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/7 "2019-12-14T08:41:33Z")
btw with help of @RGJ we nailed the problem down to this two condisions:
> [@On another topic](https://meta.discourse.org/t/135755/19):
>
> there are two different problems:
>
> - users that cannot be found by username but can be found by id (like mojtaba11)
> - users that cannot be found by username\_lower but can be found by username (like alireza6)
>
> see:
>
> ```plaintext
> irb(main):002:0> User.username_available?('Alireza6')
> => true
> irb(main):004:0> User.username_exists?('alireza6')
> => false
> irb(main):006:0> DB.exec(User::USERNAME_EXISTS_SQL, username: 'alireza6')
> => 0
> discourse=> select username, username_lower from users where username_lower='alireza6';
> username | username_lower
> ----------+----------------
> (0 rows)
>
> discourse=> select username, username_lower from users where username='Alireza6';
> username | username_lower
> ----------+----------------
> Alireza6 | alireza6
> (1 row)
>
> ```
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [14 december 2019 om 09:02 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/8 "2019-12-14T09:02:22Z")
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.
---
### Author:  [@gerhard](https://meta.discourse.org/u/gerhard)
#### Post date: [14 december 2019 om 14:37 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/9 "2019-12-14T14:37:44Z")
Sounds like a corrupt database index. `REINDEX TABLE users` should solve the problem.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [14 december 2019 om 14:39 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/10 "2019-12-14T14:39:27Z")
What about the duplicate usernames? I have many same usernames being used for 2 distinct users.
The issue is acknowledged here:
> [@Critical Error: changed username, user got broken](https://meta.discourse.org/t/critical-error-changed-username-user-got-broken/132659/15):
>
> That’s an interesting observation! Usernames are effectively case-insensitive, so you shouldn’t be able to have two users with the same username. However: You can see what appears to be two distinct usernames returned when searching for fatemeh12. This shouldn’t have been able to happen (I’m not able to reproduce it). Both go to the same profile it seems. I’m at a loss on where to go from here. If someone doesn’t chime in by around this time tomorrow, I’ll see if I can track someo…
---
### Author:  [@gerhard](https://meta.discourse.org/u/gerhard)
#### Post date: [14 december 2019 om 14:44 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/11 "2019-12-14T14:44:27Z")
That’s probably a side effect of a corrupt index. You might need to clean that up manually before the reindexing works.
---
### Author:  [@hosna](https://meta.discourse.org/u/hosna)
#### Post date: [14 december 2019 om 14:46 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/12 "2019-12-14T14:46:17Z")
Can you explain how a corrupt index might happen? to prevent it in the future?
---
### Author:  [@gerhard](https://meta.discourse.org/u/gerhard)
#### Post date: [14 december 2019 om 14:47 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/13 "2019-12-14T14:47:43Z")
Hardware failure, bug in Postgres,… hard to tell. It happens.
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [14 december 2019 om 15:42 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/14 "2019-12-14T15:42:41Z")
> [@gerhard](#):
>
> You might need to clean that up manually before the reindexing works.
Except that that is impossible since the index is broken.
This does the trick:
```plaintext
# 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`.
---
### Author:  [@blattersturm](https://meta.discourse.org/u/blattersturm)
#### Post date: [27 april 2020 om 06:48 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/15 "2020-04-27T06:48:19Z")
> [@RGJ](#):
>
> 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 indexes` **is not enough** (it’ll ignore the `id` sequence for example).
---
### Author:  [@RGJ](https://meta.discourse.org/u/RGJ)
#### Post date: [27 april 2020 om 07:23 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/16 "2020-04-27T07:23:37Z")
You are absolutely right, I indeed remember having to recreate the constraints after renaming the tables.
My apologies for this important omission.
From my notes:
```
alter table poll_votes drop constraint fk_rails_b64de9b025;
alter table poll_votes add constraint fk_rails_b64de9b025 FOREIGN KEY (user_id) REFERENCES users(id);
alter table user_security_keys drop constraint fk_rails_90999b0454;
alter table user_security_keys add constraint fk_rails_90999b0454 FOREIGN KEY (user_id) REFERENCES users(id);
```
and nowadays also
```
alter table bookmarks drop constraint fk_rails_c1ff6fa4ac;
alter table bookmarks add constraint fk_rails_c1ff6fa4ac FOREIGN KEY (user_id) REFERENCES users(id);
```
And as an important disclaimer: **only use this when you absolutely know what you are doing**!
---
### Author:  [@blattersturm](https://meta.discourse.org/u/blattersturm)
#### Post date: [27 april 2020 om 07:24 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/17 "2020-04-27T07:24:54Z")
> [@RGJ](#):
>
> I indeed remember having to recreate the constraints after renaming the tables.
This does indeed seem to match what we found after querying `pg_catalog` for constraints affecting `users_old`.
Also, I recall `including defaults` being _at minimum_ required to not break registration.
Thanks for the rectification!
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [14 juni 2024 om 13:38 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/18 "2024-06-14T13:38:39Z")
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [14 juni 2024 om 13:40 UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/19 "2024-06-14T13:40:26Z")