package tests;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.concurrent.Callable;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

public class MySqlKillExecTest {

//	!!! put a correct JDBC URL here !!! 
	public final static String MYURL="jdbc:mysql://mysql_hostname/?user=myusername&password=mypassword";

//  !!! choose one 
//	public final static KillTestMode testMode = KillTestMode.KILL_WHILE_READING;
	public final static KillTestMode testMode = KillTestMode.KILL_WHILE_CLOSING;
//	public final static KillTestMode testMode = KillTestMode.KILL_WHILE_CLOSING_WITH_CLOBBER;

	enum KillTestMode {
		KILL_WHILE_READING, // Fails with mysql-connector-java-5.1.34-bin.jar 
							// passes with mysql-connector-java-5.1.35-bin.jar
							// passes with custom-build version ( 5.1.34 + Fix for Bug#75309 (20272931),
							// mysql connector/J driver in streaming mode will in the blocking state.
							// see https://github.com/mysql/mysql-connector-j/commit/3ee21505359e23168919f8bbda5ac36a265bee09

		KILL_WHILE_CLOSING, // Fails with mysql-connector-java-5.1.40-bin.jar 
							// see bug #77658

		KILL_WHILE_CLOSING_WITH_CLOBBER // Passes with mysql-connector-java-5.1.18-bin.jar and later 
										// (haven't checked it with older connectors)
										// looks like it can serve as a workaround for bug #77658 
	}

	MySqlKillExecTest(String testName) {
		this.testName = testName;
	}

	private Connection conn;
	private Statement stmt;
	private ResultSet rs;
	private String testName;

	public static void main(String args[]) {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			MySqlKillExecTest test = new MySqlKillExecTest("Test of Kill Query statement vs StreamingResultset: mode " + testMode);
			test.testKillquery(testMode);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	private void testKillquery (KillTestMode testMode) {
		final Properties props = new Properties();
		props.setProperty("socketTimeout", "15000");
		if (testMode==KillTestMode.KILL_WHILE_CLOSING_WITH_CLOBBER) {
			props.setProperty("clobberStreamingResults", "true");
		}
		System.out.println(this.testName);
		try {
			this.conn = DriverManager.getConnection(MYURL, props);
			this.conn.setCatalog("information_schema");
			this.stmt = this.conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			this.stmt.setFetchSize(Integer.MIN_VALUE); // turn on streaming mode

			this.rs = this.stmt.executeQuery("SELECT CONNECTION_ID()");
			this.rs.next();
			final String connectionId = this.rs.getString(1);
			this.rs.close();

			System.out.println("testBug_.main: PID is " + connectionId);

			ScheduledExecutorService interruptorExecService = Executors.newSingleThreadScheduledExecutor();
			interruptorExecService.schedule(new Callable<Boolean>() {

				public Boolean call() throws Exception {
					boolean res = false;
					Connection con2 = DriverManager.getConnection(MYURL, props);
					con2.setCatalog("information_schema");
					con2.setAutoCommit(true);

					Statement st2 = con2.createStatement();
					try {
						System.out.println("testBug.slave: Running KILL QUERY " + connectionId);
						st2.execute("KILL QUERY " + connectionId + ";");
						res = true;
					} finally {
						if (st2 != null) {
							st2.close();
						}
						if (con2 != null) {
							con2.close();
						}
					}

					System.out.println("testBug_.slave: interruptor thread done.");
					return res;
				}
			}, 1500, TimeUnit.MILLISECONDS);

			try {

				String queryingSql = "select sleep(1) from information_schema.plugins";
				System.out.println("Starting query ["+queryingSql+"]");
				try {
					this.rs = this.stmt.executeQuery(queryingSql);
					if (testMode==KillTestMode.KILL_WHILE_CLOSING || testMode==KillTestMode.KILL_WHILE_CLOSING_WITH_CLOBBER) {
					//	-- first variant:  arranging so that KILL QUERY would be noticed on ResultSet.close() operation
						if (this.rs.next()) {
							System.out.println("a query returned a row and now we're going to close the recordset afterwards");
							Thread.sleep(3000);
						} 
					} else if (testMode==KillTestMode.KILL_WHILE_READING) {
						// -- second variant:  arranging so that KILL QUERY would be noticed on ResultSet.next() operation
						while (this.rs.next()) {
							System.out.println("a query returned a row, we're trying to get another...");
							Thread.sleep(3000);
						}
					}
				} catch (Exception ex) {
					System.out.println("Iterating over streaming RS got an exception: "+ex.toString());
				} finally {
					try {
						System.out.println("Closing RS (some rather old Connector/J implementations would hang in SocketIO here, see bug#64204 ) ...");
						this.rs.close();
						System.out.println("... successfully closed a streaming RS");
					} catch (Exception ex) {
						System.out.println("Closing streaming RS got an exception: "+ex.toString());
						//ex.printStackTrace(System.out);
					}
				}

				String secondSqlQuery = "select '* * * second query intended to fail * * *'";
				System.out.println("Attempting 2nd query :" + secondSqlQuery); 
				try( ResultSet rs2 = this.stmt.executeQuery(secondSqlQuery) ) {
					if (rs2.next()) {
						System.out.println("2nd query result = " + rs2.getString(1));
					} else {
						System.out.println("2nd query NO result");
					}
				}
				System.out.println("2nd query finished"); 

			} catch (SQLException se) {
				System.out.println("Querying thread got exception when exec query :"+se.toString());
				se.printStackTrace(System.out);
			} finally {
				System.out.println("Interruptor executor shut down");
				interruptorExecService.shutdown();
			}

		} catch (Exception e) {
			e.printStackTrace(System.out);
		}
	}
}