com.mysql.jdbc.MySQLConnection Java Examples

/**
 * Tests fix for Bug#73663 (19479242), utf8mb4 does not work for connector/j >=5.1.13
 * 
 * This test is only run when character_set_server=utf8mb4 and collation-server set to one of utf8mb4 collations (it's better to test two configurations:
 * with default utf8mb4_general_ci and one of non-default, say utf8mb4_bin)
 * 
 * @throws Exception
 */
public void testBug73663() throws Exception {

    this.rs = this.stmt.executeQuery("show variables like 'collation_server'");
    this.rs.next();
    String collation = this.rs.getString(2);

    if (collation != null && collation.startsWith("utf8mb4") && "utf8mb4".equals(((MySQLConnection) this.conn).getServerVariable("character_set_server"))) {
        Properties p = new Properties();
        p.setProperty("characterEncoding", "UTF-8");
        p.setProperty("statementInterceptors", Bug73663StatementInterceptor.class.getName());

        getConnectionWithProps(p);
        // exception will be thrown from the statement interceptor if any "SET NAMES utf8" statement is issued instead of "SET NAMES utf8mb4"
    } else {
        System.out.println(
                "testBug73663 was skipped: This test is only run when character_set_server=utf8mb4 and collation-server set to one of utf8mb4 collations.");
    }
}
 

public void testLocalInfileHooked() throws Exception {
    createTable("localInfileHooked", "(field1 int, field2 varchar(255))");
    String streamData = "1\tabcd\n2\tefgh\n3\tijkl";
    InputStream stream = new ByteArrayInputStream(streamData.getBytes());
    try {
        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(stream);
        this.stmt.execute("LOAD DATA LOCAL INFILE 'bogusFileName' INTO TABLE localInfileHooked CHARACTER SET "
                + CharsetMapping.getMysqlCharsetForJavaEncoding(((MySQLConnection) this.conn).getEncoding(), (com.mysql.jdbc.Connection) this.conn));
        assertEquals(-1, stream.read());
        this.rs = this.stmt.executeQuery("SELECT field2 FROM localInfileHooked ORDER BY field1 ASC");
        this.rs.next();
        assertEquals("abcd", this.rs.getString(1));
        this.rs.next();
        assertEquals("efgh", this.rs.getString(1));
        this.rs.next();
        assertEquals("ijkl", this.rs.getString(1));
    } finally {
        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(null);
    }
}
 

public void testLocalInfileHooked() throws Exception {
    createTable("localInfileHooked", "(field1 int, field2 varchar(255))");
    String streamData = "1\tabcd\n2\tefgh\n3\tijkl";
    InputStream stream = new ByteArrayInputStream(streamData.getBytes());
    try {
        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(stream);
        this.stmt.execute("LOAD DATA LOCAL INFILE 'bogusFileName' INTO TABLE localInfileHooked CHARACTER SET "
                + CharsetMapping.getMysqlCharsetForJavaEncoding(((MySQLConnection) this.conn).getEncoding(), (com.mysql.jdbc.Connection) this.conn));
        assertEquals(-1, stream.read());
        this.rs = this.stmt.executeQuery("SELECT field2 FROM localInfileHooked ORDER BY field1 ASC");
        this.rs.next();
        assertEquals("abcd", this.rs.getString(1));
        this.rs.next();
        assertEquals("efgh", this.rs.getString(1));
        this.rs.next();
        assertEquals("ijkl", this.rs.getString(1));
    } finally {
        ((com.mysql.jdbc.Statement) this.stmt).setLocalInfileInputStream(null);
    }
}
 

/**
 * Tests fix for Bug#73663 (19479242), utf8mb4 does not work for connector/j >=5.1.13
 * 
 * This test is only run when character_set_server=utf8mb4 and collation-server set to one of utf8mb4 collations (it's better to test two configurations:
 * with default utf8mb4_general_ci and one of non-default, say utf8mb4_bin)
 * 
 * @throws Exception
 */
