wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problems with jdbc and tomcat Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problems with jdbc and tomcat" Watch "Problems with jdbc and tomcat" New topic
Author

Problems with jdbc and tomcat

Alister Pate
Ranch Hand

Joined: Sep 13, 2002
Posts: 34
Hi Guys
Not sure whether this should be here or in the tomcat forum. I'm sure someone will move it if it is in the wrong place.

My problem is that I am trying to import a large number of rows (10,000) into a MySql database. If I import say 1000 it seems to work ok. But with 10,000 I get connection errors (sorry, i'm at home and can't lay my hands on a stacktrace but it says something like "connect is already in use"). I'm using straight jdbc - no fussing around with connection pooling or what have you at the moment.

I have a method getConnection which supplies all the connections, and it is synchronized, which I would have thought would solve this sort of problem.

My thought is that the jvm might be trying to be clever and open multiple threads to access the database simultaneously.

Any diagnostic thoughts? Any suggested solutions? Any better approaches that I could be using? Or should I be asking this in the Tomcat forum?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30795
    
157

Alister,
The large batch insert is probably exceeding some buffer size or timeout. It could be a mysql setting. Is it necessary to do so many updates in one shot. If you can do them in batches of 1000, you could avoid this problem.

I agree that it makes more sense here than in tomcat because it is a jdbc question.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Alister Pate
Ranch Hand

Joined: Sep 13, 2002
Posts: 34
Thanks for replying!

Ah. Sorry, should have mentioned - I batch them up in lots of 100. Perhaps I need to change the number of available connections in mysql. Hmm.

But what's odd is that out of the 10k, say 8k work ok. There is just a problem with some of them. And it isn't the first 8k which work - the errors happen in the last half of the upload, but some won't work, then some will work.
Alister Pate
Ranch Hand

Joined: Sep 13, 2002
Posts: 34
Just for the sake of interest:
Here is the error I'm getting:

Alister Pate
Ranch Hand

Joined: Sep 13, 2002
Posts: 34
Hurrah! I've fixed it.

For anyone else with similar problems, the solution is to use the addBatch() method on preparedstatement. Also, I set the size of my batch to 1000;
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30795
    
157

Alister,
Thanks for providing the solution!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problems with jdbc and tomcat