# So many broken users due to corrupt database

**URL:** <https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779>\
**Category:** Product Support\
**Created:** [December 12, 2019, 4:32pm UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779 "2019-12-12T16:32:12Z")\
**Posts on this page:** 1\
**Showing post:** 14

<div class="post-metadata">

**Author:** ![RGJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/rgj/32/523185_2.png) [@RGJ](https://meta.discourse.org/u/RGJ)\
**Post date:** [December 14, 2019, 3:42pm UTC](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779/14 "2019-12-14T15:42:41Z")

</div>

> [@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`.

---

_[View the full topic](https://meta.discourse.org/t/so-many-broken-users-due-to-corrupt-database/135779)._