public void testBug73663() throws Exception {

    this.rs = this.stmt.executeQuery("show variables like 'collation_server'");
    this.rs.next();
    String collation = this.rs.getString(2);

    if (collation != null && collation.startsWith("utf8mb4") && "utf8mb4".equals(((MySQLConnection) this.conn).getServerVariable("character_set_server"))) {
        Properties p = new Properties();
        p.setProperty("characterEncoding", "UTF-8");
        p.setProperty("statementInterceptors", Bug73663StatementInterceptor.class.getName());

        getConnectionWithProps(p);
        // exception will be thrown from the statement interceptor if any "SET NAMES utf8" statement is issued instead of "SET NAMES utf8mb4"
    } else {
        System.out.println(
                "testBug73663 was skipped: This test is only run when character_set_server=utf8mb4 and collation-server set to one of utf8mb4 collations.");
    }
}
 

private static boolean pingInternal(MySQLConnection connection, int timeout) {
    if (connection == null)
        return false;

    try {
        connection.pingInternal(false, timeout * 1000);
    } catch (Throwable e) {
        DataSourceValidatorException exception =
                new DataSourceValidatorException(PING_INTERNAL_EXCEPTION_MESSAGE, e);
        LOGGER.warn(exception);
        return false;
    }

    return true;
}
 

private static byte[] encryptPassword(String password, String seed, Connection connection, String key) throws SQLException {
    byte[] input = null;
    try {
        input = password != null ? StringUtils.getBytesNullTerminated(password, connection.getPasswordCharacterEncoding()) : new byte[] { 0 };
    } catch (UnsupportedEncodingException e) {
        throw SQLError.createSQLException(Messages.getString("Sha256PasswordPlugin.3", new Object[] { connection.getPasswordCharacterEncoding() }),
                SQLError.SQL_STATE_GENERAL_ERROR, null);
    }
    byte[] mysqlScrambleBuff = new byte[input.length];
    Security.xorString(input, mysqlScrambleBuff, seed.getBytes(), input.length);
    return ExportControlled.encryptWithRSAPublicKey(mysqlScrambleBuff,
            ExportControlled.decodeRSAPublicKey(key, ((MySQLConnection) connection).getExceptionInterceptor()),
            ((MySQLConnection) connection).getExceptionInterceptor());
}
 

public CommunicationsException(MySQLConnection conn, long lastPacketSentTimeMs, long lastPacketReceivedTimeMs, Exception underlyingException) {
    this.exceptionMessage = SQLError.createLinkFailureMessageBasedOnHeuristics(conn, lastPacketSentTimeMs, lastPacketReceivedTimeMs, underlyingException);

    if (underlyingException != null) {
        initCause(underlyingException);
    }
}
 

public SQLException interceptException(SQLException sqlEx, Connection conn) {
    MySQLConnection mysqlConn = (MySQLConnection) conn;

    // don't intercept exceptions during initialization, before the proxy has a chance to setProxy() on the physical connection
    if (ConnectionImpl.class.isAssignableFrom(mysqlConn.getMultiHostSafeProxy().getClass())) {
        return null;
    }

    FabricMySQLConnectionProxy fabricProxy = (FabricMySQLConnectionProxy) mysqlConn.getMultiHostSafeProxy();
    try {
        return fabricProxy.interceptException(sqlEx, conn, this.fabricHaGroup, this.hostname, this.port);
    } catch (FabricCommunicationException ex) {
        return SQLError.createSQLException("Failed to report error to Fabric.", SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, ex, null);
    }
}
 

public MySQLConnection getActiveMySQLConnection() {
    try {
        return getActiveMySQLConnectionChecked();
    } catch (SQLException ex) {
        throw new IllegalStateException("Unable to determine active connection", ex);
    }
}
 

