Proxysql crash

[KlausOvergaard]

We upgraded from 1.4.3 to 1.4.4, but this caused proxysql to crash every 5 min or so with this in the logs:

Error: signal 11:
proxysql(_Z13crash_handleri+0x1a)[0x44baba]
/lib64/libc.so.6(+0x35270)[0x7fb301c7b270]
proxysql(_ZN22MySQL_STMT_Manager_v1416ref_count_clientEmib+0x227)[0x4c53f7]
proxysql(_ZN21MySQL_STMTs_local_v1427generate_new_client_stmt_idEm+0x166)[0x4c5776]
proxysql(_ZN13MySQL_Session7handlerEv+0x25de)[0x488f5e]
proxysql(_ZN12MySQL_Thread20process_all_sessionsEv+0x29c)[0x47030c]
proxysql(_ZN12MySQL_Thread3runEv+0xd66)[0x478f06]
proxysql(_Z24mysql_worker_thread_funcPv+0x6c)[0x449afc]
/lib64/libpthread.so.0(+0x7e25)[0x7fb303525e25]
/lib64/libc.so.6(clone+0x6d)[0x7fb301d3e34d]
2017-12-27 08:08:01 main.cpp:913:ProxySQL_daemonize_phase3(): [ERROR] ProxySQL crashed. Restarting!
2017-12-27 08:08:01 [INFO] Angel process started ProxySQL process 23839
Standard ProxySQL Cluster rev. 0.1.0702 -- ProxySQL_Cluster.cpp -- Wed Dec 20 12:28:37 2017
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Dec 20 12:28:37 2017
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Dec 20 12:28:37 2017
Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Wed Dec 20 12:28:37 2017
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Dec 20 12:28:37 2017
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Dec 20 12:28:37 2017

We have therefore downgraded to 1.4.3 again.

[renecannao]

Do you have a core dump?

[KlausOvergaard]

Do you have a mail that I can sent a core dump download link to?

[renecannao]

rene.cannao at gmail

Thanks

[renecannao]

Hi Klaus,

Thank you again for providing the core dump, really useful: I think I found the bug!
I will create a unit test to make sure I can easily reproduce the bug, and that the fix really works.
It will probably take me 2 more days.

The bug is indeed a proxysql bug, but it seems triggered by some application misused of prepared statements.

Quoting wikipedia:

In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution.

In your case, it seems your application isn't using prepared statements as "templates", but all the parameters are part of the prepare. That also mean that the number of parameters is zero.
This leads to th fact that for ProxySQL every prepared statement is an unique prepared statement.
That is what triggered issue #1198 .
Issue #1198 was indeed fixed in 1.4.4 , but at high concurrency it leads to crash: #1307

Quoting again wikipedia:

On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server.

Please keep in mind that your application use of prepared statements is introducing some performance penalty: unless you use prepared statements as "templates" , I would recommend to not use them.

[KlausOvergaard]

Hi Rene Sounds great that you found the issue ! Your right about the prepared statements. We use prepared statements for security reasons and not for performance reasons. We choosed to use prepared statements everywehere for the ease of it and to avoid making mistakes that could lead to security issues like sql injection. So yes, 90% of our SQL statements are "real" prepared statements with parameters while approx. 10% does not contain parameters. Best, Klaus Overgaard 2017-12-30 0:27 GMT+01:00 René Cannaò <[email protected]>:

…

Hi Klaus, Thank you again for providing the core dump, really useful: I think I found the bug! I will create a unit test to make sure I can easily reproduce the bug, and that the fix really works. It will probably take me 2 more days. The bug is indeed a proxysql bug, but it seems triggered by some application misused of prepared statements. Quoting wikipedia <https://en.wikipedia.org/wiki/Prepared_statement>: In database management systems, a prepared statement or parameterized statement is a feature used to execute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into which certain constant values are substituted during each execution. In your case, it seems your application isn't using prepared statements as "templates", but all the parameters are part of the prepare. That also mean that the number of parameters is zero. This leads to th fact that for ProxySQL every prepared statement is an *unique* prepared statement. That is what triggered issue #1198 <#1198> . Issue #1198 <#1198> was indeed fixed in 1.4.4 , but at high concurrency it leads to crash: #1307 <#1307> Quoting again wikipedia: On the other hand, if a query is executed only once, server-side prepared statements can be slower because of the additional round-trip to the server. Please keep in mind that your application use of prepared statements is introducing some performance penalty: unless you use prepared statements as "templates" , I would recommend to not use them. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1307 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AfByLGLA48ieAg7Nh6GO4KcK-plGWPgAks5tFXVlgaJpZM4ROSH_> .

-- Bh. Klaus

[renecannao]

Thanks for the details.
The 10% not using parameters are creating a lot of pressure and inefficiency in prepared statements metadata cache. In fact, it is possible that nearly all the prepared statements metadata cached are of ps without parameters.
I will evaluate if it makes sense for ProxySQL to either automatically tune down the size of metadata cache, and/or try to aggressively purge prepared statements without parameters