LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 11-23-2007, 06:16 AM   #1
kpachopoulos
Member
 
Registered: Feb 2004
Location: Athens, Greece
Distribution: Gentoo,FreeBSD, Debian
Posts: 705

Rep: Reputation: 30
MySQL+Java hangs


Hi,
i am writing a program, which processes data and inserts them to the MySQL 5 db. After 8580 or 8581 INSERTs it hangs; no exception/error from the program. MySQL stops accepting data. Somebody told me this might be a security issue; MySQL can accept a certain number of INSERTS/queries for a given period and then stops, because it suspects DoS. I tried to find a parameter to change that, but no success....
1/Does anybody suspect, that this is a another problem/limitation? Maybe a mysql-connector-5.0.4 bug?
2/if this is the security limitation above, can somebody point me to the right parameter?

Thanks
 
Old 11-23-2007, 08:59 AM   #2
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
I'm just guessing, so may be wrong.
Do I understand that you are inserting in a loop without commitng? Sounds like you have maxed out the journal buffer. It's a ring buffer and normally has a percentage where it warns and a rollback point where it will just rollback your requests to save over running the beginning of the start of your data. I have forgotten the correct name of this buffer, but I'm sure that you get the idea. (my knowledge is ex Ingres)
Now I can understand that it's a pain and a significant performance hit to commit each transacton, but a median path is probably called for. Within your loop you need to maintain a counter and pop in a commit every so often, say 100, 200 or 500 records.

Do let us know if the situation is at all as suggested. if so, it would suggest that it's not a Java problem, but just a basic dbms programming oversight because the amount of data you are shooting in one transaction has probably become a lot bigger than it used to be.
 
Old 11-23-2007, 11:47 AM   #3
kpachopoulos
Member
 
Registered: Feb 2004
Location: Athens, Greece
Distribution: Gentoo,FreeBSD, Debian
Posts: 705

Original Poster
Rep: Reputation: 30
Thanks PAix, i've now tried with batched execution -10 rows each time- and it fails again in the same way. In the db table a COUNT shows 5208 rows. In my Java program, i can see however, that 5291 have been processed. More or less as before.
I didn't commit in my previous solution explicitly however, because "autocommit" was ON for the Connection. Now i have switched "autocommit" OFF and explicitly commit for every 10 batched rows. Here's my code:
Code:
....
            //get the INSERT statement and update the DB; just returns an INSERT statement
            String sStmt=SQLUtils.createInsertStatement(tableInsert_);
            logger_.debug(mailParser_.getCurrentPath()+": "+ sStmt);
            
            try 
            {                 
                logger_.debug("adding batch statement...");
                insStmt_.addBatch(sStmt); //add batch command
                
                //if we have reached the maximum number of batched commands
                if (iCurrentBatchCommands_==iBatchCommands_)
                {
                    logger_.debug("Will now execute batched command set");
                    int[] warns=insStmt_.executeBatch();
                    
                    logger_.debug("Finished batch execution with warnings "+Arrays.toString(warns));
                    insStmt_.clearBatch();
                    con_.commit();
                    insStmt_.clearWarnings();
                    iCurrentBatchCommands_=0;
                }
                else
                {
                    iCurrentBatchCommands_++;
                }
            }//try 
            catch (SQLException ex) 
            {
                logger_.warn("SQLException with state: "+ex.getSQLState()+" and error code: "+ ex.getErrorCode());
                
                //if this is an error for trying to create a duplicate entry in the table for 
                //the message-id, handle accordingly; this is an expected exception; see note (1) 
                //on top of the class
                if ((ex.getSQLState().equals("23000")) && (ex.getErrorCode()==1062))
                {
                    logger_.warn(MyClassUtils.getStackTrace(ex)+"\nnot exiting...");
                }
                //if this error was thrown for another unexpected reason, log it and exit
                else
                {
                    logger_.fatal(mailParser_.getCurrentPath()+": "+ sStmt+"\n"+MyClassUtils.getStackTraceTabbed(ex)+"\nexiting...");                
                    System.exit(-1);
                }
            }//catch
...
Any ideas?
 
Old 11-23-2007, 04:04 PM   #4
Alien_Hominid
Senior Member
 
Registered: Oct 2005
Location: Lithuania
Distribution: Hybrid
Posts: 2,247

Rep: Reputation: 53
Try manual debugging. Write 5209 lines and check if line 5209 is actually written or not.
 
Old 11-23-2007, 04:53 PM   #5
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
Hi Nocturna_gr,
Short answer - NO! I am of course hoping that someone with java knowledge is going to come along to help us both out. In the interim, please read this and have a think.