public MySQLConnection getActiveMySQLConnection() {
    try {
        return getActiveMySQLConnectionChecked();
    } catch (SQLException ex) {
        throw new IllegalStateException("Unable to determine active connection", ex);
    }
}
 

public SQLException interceptException(SQLException sqlEx, Connection conn) {
    MySQLConnection mysqlConn = (MySQLConnection) conn;

    // don't intercept exceptions during initialization, before the proxy has a chance to setProxy() on the physical connection
    if (ConnectionImpl.class.isAssignableFrom(mysqlConn.getMultiHostSafeProxy().getClass())) {
        return null;
    }

    FabricMySQLConnectionProxy fabricProxy = (FabricMySQLConnectionProxy) mysqlConn.getMultiHostSafeProxy();
    try {
        return fabricProxy.interceptException(sqlEx, conn, this.fabricHaGroup, this.hostname, this.port);
    } catch (FabricCommunicationException ex) {
        return SQLError.createSQLException("Failed to report error to Fabric.", SQLError.SQL_STATE_COMMUNICATION_LINK_FAILURE, ex, null);
    }
}
 

public CommunicationsException(MySQLConnection conn, long lastPacketSentTimeMs, long lastPacketReceivedTimeMs, Exception underlyingException) {
    this.exceptionMessage = SQLError.createLinkFailureMessageBasedOnHeuristics(conn, lastPacketSentTimeMs, lastPacketReceivedTimeMs, underlyingException);

    if (underlyingException != null) {
        initCause(underlyingException);
    }
}
 

private boolean connectionIsValid(Connection connection) throws SQLException {
    boolean isValid;

    if (connection instanceof MySQLConnection) {
        MySQLConnection mySqlConnection = (MySQLConnection) connection;
        isValid = MySqlConnectionHelper.isValid(mySqlConnection, DEFAULT_VALIDATE_TIMEOUT_IN_SECONDS);
    } else {
        isValid = connection.isValid(DEFAULT_VALIDATE_TIMEOUT_IN_SECONDS);
    }

    return isValid;
}
 

private static boolean pingInternal(MySQLConnection connection, int timeout) {
    if (connection == null) {
        return false;
    }

    try {
        connection.pingInternal(false, timeout * 1000);
    } catch (Throwable t) {
        return false;
    }

    return true;
}
 

public void setProxy(MySQLConnection proxy) {
}
 

public MySQLConnection getMultiHostSafeProxy() {
    return getActiveMySQLConnection();
}
 

/**
 * @deprecated replaced by <code>getMultiHostSafeProxy()</code>
 */
@Deprecated
public MySQLConnection getLoadBalanceSafeProxy() {
    return getMultiHostSafeProxy();
}
 

public void setProxy(MySQLConnection conn) {
    this.mc.setProxy(conn);
}
 

public static boolean isValid(MySQLConnection connection, int timeout) {
    return pingInternal(connection, timeout);
}
 

/**
 * Checks whether the server we're connected to is an MySQL Enterprise edition
 */
protected boolean isEnterpriseEdition() {
    return Util.isEnterpriseEdition(((MySQLConnection) this.conn).getServerVersion());
}
 

/**
 * Checks whether the server we're connected to is a MySQL Community edition
 */
protected boolean isCommunityEdition() {
    return Util.isCommunityEdition(((MySQLConnection) this.conn).getServerVersion());
}
 

public void setProxy(MySQLConnection conn) {
    this.mc.setProxy(conn);
}
 

