session variables can not be used?

[tbdingqi]

how to repeat
db.Query("set @A=1")
db.Query("select @A")

return null

That seems to because when the "select @A" executed, the drive create a new session on mysql server. This leads to session-variables can not be used.

Does the mechanism involved by database/sql or github.com/go-sql-driver/mysql ?

[julienschmidt]

There are two things wrong with your provided example code:

1. db.Query returns rows which must be closed or completely read (which implicitly closes it). Otherwise the connection is blocked and can not be reused for other queries.
2. As you already guessed, the 2nd db.Query creates a new session in most cases, since it is not guaranteed to be executed on the same connection. Currently the only way to reserve a connection via Go's database/sql interface is to use transactions: http://golang.org/pkg/database/sql/#DB.Begin

I'd recommend you to read the database/sql tutorial at http://go-database-sql.org/

[arnehormann]

It may create a new session, it may use the same one. We don't known, it fully depends on the pooling by database/sql and there's nothing we can do to change it.

You can

• file an issue to add session support to Go and hope it gets added quick
• do it in a transaction (which uses the same connection for all queries)
• use the driver without database/sql
• use unsafe to extract the connection
• rewrite your queries so variables are initialized on SELECT

My recipe for the last one:

SET @row := 0;
SELECT @row := @row + 1, * FROM mytable;

becomes

SELECT @row := @row + 1, t.* FROM (SELECT @row := 0) cnt JOIN mytable t;

[tbdingqi]

But not all statements can be write into a single statement.
as

   db, _ := sql.Open("mysql", connect_string)

   trx, _:= db.Begin()
   db.Query("set binlog_format=row")

   rows, _ := db.Query("select @@binlog_format")
   for rows.Next() {
     var v string
     rows.Scan(&v)
     fmt.Println(v)
   }   
   rows.Close()

   trx.Commit()
   db.Close()

It prints "mixed", which is the global value.

For comman user, it looks like a bug.
Though it is because the database/sql, is there someting can be done to deal with such case?

to arnehormann, what do you mean by " use the driver without database/sql"? Without importing this package, build faild

[arnehormann]

@tbdingqi No, not all can be written into a single statement. But some of the SET problems can be solved that way.

It looks like a bug to a common user:
There's nothing we can do and some thing you can do. I listed those things.
If something else could be done, we would have done it or would do it as soon as we can.
Please read the tutorial Julien linked to, it describes the limitations of the api pretty good.

The import: You can use import "github.com/go-sql-driver/mysql" instead of

import (
  "database/sql"
  _  "github.com/go-sql-driver/mysql"
)

and speak to the driver directly without database/sql.

[xaprb]

In your sample code that printed the binlog format, you wrote

trx, := db.Begin()
db.Query("set binlogformat=row")
rows, _ := db.Query("select @@binlog_format")

You can't access the db variable there; you need to do this instead:

trx, err := db.Begin()
trx.Query(...)

Note that I am querying with trx, not db.

We may need to give clearer examples in the tutorial on how to do
"sessions" with transactions, highlighting this. If the tutorial is not
clear to you, please file a bug on
https://github.com/VividCortex/go-database-sql-tutorial/issues

[arnehormann]

@xaprb good catch, I missed the Tx misuse.

[leoleovich]

This method will not work for variables like sql_log_bin.
Mysql itself gives an error:
ERROR 1694 (HY000): Cannot modify @@session.sql_log_bin inside a transaction

Is there a chance to setup this variable for *sql.DB?