Golang MySQL Driver does not allow database changes with ProxySQL

Question

We are trying to use GO to create a web app that connects to ProxySQL, which will in turn connect to the various MySQL database servers based on MySQL query rules that are set in ProxySQL.

We can connect to ProxySQL via command line in linux and everything works as expected:

    use database1;
    select id from users where user_id=1;
    use database2;
    select id from posts where user_id=1;

The above databases are located on physically different servers. Rules in ProxySQL know how to route them.

While connected to ProxySQL, the rules work fine and we are connected to the correct database servers and the requests are processed as expected.

When we try the same thing with GO and transactions, we cannot switch the databases as the it returns that the database does not exist.

The database exists on the back end servers, which ProxySQL will direct traffic to.

For whatever reason, it works on a command line, but not in GO

We can make it work if we connect with GO and specify the database on the connection, then disconnect and reconnect with a new database connection.

We are trying to use connection pool so we don't waste the time in opening and closing the connections.

    //open the connection to ProxySQL

    db, err = sql.Open("mysql", "user:password@tcp(x.x.x.x:6033)/")

    //The above only allows us to stay on the default database for the user

    //in order to access each of the databases we would need to open a
    //connection to database1 then open another connection to database2
    //which defeats the whole purpose of connection pooling.

    db, err = sql.Open("mysql", "user:password@tcp(x.x.x.x:6033)/database1")
    ... do something...

    db, err = sql.Open("mysql", "user:password@tcp(x.x.x.x:6033)/database2")
    ... do something ...



    //code below is an example of what is not working but works from
    //command line in linux. Results are not processed below because we
    //cannot get past the first error of no database
    tx, err := db.Begin()
    res, err := tx.Exec("use database1")
    res, err = tx.Exec("select id from users where user_id=1;")
    res, err = tx.Exec("use database2")
    res, err = tx.Exec("select id from posts where user_id=1;")
    tx.Commit()

ProxySQL knows how to route properly, but we cannot seem to get the functions working correctly in GOLANG

score 0 · Answer 1 · answered Oct 01 '19 at 16:57

0

I found some infos from golang official documents https://golang.google.cn/pkg/database/sql/#Tx.Exec:

func (*Tx) Exec
func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

Maybe you should use another method func (db *DB) Prepare(query string) (*Stmt, error), good luck.

answered Oct 01 '19 at 16:57

p1gd0g

631
5
16

We are trying the Tx.Exec as stated above. It works for queries but does not allow the use of the USE db; statement We also tried "select id from database1.user_id" which works on the connection to ProxySQL from a linux mysql prompt, but doesnt work in GO Drivers – Bob Kreitz Oct 01 '19 at 17:56
@BobKreitz How about creating two sql handlers in go? Sorry I am not totally clear about what you want. – p1gd0g Oct 02 '19 at 14:29

score 0 · Answer 2 · answered Oct 23 '20 at 17:56

For reasons I don't completely understand around how ProxySQL disables multiplexing and the interplay with USE statements, transactions don't seem to work nicely here.

I had success using Connections to ensure statements are executed on the correct database.

conn, err := db.Conn(ctx)
conn.Exec("use database1")
conn.Exec("select id from users where user_id=1;")
conn.Exec("use database2")
conn.Exec("select id from posts where user_id=1;")
conn.Close()

Golang MySQL Driver does not allow database changes with ProxySQL

2 Answers2