/**
 * Tests fix for BUG#72890 - Java jdbc driver returns incorrect return code when it's part of XA transaction
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug72890() throws Exception {
    MysqlXADataSource myDs = new MysqlXADataSource();
    myDs.setUrl(BaseTestCase.dbUrl);

    try {
        final Xid xid = new MysqlXid("72890".getBytes(), "72890".getBytes(), 1);

        final XAConnection xaConn = myDs.getXAConnection();
        final XAResource xaRes = xaConn.getXAResource();
        final Connection dbConn = xaConn.getConnection();
        final long connId = ((MySQLConnection) ((com.mysql.jdbc.Connection) dbConn).getConnectionMutex()).getId();

        xaRes.start(xid, XAResource.TMNOFLAGS);
        xaRes.end(xid, XAResource.TMSUCCESS);
        assertEquals(XAResource.XA_OK, xaRes.prepare(xid));

        // Simulate a connection hang and make sure the connection really dies.
        this.stmt.execute("KILL CONNECTION " + connId);
        int connAliveChecks = 4;
        while (connAliveChecks > 0) {
            this.rs = this.stmt.executeQuery("SHOW PROCESSLIST");
            boolean connIsAlive = false;
            while (!connIsAlive && this.rs.next()) {
                connIsAlive = this.rs.getInt(1) == connId;
            }
            this.rs.close();
            if (connIsAlive) {
                connAliveChecks--;
                System.out.println("Connection id " + connId + " is still alive. Checking " + connAliveChecks + " more times.");
                try {
                    Thread.sleep(500);
                } catch (InterruptedException e) {
                }
            } else {
                connAliveChecks = -1;
            }
        }
        if (connAliveChecks == 0) {
            fail("Failed to kill the Connection id " + connId + " in a timely manner.");
        }

        XAException xaEx = assertThrows(XAException.class, "Undetermined error occurred in the underlying Connection - check your data for consistency",
                new Callable<Void>() {
                    public Void call() throws Exception {
                        xaRes.commit(xid, false);
                        return null;
                    }
                });
        assertEquals("XAException error code", XAException.XAER_RMFAIL, xaEx.errorCode);

        dbConn.close();
        xaConn.close();

    } finally {
        /*
         * After MySQL 5.7.7 a prepared XA transaction is no longer rolled back at disconnect. It needs to be rolled back manually to prevent test failures
         * in subsequent runs.
         * Other MySQL versions won't have any transactions to recover.
         */
        final XAConnection xaConnRecovery = myDs.getXAConnection();
        final XAResource xaResRecovery = xaConnRecovery.getXAResource();

        final Xid[] xidsToRecover = xaResRecovery.recover(XAResource.TMSTARTRSCAN);
        for (Xid xidToRecover : xidsToRecover) {
            xaResRecovery.rollback(xidToRecover);
        }

        xaConnRecovery.close();
    }
}
 

/**
 * WL#6054 - Temporarily disablement of users
 * 
 * Test user account locking syntax:
 * 
 * CREATE|ALTER USER (...)
 * - lock_option: { ACCOUNT LOCK | ACCOUNT UNLOCK }
 */
