Unpredictable MySQL behavior?

[569...@gmail.com]

Hi all,

I wrote a package which wraps "github.com/go-sql-driver/mysql" so that if the application gets disconnected from the database it will automatically reconnect and continue, without losing any commands. Unfortunately it appears to completely confuse the MySQL database.

My package, which I warn you not to use on anything but a test table is: https://github.com/AlasdairF/MySQL

The reconnection functionality appears to work fine, but for some reason random things happen when using this package.

One example is as follows:

// global variable
var lastinsertid *mysql.Stmt = db.MustPrepare(`SELECT LAST_INSERT_ID()`)

// later, after inserting
var id int
err = lastinsertid.QueryRow().Scan(&id)

The above never works, it does not return an error, it does not reconnect to the database, but neither does it update `id`. However, it does work if I do it the long-winded way, without the prepared statement:

// later after inserting,
var id int
li, err := db.Query(`SELECT LAST_INSERT_ID()`)
if err != nil {
 panic(err)
}
if !li.Next() {
 err = li.Err()
 li.Close()
 panic(err)
}
err = li.Scan(&id)
li.Close()

I am not using any goroutines so it doesn't make sense that `SELECT LAST_INSERT_ID()` fails when using the prepared query.

Any prepared statements from my package appear to do random things. They do not return an error but they will sometimes not update the passed pointer to the variable to to Scan, leaving it in whatever state it was before. I have not managed to work out exactly when things go wrong or why, but this package tends to entirely mess up any tables it operates on.

I can't see from the code of my package what I am doing wrong. As far as I can tell it looks good.

Does anyone see any errors in my package? https://github.com/AlasdairF/MySQL

Thanks,

Alasdair

[Giulio Iotti]

On Tuesday, November 24, 2015 at 9:47:20 AM UTC+2, 569...@gmail.com wrote:

I am not using any goroutines so it doesn't make sense that `SELECT LAST_INSERT_ID()` fails when using the prepared query.

AFAIK the sql package pools connections. Querying for the last inserted ID is very racy. The inserted ID is returned by the mysql driver on Exec() for inserts.

 

I can't see from the code of my package what I am doing wrong. As far as I can tell it looks good.

Did you run the race detector? I didn't, but looking quickly I can see some fields of Stmt (for example) are used with and without locking. Also there are strange mixes of locks, do once which might be correct, but I'd rather avoid.

This said, connections are handled automatically (also disconnections etc) by the sql package and driver implementations. What you want to achieve I think is just some sort of retry on error mechanism, which will make your code much simpler.

I hope this helps in some way!

-- 

Giulio Iotti 

[569...@gmail.com]

Hi Giulio,

Thanks for your reply. I see your point about Last Inserted ID and I've made that change.

Regarding the driver package handling the reconnections... the scenario I'm trying to account for is when MySQL goes down completely (the app is not running), for say 10 minutes. My package will wait on this, continuously retrying until it reconnects, and then it will continue effectively as normal. All SQL requests and threads which request them will wait on this instead of returning errors. It's made so there never should be an error which comes from being unable to connect to the MySQL server, the app will just keep trying to reconnect then continue as normal. Does the driver package do this automatically if all I were to do was keep trying queries on error?

[Giulio Iotti]

On Friday, November 27, 2015 at 6:27:02 AM UTC+2, 569...@gmail.com wrote:

Regarding the driver package handling the reconnections... the scenario I'm trying to account for is when MySQL goes down completely (the app is not running), for say 10 minutes. My package will wait on this, continuously retrying until it reconnects, and then it will continue effectively as normal. All SQL requests and threads which request them will wait on this instead of returning errors. It's made so there never should be an error which comes from being unable to connect to the MySQL server, the app will just keep trying to reconnect then continue as normal. Does the driver package do this automatically if all I were to do was keep trying queries on error?

No, it does. I understand what you want to do, it makes lots of sense.

Even better would be to have some kind of "generator" of connections strings to try, so that once a connection fails, a connection can be opened to another server in a round-robin fashion.

Regarding your package, have you tested with the race detector? I might be wrong, but I feel that db.once is reset in resetOnce() in a racy way. Shouldn't you acquire the lock there too?

-- 

Giulio Iotti 

[Giulio Iotti]

No, it does. I understand what you want to do, it makes lots of sense.

No, it doesn't, I wanted to say.

The driver has a pool of connections, upon error (driver.ErrBadConn) it retries on all connections until it succeeds, after that you get the error from the sql package.

-- 

Giulio Iotti