Gorm: Go test errors with mssql

[kiwih]

When using transactions the following error appears
mssql: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Edit: Let's solve mssql errors one at a time

[jinzhu]

Hi @kiwih

Could you try to test on your local and point out how to fix the issue? as I can't test it out ;(

[kiwih]

I am updating this issue to be the core source of mssql issues until we resolve them. I will perform whichever tests you would like.

Running go test as it stands (after removing the non-compiling TestCompatabilityMode function) is as follows:

testing mssql...
[info] replacing callback `create` from D:/GoDev/src/github.com/jinzhu/gorm/callback_system_test.go:94
[info] removing callback `create` from D:/GoDev/src/github.com/jinzhu/gorm/callback_system_test.go:107

←[35m(mssql: IDENTITY_INSERT is already ON for table 'rogue.dbo.posts'. Cannot perform SET operation for table 'categories'.)←[0m
←[33m[2016-03-14 14:40:59]←[0m ←[31;1m ←[0m

←[35m(mssql: IDENTITY_INSERT is already ON for table 'rogue.dbo.posts'. Cannot perform SET operation for table 'categories'.)←[0m
←[33m[2016-03-14 14:40:59]←[0m ←[31;1m ←[0m

←[35m(mssql: Explicit value must be specified for identity column in table 'posts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity
column.)←[0m
←[33m[2016-03-14 14:40:59]←[0m ←[31;1m ←[0m
--- FAIL: TestBelongsTo (0.01s)
        association_test.go:21: Got errors when save post mssql: Explicit value must be specified for identity column in table 'posts' either when IDENTITY_INSERT is set to ON or when a replication us
er is inserting into a NOT FOR REPLICATION identity column.
panic: runtime error: invalid memory address or nil pointer dereference [recovered]
        panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x40 pc=0x55a610]

goroutine 207 [running]:
testing.tRunner.func1(0xc08267f8c0)
        c:/go/src/testing/testing.go:450 +0x178
github.com/jinzhu/gorm.(*Scope).related(0x0, 0x869fc0, 0xc0825085f0, 0xc082584290, 0x1, 0x1, 0x0)
        D:/GoDev/src/github.com/jinzhu/gorm/scope.go:947 +0x40
github.com/jinzhu/gorm.(*Association).Find(0xc0825b2270, 0x869fc0, 0xc0825085f0, 0xc0825b2270)
        D:/GoDev/src/github.com/jinzhu/gorm/association.go:19 +0xc8
github.com/jinzhu/gorm_test.TestBelongsTo(0xc08267f8c0)
        D:/GoDev/src/github.com/jinzhu/gorm/association_test.go:34 +0x3d2
testing.tRunner(0xc08267f8c0, 0xcbb5f0)
        c:/go/src/testing/testing.go:456 +0x9f
created by testing.RunTests
        c:/go/src/testing/testing.go:561 +0x874

goroutine 1 [chan receive]:
testing.RunTests(0xa6c060, 0xcbb560, 0x7f, 0x7f, 0x869d01)
        c:/go/src/testing/testing.go:562 +0x8b4
testing.(*M).Run(0xc082571ef8, 0x86a3e0)
        c:/go/src/testing/testing.go:494 +0x77
main.main()
        github.com/jinzhu/gorm/_test/_testmain.go:312 +0x11d

goroutine 17 [syscall, locked to thread]:
runtime.goexit()
        c:/go/src/runtime/asm_amd64.s:1696 +0x1

goroutine 11 [chan receive]:
database/sql.(*DB).connectionOpener(0xc0824f2960)
        c:/go/src/database/sql/sql.go:634 +0x4c
created by database/sql.Open
        c:/go/src/database/sql/sql.go:481 +0x33d
exit status 2
FAIL    github.com/jinzhu/gorm  0.467s

[kiwih]

I believe that SET IDENTITY_INSERT ON must only be done when you are in fact inserting a record with a known identity. Otherwise, it must be left off

[jinzhu]

Hi @kiwih

Are you using github.com/jinzhu/gorm/dialects/mssql? I think it is only set IDENTITY_INSERT ON for inserting

[kiwih]

Hi @jinzhu,

I'm using your main_test.go file, and the import is indeed present.

Cheers

[jordanchapian]

I am experiencing this. If I attempt an insertion without explicitly specifying the fields constituting the PK, I get an error. So what is the appropriate logic here? I feel that if we do explicitly specify all PK fields on creation, we should be setting IDENTITY_INSERT ON, and if no PK fields are set, this should be turned off.

For example, the current implementation is as follows:

func setIdentityInsert(scope *gorm.Scope) {
    if scope.Dialect().GetName() == "mssql" {
        scope.NewDB().Exec(fmt.Sprintf("SET IDENTITY_INSERT %v ON", scope.TableName()))
    }
}

And I believe this would accomplish what I stated above.

func setIdentityInsert(scope *gorm.Scope) {
    if scope.Dialect().GetName() == "mssql" {
        //determine if identity insert must be on
        for _, b := range scope.PrimaryFields() {
        if b.IsBlank == false {
            scope.NewDB().Exec(fmt.Sprintf("SET IDENTITY_INSERT %v ON", scope.TableName()))
            return
        }
    }

    //turn off by default
    scope.NewDB().Exec(fmt.Sprintf("SET IDENTITY_INSERT %v OFF", scope.TableName()))
    }
}

[devint1]

I am also observing this when inserting into a MS SQL database. The change provided by @jordanchapian seemed to fix the issue for me.

Also, #941 may be a duplicate of this.

[jordanchapian]

@devint1 My proposal above is not entirely correct. If IDENTITY_INSERT is set to on for table x, and you attempt to turn IDENTITY_INSERT on for table y, mssql will throw an error like IDENTITY_INSERT is already ON for table x

When I encountered this, I split setIdentityInsert(scope *gorm.Scope) into an intro and outro callback to ensure that IDENTITY_INSERT is turned off after the transaction.

I have a fork with these changes.

[ryanwalls]

Any updates on getting these changes merged into the mssql driver?

[jbfm]

Why would IDENTITY_INSERT be enabled at all by default? Most common use-case is probably to let the database itself handle primary keys. Or am I wrong?

EDIT: I've looked into this a bit more, the issue comes from issue #841, the problem here is IDENTITY_INSERT should ONLY be enabled when you manually want to set a value in an auto incrementing identity, the default here should be OFF. Gorm should not provide its own IDs by default, that's just asking for race conditions. Enabling this by default with every transaction effectively breaks transactions.

It's also broken, IDENTITY_INSERT can only be enabled for one table at a time, and there is no place that sets it to OFF again.

IDENTITY_INSERT should have to be a choice by the developer, and never forced.

I think the setIdentityInsert method should simply be removed, it's used in the wrong way, and it's broken in its current implementation since there's no provided way, other than a raw SQL query, to disable it again.

EDIT 2: While the fix by @jordanchapian would work, it still executes an extra query for every query in the transaction, extremely ineffective.

[LuisBosquez]

@jinzhu, have these changes been merged? I am also finding this issue, even when following the basic tutorial here: http://jinzhu.me/gorm/crud.html#create

By the way, SQL Server is now available on Linux and Docker which means you don't need windows to test them :)

[jbfm]

@LuisBosquez I've opened a PR to remove the automatic IDENTITY_INSERT entirely, #1379, no progress or comments on that though.

Easiest fix at the moment is to just manually apply the changes in that PR, and vendor Gorm.

[jinzhu]

@LuisBosquez thank you for let me know the docker thing, let me test it!

[LuisBosquez]

@jinzhu this is the official Docker image mssql-server-linux I am one of the owners of the project. Currently working on a set of tutorials for SQL Server with GoLang in our new SQL Server tutorials website So please let me know if you plan to integrate this fix anytime soon :)

[jinzhu]

Hi @LuisBosquez

I have tried to setup test environment with the docker, but can't login database gorm with account gorm (able to login with sa), anything I missed?

CREATE LOGIN gorm WITH PASSWORD = 'LoremIpsum86' ;
CREATE DATABASE gorm;
sp_addrole 'gorm' , 'db_owner';

[LuisBosquez]

@jinzhu I tried to repro your issue, but it worked for me. Did you 'USE master' when you created the account? What's the error message you're seeing?

[jinzhu]

@LuisBosquez

Maybe this could help you finger out the issue easily

Here is my searched solution, but not sure how to do that in a linux docker http://www.sherweb.com/blog/the-server-principal-a-is-not-able-to-access-the-database-b-under-the-current-security-context-microsoft-sql-server-error-916/

[LuisBosquez]

@jinzhu Funny, I hadn't encountered that error before. What's the command you're using to run the docker image? The website mentions that this issue is related to setting up a different collation. Are you using one? How did you set it up?

[jinzhu]

I am using docker kitematic https://github.com/docker/kitematic started docker image (microsoft/mssql-server-linux), and only set environment ACCEPT_EULA, SA_PASSWORD, haven't setup any other things

[LuisBosquez]

@jinzhu I found the error. You are missing a step after creating the login and the database. You need to create a USER for the "gorm" database. The steps should be as it follows:

under "sa":

CREATE LOGIN gorm WITH PASSWORD = 'LoremIpsum86' ;
CREATE DATABASE gorm;
USE gorm;
CREATE USER gorm FROM LOGIN gorm -- links the user "gorm" to the login within the database.
sp_addrole 'gorm' , 'db_owner';

after that you can now run: mssql -s localhost -u gorm -p LoremIpsum86

and then:

USE gorm

Let me know if it works for you!

[jinzhu]

@LuisBosquez thank you, it works!

a little problem in your code is the last line should be:

`sp_changedbowner 'gorm';

but not

sp_addrole 'gorm' , 'db_owner';

[jinzhu]

Hi @all, Fixed all tests for mssql 👏

[smithaitufe]

Hello I am having issues with MSSQL Server.

I tried to run an update statement and it failed.

tx := db.Begin()
    var m model.Menu
    if err := tx.Where("LOWER(code) = LOWER(?)", menu.Code).Find(&m).Error; err != nil && err == gorm.ErrRecordNotFound {
        fmt.Println("menu with code", menu.Code, "does not exist")
        handleError(tx, tx.Create(&menu))
    } else {
        m.Position = menu.Position
        m.Component = menu.Component
        m.Title = menu.Title
        m.Code = menu.Code
        handleError(tx, tx.Where("id = ?", m.ID).Find(&model.Menu{}).Updates(&m))
        menu = m
    }
    tx.Commit()

The issue it complained of is cannot update identity column 'id'
Please what is the recommended solution to updating in gorm when using MSSQL Server?