connectiontest.java

mysql jdbc驱动程序 mysql jdbc驱动程序 mysql jdbc驱动程序 mysql jdbc驱动程序

				.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY)));
	}

	/**
	 * Tests functionality of using URLs in 'LOAD DATA LOCAL INFILE' statements.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testLocalInfileWithUrl() throws Exception {
		File infile = File.createTempFile("foo", "txt");
		infile.deleteOnExit();
		String url = infile.toURL().toExternalForm();
		FileWriter output = new FileWriter(infile);
		output.write("Test");
		output.flush();
		output.close();

		try {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testLocalInfileWithUrl");
			this.stmt
					.executeUpdate("CREATE TABLE testLocalInfileWithUrl (field1 LONGTEXT)");

			Properties props = new Properties();
			props.setProperty("allowUrlInLocalInfile", "true");

			Connection loadConn = getConnectionWithProps(props);
			Statement loadStmt = loadConn.createStatement();

			try {
				loadStmt.executeQuery("LOAD DATA LOCAL INFILE '" + url
						+ "' INTO TABLE testLocalInfileWithUrl");
			} catch (SQLException sqlEx) {
				sqlEx.printStackTrace();

				throw sqlEx;
			}

			this.rs = this.stmt
					.executeQuery("SELECT * FROM testLocalInfileWithUrl");
			assertTrue(this.rs.next());
			assertTrue("Test".equals(this.rs.getString(1)));
			int count = this.stmt
					.executeUpdate("DELETE FROM testLocalInfileWithUrl");
			assertTrue(count == 1);

			StringBuffer escapedPath = new StringBuffer();
			String path = infile.getCanonicalPath();

			for (int i = 0; i < path.length(); i++) {
				char c = path.charAt(i);

				if (c == '\\') {
					escapedPath.append('\\');
				}

				escapedPath.append(c);
			}

			loadStmt.executeQuery("LOAD DATA LOCAL INFILE '"
					+ escapedPath.toString()
					+ "' INTO TABLE testLocalInfileWithUrl");
			this.rs = this.stmt
					.executeQuery("SELECT * FROM testLocalInfileWithUrl");
			assertTrue(this.rs.next());
			assertTrue("Test".equals(this.rs.getString(1)));

			try {
				loadStmt
						.executeQuery("LOAD DATA LOCAL INFILE 'foo:///' INTO TABLE testLocalInfileWithUrl");
			} catch (SQLException sqlEx) {
				assertTrue(sqlEx.getMessage() != null);
				assertTrue(sqlEx.getMessage().indexOf("FileNotFoundException") != -1);
			}

		} finally {
			this.stmt
					.executeUpdate("DROP TABLE IF EXISTS testLocalInfileWithUrl");
		}
	}

	public void testLocalInfileDisabled() throws Exception {
		createTable("testLocalInfileDisabled", "(field1 varchar(255))");
		
		File infile = File.createTempFile("foo", "txt");
		infile.deleteOnExit();
		String url = infile.toURL().toExternalForm();
		FileWriter output = new FileWriter(infile);
		output.write("Test");
		output.flush();
		output.close();
		
		Connection loadConn = getConnectionWithProps(new Properties());
		
		try {
			// have to do this after connect, otherwise it's the server
			// that's enforcing it
			((com.mysql.jdbc.Connection)loadConn).setAllowLoadLocalInfile(false);
			try {
				loadConn.createStatement().execute("LOAD DATA LOCAL INFILE '" + infile.getCanonicalPath() + "' INTO TABLE testLocalInfileDisabled");
				fail("Should've thrown an exception.");
			} catch (SQLException sqlEx) {
				assertEquals(SQLError.SQL_STATE_GENERAL_ERROR, sqlEx.getSQLState());
			}
			
			assertFalse(loadConn.createStatement().executeQuery("SELECT * FROM testLocalInfileDisabled").next());
		} finally {
			loadConn.close();
		}
	}
	
	public void testServerConfigurationCache() throws Exception {
		Properties props = new Properties();

		props.setProperty("cacheServerConfiguration", "true");
		props.setProperty("profileSQL", "true");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");

		Connection conn1 = getConnectionWithProps(props);

		StandardLogger.saveLogsToBuffer();

		Connection conn2 = getConnectionWithProps(props);

		assertTrue("Configuration wasn't cached", StandardLogger.bufferedLog
				.toString().indexOf("SHOW VARIABLES") == -1);

		if (versionMeetsMinimum(4, 1)) {
			assertTrue("Configuration wasn't cached",
					StandardLogger.bufferedLog.toString().indexOf(
							"SHOW COLLATION") == -1);

		}
	}

	/**
	 * Tests whether or not the configuration 'useLocalSessionState' actually
	 * prevents non-needed 'set autocommit=', 'set session transaction isolation
	 * ...' and 'show variables like tx_isolation' queries.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testUseLocalSessionState() throws Exception {
		Properties props = new Properties();

		props.setProperty("useLocalSessionState", "true");
		props.setProperty("profileSQL", "true");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");

		Connection conn1 = getConnectionWithProps(props);
		conn1.setAutoCommit(true);
		conn1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

		StandardLogger.saveLogsToBuffer();
		StandardLogger.bufferedLog.setLength(0);

		conn1.setAutoCommit(true);
		conn1.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
		conn1.getTransactionIsolation();

		String logAsString = StandardLogger.bufferedLog.toString();

		assertTrue(logAsString.indexOf("SET SESSION") == -1
				&& logAsString.indexOf("SHOW VARIABLES LIKE 'tx_isolation'") == -1
				&& logAsString.indexOf("SET autocommit=") == -1);

	}

	/**
	 * Tests whether re-connect with non-read-only connection can happen.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testFailoverConnection() throws Exception {

		if (!isServerRunningOnWindows()) { // windows sockets don't
			                               // work for this test
			Properties props = new Properties();
			props.setProperty("autoReconnect", "true");
			props.setProperty("failOverReadOnly", "false");
	
			// Re-build the connection information
			int firstIndexOfHost = BaseTestCase.dbUrl.indexOf("//") + 2;
			int lastIndexOfHost = BaseTestCase.dbUrl.indexOf("/", firstIndexOfHost);
	
			String hostPortPair = BaseTestCase.dbUrl.substring(firstIndexOfHost,
					lastIndexOfHost);
			System.out.println(hostPortPair);
	
			StringTokenizer st = new StringTokenizer(hostPortPair, ":");
	
			String host = null;
			String port = null;
	
			if (st.hasMoreTokens()) {
				String possibleHostOrPort = st.nextToken();
	
				if (Character.isDigit(possibleHostOrPort.charAt(0)) && 
						(possibleHostOrPort.indexOf(".") == -1 /* IPV4 */)  &&
						(possibleHostOrPort.indexOf("::") == -1 /* IPV6 */)) {
					port = possibleHostOrPort;
					host = "localhost";
				} else {
					host = possibleHostOrPort;
				}
			}
	
			if (host == null) {
				host = "localhost"; 
			}
			
			if (st.hasMoreTokens()) {
				port = st.nextToken();
			}
	
			StringBuffer newHostBuf = new StringBuffer();
			newHostBuf.append(host);
			if (port != null) {
				newHostBuf.append(":");
				newHostBuf.append(port);
			}
			newHostBuf.append(",");
			newHostBuf.append(host);
			if (port != null) {
				newHostBuf.append(":");
				newHostBuf.append(port);
			}
	
			props
					.put(NonRegisteringDriver.HOST_PROPERTY_KEY, newHostBuf
							.toString());
	
			Connection failoverConnection = null;
	
			try {
				failoverConnection = getConnectionWithProps(props);
	
				String originalConnectionId = getSingleIndexedValueWithQuery(
						failoverConnection, 1, "SELECT connection_id()").toString();
				System.out.println("Original Connection Id = "
						+ originalConnectionId);
	
				assertTrue("Connection should not be in READ_ONLY state",
						!failoverConnection.isReadOnly());
	
				// Kill the connection
				this.stmt.executeUpdate("KILL " + originalConnectionId);
	
				// This takes a bit to occur
	
				Thread.sleep(3000);
	
				try {
					failoverConnection.createStatement().executeQuery("SELECT 1");
					fail("We expect an exception here, because the connection should be gone until the reconnect code picks it up again");
				} catch (SQLException sqlEx) {
					; // do-nothing
				}
	
				// Tickle re-connect
	
				failoverConnection.setAutoCommit(true);
	
				String newConnectionId = getSingleIndexedValueWithQuery(
						failoverConnection, 1, "SELECT connection_id()").toString();
				System.out.println("new Connection Id = " + newConnectionId);
	
				assertTrue(
						"We should have a new connection to the server in this case",
						!newConnectionId.equals(originalConnectionId));
				assertTrue("Connection should not be read-only",
						!failoverConnection.isReadOnly());
			} finally {
				if (failoverConnection != null) {
					failoverConnection.close();
				}
			}
		}
	}

	public void testCannedConfigs() throws Exception {
		String url = "jdbc:mysql:///?useConfigs=clusterBase";

		Properties cannedProps = new NonRegisteringDriver().parseURL(url, null);

		assertTrue("true".equals(cannedProps.getProperty("autoReconnect")));
		assertTrue("false".equals(cannedProps.getProperty("failOverReadOnly")));
		assertTrue("true".equals(cannedProps
				.getProperty("roundRobinLoadBalance")));

		// this will fail, but we test that too
		url = "jdbc:mysql:///?useConfigs=clusterBase,clusterBase2";

		try {
			cannedProps = new NonRegisteringDriver().parseURL(url, null);
			fail("should've bailed on that one!");
		} catch (SQLException sqlEx) {
			assertTrue(SQLError.SQL_STATE_INVALID_CONNECTION_ATTRIBUTE
					.equals(sqlEx.getSQLState()));
		}
	}

	public void testUseOldUTF8Behavior() throws Exception {

		Properties props = new Properties();
		props.setProperty("useOldUTF8Behavior", "true");
		props.setProperty("useUnicode", "true");
		props.setProperty("characterEncoding", "UTF-8");
		props.setProperty("logFactory", "com.mysql.jdbc.log.StandardLogger");
		props.setProperty("profileSQL", "true");
		StandardLogger.saveLogsToBuffer();
		StandardLogger.bufferedLog.setLength(0);

		try {
			getConnectionWithProps(props);

			assertTrue(StringUtils.indexOfIgnoreCase(StandardLogger.bufferedLog
					.toString(), "SET NAMES utf8") == -1);
		} finally {
			StandardLogger.bufferedLog = null;
		}
	}

	/**
	 * Checks implementation of 'dontTrackOpenResources' property.
	 * 
	 * @throws Exception
	 *             if the test fails.
	 */
	public void testDontTrackOpenResources() throws Exception {
		Properties props = new Properties();

		props.setProperty("dontTrackOpenResources", "true");
		Connection noTrackConn = null;
		Statement noTrackStatement = null;
		PreparedStatement noTrackPstmt = null;
		ResultSet rs2 = null;

		try {
			noTrackConn = getConnectionWithProps(props);
			noTrackStatement = noTrackConn.createStatement();
			noTrackPstmt = noTrackConn.prepareStatement("SELECT 1");
			rs2 = noTrackPstmt.executeQuery();
			rs2.next();

			this.rs = noTrackStatement.executeQuery("SELECT 1");
			this.rs.next();

			noTrackConn.close();

			// Under 'strict' JDBC requirements, these calls should fail
			// (and _do_ if dontTrackOpenResources == false)

			this.rs.getString(1);
			rs2.getString(1);
		} finally {
			if (rs2 != null) {
				rs2.close();
			}

			if (noTrackStatement != null) {
				noTrackStatement.close();
			}

			if (noTrackConn != null & !noTrackConn.isClosed()) {
				noTrackConn.close();
			}
		}
	}

	public void testPing() throws SQLException {
		Connection conn2 = getConnectionWithProps((Properties)null);

		((com.mysql.jdbc.Connection) conn2).ping();
		conn2.close();

		try {
			((com.mysql.jdbc.Connection) conn2).ping();