public void testUserAccountLocking() throws Exception {
    if (!versionMeetsMinimum(5, 7, 6)) {
        return;
    }

    final String user = "testAccLck";
    final String pwd = "testAccLck";
    final Properties props = new Properties();
    props.setProperty("user", user);
    props.setProperty("password", pwd);

    for (String accLock : new String[] { "/* default */", "ACCOUNT UNLOCK", "ACCOUNT LOCK" }) {
        createUser("'" + user + "'@'%'", "IDENTIFIED BY '" + pwd + "' " + accLock);
        this.stmt.execute("GRANT SELECT ON *.* TO '" + user + "'@'%'");

        if (accLock.equals("ACCOUNT LOCK")) {
            assertThrows("Test case: " + accLock + ",", SQLException.class, "Access denied for user '" + user + "'@'.*'\\. Account is locked\\.",
                    new Callable<Void>() {
                        public Void call() throws Exception {
                            getConnectionWithProps(props);
                            return null;
                        }
                    });
            this.stmt.execute("ALTER USER '" + user + "'@'%' ACCOUNT UNLOCK");
        }

        final Connection testConn1 = getConnectionWithProps(props);
        assertTrue("Test case: " + accLock + ",", testConn1.createStatement().executeQuery("SELECT 1").next());

        this.stmt.execute("ALTER USER '" + user + "'@'%' ACCOUNT LOCK");
        assertTrue("Test case: " + accLock + ",", testConn1.createStatement().executeQuery("SELECT 1").next()); // Previous authentication still valid.

        assertThrows("Test case: " + accLock + ",", SQLException.class, "Access denied for user '" + user + "'@'.*'\\. Account is locked\\.",
                new Callable<Void>() {
                    public Void call() throws Exception {
                        ((MySQLConnection) testConn1).changeUser(user, pwd);
                        return null;
                    }
                });
        assertFalse("Test case: " + accLock + ",", testConn1.isClosed());
        assertThrows("Test case: " + accLock + ",", SQLException.class, "(?s)Communications link failure.*", new Callable<Void>() {
            public Void call() throws Exception {
                testConn1.createStatement().executeQuery("SELECT 1");
                return null;
            }
        });
        assertTrue("Test case: " + accLock + ",", testConn1.isClosed());

        this.stmt.execute("ALTER USER '" + user + "'@'%' ACCOUNT UNLOCK");
        Connection testConn2 = getConnectionWithProps(props);
        assertTrue("Test case: " + accLock + ",", testConn2.createStatement().executeQuery("SELECT 1").next());
        testConn2.close();

        dropUser("'" + user + "'@'%'");
    }
}
 

/**
 * Tests Util.getPackageName()
 */
public void testGetPackageName() {
    assertEquals(MultiHostConnectionProxy.class.getPackage().getName(), Util.getPackageName(MultiHostConnectionProxy.class));
    assertEquals(MySQLConnection.class.getPackage().getName(), Util.getPackageName(this.conn.getClass().getInterfaces()[0]));
}
 

@Override
public boolean validate(Connection connection, int validateAction) {
    boolean isValid = false;
    try {
        String query = null;
        int validationQueryTimeout = -1;

        if (validateAction == PooledConnection.VALIDATE_INIT) {
            PoolProperties poolProperties = getPoolProperties(connection);
            if (poolProperties != null) {
                query = poolProperties.getInitSQL();
                validationQueryTimeout = poolProperties.getValidationQueryTimeout();
                if (validationQueryTimeout <= 0) {
                    validationQueryTimeout = DEFAULT_VALIDATE_TIMEOUT_IN_SECONDS;
                }
            }
        }

        if (query == null) {
            if (connection instanceof MySQLConnection) {
                MySQLConnection mySqlConnection = (MySQLConnection) connection;
                isValid = MySqlConnectionHelper.isValid(mySqlConnection, DEFAULT_VALIDATE_TIMEOUT_IN_SECONDS);
            } else {
                isValid = connection.isValid(DEFAULT_VALIDATE_TIMEOUT_IN_SECONDS);
            }

            if (!isValid) {
                LOGGER.warn("isValid() returned false.");
            }
        } else {
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                stmt.setQueryTimeout(validationQueryTimeout);
                stmt.execute(query);
                isValid = true;
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (Exception ignore2) {
                        /* NOOP */}
                }
            }
        }
    } catch (Throwable ex) {
        LOGGER.warn("Datasource validation error", ex);
    }

    return isValid;
}
 

public void setProxy(MySQLConnection proxy) {
}
 

public MySQLConnection getMultiHostSafeProxy() {
    return getActiveMySQLConnection();
}
 

/**
 * @deprecated replaced by <code>getMultiHostSafeProxy()</code>
 */
@Deprecated
public MySQLConnection getLoadBalanceSafeProxy() {
    return getMultiHostSafeProxy();
}
 

/**
 * Checks whether the server we're connected to is an MySQL Enterprise edition
 */
protected boolean isEnterpriseEdition() {
    return Util.isEnterpriseEdition(((MySQLConnection) this.conn).getServerVersion());
}