Querying XML columns from MS SQL server via FreeTDS consumes too much memory. about odbc HOT 12 CLOSED

I didn't look at your problem properly, but it sounds like it works fine on 
Windows. If that is true, you can achieve similar behaviour on Linux by 
changing NewVariableWidthColumn function. Just go for NonBindableColumn not 
only when colWidth is 0, but also when it is “very large”. Pick a number 
for a “very large” - maybe 2MB or something - it is up to you.

I will think about this more, but I don’t see any better solution for it. If 
it is not too much trouble, please provide small example program for me to try. 
Thank you.

Alex

I cannot reproduce your problem here. I made changes similar to yours:

diff -r 78314be168c8 column.go
--- a/column.go Tue Sep 02 11:28:47 2014 +1000
+++ b/column.go Mon Oct 20 12:43:03 2014 +1100
@@ -192,6 +192,7 @@
 }

 func NewVariableWidthColumn(b *BaseColumn, ctype api.SQLSMALLINT, colWidth api.SQLULEN) Column {
+   fmt.Printf("col: %+v  type: %v  width: %v\n", b, ctype, colWidth)
    if colWidth == 0 {
        b.CType = ctype
        return &NonBindableColumn{b}
diff -r 78314be168c8 mssql_test.go
--- a/mssql_test.go Tue Sep 02 11:28:47 2014 +1000
+++ b/mssql_test.go Mon Oct 20 12:43:03 2014 +1100
@@ -1344,3 +1344,21 @@

    exec(t, db, "drop table dbo.temp")
 }
+
+func TestALEX(t *testing.T) {
+   db, sc, err := mssqlConnect()
+   if err != nil {
+       t.Fatal(err)
+   }
+   defer closeDB(t, db, sc, sc)
+
+   query := fmt.Sprintf("select cast('hello' as xml)")
+   var s string
+   err = db.QueryRow(query).Scan(&s)
+   if err != nil {
+       t.Fatal(err)
+   }
+   if s != "hello" {
+       t.Errorf("expected \"hello\", but received %v", s)
+   }
+}

and new test prints this on both linux and windows:

# go test -mssrv=xxx -msdb=xxx -msuser=xxxx -mspass=xxx -v -run=ALEX
=== RUN TestALEX
col: &{name: CType:0}  type: -8  width: 0
--- PASS: TestALEX (0.01s)
PASS
ok      code.google.com/p/odbc  0.011s

I use freetds-0.91 and unixODBC-2.3.1 on linux.

Alex

I copied your test into my clone and got this:

$ TDSVER=7.2 go test -run TestALEX -mssrv=xxx -msdb=xxx -msuser=xxx -mspass=xxx
col: &{name: CType:0}  type: 1  width: 2147483647
PASS
ok      code.google.com/p/odbc  0.009s

The client is running CentOS release 6.4 (Final) with FreeTDS 0.91 and 
unixODBC-2.3.1 both installed from source. Note that I have to set the TDSVER 
to get the test to work at all. I tried to set the default TDSVER during 
FreeTDS install, but it didn't seem to take.

The DB server is running MS SQL Server 2012.

TDSVER=7.2 does the trick. Thank you.

I will fix it when I have time.

Alex

Here https://codereview.appspot.com/161270043 is the fix. Does it work for you?

Alex

This solves the out of memory problem, but creates a new problem. When scanning 
an XML value with more than 1024 bytes of data the code panics in 
NonBindableColumn.Value().

To diagnose I inserted: "fmt.Println(len(b), l)" at column.go:267 and ran a 
test program that scans some real XML data from a table. This is the output:

1024 997
1024 379
1024 311
1024 185
1024 648
1024 416
1024 1597
panic: runtime error: slice bounds out of range

goroutine 16 [running]:
runtime.panic(0x57f2a0, 0x887a4f)
        /usr/local/go/src/pkg/runtime/panic.c:279 +0xf5
code.google.com/p/odbc.(*NonBindableColumn).Value(0xc20803a038, 0x1ee5900, 0x2, 
0x0, 0x0, 0x0, 0x0)
        /home/chines/go/src/code.google.com/p/odbc/column.go:268 +0x3ce
code.google.com/p/odbc.(*Rows).Next(0xc20803a040, 0xc20800e7e0, 0x3, 0x3, 0x0, 
0x0)
        /home/chines/go/src/code.google.com/p/odbc/rows.go:34 +0x1aa
database/sql.(*Rows).Next(0xc208004420, 0xc20800eb40)
        /usr/local/go/src/pkg/database/sql/sql.go:1542 +0xe5
main.main()

Note that the same program works fine from a Windows client. It looks like we 
are hitting the second part of the problem with FreeTDS mentioned in the link I 
provided in my original bug report. Specifically:

> Second, SQLGetData always returns SQL_SUCCESS, even when the buffer is too
> small. It NULL terminates at the given buffer length but gives no
> indication more should be read.

But it seems that var l (the BufferLen) does get the proper length, even when 
SQL_SUCCESS is returned. I tried to use that clue to get the rest of the data, 
but haven't had any success. I get the feeling that we can't call GetData again 
on the same column once it returns SQL_SUCCESS.

I agree with your assessment. We could return truncated data silently, but I 
think we should just report error instead.

I can see how my suggestion is not helping you.

I can only reproduce your error, if I set TDSVER=7.2 in

$ TDSVER=7.2 go test ...

It works find otherwise, even on linux. Why cannot you do the same?

Alex

Starting fresh:

$ cd $GOPATH
$ rm -rf pkg
$ rm -rf src/code.google.com/p/odbc/
$ go get code.google.com/p/odbc
$ go test -run=MS code.google.com/p/odbc -mssrv=xxx -msdb=xxx -msuser=xxx 
-mspass=xxx

--- FAIL: TestMSSQLCreateInsertDelete (0.01 seconds)
        mssql_test.go:192: db.Prepare("create table dbo.temp (name varchar(20), age int, isGirl bit, weight decimal(5,2), dob datetime, data varbinary(10) null, canBeNull varchar(10) null)") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
                {01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
                {01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
--- FAIL: TestMSSQLTransactions (0.00 seconds)
        mssql_test.go:192: db.Prepare("create table dbo.temp (name varchar(20))") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
                {01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
                {01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed

.... <snip> ....

--- FAIL: TestMSSQLSingleCharParam (0.00 seconds)
        mssql_test.go:192: db.Prepare("create table dbo.temp(name nvarchar(50), age int)") failed: SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
                {01000} [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
                {01000} [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
FAIL
FAIL    code.google.com/p/odbc  0.210s

$ TDSVER=7.2 go test -run=MS code.google.com/p/odbc -mssrv=CSIADTSA01 
-msdb=Borg_QA -msuser=anthill -mspass=4nTh177
ok      code.google.com/p/odbc  12.265s

Based on that result, I assumed I would need "TDSVER=7.2" when running my app 
as well. In fact, my app does get the same error without TDSVER=7.2 set in the 
environment ... unless I set TDS_Version=7.2 in the connection string. With 
TDS_Version=7.2 in the connection string my app will connect and retrieve data, 
but with code.google.com/p/odbc at tip XML columns use a 2GB buffer. If I then 
apply the patch to add "|| colWidth > 1024" to func NewVariableWidthColumn, 
then the app panics when it attempts to scan an XML column with more than 1K of 
data:

panic: runtime error: slice bounds out of range

goroutine 16 [running]:
runtime.panic(0x57f1e0, 0x8879cf)
        /usr/local/go/src/pkg/runtime/panic.c:279 +0xf5
code.google.com/p/odbc.(*NonBindableColumn).Value(0xc20803a038, 0xc44900, 0x2, 
0x0, 0x0, 0x0, 0x0)
        /home/chines/go/src/code.google.com/p/odbc/column.go:267 +0x338
code.google.com/p/odbc.(*Rows).Next(0xc20803a040, 0xc20800e7e0, 0x3, 0x3, 0x0, 
0x0)
        /home/chines/go/src/code.google.com/p/odbc/rows.go:34 +0x1aa
database/sql.(*Rows).Next(0xc208004420, 0xc20800eb40)
        /usr/local/go/src/pkg/database/sql/sql.go:1542 +0xe5
main.main()

(Note, I use goimports, which adds a blank line in the imports section of 
column.go when I manually add the patch, so my column.go:267 is probably your 
column.go:266.)

Summary: Without TDSVER=7.2 my app cannot connect to the DB, with TDSVER=7.2 my 
app uses too much memory for XML columns, with TDSVER=7.2 and the proposed 
patch my app panics on XML columns with more than 1K of data.

Moving on to other strategies.

I did some poking around in the FreeTDS code, and it looks like it has proper 
support for SQL_WLONGVARCHAR, so I changed my query to cast my XML column like 
so:

    select ... cast(XMLDATA as nvarchar(max)) ...

With code.google.com/p/odbc at tip, printing the parameters to 
NewVariableWidthColumn produces:

    col: &{name: CType:0}  type: -8  width: 536870911

The key win here is the type coming back as -8 (SQL_WCHAR) because that's what 
a Windows client gets for an XML column. Unfortunately the width is still a big 
number. The Windows client gets width=0, which triggers the NonBindableColumn 
logic. Adding the proposed patch deals with that nicely. I have verified that 
the proposed patch in combination with the work around of casting XML columns 
to nvarchar(max) works as desired (memory <= to actual data size) and supports 
XML data at least 20,305 characters (which used a buffer 39,590 bytes on the 
second call to SQLGetData) without any data truncation. I was concerned about 
the 8,000 byte limit often associated with varchar types, but that doesn't seem 
to be a problem, at least not for basic queries.

I have updated https://codereview.appspot.com/161270043 to return error if 
buffer overflow.

I still don't see why you cannot connect without TDSVER environment variable 
set. I tried connecting to MS SQL Server version 12 here, and I have no problem.

Up to you, but maybe you can check your log file - just set 
TDSDUMP=/tmp/freetds.log or similar.

You also might try latest dev version of freetds.

Also 
http://www.mssqltips.com/sqlservertip/2198/determine-which-version-of-sql-server
-data-access-driver-is-used-by-an-application might be helpful.

Please, review https://codereview.appspot.com/161270043 , and I will submit it. 
Thank you.

Alex

Thanks for the tip about TDSDUMP.

The TDSVER environment variable is needed because my FreeTDS install defaults 
to TDS version 5.0 (as shown by `tsql -C`). I don't know why either, I followed 
these directions: https://code.google.com/p/odbc/wiki/InstallingFreeTDS. 
Setting the version in the connection string works just fine, so I'm not going 
to lose sleep over it.

This issue was closed by revision c7df2c6eae12.