Skip to Main Content
Forums

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Please Help!!! JDBC Connection Pooling With MYSQL

843854May 7 2003 — edited Apr 7 2004
I am having problems with using the com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource. I am using Apache Tomcat 4.1.24 with Connector/J 3.0.7 and 3.0.6 and Mysql 4.0.12. I am using the factory from http://www.java-internals.com/code/resourcefactory/readme.html. I also have a servlet ties the connection pool to JNDI and get the exact same results. My server.xml has the following code in the <GlobalNamingResources> section:

<Resource name="jdbc/DB" auth="Container" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource"/>
<ResourceParams name="jdbc/DB">
<parameter>
<name>factory</name>
<value>com.java_internals.resourcefactory.MyResourceFactory</value>
</parameter>
<parameter>
<name>serverName</name>
<value>localhost</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>DB</value>
</parameter>
<parameter>
<name>port</name>
<value>3306</value>
</parameter>
</ResourceParams>

Then in the <Contex> Section of server.xml for the Web App I Have

<ResourceLink name="jdbc/DB" global="jdbc/DB" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" />

In my program I access the connection with the following code:

Context ctx = new InitialContext();
MysqlConnectionPoolDataSource ds = (MysqlConnectionPoolDataSource) ctx.lookup("java:comp/env/jdbc/DB");
Connection con = ds.getPooledConnection(username, password).getConnection();

This gets a connection and works just fine but the connections pile up in mysql until it reachs 55 and then it starts over again. I am 100% sure that I am closing all Statements, ResultSets and Connections but the connections at the db hang arround. The following is the result of refreshing a page a bunch of times. The connection are not reused in the pool.

-------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
-------------------------------------------------------------------------------------+
| 554 | test | localhost:35261 | ebayListings | Sleep | 1475 | | |
| 940 | ebayRW | localhost:35949 | ebayListings | Sleep | 18 | | |
| 941 | ebayRW | localhost:35950 | ebayListings | Sleep | 18 | | |
| 942 | ebayRW | localhost:35951 | ebayListings | Sleep | 18 | | |
| 943 | ebayRW | localhost:35952 | ebayListings | Sleep | 18 | | |
| 956 | ebayRW | localhost:35965 | ebayListings | Sleep | 17 | | |
| 957 | ebayRW | localhost:35966 | ebayListings | Sleep | 17 | | |
| 958 | ebayRW | localhost:35967 | ebayListings | Sleep | 17 | | |
| 959 | ebayRW | localhost:35968 | ebayListings | Sleep | 17 | | |
| 960 | ebayRW | localhost:35969 | ebayListings | Sleep | 17 | | |
| 961 | ebayRW | localhost:35970 | ebayListings | Sleep | 17 | | |
| 962 | ebayRW | localhost:35971 | ebayListings | Sleep | 17 | | |
| 963 | ebayRW | localhost:35972 | ebayListings | Sleep | 17 | | |
| 964 | ebayRW | localhost:35973 | ebayListings | Sleep | 17 | | |
| 965 | ebayRW | localhost:35974 | ebayListings | Sleep | 17 | | |
| 966 | ebayRW | localhost:35975 | ebayListings | Sleep | 16 | | |
| 967 | ebayRW | localhost:35976 | ebayListings | Sleep | 16 | | |
| 968 | ebayRW | localhost:35977 | ebayListings | Sleep | 16 | | |
| 969 | ebayRW | localhost:35978 | ebayListings | Sleep | 16 | | |
| 983 | ebayRW | localhost:35993 | ebayListings | Sleep | 6 | | |
| 984 | ebayRW | localhost:35994 | ebayListings | Sleep | 6 | | |
| 985 | ebayRW | localhost:35995 | ebayListings | Sleep | 6 | | |
| 986 | ebayRW | localhost:35996 | ebayListings | Sleep | 6 | | |
| 987 | ebayRW | localhost:35997 | ebayListings | Sleep | 6 | | |
| 988 | ebayRW | localhost:35998 | ebayListings | Sleep | 6 | | |
| 989 | ebayRW | localhost:35999 | ebayListings | Sleep | 6 | | |
| 990 | ebayRW | localhost:36000 | ebayListings | Sleep | 6 | | |
| 991 | ebayRW | localhost:36001 | ebayListings | Sleep | 6 | | |
| 992 | ebayRW | localhost:36002 | ebayListings | Sleep | 6 | | |
| 993 | ebayRW | localhost:36003 | ebayListings | Sleep | 5 | | |
| 994 | ebayRW | localhost:36004 | ebayListings | Sleep | 5 | | |
| 995 | ebayRW | localhost:36005 | ebayListings | Sleep | 5 | | |
| 996 | ebayRW | localhost:36006 | ebayListings | Sleep | 5 | | |
| 1003 | ebayRW | localhost:36014 | ebayListings | Sleep | 5 | | |
| 1009 | ebayRW | localhost:36020 | ebayListings | Sleep | 4 | | |
| 1010 | ebayRW | localhost:36021 | ebayListings | Sleep | 4 | | |
| 1011 | ebayRW | localhost:36022 | ebayListings | Sleep | 4 | | |
| 1012 | ebayRW | localhost:36023 | ebayListings | Sleep | 4 | | |
| 1013 | ebayRW | localhost:36024 | ebayListings | Sleep | 4 | | |
| 1014 | ebayRW | localhost:36025 | ebayListings | Sleep | 4 | | |
| 1015 | ebayRW | localhost:36026 | ebayListings | Sleep | 4 | | |
| 1016 | ebayRW | localhost:36027 | ebayListings | Sleep | 4 | | |
| 1017 | ebayRW | localhost:36028 | ebayListings | Sleep | 4 | | |
| 1018 | ebayRW | localhost:36029 | ebayListings | Sleep | 4 | | |
| 1019 | root | localhost | | Query | 0 | | show processlist |
-------------------------------------------------------------------------------------+


