Transact-SQL 마이그레이션 오류시 Flyway 실패
Microsoft SQL Server와 함께 Flyway를 사용할 때이 질문에 설명 된 문제를 관찰하고 있습니다.
기본적으로 다음과 같은 마이그레이션 스크립트는 GO다른 부분이 실패 할 때 성공적으로 구분 된 배치를 롤백하지 않습니다 .
BEGIN TRANSACTION
-- Create a table with two nullable columns
CREATE TABLE [dbo].[t1](
[id] [nvarchar](36) NULL,
[name] [nvarchar](36) NULL
)
-- add one row having one NULL column
INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)
-- set one column as NOT NULLABLE
-- this fails because of the previous insert
ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
GO
-- create a table as next action, so that we can test whether the rollback happened properly
CREATE TABLE [dbo].[t2](
[id] [nvarchar](36) NOT NULL
)
GO
COMMIT TRANSACTION
위의 예에서는 t2앞의 ALTER TABLE문이 실패 하더라도 테이블 이 생성 됩니다.
연결된 질문에 대해 다음과 같은 접근 방식 (비행로 컨텍스트 외부)이 제안되었습니다.
-
다중 배치 스크립트에는 오류시 트랜잭션을 롤백하고 마지막에 커밋하는 단일 오류 처리기 범위가 있어야합니다. TSQL에서는 동적 SQL로이를 수행 할 수 있습니다.
- 동적 SQL은 읽기 어려운 스크립트를 만들고 매우 불편합니다.
-
SQLCMD를 사용하면
-b옵션을 사용하여 오류 발생시 스크립트를 중단 할 수 있습니다.- 플라이 웨이에서 사용할 수 있습니까?
-
또는 자신 만의 스크립트 실행기를 굴립니다.
- 이것이 플라이 웨이의 경우일까요? 오류시 적절한 실패를 가능하게하는 이동 경로 별 구성이 있습니까?
편집 : 대체 예
주어진 : 간단한 데이터베이스
BEGIN TRANSACTION
CREATE TABLE [a] (
[a_id] [nvarchar](36) NOT NULL,
[a_name] [nvarchar](100) NOT NULL
);
CREATE TABLE [b] (
[b_id] [nvarchar](36) NOT NULL,
[a_name] [nvarchar](100) NOT NULL
);
INSERT INTO [a] VALUES (NEWID(), 'name-1');
INSERT INTO [b] VALUES (NEWID(), 'name-1'), (NEWID(), 'name-2');
COMMIT TRANSACTION
마이그레이션 스크립트 1 (실패, GO 없음)
BEGIN TRANSACTION
ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
ALTER TABLE [b] DROP COLUMN [a_name];
COMMIT TRANSACTION
이로 인해 명령문 에 대한 오류 메시지가 표시 Invalid column name 'a_id'.됩니다 UPDATE.
가능한 해결책 : GO문 사이에 도입
마이그레이션 스크립트 2 (GO 사용 : "행복한 케이스"를 위해 작동하지만 오류가있는 경우 부분 롤백 만 해당)
BEGIN TRANSACTION
SET XACT_ABORT ON
GO
ALTER TABLE [b] ADD [a_id] [nvarchar](36) NULL;
GO
UPDATE [b] SET [a_id] = [a].[a_id] FROM [a] WHERE [a].[a_name] = [b].[a_name];
GO
ALTER TABLE [b] ALTER COLUMN [a_id] [nvarchar](36) NOT NULL;
GO
ALTER TABLE [b] DROP COLUMN [a_name];
GO
COMMIT TRANSACTION
- 테이블의 모든 값이 테이블
[b]에 일치하는 항목이있는 한 원하는 마이그레이션을 수행 합니다[a]. - 주어진 예에서는 그렇지 않습니다. 즉, 두 가지 오류가 발생합니다.
- 예상 :
Cannot insert the value NULL into column 'a_id', table 'test.dbo.b'; column does not allow nulls. UPDATE fails. - 예기치 않은:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. - 끔찍하게도 마지막
ALTER TABLE [b] DROP COLUMN [a_name]명령문이 실제로 실행되고 커밋되고 롤백되지 않았습니다. 즉, 연결 열이 손실되어 나중에이를 수정할 수 없습니다.
- 예상 :
이 동작은 실제로 플라이 웨이와 독립적이며 SSMS를 통해 직접 재현 할 수 있습니다.
답변
문제는 GO 명령의 기본입니다. T-SQL 언어의 일부가 아닙니다. SQL Server Management Studio, sqlcmd 및 Azure Data Studio 내에서 사용중인 구성입니다. Flyway는 단순히 JDBC 연결을 통해 SQL Server 인스턴스에 명령을 전달합니다. Microsoft 도구처럼 GO 명령을 처리하지 않고 독립된 배치로 분리합니다. 그렇기 때문에 오류에 대한 개별 롤백이 표시되지 않고 대신 전체 롤백이 표시됩니다.
내가 알고있는이 문제를 해결하는 유일한 방법은 배치를 개별 마이그레이션 스크립트로 분리하는 것입니다. 명확하게 V3.1.1, V3.1.2 등으로 이름을 지정하여 모든 것이 V3.1 * 버전 (또는 유사) 아래에 있도록합니다. 그러면 각각의 개별 마이그레이션이 모두 진행되거나 모두 실패하는 대신 통과 또는 실패합니다.
20201102 편집-이것에 대해 더 많이 배우고 대체로 다시 작성했습니다! 지금까지 SSMS에서 테스트 해 왔으며 Flyway에서도 테스트 할 계획을 세우고 블로그 게시물을 작성하십시오. 마이그레이션의 간결함을 위해 원하는 경우 @@ trancount 검사 / 오류 처리를 저장 프로 시저에 넣을 수 있다고 생각합니다.이 항목도 테스트 할 목록에 있습니다.
수정 된 성분
SQL Server의 오류 처리 및 트랜잭션 관리에 도움이 될 수있는 세 가지가 있습니다.
- XACT_ABORT를 ON으로 설정합니다 (기본적으로 해제되어 있음). 이 설정은 "Transact-SQL 문은 런타임 오류 발생 시키면 SQL 서버가 자동으로 롤 현재의 트랜잭션 (transaction)를 백업할지 여부를 지정합니다" 문서를
- 보내는 각 배치 구분 기호 다음에 @@ TRANCOUNT 상태를 확인하고 필요한 경우 RAISERROR / RETURN으로 "베일 아웃"하기 위해이를 사용합니다.
- 시도 / 잡기 / 던져 - 나는이 예제에서 RAISERROR를 사용하고, 마이크로 소프트가 당신에게 가능한 경우 THROW를 사용하는 것이 좋습니다 (그것은 가능한 SQL 서버 2016+ 내 생각이다) - 문서
원본 샘플 코드 작업
두 가지 변경 사항 :
- XACT_ABORT를 ON으로 설정하십시오.
- 각 배치 구분 기호를 보낸 후 @@ TRANCOUNT를 확인하여 다음 배치를 실행해야하는지 확인합니다. 여기서 핵심은 오류가 발생하면 @@ TRANCOUNT가 0이된다는 것입니다. 오류가 발생하지 않은 경우 1이됩니다 (참고 : 여러 "중첩 된"트랜잭션을 명시 적으로 여는 경우 트랜잭션 수를 조정해야합니다. 1보다 클 수 있으므로 확인합니다.)
이 경우 @@ TRANCOUNT 검사 절은 XACT_ABORT가 꺼져 있어도 작동하지만 다른 경우에는 켜야한다고 생각합니다. (이것에 대해 더 읽어 볼 필요가 있지만, 아직 켜두는 것에 대한 단점을 발견하지 못했습니다.)
BEGIN TRANSACTION;
SET XACT_ABORT ON;
GO
-- Create a table with two nullable columns
CREATE TABLE [dbo].[t1](
[id] [nvarchar](36) NULL,
[name] [nvarchar](36) NULL
)
-- add one row having one NULL column
INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)
-- set one column as NOT NULLABLE
-- this fails because of the previous insert
ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
GO
IF @@TRANCOUNT <> 1
BEGIN
DECLARE @ErrorMessage AS NVARCHAR(4000);
SET @ErrorMessage
= N'Transaction in an invalid or closed state (@@TRANCOUNT=' + CAST(@@TRANCOUNT AS NVARCHAR(10))
+ N'). Exactly 1 transaction should be open at this point. Rolling-back any pending transactions.';
RAISERROR(@ErrorMessage, 16, 127);
RETURN;
END;
-- create a table as next action, so that we can test whether the rollback happened properly
CREATE TABLE [dbo].[t2](
[id] [nvarchar](36) NOT NULL
)
GO
COMMIT TRANSACTION;
대체 예
테스트 데이터베이스를 재설정 할 수 있도록 상단에 약간의 코드를 추가했습니다. XACT_ABORT ON을 사용하고 각 배치 종료 자 (GO)가 전송 된 후 @@ TRANCOUNT를 확인하는 패턴을 반복했습니다.
/* Reset database */
USE master;
GO
IF DB_ID('transactionlearning') IS NOT NULL
BEGIN
ALTER DATABASE transactionlearning
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE transactionlearning;
END;
GO
CREATE DATABASE transactionlearning;
GO
/* set up simple schema */
USE transactionlearning;
GO
BEGIN TRANSACTION;
CREATE TABLE [a]
(
[a_id] [NVARCHAR](36) NOT NULL,
[a_name] [NVARCHAR](100) NOT NULL
);
CREATE TABLE [b]
(
[b_id] [NVARCHAR](36) NOT NULL,
[a_name] [NVARCHAR](100) NOT NULL
);
INSERT INTO [a]
VALUES
(NEWID(), 'name-1');
INSERT INTO [b]
VALUES
(NEWID(), 'name-1'),
(NEWID(), 'name-2');
COMMIT TRANSACTION;
GO
/*******************************************************/
/* Test transaction error handling starts here */
/*******************************************************/
USE transactionlearning;
GO
BEGIN TRANSACTION;
SET XACT_ABORT ON;
GO
IF @@TRANCOUNT <> 1
BEGIN
DECLARE @ErrorMessage AS NVARCHAR(4000);
SET @ErrorMessage
= N'Check 1: Transaction in an invalid or closed state (@@TRANCOUNT=' + CAST(@@TRANCOUNT AS NVARCHAR(10))
+ N'). Exactly 1 transaction should be open at this point. Rolling-back any pending transactions.';
RAISERROR(@ErrorMessage, 16, 127);
RETURN;
END;
ALTER TABLE [b] ADD [a_id] [NVARCHAR](36) NULL;
GO
IF @@TRANCOUNT <> 1
BEGIN
DECLARE @ErrorMessage AS NVARCHAR(4000);
SET @ErrorMessage
= N'Check 2: Transaction in an invalid or closed state (@@TRANCOUNT=' + CAST(@@TRANCOUNT AS NVARCHAR(10))
+ N'). Exactly 1 transaction should be open at this point. Rolling-back any pending transactions.';
RAISERROR(@ErrorMessage, 16, 127);
RETURN;
END;
UPDATE [b]
SET [a_id] = [a].[a_id]
FROM [a]
WHERE [a].[a_name] = [b].[a_name];
GO
IF @@TRANCOUNT <> 1
BEGIN
DECLARE @ErrorMessage AS NVARCHAR(4000);
SET @ErrorMessage
= N'Check 3: Transaction in an invalid or closed state (@@TRANCOUNT=' + CAST(@@TRANCOUNT AS NVARCHAR(10))
+ N'). Exactly 1 transaction should be open at this point. Rolling-back any pending transactions.';
RAISERROR(@ErrorMessage, 16, 127);
RETURN;
END;
ALTER TABLE [b] ALTER COLUMN [a_id] [NVARCHAR](36) NOT NULL;
GO
IF @@TRANCOUNT <> 1
BEGIN
DECLARE @ErrorMessage AS NVARCHAR(4000);
SET @ErrorMessage
= N'Check 4: Transaction in an invalid or closed state (@@TRANCOUNT=' + CAST(@@TRANCOUNT AS NVARCHAR(10))
+ N'). Exactly 1 transaction should be open at this point. Rolling-back any pending transactions.';
RAISERROR(@ErrorMessage, 16, 127);
RETURN;
END;
ALTER TABLE [b] DROP COLUMN [a_name];
GO
COMMIT TRANSACTION;
이 주제에 대한 내가 좋아하는 참조
오류 및 트랜잭션 처리에 대해 자세히 설명하는 멋진 무료 리소스가 온라인에 있습니다. Erland Sommarskog가 작성하고 유지 관리합니다.
- 1 부 – Jumpstart 오류 처리
- 2 부 – 명령 및 메커니즘
- 3 부 – 구현
한 가지 일반적인 질문은 XACT_ABORT가 여전히 필요한 이유입니다. TRY / CATCH로 완전히 대체 된 경우입니다. 불행히도 그것은 완전히 대체되지 않았고 Erland는 그의 논문에서 이것에 대한 몇 가지 예를 가지고 있습니다. 이것은 시작하기에 좋은 곳입니다 .