[#CONJ-796] Com_stmt_close is not increasing

customer reported that they hit this error initially with mariadb-java-client-1.4.6.jar

Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.5.5-10.3.22-MariaDB-log]Can't create more than max_prepared_stmt_count statements (current value: 32768)

With some investigation and trial and recommendation from engineering, they have upgraded to mariadb-java-client-2.5.4.jar, but still hitting the same error.

Looking at Com_stmt_close, it's value is not even close to Com_stmt_prepare.

-initial report

Starting of the Day

perf_mon_20200307_002201.log:| Com_stmt_close | 0 |

perf_mon_20200307_002201.log-| Com_stmt_execute | 167909849 |

perf_mon_20200307_002201.log-| Com_stmt_fetch | 0 |

perf_mon_20200307_002201.log-| Com_stmt_prepare | 1614118 |

perf_mon_20200307_002201.log-| Com_stmt_reprepare | 37448 |

perf_mon_20200307_002201.log-| Com_stmt_reset | 0 |

perf_mon_20200307_002201.log-| Com_stmt_send_long_data | 0 |

End Of the Day

perf_mon_20200307_235601.log:| Com_stmt_close | 0 |

perf_mon_20200307_235601.log-| Com_stmt_execute | 182945817 |

perf_mon_20200307_235601.log-| Com_stmt_fetch | 0 |

perf_mon_20200307_235601.log-| Com_stmt_prepare | 1872925 |

perf_mon_20200307_235601.log-| Com_stmt_reprepare | 37448 |

perf_mon_20200307_235601.log-| Com_stmt_reset | 0 |

perf_mon_20200307_235601.log-| Com_stmt_send_long_data | 0 |

After upgrade

MariaDB [(none)]> show global status like 'Com_stmt%';

+-------------------------+---------+

| Variable_name | Value |

+-------------------------+---------+

| Com_stmt_close | 2 |

| Com_stmt_execute | 1419399 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 163326 |

| Com_stmt_reprepare | 171 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

+-------------------------+---------+

7 rows in set (0.024 sec)

This is EJB configuration:

<xa-datasource jndi-name="" pool-name="connpool" enabled="true" use-java-context="true" spy="true" use-ccm="true" statistics-enabled="true">

<xa-datasource-property name="URL">

</xa-datasource-property>

<xa-datasource-class>org.mariadb.jdbc.MariaDbDataSource</xa-datasource-class>

<driver>mariadb</driver>

<new-connection-sql>use test</new-connection-sql>

<transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>

<xa-pool>

<min-pool-size>100</min-pool-size>

<max-pool-size>400</max-pool-size>

<prefill>true</prefill>

<flush-strategy>IdleConnections</flush-strategy>

</xa-pool>

<security>

<user-name></user-name>

<password></password>

</security>

<validation>

<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>

<validate-on-match>true</validate-on-match>

<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLReplicationValidConnectionChecker"/>

<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

</validation>

<timeout>

<blocking-timeout-millis>60000</blocking-timeout-millis>

<idle-timeout-minutes>3</idle-timeout-minutes>

</timeout>

<statement>

<prepared-statement-cache-size>2000</prepared-statement-cache-size>

<share-prepared-statements>true</share-prepared-statements>

</statement>

</xa-datasource>

[CONJ-796] Com_stmt_close is not increasing Created: 2020-06-08 Updated: 2020-11-18 Resolved: 2020-11-18
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	question
Affects Version/s:	2.5.4
Fix Version/s:	N/A

[CONJ-796] Com_stmt_close is not increasing Created: 2020-06-08 Updated: 2020-11-18 Resolved: 2020-11-18