connectiontest.java

mysql 5.1的 jdbc驱动 Connector/J 5.1 支持Mysql 4.1、Mysql 5.0、Mysql 5.1、Mysql 6.0 alpha这些版本。 Connector/J

		} 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((String)null);

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

		try {
			((com.mysql.jdbc.Connection) conn2).ping();
			fail("Should have failed with an exception");
		} catch (SQLException sqlEx) {
			// ignore for now
		}

		//
		// This feature caused BUG#8975, so check for that too!

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

		getConnectionWithProps(props);
	}

	public void testSessionVariables() throws Exception {
		String getInitialMaxAllowedPacket = getMysqlVariable("max_allowed_packet");

		int newMaxAllowedPacket = Integer.parseInt(getInitialMaxAllowedPacket) + 1024;

		Properties props = new Properties();
		props.setProperty("sessionVariables", "max_allowed_packet="
				+ newMaxAllowedPacket);
		props.setProperty("profileSQL", "true");

		Connection varConn = getConnectionWithProps(props);

		assertTrue(!getInitialMaxAllowedPacket.equals(getMysqlVariable(varConn,
				"max_allowed_packet")));
	}

	/**
	 * Tests setting profileSql on/off in the span of one connection.
	 * 
	 * @throws Exception
	 *             if an error occurs.
	 */
	public void testSetProfileSql() throws Exception {
		((com.mysql.jdbc.Connection) this.conn).setProfileSql(false);
		stmt.executeQuery("SELECT 1");
		((com.mysql.jdbc.Connection) this.conn).setProfileSql(true);
		stmt.executeQuery("SELECT 1");
	}

	public void testCreateDatabaseIfNotExist() throws Exception {
		if (isAdminConnectionConfigured()) {
			Properties props = new Properties();
			props.setProperty("createDatabaseIfNotExist", "true");
			props.setProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY,
					"testcreatedatabaseifnotexists");

			Connection newConn = getAdminConnectionWithProps(props);
			newConn.createStatement().executeUpdate(
					"DROP DATABASE testcreatedatabaseifnotexists");
		}
	}
    
    /**
     * Tests if gatherPerfMetrics works.
     * 
     * @throws Exception if the test fails
     */
    public void testGatherPerfMetrics() throws Exception {
        if(versionMeetsMinimum(4, 1)) {
            try {
                Properties props = new Properties();
                props.put("autoReconnect", "true");
                props.put("relaxAutoCommit", "true");
                props.put("logSlowQueries", "true");
                props.put("slowQueryThresholdMillis", "2000");
                // these properties were reported as the cause of NullPointerException
                props.put("gatherPerfMetrics", "true"); 
                props.put("reportMetricsIntervalMillis", "3000"); 
                
                Connection conn1 = getConnectionWithProps(props);
                Statement stmt1 = conn1.createStatement();
                ResultSet rs1 = stmt1.executeQuery("SELECT 1");
                rs1.next();
                conn1.close();
            } catch (NullPointerException e) {
                e.printStackTrace();
                fail();
            }
        }
    }

    /**
     * Tests if useCompress works.
     * 
     * @throws Exception if the test fails
     */
    public void testUseCompress() throws Exception {
        Properties props = new Properties();
        props.put("useCompression", "true");
        props.put("traceProtocol", "true");
        Connection conn1 = getConnectionWithProps(props);
        Statement stmt1 = conn1.createStatement();
        ResultSet rs1 = stmt1.executeQuery("SELECT VERSION()");
        rs1.next();
        rs1.getString(1);
        stmt1.close();
        conn1.close();
    }
    
    /**
     * Tests feature of "localSocketAddress", by enumerating local IF's and
     * trying each one in turn. This test might take a long time to run, since
     * we can't set timeouts if we're using localSocketAddress. We try and keep
     * the time down on the testcase by spawning the checking of each interface
     * off into separate threads.
     * 
     * @throws Exception if the test can't use at least one of the local machine's
     *                   interfaces to make an outgoing connection to the server.
     */
    public void testLocalSocketAddress() throws Exception {
    	if (isRunningOnJdk131()) {