Flyway bị lỗi do di chuyển Transact-SQL
Khi sử dụng Flyway kết hợp với Microsoft SQL Server, chúng tôi đang quan sát sự cố được mô tả trong câu hỏi này .
Về cơ bản, tập lệnh di chuyển như tập lệnh này không khôi phục các GOlô được giới hạn thành công khi một phần khác không thành công:
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
Trong ví dụ trên, bảng t2đang được tạo mặc dù ALTER TABLEcâu lệnh trước không thành công.
Đối với câu hỏi được liên kết, các phương pháp tiếp cận sau (bên ngoài ngữ cảnh đường bay) được đề xuất:
-
Một tập lệnh nhiều lô phải có một phạm vi xử lý lỗi duy nhất để khôi phục giao dịch do lỗi và cam kết ở cuối. Trong TSQL, bạn có thể làm điều này với sql động
- SQL động tạo ra tập lệnh khó đọc và sẽ rất bất tiện
-
Với SQLCMD, bạn có thể sử dụng
-btùy chọn để hủy bỏ tập lệnh do lỗi- Điều này có sẵn trong đường bay không?
-
Hoặc cuộn trình chạy kịch bản của riêng bạn
- Đây có thể là trường hợp trong đường bay? Có cấu hình dành riêng cho đường bay để kích hoạt lỗi thích hợp không?
EDIT: ví dụ thay thế
Cho: cơ sở dữ liệu đơn giản
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
Tập lệnh di chuyển 1 (không thành công, không có 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
Điều này dẫn đến thông báo lỗi Invalid column name 'a_id'.cho UPDATEcâu lệnh.
Giải pháp khả thi: giới thiệu GOgiữa các câu lệnh
Migration Script 2 (với GO: hoạt động cho "trường hợp vui vẻ" nhưng chỉ khôi phục một phần khi có lỗi)
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
- Điều này thực hiện việc di chuyển mong muốn miễn là tất cả các giá trị trong bảng
[b]có mục nhập phù hợp trong bảng[a]. - Trong ví dụ đã cho, không phải vậy. Tức là chúng tôi nhận được hai lỗi:
- hy vọng:
Cannot insert the value NULL into column 'a_id', table 'test.dbo.b'; column does not allow nulls. UPDATE fails. - bất ngờ:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. - Kinh hoàng:
ALTER TABLE [b] DROP COLUMN [a_name]câu lệnh cuối cùng đã thực sự được thực thi, được cam kết và không được lùi lại. Tức là người ta không thể sửa lỗi này sau đó vì cột liên kết bị mất.
- hy vọng:
Hành vi này thực sự độc lập với đường bay và có thể được tái tạo trực tiếp thông qua SSMS.
Trả lời
Vấn đề là cơ bản đối với lệnh GO. Nó không phải là một phần của ngôn ngữ T-SQL. Đó là một cấu trúc được sử dụng trong SQL Server Management Studio, sqlcmd và Azure Data Studio. Flyway chỉ đơn giản là chuyển các lệnh tới phiên bản SQL Server của bạn thông qua kết nối JDBC. Nó sẽ không xử lý các lệnh GO đó như các công cụ của Microsoft, tách chúng thành các lô độc lập. Đó là lý do tại sao bạn sẽ không thấy các lỗi quay lại riêng lẻ mà thay vào đó, bạn sẽ thấy tổng số lần khôi phục.
Cách duy nhất để giải quyết vấn đề này mà tôi biết là chia nhỏ các lô thành các tập lệnh di chuyển riêng lẻ. Đặt tên chúng theo cách sao cho rõ ràng, V3.1.1, V3.1.2, v.v. để mọi thứ đều thuộc phiên bản V3.1 * (hoặc một cái gì đó tương tự). Sau đó, mỗi lần di chuyển riêng lẻ sẽ vượt qua hoặc thất bại thay vì tất cả đều sẽ hoặc tất cả đều thất bại.
Đã chỉnh sửa 20201102 - đã tìm hiểu thêm nhiều điều về điều này và phần lớn đã viết lại nó! Cho đến nay đã được thử nghiệm trong SSMS, hãy lập kế hoạch thử nghiệm trong Flyway và viết một bài đăng trên blog. Để ngắn gọn trong quá trình di chuyển, tôi tin rằng bạn có thể đặt kiểm tra / xử lý lỗi tài khoản @@ vào một quy trình được lưu trữ nếu bạn muốn, đó cũng nằm trong danh sách của tôi để kiểm tra.
Thành phần trong bản sửa lỗi
Để xử lý lỗi và quản lý giao dịch trong SQL Server, có ba điều có thể giúp ích rất nhiều:
- Đặt XACT_ABORT thành BẬT (nó bị tắt theo mặc định). Cài đặt này "chỉ định liệu SQL Server có tự động khôi phục giao dịch hiện tại khi câu lệnh Transact-SQL phát sinh lỗi thời gian chạy hay không" tài liệu
- Kiểm tra trạng thái @@ TRANCOUNT sau mỗi dấu phân cách hàng loạt bạn gửi và sử dụng điều này để "cứu trợ" bằng RAISERROR / RETURN nếu cần
- Hãy thử / bắt / ném - Tôi đang sử dụng RAISERROR trong các ví dụ này, Microsoft khuyên bạn nên sử dụng THROW nếu nó có sẵn cho bạn (tôi nghĩ nó có sẵn SQL Server 2016+) - tài liệu
Làm việc trên mã mẫu ban đầu
Hai thay đổi:
- Đặt XACT_ABORT ON;
- Thực hiện kiểm tra @@ TRANCOUNT sau khi mỗi dấu phân cách hàng loạt được gửi để xem có nên chạy đợt tiếp theo hay không. Chìa khóa ở đây là nếu đã xảy ra lỗi, @@ TRANCOUNT sẽ là 0. Nếu không xảy ra lỗi, nó sẽ là 1. (Lưu ý: nếu bạn mở rõ ràng nhiều giao dịch "lồng nhau", bạn cần điều chỉnh tài khoản kiểm tra vì nó có thể cao hơn 1)
Trong trường hợp này, điều khoản kiểm tra @@ TRANCOUNT sẽ hoạt động ngay cả khi XACT_ABORT bị tắt, nhưng tôi tin rằng bạn muốn nó bật cho các trường hợp khác. (Cần phải đọc thêm về điều này, nhưng tôi chưa tìm thấy nhược điểm của việc BẬT nó.)
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;
Ví dụ thay thế
Tôi đã thêm một chút mã ở trên cùng để có thể đặt lại cơ sở dữ liệu thử nghiệm. Tôi đã lặp lại mô hình sử dụng XACT_ABORT ON và kiểm tra @@ TRANCOUNT sau mỗi đợt kết thúc hàng loạt (GO) được gửi.
/* 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;
Tài liệu tham khảo yêu thích của tôi về chủ đề này
Có một nguồn tài nguyên miễn phí tuyệt vời trực tuyến đào sâu về xử lý lỗi và giao dịch rất chi tiết. Nó được viết và duy trì bởi Erland Sommarskog:
- Phần Một - Xử lý lỗi Khởi động
- Phần hai - Lệnh và Cơ chế
- Phần ba - Thực hiện
Một câu hỏi phổ biến là tại sao XACT_ABORT vẫn cần thiết / nếu nó được thay thế hoàn toàn bằng TRY / CATCH. Thật không may, nó không được thay thế hoàn toàn, và Erland có một số ví dụ về điều này trong bài báo của mình, đây là một nơi tốt để bắt đầu về điều đó .