import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;


public class CrashMySQL {
	private String host = "127.0.0.1";
	private String user = "root";
	private String pwd = "";
	
	public CrashMySQL () {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public void  prepareData () {
		Connection conn;
		PreparedStatement pstmt;
		System.out.println("Preparing data...");
		try {
			conn = DriverManager.getConnection("jdbc:mysql://" + host + "/test", user, pwd);
			pstmt = conn.prepareStatement("drop table if exists test.tab1");
			pstmt.execute();
			pstmt.close();
			pstmt = conn.prepareStatement("create table test.tab1(str varchar(32), i int, primary key (str)) engine = innodb");
			pstmt.execute();
			pstmt.close();
			pstmt = conn.prepareStatement("drop table if exists test.tab2");
			pstmt.execute();
			pstmt.close();
			pstmt = conn.prepareStatement("create table test.tab2(str varchar(32), i int, t datetime, index(str)) engine = innodb" + 
					" partition by range(UNIX_TIMESTAMP(t)) subpartition by key(str) subpartitions 5" +
					" (partition p0 values less than (UNIX_TIMESTAMP('2006-05-05 12:00:00'))," + 
					"  partition p1 values less than (UNIX_TIMESTAMP('2006-05-12 12:00:00'))," +
					"  partition p2 values less than (UNIX_TIMESTAMP('2006-05-19 12:00:00'))," + 
					"  partition p3 values less than (UNIX_TIMESTAMP('2006-05-26 12:00:00')))");
			pstmt.execute();
			pstmt.close();
			pstmt = conn.prepareStatement("insert into test.tab1 values (?, ?)");
			for (int i = 0; i < 100000; i ++) {
				pstmt.setString(1, "str " + i);
				pstmt.setInt(2, i);
				pstmt.execute();
				if (i % 1000 == 0) {
					System.out.println(i + " rows inserted into test.tab1");
				}
			}
			pstmt.close();
			conn.close();
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
	}
	
	public void crashIt () {
		for (int i = 0; i < 100; i++) {
			final int t = i;
			new Thread () {
				Connection conn = null;
				PreparedStatement pstmt = null;
				public void run () {
					System.out.println("Thread " + t + " started");
					try {
						conn = DriverManager.getConnection("jdbc:mysql://" + host + "/test", user, pwd);
						conn.setAutoCommit(false);
						pstmt = conn.prepareStatement("select * from test.tab1 where str = ? for update");
						for (int j = 0; j < 1000; j++) {
							pstmt.setString(1, "str " + (t * 1000 + j));
							ResultSet rs = pstmt.executeQuery();
							rs.close();
						}
						pstmt.close();
						pstmt = conn.prepareStatement("update test.tab1 set i = ? where str = ?");
						for (int j = 0; j < 1000; j++) {
							pstmt.setInt(1, t * 1000 + j + 1);
							pstmt.setString(2, "str " + (t * 1000 + j));
							pstmt.execute();
						}
						pstmt.close();
						pstmt = conn.prepareStatement("insert into test.tab2 values (?, ?, ?)");
						for (int j = 0; j < 1000; j++) {
							pstmt.setString(1, "str " + (t * 1000 + j));
							pstmt.setInt(2, t * 1000 + j);
							pstmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
							pstmt.execute();
						}
						pstmt.close();
						conn.commit();
						conn.close();
					}
					catch (SQLException sqle) {
						try {
							if (conn != null)
								conn.rollback();
							if (pstmt != null)
								pstmt.close();
						} catch (SQLException e) {}
						sqle.printStackTrace();
					}
					System.out.println("Thread " + t + " ended");
				}
			}.start();
		}
	}
	
	public static void main(String [] args) {
		CrashMySQL crash = new CrashMySQL();
		crash.prepareData();
		crash.crashIt();
	}

}