package com.glide.db;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Demonstrates MySQL bug
 * 
 *   Quick Description: If in streaming mode and something issues a KILL QUERY on your statement, invoking ResultSet.close will hang on socketRead
 * 
 * - Parent: Start a query that takes a while
 * - Parent: Make sure the query is in streaming mode
 * - Parent: Iterate the result set
 * - Parent: Spawn a child thread
 * - Child:  Sleep a little then kill the query the parent is running
 * - Parent: rs.next() throws Exception "Query execution was interrupted"
 * - Parent: Issues rs.close()
 * - Parent: Now hung on socketRead from rs.close()
 *
 */
public class MySQLBugTest {
	private static final String DEFAULT_MYSQL_HOST = "127.0.0.1";
	private static final String DEFAULT_MYSQL_USER = "root";
	private static final String DEFAULT_MYSQL_PASS = "";
	private static final String DEFAULT_MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	
	private String fURL;
	private String fTableName;
	private String fUserName;
	private String fPassword;
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		new MySQLBugTest("jdbc:mysql://" + DEFAULT_MYSQL_HOST + "/information_schema", "character_sets", DEFAULT_MYSQL_USER, DEFAULT_MYSQL_PASS).read(10);
	}
	
	public MySQLBugTest(String url, String tableName, String userName, String password) {
		fURL = url;
		fTableName = tableName;
		fUserName = userName;
		fPassword = password;
	}
	
	public void read() {
		read(0);
	}
	
	public void read(long limit) {
		try {
			Thread.currentThread().setName("bug.test.main");
			
			// Register the JDBC driver for MySQL.
			Class.forName(DEFAULT_MYSQL_DRIVER);
			
			// show where the driver is loaded from
			URL myClassURL = com.mysql.jdbc.Driver.class.getProtectionDomain().getCodeSource().getLocation();
			log("Driver loaded from: " + myClassURL);

			Connection con = newConnection();
			Statement stmt = newStatement(con);

			int myPid = getConnectionPid(stmt);
			
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT sleep(5) FROM " + fTableName);
			
			if (limit > 0)
				sb.append(" LIMIT " + limit);

			log("My pid is: " + myPid);
			log("Select statement: " + sb.toString());
			
			QueryKilla killa = new QueryKilla(myPid);
			killa.start();
			
			try {
				ResultSet rs = stmt.executeQuery(sb.toString());
				dump(rs);
				rs.close();
			} catch(SQLException se) {
				log("Got SQLException: state: " + se.getSQLState() + "        msg:" + se.getMessage());
			}
			
			log("Closing statement");
			stmt.close();
			log("Closing connection");
			con.close();
		} catch (Exception e) {
			log("Unable to read test: " + e.getMessage());
		}
	}
	
	public Connection newConnection() {
		Connection con = null;
		
		try {
			con = DriverManager.getConnection(fURL, fUserName, fPassword);
	
			// Display URL and connection information
			log("URL: " + fURL);
			log("Connection: " + con);
	
			con.setAutoCommit(true);
		} catch (Exception e) {
			log("Unable to get new connection: " + e.getMessage());
		}
		
		return con;
	}
	
	public Statement newStatement(Connection con) {
		// Get a Statement object
		try {
			Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(Integer.MIN_VALUE); // turn on streaming mode
			return stmt;
		} catch(Exception e) {
			log("Unable to get statement: " + e.getMessage());
		}
		
		return null;
	}
	
	public int getConnectionPid(Statement stmt) {
		int pid = -1;
		
    	try {
			ResultSet rs = stmt.executeQuery("SELECT CONNECTION_ID()");
			
			if (rs.next()) {
				// "Table" column is 1, "Create Table" column is 2
				pid = Integer.parseInt(rs.getString(1), 10);
			}
			
			rs.close();
		} catch (SQLException e) {
			// nothing
		}
    	
    	return pid;
	}

	private void log(String msg) {
		System.out.println(Thread.currentThread().getName() + ": " + msg);
 	}
	
	private void dump(ResultSet rs) throws SQLException {
		int rows = 0;

		int columnCount = rs.getMetaData().getColumnCount();
		log("fetched result set, " + columnCount + " columns");
		
		long totalDataCount = 0;
		long netReadTime = 0;
		boolean shouldLoop = true;
		
		while(shouldLoop) {
			shouldLoop = rs.next();
			
			if (!shouldLoop)
				break;

			rows++;
			long rowSize = getRowSize(rs, columnCount);
			totalDataCount += rowSize;
		}

		log(fTableName + " total rows " + rows + ", data " + totalDataCount);
	}
	
	private long getRowSize(ResultSet rs, int columnCount) {
		long size = 0;
		
		for(int i = 0; i < columnCount; i++) {
			try {
				String s = rs.getString(i + 1);
				
				if (s != null)
					size += s.length();
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
		}
		
		return size;
	}
	
	
	class QueryKilla extends Thread {
		int fThePid;
		
		QueryKilla(int thePid) {
			fThePid = thePid;
			setName("thread.killa");
		}
		
		public void run() {
			log("Running thread...");
			
			try {
				log("Sleeping...");
				Thread.sleep(10000);
			} catch (Exception e) {
				
			}
			
			Connection con = newConnection();
			Statement stmt = newStatement(con);

			try {
				log("Running: KILL QUERY " + fThePid);
				stmt.execute("KILL QUERY " + fThePid + ";");
				stmt.close();
				con.close();
				Thread.sleep(5000);
				log("parent thread should be hung now!!!");
			} catch(Exception e) {
				log("Unable to kill query: " + e.getMessage());
			}
			
			
			log("Bye bye");
		}
	}
}