• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problems with jdbc and tomcat

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Alister Pate
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just for the sake of interest:
Here is the error I'm getting:

 
Alister Pate
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Alister,
Thanks for providing the solution!
 
reply
    Bookmark Topic Watch Topic
  • New Topic