Prepared statements exceeding limits when upgrading from 0.5 to 0.6

[lucacorti]

After upgrading to myxql 0.6 we saw prepared statemets constantly growing over time and exceeding the configured limit (16k)

while on 0.5 they are a small constant number

[pierot]

We are experiencing the exact same.

[josevalim]

Thank you for the report. I am afraid this commit introduced the issue. /cc @greg-rychlewski

Can you please try running from commit: 1014f1c and see if the issue persists?

[greg-rychlewski]

Hi @pierot @lucacorti ,

Do you know if you are using prepared: :named or :unnamed? Thank you

[pierot]

@greg-rychlewski we don't have it set, so I guess it is :named?

[lucacorti]

@josevalim We'll try using that version.

@greg-rychlewski No, we are not setting any prepared configuration.

[greg-rychlewski]

@pierot @lucacorti Thanks and sorry a couple other questions:

are you using Ecto with MyXQL adapter or MyXQL directly?
- if Ecto are you issuing them with Repo.all/Repo.one, etc or with Ecto.Adapters.SQL.query?
- if MyXQL how are you calling the queries

[lucacorti]

@greg-rychlewski We are mostly using myxql via the Ecto adapter with Repo.all/one

[pierot]

@greg-rychlewski Only using Ecto with MyXQL adapter and Repo.all.

[lucacorti]

@josevalim looks like it's working with 1014f1c:

> SHOW GLOBAL STATUS LIKE '%prepared_stmt_count%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Prepared_stmt_count | 0     |
+---------------------+-------+
1 row in set
Time: 0.113s

[lucacorti]

No, sorry, I was on the wrong node, still having problems with prepared statements growing indefinitely even with that version.

[josevalim]

@lucacorti @pierot can you please try master? I have reverted both commits that could have caused this.

[greg-rychlewski]

@josevalim I have an idea what the issue could be. But it's difficult for me to set up the environment needed right now.

In multi-node setups prepared statements with the same name can be saved with different refs. If it was prepared on one node then executed on another it will be re-prepared and the next time it's executed on the original node, the refs won't match and it will be re-prepared again.

Before the cache change commit, executions would check for the name and not the ref. So each node would only be able to re-prepare an existing statement once. If it went back to checking for name instead of ref I believe this issue should disappear.

[lucacorti]

@josevalim I'm running latest master in production, seems to behave like 0.5.2.

[josevalim]

Thank you! v0.6.1 is out!