ODBC Driver 17 for SQL Server: Invalid transaction state on disconnect

[olafklinke]

To reproduce: Pristine MS SQL server 2012. Installed the driver on Debian using apt-get install msodbcsql17

Upon running either printMigration or runMigration, the commands seem to execute allright but the disconnect produces an error:

*** Exception: SqlError {seState = "[\"25000\"]", seNativeError = -1, seErrorMsg = "freeDbcIfNotAlready/SQLDisconnect: [\"0: [Microsoft][ODBC Driver 17 for SQL Server]Invalid transaction state\"]"}

A web search suggests that after the last command the SQL driver implicitly starts another transaction which gets in the way of the disconnect. Putting SQL_AUTOCOMMIT_OFF in the connection string does not help.

The migration to run:

Migrating: CREATe TABLE [meter]([id] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY ,[serial] VARCHAR(1000) NOT NULL,[type] VARCHAR(1000) NOT NULL,[obis] VARCHAR(1000) NOT NULL)
Migrating: CREATe TABLE [lastgang]([id] BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY ,[meter] BIGINT NOT NULL,[end_time] DATETIME2 NOT NULL,[k_w] REAL NOT NULL)
Migrating: ALTER TABLE [meter] ADD CONSTRAINT [unique_meter] UNIQUE([serial],[obis])
Migrating: ALTER TABLE [lastgang] ADD CONSTRAINT [unique_lastgang] UNIQUE([meter],[end_time])
Migrating: ALTER TABLE [lastgang] ADD CONSTRAINT [lastgang_meter_fkey] FOREIGN KEY([meter]) REFERENCES [meter]([id])

[olafklinke]

Update: Executing the same SQL statements using isql succeeds. Thus it is most likely a persistent-odbc or persistent issue. How can I find out which is the culprit?

[gbwey9]

Hi Olaf,

I've attached the code and output below, that tries to emulate your setup. It does run successfully, but I am running this on Windows.

If I explicitly call connClose at the end, then I do get the same error as here:

test.EXE: SqlError {seState = "[\"25000\"]", seNativeError = -1, seErrorMsg = "freeDbcIfNotAlready/SQLDisconnect: [\"0: [Microsoft][ODBC Driver 17 for SQL Server]Invalid transaction state\"]"}

Is this also what you are seeing?

Best, Grant

test.hs output

[olafklinke]

Thanks for investigating. Two differences: My code did not have the (deprecated) mkDeleteCascade sqlSettings but that makes no difference. Second difference is I did not use runSqlConn which makes me wonder whether we're talking about the same persistent package major versions, because your example does not type check in my project. The type of the generated migrateAll is ReaderT SqlBackend m () while in your code it seems to be Migration m -> m () which was changed from persistent-1.3.3 to persistent-2.0.0. Would you mind testing against persistent >= 2.0.0 again? I'm using the latest persistent-odbc-0.2.1.1 against persistent-2.10.5.3 (due to the constraint persistent < 2.11)

[gbwey9]

So the example I gave ran correctly with the stack.yaml overrides below. Can you send me a code snippet of what you actually used instead of runSqlConn so I can see where things are going wrong?

stack.yaml

resolver: lts-17.0 packages:

• . extra-deps:
• HDBC-odbc-2.6.0.0
• persistent-2.10.5.3
• persistent-template-2.8.2.3
• persistent-odbc-0.2.1.1

stack ls dependencies ... persistent 2.10.5.3 persistent-odbc 0.2.1.1 persistent-template 2.8.2.3 ...

ghci session

:t migrateAll migrateAll :: Migration

:i Migration type Migration :: * type Migration = Control.Monad.Trans.Writer.Lazy.WriterT [Text] (Control.Monad.Trans.Writer.Lazy.WriterT CautiousMigration (Control.Monad.Trans.Reader.ReaderT SqlBackend IO)) () -- Defined in ‘persistent-2.10.5.3:Database.Persist.Sql.Types’

[olafklinke]

Aha, I found the culprit. I defined my server

myserverType :: DBType
myserverType = MSSQL {mssql2012 = True}
myserver :: ConnectionString
myserver = undefined -- some real connection string"

Then I defined a shortcut

with_myserver :: (MonadUnliftIO m, MonadLogger m) => ReaderT SqlBackend m a -> m a
with_myserver = withODBCConn (Just myserverType) myserver . runReaderT

Note the runReaderT at the end, which has the same type as runSqlConn. I assumed that the latter is just a specialized form of the former, but apparently it is not. Looking at the source it is defined as runSqlConn r conn = with (acquireSqlConn conn) $ runReaderT r

The call in Main looks then like this: (runResourceT.runStderrLoggingT.with_myserver) (printMigration migrateAll)

If I replace runReaderT with runSqlConn the disconnect error disappears. What threw me off course was that the error message said something about disconnect, where the absence of acquireSqlConn probably means there was no connection to disconnect at all. Thanks anyways for your help!