MSSQL Restore not working #12203

New Issue

GiteaMirror · 2025-11-02T10:02:00-06:00

GiteaMirror commented

2025-11-02 10:02:00 -06:00

Originally created by @Drardollan on GitHub (Dec 14, 2023).

Description

Restoring database by using the .sql file created by Gitea dump gives an error:

Msg 8107, Level 16, State 1, Line 12
IDENTITY_INSERT is already ON for table ‘Gitea.dbo.oauth2_application’. Cannot perform SET operation for table ‘oauth2_authorization_code’.

Which is correct, the IDENTITY_INSERT is already to import the oauth2_application table. This should be set to off.

Part of the script generated by the dump command:
/Generated by xorm 2023-12-12 14:06:24, from mssql to mssql/
IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_application]’ ) CREATE TABLE [oauth2_application] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [uid] BIGINT NULL, [name] NVARCHAR(255) NULL, [client_id] NVARCHAR(255) NULL, [client_secret] NVARCHAR(255) NULL, [confidential_client] BIT DEFAULT 1 NOT NULL, [redirect_uris] NVARCHAR(MAX) NULL, [created_unix] BIGINT NULL, [updated_unix] BIGINT NULL);
SET IDENTITY_INSERT [oauth2_application] ON;
CREATE INDEX [IDX_oauth2_application_created_unix] ON [oauth2_application] ([created_unix]);
CREATE INDEX [IDX_oauth2_application_updated_unix] ON [oauth2_application] ([updated_unix]);
CREATE INDEX [IDX_oauth2_application_uid] ON [oauth2_application] ([uid]);
CREATE UNIQUE INDEX [UQE_oauth2_application_client_id] ON [oauth2_application] ([client_id]);
INSERT INTO [oauth2_application] ([id], [uid], [name], [client_id], [client_secret], [confidential_client], [redirect_uris], [created_unix], [updated_unix])
INSERT INTO [oauth2_application] ([id], [uid], [name], [client_id], [client_secret], [confidential_client], [redirect_uris], [created_unix], [updated_unix])

IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_authorization_code]’ ) CREATE TABLE [oauth2_authorization_code] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [grant_id] BIGINT NULL, [code] NVARCHAR(255) NULL, [code_challenge] NVARCHAR(255) NULL, [code_challenge_method] NVARCHAR(255) NULL, [redirect_uri] NVARCHAR(255) NULL, [valid_until] BIGINT NULL);
SET IDENTITY_INSERT [oauth2_authorization_code] ON;
CREATE UNIQUE INDEX [UQE_oauth2_authorization_code_code] ON [oauth2_authorization_code] ([code]);
CREATE INDEX [IDX_oauth2_authorization_code_valid_until] ON [oauth2_authorization_code] ([valid_until]);

IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_grant]’ ) CREATE TABLE [oauth2_grant] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [user_id] BIGINT NULL, [application_id] BIGINT NULL, [counter] BIGINT DEFAULT 1 NOT NULL, [scope] NVARCHAR(MAX) NULL, [nonce] NVARCHAR(MAX) NULL, [created_unix] BIGINT NULL, [updated_unix] BIGINT NULL);
SET IDENTITY_INSERT [oauth2_grant] ON;
CREATE UNIQUE INDEX [UQE_oauth2_grant_user_application] ON [oauth2_grant] ([user_id],[application_id]);
CREATE INDEX [IDX_oauth2_grant_user_id] ON [oauth2_grant] ([user_id]);
CREATE INDEX [IDX_oauth2_grant_application_id] ON [oauth2_grant] ([application_id]);

(I have removed the INSERT values).

I expect the dump to add the needed "SET IDENTITY_INSERT [xxx] OFF;" statements.

Gitea Version

1.21.1

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

No response

How are you running Gitea?

I use: "gitea-1.21.1-windows-4.0-amd64.exe", downloaded from the Gitea site.

Database

MSSQL

