Professional Web Applications Themes

jdbc mysql timeout error - prepared statements? - MySQL

I have a mysql table with 9M records which I am reading row by row though j/connection/jdbc.. The program stops around 100,000 records with the following Exception(s): com.mysql.jdbc.Communicationception: Communications link failure due to underlying exception: Last packet sent to the server was 2369890 ms ago. From a little research it seemed that this was a problem with buffered prepared statements creating too much idle time on the server connection and the connection timing out, so I inserted the option 'useServerPrepStmts=false' in the connection string to process it one row at a time from a suggestion on mysql website - but ...

  1. #1

    Default jdbc mysql timeout error - prepared statements?

    I have a mysql table with 9M records which I am reading row by row though
    j/connection/jdbc..

    The program stops around 100,000 records with the following Exception(s):

    com.mysql.jdbc.Communicationception: Communications link failure due to
    underlying exception:
    Last packet sent to the server was 2369890 ms ago.

    From a little research it seemed that this was a problem with buffered
    prepared statements creating too much idle time on the server connection and
    the connection timing out, so I inserted the option
    'useServerPrepStmts=false' in the connection string to process it one row at
    a time from a suggestion on mysql website - but this did not fix or change
    the problem in any way.

    Any ideas? I'm kinda new to Java and never had this issue with Perl/DBI with
    the same table...


    seth Guest

  2. #2

    Default Re: jdbc mysql timeout error - prepared statements?

    seth brundle wrote: 

    Your application may be exceeding the wait_timeout server setting, as
    described in the manual:

    http://dev.mysql.com/doc/refman/5.0/en/instance-manager-command-options.html

    If that's the case, then useServerPrepStmts=false isn't going to help
    you, but you *can* set the wait_timeout server variable to a larger
    value for all clients in the server config file, or on a per-connection
    basis by executing this command on your connection:

    set session wait_timeout = <value>

    where <value> is the desired timeout value in seconds. I use this
    method in my code:

    public void setWaitTimeout(Connection conn, int timeout)
    throws SQLException {
    String sql = "set session wait_timeout = " + timeout;
    Statement stmt = conn.createStatement();
    stmt.execute(sql);
    stmt.close();
    }

    However, I'm tempted to ask whether you *really* need to retrieve all 9
    million rows? Surely there must be a better way to aproach your problem?

    David Harper
    Cambridge, England
    David Guest

  3. #3

    Default Re: jdbc mysql timeout error - prepared statements?

    > Your application may be exceeding the wait_timeout server setting, as 

    The setting is 8 hours, and mine is still set to that default, and the
    program
    stops after only 42 minutes or ~100,000 rows, so I dont think thats the
    case.
     

    Yes, it is going to be a program which runs regularly which transfers the
    contents of a backup
    MySQL database to a Lucene index, so yeah, the entire data set needs to be
    read.

    Whats really puzzling me is that I have been using Perl DBI on this table
    for years in production and never ran into this issue.

    I also have been seeing a recent surge in forum posts on the MySQL forums
    for jdb/jconnection people with the same error
    with no solution which has worked for me.

    I realize I might be able to get around this problem by hacking the query
    into LIMIT statements etc but I would much rather
    solve the problem then work around it. This is not expected MySQL behavior
    so either I am doing something wrong or a
    recent update to MySQL/JDBC/jconnect/jdk/jre has a bug.


    UsenetBinaries.com Guest

  4. #4

    Default Re: jdbc mysql timeout error - prepared statements?

     

    This may be your client JVM getting progressively slower, perhaps
    due to memory limitations, and having to scrabble for free memory
    as you process your result set. Try:
    1 - Starting the JVM with arguments to give it more memory.
    2 - Make sure you get forward-only, non-scrollable result sets,
    and close every JDBC object ASAP.
    3 - Try a plain statement for the query, not a prepared one. Unless
    you're going to rerun the same prepared statement a lot of times,
    there may be no benefit and some loss in using them.

    Joe Weinstein at BEA Systems

    joeNOSPAM@BEA.com Guest

  5. #5

    Default Re: jdbc mysql timeout error - prepared statements?

    UsenetBinaries.com wrote:
    [SNIP] 
    >
    > Yes, it is going to be a program which runs regularly which transfers
    > the contents of a backup
    > MySQL database to a Lucene index, so yeah, the entire data set needs to
    > be read.[/ref]

    According to the Lucene web site (http://lucene.apache.org/java/docs/),

    "Apache Lucene is a high-performance, full-featured text search engine
    library written entirely in Java. It is a technology suitable for nearly
    any application that requires full-text search, especially cross-platform."

    MySQL already provides full-text search so I can't help but think that
    you may not be making optimal use of the software components at hand.
    Given that you need to store your text in a database, why not make use
    of the full-text search functionality provided by MySQL?

    David Harper
    Cambridge, England
    David Guest

  6. #6

    Default Re: jdbc mysql timeout error - prepared statements?

    > "Apache Lucene is a high-performance, full-featured text search engine 

    I've been using MySQL fulltext for user web search on this dataset for about
    4 years, but the requirements and dataset have outgrown MySQL's limited
    search capabilties.

    seth Guest

Similar Threads

  1. Prepared Statements within stored procedures
    By martinacevedo@gmail.com in forum MySQL
    Replies: 3
    Last Post: October 19th, 01:33 PM
  2. Replies: 2
    Last Post: October 18th, 08:12 PM
  3. Prepared statements in ASP
    By No one in forum ASP Components
    Replies: 8
    Last Post: June 6th, 11:48 AM
  4. Replies: 0
    Last Post: August 10th, 04:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139