SQL Error during import

Product Support Self-hosting

AI-generated summary

The discussion revolves around a SQL error encountered by cocojoe and later Bahlsen when trying to import a Discourse backup with a size of ~3GB from version v1.3.0.beta5 to the latest-release. The error message indicates a duplicate key issue with the post_timings_unique index.

elberet suggests that the issue may be due to old data in the database and recommends destroying the Discourse container, backing up, and deleting /var/discourse/shared/standalone before re-bootstrapping. riking proposes deleting the problematic line from the import file as a solution.

Bahlsen attempts to edit the file but encounters issues when trying to re-package the backup. elberet provides guidance on re-packaging the backup, but Bahlsen still experiences the same error.

The conversation continues with elberet requesting the output of a command to verify the contents of the backup file, and Bahlsen shares the output. elberet asks for the content of the meta.json file and questions whether the correct backup file was used for restoration.

Bahlsen confirms the content of the meta.json file and mentions that the original backup came from a different location. elberet suggests trying again with a different filename, but Bahlsen still encounters the same error.

The issue is eventually identified as a problem with the compression process, and Bahlsen discovers that the error occurs when re-compressing the backup without modifying the folder. elberet finally identifies the solution, which involves naming the files individually when re-compressing the backup.

With the solution found, Bahlsen successfully imports the backup and expresses gratitude to elberet for their time and assistance.

1

Trying to import a discourse backup ~ 3GB from version v1.3.0.beta5 +97 to latest-release +87.

During the import the following sql issue

