Index_users_on_username_lower error during database restore: import failed

support
1

Hi!

Last night, I had a massive issue on a forum and I had to re-create everything. But during the restore, I have an issue and it failed. Here is the error

ERROR:  could not create unique index "index_users_on_username_lower"
DETAIL:  Key (username_lower)=(lea) is duplicated.
EXCEPTION: psql failed: DETAIL:  Key (username_lower)=(lea) is duplicated.

I think it can be connected to twitter login, but can the restore change a username_lower name if it’s duplicated? I don’t think I would be able to change in the sql file (it’s pretty big) and reupload it.

1 Like
2

Another case of database index corruption as previously mentioned here Critical Error: changed username, user got broken

I don’t know what kind of issue you had that made you restore the backup but it could be connected.

3 Likes
3

It was a different issue, the server was unable to access pups.git, Could not resolve host: github.com. I tried some solutions found last night, but it didn’t work.

With the restoration, I think it’s only an issue with one username (maybe linked to twitter one click account creation, or something like that). I’m trying to fix it manually, but managing a 1gb sql file is not ideal

1 Like
4

edit : with the software vim, I was able to edit the sql file, I found the right lines. Restore worked. I just need to rebake everything and it should be good to go

There was a “Lea” username and a “lea” username. It’s weird that Discourse let that happened. It’s a pretty old forum (june 2014), and I updated it frequently, so it was maybe due to a specific version.

3 Likes
5

The day was crazy, but I’ll try to explain how I fixed everything if anyone has reaaaally bad luck like me :upside_down_face:

  1. Download the backup. And extract a few time until you have the dump.sql file

  2. Edit the dump.sql with a software like vim download : vim online

  3. I suck so bad to sql. To find the right table, I searched username_lower,. It pointed me to the users table, then I researched “lea”. I edited the two Lea entries. It was probably way easier. But remember, I suck at sql, especially when they are 1.5gb. Save the file.

  4. I gzip the dump.sql with 7zip. You should have a new file named : dump.sql.gz

  5. Create a new folder in :file_folder: /var/discourse/shared/standalone/backups/default/ I used test

  6. If you have a good internet connection, upload the :file_folder: uploads folder you obtained when you decompressed your backup file, put the folder in /var/discourse/shared/standalone/backups/default/test/
    6.1 If you don’t have a good connection like me, you have to use your server note the name of your backup file and use this command tar xvzf /var/discourse/shared/standalone/backups/default/yourbackupfile.tar.gz -C /var/discourse/shared/standalone/backups/default/test
    6.2 In the test folder, you’ll have a dump.sql.gz file and uploads folder. It’s good.

  7. Upload the dump.sql.gz file in your test folder to replace the broken dump.sql.gz

  8. In your server : go to cd /var/discourse/shared/standalone/backups/default/test

  9. You need to remake your backup file, you have to use the exact name of the older backup file : tar -czvf yourbackupfile.tar.gz uploads/ dump.sql.gz

  10. In your ftp go to :file_folder: /var/discourse/shared/standalone/backups/default/, delete the broken backup or move in to another folder

  11. Move the new backup file in /var/discourse/shared/standalone/backups/default/

  12. Restore the backup. I prefer to use this method, and if you got there, you should be able to use it easily : Restore a backup from command line

The backup should succeed :sweat_smile:

Easy.

4 Likes
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.