Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2011
    Posts
    4

    Unanswered: Fetching verly large result set in mySql

    Hi, I'm using the streaming technique to fetch very large result set from mysql using Java JDBC connection.
    Code:
    objStatement = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    objStatement.setFetchSize(Integer.MIN_VALUE);
    I'm able to get the results successfully. My query returns 6*(10^10)+ rows and takes about and hour to fetch all results. My requirement is to cancel the query and clean close the db connection if the query execution takes more than 1 minute. I tried using the following approaches:
    1. Statement.setQueryTimeOut(60)
    2. connection property netTimeoutForStreamingResults=60
    but neither of them works. Mysql document claims that it supports both of these methods.

    Here is the code that i used:

    Code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    
    public class MySqlConnector {
    	private static final int nTimeOut = 60;
    	
    	private static Connection getConnection() throws SQLException, ClassNotFoundException {
    		System.out.println("Getting Connection...");
    		String driver = "com.mysql.jdbc.Driver";
    		Class.forName(driver);
    		String dbURL = "jdbc:mysql://localhost:3306/mydatabase";
    		String dbUsername = "root";
    		String dbPassword = "password";
    		
    		Properties connProperties = new Properties();
    		connProperties.put("user", dbUsername);
    		connProperties.put("password", dbPassword);
    		connProperties.put("netTimeoutForStreamingResults", String.valueOf(nTimeOut));
    
    		Connection conn = DriverManager.getConnection(dbURL, connProperties);
    		System.out.println("Connected!");
    		return conn;
    	}
    	
    	public static void main(String[] args) throws SQLException, ClassNotFoundException {
    		
    		
    		Connection con = getConnection();
    		
    		String strQuery = "SELECT vwdynmiscsecprices.compname as vwdynmiscsecprices_compname, vwdynmiscsecprices.ticker as vwdynmiscsecprices_ticker, vwdynmiscsecprices.exchange as vwdynmiscsecprices_exchange, vwdynmiscsecprices.currency as vwdynmiscsecprices_currency, vwdynmiscsecprices.pricedate as vwdynmiscsecprices_pricedate, vwdynmiscsecprices.price as vwdynmiscsecprices_price, vwdynmiscfxrates.currency as vwdynmiscfxrates_currency, vwdynmiscfxrates.fxdate as vwdynmiscfxrates_fxdate, vwdynmiscfxrates.fxrate as vwdynmiscfxrates_fxrate FROM  vwdynmiscsecprices   JOIN  vwdynmiscfxrates";
    		
    		Statement objStatement=null;
    		ResultSet objResultSet=null;
    		try {
    			objStatement = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    			objStatement.setFetchSize(Integer.MIN_VALUE);
    			objStatement.setQueryTimeout(nTimeOut);
    			
    			objResultSet = objStatement.executeQuery(strQuery);
    			long count=0;
    			while(objResultSet.next()) {
    				count++;
    				if(count%1000000==0) {
    					System.out.println(count);
    				}
    			}
    			System.out.println(count);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		} finally {
    			try {
    				objResultSet.close();
    				objStatement.cancel();
    				objStatement.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			} finally {
    				con.close();
    			}
    		}
    	}
    }
    Does mysql provides these facility? I'm using MySQL Community Server 5.1.51 and Connector/J version 5.1.15.
    If yes, why doesn't it work? Do i need to change some configuration in mysql server?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    reading up some other material on this and it appears that setQueryTimeout appears to be an issue. It should be interpreted as a minimum period before a signal is sent to the database. Furthermore, if the connection is sleeping it will remain in this state until it awakes to handle the timeout (this is a known issue with Oracle databases).

    On MySQL documentation I also found the following:

    Failures to cancel the statement for setQueryTimeout() may manifest themselves as RuntimeException rather than failing silently, as there is currently no way to unblock the thread that is executing the query being cancelled due to timeout expiration and have it throw the exception instead.
    Which also indicates that there is no way of unblocking the thread executing the query. It will happen but most likely at the end when the query has completed its work.

    What does the stacktrace indicate?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by choudharypranay View Post
    My query returns 6*(10^10)+ rows and takes about and hour to fetch all results.
    not that i am disputing what you claim, but OMFG, sixty billion is one heck of a lot of rows

    may i ask some questions just out of curiosity?

    what kind of data is this, what is the datatype of the primary key, and what does your app do with sixty billion rows? print them out 25 per page or something?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2011
    Posts
    4
    Quote Originally Posted by it-iss.com View Post
    What does the stacktrace indicate?
    There is no stack trace. Its equivalent to running the program without Statement.setQueryTimeout or without using the property netTimeoutForStreamingResults. these two does not make any effect and query runs till it has finished processing (in my case it can be 6 hours). Many such requests can bring down the mysql server itself. I'm trying a way to prevent this.

  5. #5
    Join Date
    Feb 2011
    Posts
    4
    Quote Originally Posted by r937 View Post
    what kind of data is this, what is the datatype of the primary key, and what does your app do with sixty billion rows? print them out 25 per page or something?
    in one of the modules we expose our database schema to the user where user where the user can select several tables, give filter conditions and get data in form of reports. Generally the user will know what he's doing. If the user selects two related tables, an appropriate join is done at the backed. but if he selects two independent tables, a cross join is performed, as in this case. And these are two tables with max number of rows in our database. Hence the result set has got no meaning at all. But i cant restrict the user from doing these mistakes(its a requirement). Some how i need to handle at the backend.

  6. #6
    Join Date
    Feb 2011
    Posts
    4
    is it a bug in mysql-server / mysql jdbc driver?

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r937 View Post
    not that i am disputing what you claim, but OMFG, sixty billion is one heck of a lot of rows
    Quote Originally Posted by choudharypranay
    i cant restrict the user from doing these mistakes(its a requirement) ... is it a bug in mysql-server / mysql jdbc driver?
    It looks to me like the bug is in your requirements

Tags for this Thread

Posting Permissions

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