# SQL Error during import
**URL:** https://meta.discourse.org/t/sql-error-during-import/27793
**Category:** Self-hosting
**Created:** [April 21, 2015, 9:06am UTC](https://meta.discourse.org/t/sql-error-during-import/27793 "2015-04-21T09:06:16Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@cocojoe](https://meta.discourse.org/u/cocojoe)
#### Post date: [April 21, 2015, 9:06am UTC](https://meta.discourse.org/t/sql-error-during-import/27793/1 "2015-04-21T09:06:16Z")
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
```plaintext
[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'
```
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [April 21, 2015, 10:28am UTC](https://meta.discourse.org/t/sql-error-during-import/27793/2 "2015-04-21T10:28:10Z")
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. 🙂
---
### Author:  [@cocojoe](https://meta.discourse.org/u/cocojoe)
#### Post date: [April 21, 2015, 11:37am UTC](https://meta.discourse.org/t/sql-error-during-import/27793/3 "2015-04-21T11:37:23Z")
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
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [April 22, 2015, 1:41am UTC](https://meta.discourse.org/t/sql-error-during-import/27793/4 "2015-04-22T01:41:20Z")
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.)
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 12:24pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/5 "2015-05-03T12:24:01Z")
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.
Cheers
Lars
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 1:27pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/6 "2015-05-03T13:27:50Z")
Try this to re-package your backup:
```plaintext
tar -cvzf cocos2d-forum-2015-05-01-041307.tar.gz -C cocos2d-forum-2015-05-01-041307 .
```
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 2:35pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/7 "2015-05-03T14:35:11Z")
The output log when compressing is slightly different, but the error is the same ☹
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 2:41pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/8 "2015-05-03T14:41:23Z")
What’s the output of this command?
```plaintext
tar -tzf cocos2d-forum-2015-05-01-041307.tar.gz
```
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 2:54pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/9 "2015-05-03T14:54:57Z")
> [@elberet](#):
>
> tar -tzf cocos2d-forum-2015-05-01-041307.tar.gz
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/
```
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 2:58pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/10 "2015-05-03T14:58:42Z")
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`?
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 3:00pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/11 "2015-05-03T15:00:47Z")
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
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 3:07pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/12 "2015-05-03T15:07:16Z")
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.
```plaintext
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 .
```
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 3:08pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/13 "2015-05-03T15:08:32Z")
Okay, let me try a new filename
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 3:20pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/14 "2015-05-03T15:20:17Z")
Arghhh …
Made a new filename, which showed up in Admin-\>Backups, but same error …
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 3:44pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/15 "2015-05-03T15:44:48Z")
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. ☹
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 3:51pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/16 "2015-05-03T15:51:58Z")
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 😄
---
### Author:  [@elberet](https://meta.discourse.org/u/elberet)
#### Post date: [May 3, 2015, 4:10pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/17 "2015-05-03T16:10:58Z")
Argh. I’m so blind. 😭
You have to name the files individually:
```bash
tar -czf new-backup.tar.gz -C cocos2d-forum-2015-05-01-041307 meta.json dump.sql uploads
```
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 4:30pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/18 "2015-05-03T16:30:59Z")
AHHH YES! Progress 😄
I can now extract and compress the backup. Next step is to try to edit it
---
### Author:  [@Bahlsen](https://meta.discourse.org/u/Bahlsen)
#### Post date: [May 3, 2015, 4:53pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/19 "2015-05-03T16:53:06Z")
@elberet, it works 🎉
[forum.cocos2d-objc.org](http://forum.cocos2d-objc.org)
Thanks ever so much for your time. I can not thank you enough for this.
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [May 3, 2015, 5:17pm UTC](https://meta.discourse.org/t/sql-error-during-import/27793/20 "2015-05-03T17:17:08Z")