Uploaded image for project: 'Debezium'
  1. Debezium
  2. DBZ-4245

SQL Server connector doesn't handle retriable errors during database state transitions

    XMLWordPrintable

Details

    Description

      An SQL Server database may be switched to the read-only mode which on its own doesn't make it unusable for the connector but the process of transition does.

      Success scenario

      First, a simple scenario that the connector handles properly:

      1. Start a pipeline from the tutorial.
      2. Execute the following in the database:
        ALTER TABLE testDB SET READ_ONLY WITH ROLLBACK IMMEDIATE
        

      In this case, the connector will encounter the following error and handle it as retriable:

      com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server did not return a response. The connection has been closed.
      

      By the time when the task has restarted, the database will be already available, and the task will successfully resume.

      Failure scenario #1

      Now, a more complex scenario when the transition takes longer than the task restart.

      1. Start the same pipeline.
      2. Start a transaction in testDB in one client session:
        USE testDB;
        BEGIN TRAN;
        
      1. In another session, switch the database into read-only mode. This command will block since there is a transaction in the previous session:
        ALTER DATABASE testDB SET READ_ONLY;
        
      1. Restart the connector task:
        $ curl -i -XPOST localhost:8083/connectors/inventory-connector/tasks/0/restart
        

      The task will fail with:

      com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "testDB" requested by the login. The login failed.
      

      Failure scenario #2

      This is similar to the previous one but with a slightly different error message.
      Replace database.dbname: testDB with database.names: testDB in the connector configuration. This will turn the connector into multi-partition mode. Now, in the above scenario, the error will be:

      com.microsoft.sqlserver.jdbc.SQLServerException: Database 'testDB' is in transition. Try the statement later.
      

      Additional information

      A real production scenario may look like the following: a database administrator will execute something like:

      ALTER TABLE testDB SET READ_ONLY WITH ROLLBACK AFTER 30 SECONDS 
      

      During these 30 seconds, the database will wait for all transactions to complete but won't allow any new transactions. This is when one of the above errors may occur.

      Attachments

        Activity

          People

            Unassigned Unassigned
            sergeimorozov Sergei Morozov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: