gbwey/persistent-odbc

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

olafklinke opened this issue · 5 comments

olafklinke commented

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 commented

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 commented

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 commented

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 commented

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 commented

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!