Originally created by @Drardollan on GitHub (Dec 14, 2023). ### Description Restoring database by using the .sql file created by Gitea dump gives an error: Msg 8107, Level 16, State 1, Line 12 IDENTITY_INSERT is already ON for table ‘Gitea.dbo.oauth2_application’. Cannot perform SET operation for table ‘oauth2_authorization_code’. Which is correct, the IDENTITY_INSERT is already to import the oauth2_application table. This should be set to off. Part of the script generated by the dump command: /Generated by xorm 2023-12-12 14:06:24, from mssql to mssql/ IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_application]’ ) CREATE TABLE [oauth2_application] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [uid] BIGINT NULL, [name] NVARCHAR(255) NULL, [client_id] NVARCHAR(255) NULL, [client_secret] NVARCHAR(255) NULL, [confidential_client] BIT DEFAULT 1 NOT NULL, [redirect_uris] NVARCHAR(MAX) NULL, [created_unix] BIGINT NULL, [updated_unix] BIGINT NULL); SET IDENTITY_INSERT [oauth2_application] ON; CREATE INDEX [IDX_oauth2_application_created_unix] ON [oauth2_application] ([created_unix]); CREATE INDEX [IDX_oauth2_application_updated_unix] ON [oauth2_application] ([updated_unix]); CREATE INDEX [IDX_oauth2_application_uid] ON [oauth2_application] ([uid]); CREATE UNIQUE INDEX [UQE_oauth2_application_client_id] ON [oauth2_application] ([client_id]); INSERT INTO [oauth2_application] ([id], [uid], [name], [client_id], [client_secret], [confidential_client], [redirect_uris], [created_unix], [updated_unix]) INSERT INTO [oauth2_application] ([id], [uid], [name], [client_id], [client_secret], [confidential_client], [redirect_uris], [created_unix], [updated_unix]) IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_authorization_code]’ ) CREATE TABLE [oauth2_authorization_code] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [grant_id] BIGINT NULL, [code] NVARCHAR(255) NULL, [code_challenge] NVARCHAR(255) NULL, [code_challenge_method] NVARCHAR(255) NULL, [redirect_uri] NVARCHAR(255) NULL, [valid_until] BIGINT NULL); SET IDENTITY_INSERT [oauth2_authorization_code] ON; CREATE UNIQUE INDEX [UQE_oauth2_authorization_code_code] ON [oauth2_authorization_code] ([code]); CREATE INDEX [IDX_oauth2_authorization_code_valid_until] ON [oauth2_authorization_code] ([valid_until]); IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = ‘[oauth2_grant]’ ) CREATE TABLE [oauth2_grant] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [user_id] BIGINT NULL, [application_id] BIGINT NULL, [counter] BIGINT DEFAULT 1 NOT NULL, [scope] NVARCHAR(MAX) NULL, [nonce] NVARCHAR(MAX) NULL, [created_unix] BIGINT NULL, [updated_unix] BIGINT NULL); SET IDENTITY_INSERT [oauth2_grant] ON; CREATE UNIQUE INDEX [UQE_oauth2_grant_user_application] ON [oauth2_grant] ([user_id],[application_id]); CREATE INDEX [IDX_oauth2_grant_user_id] ON [oauth2_grant] ([user_id]); CREATE INDEX [IDX_oauth2_grant_application_id] ON [oauth2_grant] ([application_id]); (I have removed the INSERT values). I expect the dump to add the needed "SET IDENTITY_INSERT [xxx] OFF;" statements. ### Gitea Version 1.21.1 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Screenshots _No response_ ### Git Version _No response_ ### Operating System _No response_ ### How are you running Gitea? I use: "gitea-1.21.1-windows-4.0-amd64.exe", downloaded from the Gitea site. ### Database MSSQL

GiteaMirror added the type/bug label 2025-11-02 10:02:00 -06:00

GiteaMirror commented

2025-11-02 10:02:01 -06:00

@Xulunix commented on GitHub (Jan 29, 2024):

Any updates on this?
Being able to restore a dumped database would be something i consider important

@Xulunix commented on GitHub (Jan 29, 2024): Any updates on this? Being able to restore a dumped database would be something i consider important

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#12203