Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Restore database MSSQL fails (IDENTITY_INSERT) #4410

Open
MrDark opened this Issue Apr 10, 2017 · 6 comments

Comments

4 participants
@MrDark
Copy link

MrDark commented Apr 10, 2017

  • Gogs version (or commit ref): 0.11.4
  • Git version: 2.8.2.windows.1
  • Operating system: Windows Server 2012 R2
  • Database (use [x]):
    • PostgreSQL
    • MySQL
    • MSSQL
    • SQLite
  • Can you reproduce the bug at https://try.gogs.io:
    • Yes (provide example URL)
    • No
    • Not relevant
  • Log gist (usually found in log/gogs.log):

Description

When restoring a database backup (from SQLite) to a MSSQL server the following error is thrown:

gogs.exe restore --database-only --config=conf/app.ini --from="gogs-backup-1491558618.zip"
2017/04/07 14:18:49 [ INFO] Restore backup from: gogs-backup-1491558618.zip
2017/04/07 14:18:49 [FATAL] Fail to import database: fail to insert strcut: mssql: Cannot insert explicit value for identity column in table 'user' when IDENTITY_INSERT is set to OFF.

When in MSSQL you want to insert rows into a table including their identifier (primary key), you need to run the sql command as follow:

SET IDENTITY_INSERT [table] ON
-- execute insert query here
SET IDENTITY_INSERT [table] OFF
@LukeMauldin

This comment has been minimized.

Copy link

LukeMauldin commented Apr 21, 2017

I have duplicated the same issue. The host install system was Linux VMware Photon 4.4.41 kernel.

@Unknwon

This comment has been minimized.

Copy link
Member

Unknwon commented Jun 2, 2017

@MrDark would restore be successful if you do the following (as you provided)?

SET IDENTITY_INSERT [table] ON
-- execute insert query here
SET IDENTITY_INSERT [table] OFF
@MrDark

This comment has been minimized.

Copy link
Author

MrDark commented Jun 5, 2017

@Unknwon Correct

I wasn't able to fix it in the code in a timely fashion so I converted the JSON to SQL and manually imported all tables with those two lines wrapped around each table insert which made it work.

@Unknwon Unknwon added this to the 0.12 milestone Jun 5, 2017

@vitallish

This comment has been minimized.

Copy link

vitallish commented Jul 5, 2017

I also have this issue, but am currently trying to migrate my database instance. Is there a way I could capture the insert statements that are being created by gogs restore so I can restore this by hand while the bug is being fixed?

@Unknwon

This comment has been minimized.

Copy link
Member

Unknwon commented Jul 14, 2017

@vitallish hi, the OP already gave the SQL commands to turn on/off the IDENTITY_INSERT. I think you can turn on before ./gogs restore and turn off afterwards.

@MrDark

This comment has been minimized.

Copy link
Author

MrDark commented Jul 17, 2017

@Unknwon This command has to be run per table.

I actually switched back to SQLite for now because after a restart Gogs wanted to do some magic on the database but wasn't able (something about DATETIME2 column type, I'll post the full error later as a new issue)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.