[CONJ-473] MariaDB Connector creates server prepared statements even when useServerPrepStmts=false Created: 2017-05-17  Updated: 2017-06-26  Resolved: 2017-06-26

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 1.6.0, 2.0.1
Fix Version/s: 2.0.3, 1.6.2

Type: Bug Priority: Minor
Reporter: Tomi Joki-Korpela Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None
Environment:

All operating systems.


 Description   

I've encountered an issue that prevents us from using the MariaDB Connector/J. When I use the MariaDB Connector it issues prepared statements on the server even though I've disabled those with useServerPrepStmts=false URL parameter. Here's the full list of URL parameters we use: cachePrepStmts=true&useServerPrepStmts=false&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048

The prepared statements issued on the server are problematic for us, since we are using thousands of connections to the database and thus easily exceed the maximum number of server prepared statements that are specified with max_prepared_stmt_count variable. So after running our application for a while, I see the following error:
Error preparing query: Can't create more than max_prepared_stmt_count statements (current value: 16382)

I've been testing with MariaDB Connector 1.6.0 and comparing the results using MySQL Connector 5.1.41.

Our application uses Apache Commons DbUtils 1.6 to access the database. The core class on DbUtils is QueryRunner, which you use to make queries to the database.

I've been tracking the source of the server prepared statements to following call path:
QueryRunner.query() calls PreparedStatement.getParameterMetaData(), which causes MariaDB Connector to issue a prepared statement on the server (Com_stmt_prepare variable goes up). This behaviour is different from MySQL Connector, where PreparedStatement.getParameterMetaData() does not issue a prepared statement on the server. So if I run the same code with disableMariaDbDriver=true URL parameter which forces the use of MySQL Connector, I observe no prepared statements on the server (Com_stmt_prepare variable value remains the same).

I'd like to use MariaDB Connector, as that's the recommended connector to use with Aurora RDS. However the use of server prepared statements don't allow me to take the MariaDB Connector into use.

 Comments   
Comment by Diego Dupin [ 2017-05-17 ]

Even with useServerPrepStmts set to false, there is some cases where driver will use PREPARE. Here is those case and reasons :

stored procedure :

What MySQL driver do (only using text protocol) :

  • SELECT name, type, comment FROM mysql.proc WHERE name LIKE '<storeprocedureName>' AND db <=> '<myDB>' ORDER BY name, type
  • USE `<myDB>`
  • SELECT DATABASE()
  • SHOW CREATE PROCEDURE `<myDB>`.`<storeprocedureName>`
  • SET @com_mysql_jdbc_outparam_y='3'
  • CALL <storeprocedureName>(2,@com_mysql_jdbc_outparam_y)
  • SELECT @com_mysql_jdbc_outparam_y

In MariaDB driver when executing a CallableStatement that is not a function, underlying, those query will ne executed :

  • PREPARE CALL <storeprocedureName>(?,?, ...) (if not in cache)
  • EXECUTE (send parameters)
  • CLOSE prepare (according to cache parameter)

That's a lot less exchanges, one if query is already in cache, 3 max.
Reason is that output parameters are send back as a results of execute when using binary protocol.

Metadata :

To have ResultSet metaDatas (i.e.: PrepareStatement.getMetaData() ), a "prepare" command will be executed, permitting to list parameters informations.

Solution :

if you have so many connections, then it would be best to not cache for PREPARE command.
This can be set by setting the cachePrepStmts option to false.
Your connection String will be like "jdbc:mysql://somehosts/db?cachePrepStmts=false&useServerPrepStmts=false".

That way, each time you execute a StoreProcedure for example, there will be underlying 3 queries executed : PREPARE + EXECUTE + CLOSE.
PREPARE will then not be stored in cached, and CLOSE won't be delayed.

That may take a little bit more time than your existing configuration, but at the same time, DB will then not used to many ressources to keep all prepare results in memory.

There is a task to avoid exactly this case (to permit having callableStatement output parameters without the need of this additional PREPARE) https://jira.mariadb.org/browse/MDEV-12400. Please add your vote on it to gave it priority for server.

Comment by Tomi Joki-Korpela [ 2017-05-17 ]

Thank you for your prompt reply and the explanation. I think the case I'm hitting is the metadata one.

Do you know why I'm not seeing the same behaviour with MySQL Connector? Do they handle the PrepareStatement.getMetaData() call somehow differently?

I'm a bit worried to turn cachePrepStmts off, as we have quite a lot of calls to the database and I'm wondering how much extra processing there will be if the cache is off.

Comment by Seonmi Anderson [ 2017-06-14 ]

Just FYI, I ran into 'Can't create more than max_prepared_stmt_count statements' sql exception after upgrading 2.0.2. I had to roll back to version 1.5.7 to avoid the exception and seems working well.

Comment by Diego Dupin [ 2017-06-15 ]

tojoko when useServerPrepStmts=false, MySQL driver thow an exception if using PrepareStatement.getParameterMetaData() result. Mariadb Will execute a PREPARE + CLOSE and send good results, but then if cachePrepStmts=true option is set, then it will be cached (CLOSE is then delayed).

seanderson PrepareStatement.getParameterMetaData() use prepare, and since cachePrepStmts default to true, prepare statement will stay in cache leading to
max_prepared_stmt_count error. When useServerPrepStmts is false, cachePrepStmts must be set to false accordingly. 2.0.3 will correct this behaviour. (but there is no reason 1.5.7 hasn't this problem).

Generated at Thu Feb 08 03:15:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.