SQL Error during import

(Martin Walsh) #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'

(Jens Maier) #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:

(Martin Walsh) #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

(Kane York) #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.)

(Bahlsen) #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

I really hope you can help me.


(Jens Maier) #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 .

(Bahlsen) #7

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

(Jens Maier) #8

What’s the output of this command?

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

(Bahlsen) #9

One gazillion lines, starting with

root@forum:/var/discourse/shared/standalone/backups/default# tar -tzf cocos2d-forum-2015-05-01-041307.tar.gz

(Jens Maier) #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?

(Bahlsen) #11

meta.json is 47 bytes. A single line


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

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

(Jens Maier) #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 .

(Bahlsen) #13

Okay, let me try a new filename

(Bahlsen) #14

Arghhh …

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

(Jens Maier) #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:

(Bahlsen) #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


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

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

Anyway, thanks for trying :smile:

(Jens Maier) #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

(Bahlsen) #18

AHHH YES! Progress :smile:

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

(Bahlsen) #19

@elberet, it works :tada:

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

(Régis Hanol) #20