tsql - Flyway 在 Transact-SQL 迁移中因错误而失败

Question

将 Flyway 与 Microsoft SQL Server 结合使用时，我们观察到 this question 上描述的问题。

基本上，像这样的迁移脚本不会在另一部分失败时回滚成功的 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

在上面的示例中，即使前面的 ALTER TABLE 语句失败，表 t2 也会被创建。

在链接的问题上，建议采用以下方法(在飞路上下文之外):

1. >

   A multi-batch script should have a single error handler scope that rolls back the transaction on error, and commits at the end. In TSQL you can do this with dynamic sql

   • 动态 SQL 使脚本难以阅读并且会非常不方便
2. >

   With SQLCMD you can use the -b option to abort the script on error

   • 这在 flyway 中可用吗？
3. >

   Or roll your own script runner

   • flyway 可能就是这种情况吗？是否有特定于飞路的配置来启用正确的错误失败？

---

编辑:替代示例

给定:简单的数据库

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

这会导致 UPDATE 语句的错误消息 Invalid column name 'a_id'.。
可能的解决方案:在语句之间引入GO

迁移脚本 2(使用 GO:适用于“happy case”但出现错误时仅部分回滚)

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] 中都有匹配条目，这就会执行所需的迁移。
• 在给定的示例中，情况并非如此。 IE。我们得到两个错误:
  • 预期:无法将 NULL 值插入表“test.dbo.b”的列“a_id”；列不允许空值。更新失败。
  • 意外:COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。
  • 令人震惊的是:最后一个 ALTER TABLE [b] DROP COLUMN [a_name] 语句实际上已执行、提交且未回滚。 即。由于链接列丢失，此后无法修复。

这种行为实际上独立于 flyway，可以通过 SSMS 直接重现。

Answer 1

这个问题是 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* 版本(或类似的版本)下。然后，每个单独的迁移将通过或失败，而不是全部进行或全部失败。