[2015-04-21 08:34:05] psql:/var/www/discourse/tmp/restores/default/2015-04-21-083104/dump.sql:3781134: ERROR:  could not create unique index "post_timings_unique"
[2015-04-21 08:34:05] DETAIL:  Key (topic_id, post_number, user_id)=(13868, 7, 6580) is duplicated.
[2015-04-21 08:34:05] EXCEPTION: psql failed
[2015-04-21 08:34:05] /var/www/discourse/lib/backup_restore/restorer.rb:217:in `restore_dump'
2

Your database may still contain old data.

Destroy the Discourse container, then backup and delete /var/discourse/shared/standalone. Re-bootstrap and you should end up with a pristine, completely empty Discourse instance that should be able to import your backup. :slight_smile:

3

This new server has a clean discourse setup however it is perhaps possible from previous failed imports when the versions were initially further out. Will look into this. Thanks

4

If all else fails, you can delete that line from the import. (I suggest using nano or other terminal editor, NOT a gui editor, to do this, as the import dump.sql file can be large.)

5

I am the one with the problem. When editing the file, I get this.

Trying to delete the line, I get this error when running the restore. The restore fails fairly quickly, right after starting to unzip, and before any text dump

[2015-05-03 11:10:16] Marking restore as running...
[2015-05-03 11:10:16] Making sure /var/www/discourse/tmp/restores/default/2015-05-03-111015 exists...
[2015-05-03 11:10:16] Copying archive to tmp directory...
[2015-05-03 11:11:15] Unzipping archive...
[2015-05-03 11:13:05] Extracting metadata file...
[2015-05-03 11:13:07] Validating metadata...
[2015-05-03 11:13:07]   Current version: 20150422160235
[2015-05-03 11:13:08] EXCEPTION: undefined method `[]' for nil:NilClass
[2015-05-03 11:13:08] /var/www/discourse/lib/backup_restore/restorer.rb:176:in `validate_metadata'
/var/www/discourse/lib/backup_restore/restorer.rb:35:in `run'
/var/www/discourse/lib/backup_restore/backup_restore.rb:161:in `block in start!'
/var/www/discourse/lib/backup_restore/backup_restore.rb:158:in `fork'
/var/www/discourse/lib/backup_restore/backup_restore.rb:158:in `start!'
/var/www/discourse/lib/backup_restore/backup_restore.rb:17:in `restore!'
/var/www/discourse/app/controllers/admin/backups_controller.rb:74:in `restore'

I do the following.

  1. I have renamed the backup to “original-cocos2d-forum-2015-05-01-041307.tar.gz”
  2. I then create a new folder: “mkdir cocos2d-forum-2015-05-01-041307”
  3. I unzip the original backup: “tar -xvzf original-cocos2d-forum-2015-05-01-041307.tar.gz -C cocos2d-forum-2015-05-01-041307”
  4. I switch to the folder, and fire up nano: “nano dump.sql”
  5. I goto the line by using Ctrl+_ and enter line 3864981. I edit the line from: “CREATE UNIQUE INDEX post_timings_unique ON post_timings USING btree (topic_id, post_number, user_id);” to “-- CREATE UNIQUE INDEX post_timings_unique ON post_timings USING btree (topic_id, post_number, user_id);”
  6. I Ctrl+O and Ctrl+X out of nano
  7. I switch back to backups/default folder, and create a tar.gz: “tar -cvzf cocos2d-forum-2015-05-01-041307.tar.gz cocos2d-forum-2015-05-01-041307”

In FileZilla, it then looks like this

Screen Shot 2015-05-03 at 13.33.48.png2302×440 37 KB

I really hope you can help me.

Cheers
Lars

6

Try this to re-package your backup:

tar -cvzf cocos2d-forum-2015-05-01-041307.tar.gz -C cocos2d-forum-2015-05-01-041307 .
7

The output log when compressing is slightly different, but the error is the same :frowning:

8

What’s the output of this command?

tar -tzf cocos2d-forum-2015-05-01-041307.tar.gz
9

One gazillion lines, starting with

root@forum:/var/discourse/shared/standalone/backups/default# tar -tzf cocos2d-forum-2015-05-01-041307.tar.gz
./
./meta.json
./dump.sql
./uploads/
./uploads/default/
./uploads/default/5458/
./uploads/default/5458/0c96160dc144e324.jpg
./uploads/default/3376/
./uploads/default/3376/8e2243ed0572a63e.png
./uploads/default/3291/
./uploads/default/3291/3f1ac78a77f0d55f.png
./uploads/default/2090/
10

That looks about right. Are you sure you actually restored that re-packaged backup and not the older version? And just to make sure, what’s the content of cocos2d-forum-2015-05-01-041307/meta.json?

11

meta.json is 47 bytes. A single line

{“source”:“discourse”,“version”:20150422160235}

Edit:
Unless there is something tricky in the backup system, I am 99.9% certain it was the right file

EditEdit:
Not sure if it matters, but the original backup came from var/docker. The new install is located in var/discourse
The original backup loads though, until the error in line 3.9Million

12

That, too, looks about right, so it’s pretty much got to be the wrong file. It may have gotten cached or not overwritten or something.

I’d try again with a different filename, e.g.

rm cocos2d-forum-2015-05-01-041307.tar.gz
tar -cvzf cocos2d-forum-2015-05-01-041307-v2.tar.gz -C cocos2d-forum-2015-05-01-041307 .
13

Okay, let me try a new filename

14

Arghhh …

Made a new filename, which showed up in Admin->Backups, but same error …

15

Same error as in EXCEPTION: undefined method '[]' for nil:NilClass?

I guess without a closer look at your system, I won’t be able to figure out what’s wrong. Sorry. :frowning:

16

Yeah, 100% same error.

The bug is in the compression. If I expand and compress, without touching the folder, the new tar.gz crashes. If I try to restore from the original backup, it starts, but crashes in line 3.9M

The lines I use

tar -xvzf org-backup -C clean-folder

and

tar -cvzf new-backup -C clean-folder .

There is a dot in compress line, not in extract line

Anyway, thanks for trying :smile:

17

Argh. I’m so blind. :sob:

You have to name the files individually:

tar -czf new-backup.tar.gz -C cocos2d-forum-2015-05-01-041307 meta.json dump.sql uploads
18

AHHH YES! Progress :smile:

I can now extract and compress the backup. Next step is to try to edit it

19

@elberet, it works :tada:

forum.cocos2d-objc.org

Thanks ever so much for your time. I can not thank you enough for this.