As I understand it, you are inserting into an existing table that already contains data?
There are no constraints on the receiving table that are getting in the way?
What happens if you create an empty scratch table (don't tell mysql its temporary though) from the structure of the target table. Can you change the code to populate this new table, and is the behaviour the same when you run your code to populate this empty table? At least we will be 100% sure that we aren't being pranged by duplicate key - the data that you are sending has unique keys?
I'm concerned about the difference in the number of records that you say you have processed 5291 and the number that have arrived 5208.
Does the code set up a database connection prior to the insert, or are separate connections made dynamically, in which case is it available open files, connections that are perhaps being exceeded? I seem to think that the default value there is around 8000 so am currently at a bit of a loss. Just a concern that unwittingly lots of connections are being opened and not closed, although I don't suppose that this is the case.
Another strategy is to put a wait state in every 200 inserts, just in case it is the dbms being overwhelmed although I can't see how that should be.
My favoured strategy for bulk inserts from outside the system is to use a scratch table. When it's populated ok then the data is inside the system and can be either checked out prior to moving into the main target table. Just call me paranoid, not such a bad thing.

If someone else comes along with something positive then feel free to ignore my ramblings, but it's probably worth considering otherwise. Sorry I haven't a MySQL database up that I can run anything on just at the moment.
 
Old 11-23-2007, 07:39 PM   #6
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
I don't know what sort of dbms user you are or your privileges, but if you are the DBA then after connecting to the dbms specifying the database to use, try this
Code:
SELECT host, user, max_questions, max_updates, max_connections, max_user_connections
FROM user;
The fields max_updates, max_connections and max_user_connections might possibly shed some light.

Ho ho, look HERE I think the above is probablly us on the right track. I imagine that there is possibly some hourly limit set to protect the database resources. If you can't do it yourself get the DBA to give you the appropriate details.

If any of the values are non-zero then it might well be the cause of the problem that you are experiencing.
Do let us know how you get on please.

Last edited by PAix; 11-23-2007 at 07:45 PM.
 
Old 11-25-2007, 04:30 PM   #7
kpachopoulos
Member
 
Registered: Feb 2004
Location: Athens, Greece
Distribution: Gentoo,FreeBSD, Debian
Posts: 705

Original Poster
Rep: Reputation: 30
It is a primary key problem... Thanks for the help
 
Old 11-26-2007, 03:34 AM   #8
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
Thank you for that, Nocturna-gr, the dreaded "Duplicate Key on Insert", always the first thing to suspect when inserting bulk data from a source external to the database. One of the reasons for bulk inserting into a temporary structure as possibly a heap structure in the first instance; at least you know that the original data is free of duplicates or not.

So, for the benefit of others, how did you finally detect the presence of a duplicate key, or was it that the Primary key is now slightly more encompassing ( order_number changed to order_number, Order_line ) for example and what did you actually have to do to rectify the problem. A blow by blow account would be very useful to othes as Duplicate key on inserts while bulk inserting is not something that is particularly rare and is very much a pit fall for the unwary. In your case, the fact that Java or another language was involved merely masked where the problem was occurring.

Good luck with your project and please do consider sharing the experience for the benefit of others. Best Regards,
 
Old 11-26-2007, 09:51 AM   #9
kpachopoulos
Member
 
Registered: Feb 2004
Location: Athens, Greece
Distribution: Gentoo,FreeBSD, Debian
Posts: 705

Original Poster
Rep: Reputation: 30
I'm still working on it (Arghh!). It isn't just a duplicate key thing involved; this didn't cause the problem -maybe not on its own. I was trying to read some mails containing attachments (mostly pics) using buffered reader; the reader searches for newlines in a buffer; doesn't find them; extends the buffer and so on... This is the (or one of the) place where the problem is caused.
I will possibly open another thread for the issue
 
Old 11-26-2007, 10:26 AM   #10
PAix
Member
 
Registered: Jul 2007
Location: United Kingdom, W Mids
Distribution: SUSE 11.0 as of Nov 2008
Posts: 195

Rep: Reputation: 40
Thanks very much for coming back so quickly with the comments. It's realy good to get a feel for these things and very helpful for others that might experience similar problems.
Good luck with the project and I look forward to reading more in your new thread.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Java hangs mitsulas SUSE / openSUSE 1 04-23-2009 04:59 AM
Mysql and mysql java connector, connection refused arubin Slackware 3 03-29-2008 01:41 AM
mySQL 4.0.22 hangs rcs1000 Linux - Software 1 12-19-2004 01:32 PM
Mysql hangs with no response Emist Linux - Software 4 12-06-2004 03:39 PM
java installed, but hangs king_nothingzzz Linux - Software 3 02-18-2004 12:45 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 03:43 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration