Prepared statements are never deallocated

[mattetti]

jet/query.go

Line 72 in ae59b2c

stmt, err = q.qo.Prepare(query)

In the case of a transaction, jet always creates a prepared statement but never deallocate them. It looks like postgres automatically clears prepared statement when the session is reset, MySQL doesn't. When using this feature for too long, MySQL quickly reaches the max amount of created statements.

I'm not quite sure what the right behavior should be but deallocation should at least be doable by the users (currently the statement isn't exposed).

[mattetti]

Just checked mysql documentation and noticed that it behaves the same as postgres:

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

I'm still reaching the default limit quite quickly

[tcolar]

Are you sure that's the issue ?
With mysql I've been having a similar looking issue (could not connect anymore, hangs) and after much research it turned out to be this issue here, whihc is quite nasty:

https://code.google.com/p/go/issues/detail?id=5718

This is now fixed in golang "master" but after the 1.2 was released unfortunately.

In the mean time I had to create a golang 1.2 version with that fix to be able to use jet + MySql in production dafely:

https://github.com/tcolar/golang

[mattetti]

I'm not 100% sure, the error you mentioned is one that I reported myself ;) It was fixed and I believe is available in 1.2.1 which was released a few days ago.

I'm seeing the following error:

Error 1461: Can't create more than max_prepared_stmt_count statements (current value: 16382)

And indeed, the amount of prepared statements goes up and none of the them are being closed.

[tcolar]

Ho, you reported that, duh, That's pretty funny.

I haven't run into the max_prepared_stmt_count you reported but could be that I'm just not creating enough queries, will keep an eye open for it.

[mattetti]

You can try to run the following query in mysql to see:

 show global status like "com_stmt%";

I only started having this issue when I used transactions, and jet seems to
be doing something else with the prepared statements in that case and
that's where I think the problem is.

On Thu, Mar 6, 2014 at 3:33 PM, Thibaut Colar notifications@github.comwrote:

Ho, you reported that, duh, That's pretty funny.

I haven't run into the max_prepared_stmt_count you reported but could be
that I'm just not creating enough queries, will keep an eye open for it.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/30#issuecomment-36951456
.

[mattetti]

We are going to test adding an else statement here to close the SQL
statement:
https://github.com/eaigner/jet/blob/master/query.go#L78

On Thu, Mar 6, 2014 at 3:34 PM, Matt Aimonetti mattaimonetti@gmail.comwrote:

You can try to run the following query in mysql to see:

 show global status like "com_stmt%";

I only started having this issue when I used transactions, and jet seems
to be doing something else with the prepared statements in that case and
that's where I think the problem is.

On Thu, Mar 6, 2014 at 3:33 PM, Thibaut Colar notifications@github.comwrote:

Ho, you reported that, duh, That's pretty funny.

I haven't run into the max_prepared_stmt_count you reported but could be
that I'm just not creating enough queries, will keep an eye open for it.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/30#issuecomment-36951456
.

[tcolar]

I checked on "com_stmt%" but since the db as some other systems using it it's hard to tell.
Also currently I'm using very few transactions with Jet, although that will change soon.

[mattetti]

@eaigner any idea what might be happening? we are running with this patch in prod: splice@3aa0c34

Seems to be helping some.

[tcolar]

Doesn't seem Eric has been around Github in a while ?

Matt juts FYI, I've been using your fork for now. I did verify/see the issue, although it was not causing issues yet on our DB because of not too huge (but growing) number of transactions coming from Go so far.

See you at gophercon.

[emadera52]

Jet looks to be just right for a Go project I'm starting. I'd like to confirm that this issue was resolved with the addition of lines 81-85 in query.go. I found the following in a "go database sql tutorial" here on github. In the context of using Exec() instead of Query() for statements like Delete, Update, etc. it says...

"The Query() will return a sql.Rows, which will not be released until it's garbage collected, which can be a long time. During that time, it will continue to hold open the underlying connection, and this anti-pattern is therefore a good way to run out of resources (too many connections, for example)."

The "anti-pattern" example is...

_, err := db.Exec("DELETE FROM users") // OK
_, err := db.Query("DELETE FROM users") // BAD