After a while it will just terminates all connection and start piling them up again. Notice the higher ID numbers.

-------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
-------------------------------------------------------------------------------------+
| 554 | test | localhost:35261 | ebayListings | Sleep | 1523 | | |
| 1042 | ebayRW | localhost:36061 | ebayListings | Sleep | 7 | | |
| 1043 | ebayRW | localhost:36062 | ebayListings | Sleep | 7 | | |
| 1046 | ebayRW | localhost:36067 | ebayListings | Sleep | 6 | | |
| 1047 | ebayRW | localhost:36068 | ebayListings | Sleep | 6 | | |
| 1048 | ebayRW | localhost:36069 | ebayListings | Sleep | 6 | | |
| 1049 | ebayRW | localhost:36070 | ebayListings | Sleep | 6 | | |
| 1050 | ebayRW | localhost:36071 | ebayListings | Sleep | 6 | | |
| 1051 | ebayRW | localhost:36072 | ebayListings | Sleep | 6 | | |
| 1052 | ebayRW | localhost:36073 | ebayListings | Sleep | 6 | | |
| 1053 | ebayRW | localhost:36074 | ebayListings | Sleep | 6 | | |
| 1054 | root | localhost | | Query | 0 | | show processlist |
-------------------------------------------------------------------------------------+

I am in the process of building a new webapp and wanted to test out the DBCP instructions at http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html and noticed that it actually resuses the mysql connection ids. Here is the same jsp file refreshed but it gets its connection from the DBCP: The code in the application is:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/EbayDatabase2");
Connection con = ds.getConnection();

Here are the ID to Show What I am Talking About.

-----------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
-----------------------------------------------------------------------------------+
| 1059 | test | localhost:36155 | ebayListings | Sleep | 69 | | |
| 1060 | test | localhost:36157 | ebayListings | Sleep | 69 | | |
| 1061 | test | localhost:36160 | ebayListings | Sleep | 69 | | |
| 1062 | test | localhost:36168 | ebayListings | Sleep | 4 | | |
| 1065 | root | localhost | | Query | 0 | | show processlist |
-----------------------------------------------------------------------------------+

After refreshing about 100 Times

-----------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
-----------------------------------------------------------------------------------+
| 1059 | test | localhost:36155 | ebayListings | Sleep | 210 | | |
| 1060 | test | localhost:36157 | ebayListings | Sleep | 210 | | |
| 1061 | test | localhost:36160 | ebayListings | Sleep | 210 | | |
| 1062 | test | localhost:36168 | ebayListings | Sleep | 3 | | |
| 1066 | root | localhost | | Query | 0 | | show processlist |
-----------------------------------------------------------------------------------+


I do not understand why the MysqlConnectionPoolDataSource does not act the same as the DBCP. I would just start using DBCP since it is recommended by I need to have access to the .getLastInsertID() method of com.mysql.jdbc.PreparedStatement.

Thanks For Your Help
James Fredley
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 5 2004
Added on May 7 2003
#java-database-connectivity-jdbc
5 comments
329 views