connectionregressiontest.java

MySql Java Connector

                Properties autoReconnectProps = new Properties();
                autoReconnectProps.put("autoReconnect", "true");

                System.out.println(newUrlToTestPortNum);

                //
                // First test that port #'s are being correctly picked up
                //
                // We do this by looking at the error message that is returned
                //
                Connection portNumConn = DriverManager.getConnection(newUrlToTestPortNum
                        .toString(), autoReconnectProps);
                Statement portNumStmt = portNumConn.createStatement();
                this.rs = portNumStmt.executeQuery("SELECT connection_id()");
                this.rs.next();

                killConnection(adminConnection, this.rs.getString(1));

                try {
                    portNumStmt.executeQuery("SELECT connection_id()");
                } catch (SQLException sqlEx) {
                    // we expect this one
                }

                try {
                    portNumStmt.executeQuery("SELECT connection_id()");
                } catch (SQLException sqlEx) {
                    assertTrue(sqlEx.getMessage().toLowerCase().indexOf("connection refused") != -1);
                }

                //
                // Now make sure failover works
                //
                StringBuffer newUrlToTestFailover = new StringBuffer(
                        "jdbc:mysql://");

                if (host != null) {
                    newUrlToTestFailover.append(host);
                }

                newUrlToTestFailover.append(":").append(port);
                newUrlToTestFailover.append(",");

                if (host != null) {
                    newUrlToTestFailover.append(host);
                }

                newUrlToTestFailover.append(":").append(bogusPortNumber);
                newUrlToTestFailover.append("/");

                if (database != null) {
                    newUrlToTestFailover.append(database);
                }

                if ((user != null) || (password != null)) {
                    newUrlToTestFailover.append("?");

                    if (user != null) {
                        newUrlToTestFailover.append("user=").append(user);

                        if (password != null) {
                            newUrlToTestFailover.append("&");
                        }
                    }

                    if (password != null) {
                        newUrlToTestFailover.append("password=").append(password);
                    }
                }

                Connection failoverConn = DriverManager.getConnection(newUrlToTestFailover
                        .toString(), autoReconnectProps);
                Statement failoverStmt = portNumConn.createStatement();
                this.rs = failoverStmt.executeQuery("SELECT connection_id()");
                this.rs.next();

                killConnection(adminConnection, this.rs.getString(1));

                try {
                    failoverStmt.executeQuery("SELECT connection_id()");
                } catch (SQLException sqlEx) {
                    // we expect this one
                }

                failoverStmt.executeQuery("SELECT connection_id()");
            } finally {
                if (adminConnection != null) {
                    adminConnection.close();
                }
            }
        }
    }

    /**
     * Tests if the driver configures character sets correctly for 4.1.x
     * servers.  Requires that the 'admin connection' is configured, as this
     * test needs to create/drop databases.
     *
     * @throws Exception if an error occurs
     */
    public void testCollation41() throws Exception {
        if (versionMeetsMinimum(4, 1) && isAdminConnectionConfigured()) {
            Map charsetsAndCollations = getCharacterSetsAndCollations();
            charsetsAndCollations.remove("latin7"); // Maps to multiple Java charsets
            charsetsAndCollations.remove("ucs2"); // can't be used as a connection charset

            Iterator charsets = charsetsAndCollations.keySet().iterator();

            while (charsets.hasNext()) {
                Connection charsetConn = null;
                Statement charsetStmt = null;

                try {
                    String charsetName = charsets.next().toString();
                    String collationName = charsetsAndCollations.get(charsetName)
                                                                .toString();
                    Properties props = new Properties();
                    props.put("characterEncoding", charsetName);

                    System.out.println("Testing character set " + charsetName);

                    charsetConn = getAdminConnectionWithProps(props);

                    charsetStmt = charsetConn.createStatement();

                    charsetStmt.executeUpdate(
                        "DROP DATABASE IF EXISTS testCollation41");
                    charsetStmt.executeUpdate(
                        "DROP TABLE IF EXISTS testCollation41");

                    charsetStmt.executeUpdate(
                        "CREATE DATABASE testCollation41 DEFAULT CHARACTER SET "
                        + charsetName);
                    charsetConn.setCatalog("testCollation41");

                    // We've switched catalogs, so we need to recreate the statement to pick this up...
                    charsetStmt = charsetConn.createStatement();

                    StringBuffer createTableCommand = new StringBuffer(
                            "CREATE TABLE testCollation41"
                            + "(field1 VARCHAR(255), field2 INT)");

                    charsetStmt.executeUpdate(createTableCommand.toString());

                    charsetStmt.executeUpdate(
                        "INSERT INTO testCollation41 VALUES ('abc', 0)");

                    int updateCount = charsetStmt.executeUpdate(
                            "UPDATE testCollation41 SET field2=1 WHERE field1='abc'");
                    assertTrue(updateCount == 1);
                } finally {
                    if (charsetStmt != null) {
                        charsetStmt.executeUpdate(
                            "DROP TABLE IF EXISTS testCollation41");
                        charsetStmt.executeUpdate(
                            "DROP DATABASE IF EXISTS testCollation41");
                        charsetStmt.close();
                    }

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

    /**
     * Tests setReadOnly() being reset during failover
     *
     * @throws Exception if an error occurs.
     */
    public void testSetReadOnly() throws Exception {
        Properties props = new Properties();
        props.put("autoReconnect", "true");

        String sepChar = "?";

        if (BaseTestCase.dbUrl.indexOf("?") != -1) {
            sepChar = "&";
        }

        Connection reconnectableConn = DriverManager.getConnection(BaseTestCase.dbUrl
                + sepChar + "autoReconnect=true", props);

        rs = reconnectableConn.createStatement().executeQuery("SELECT CONNECTION_ID()");
        rs.next();

        String connectionId = rs.getString(1);

        reconnectableConn.setReadOnly(true);

        boolean isReadOnly = reconnectableConn.isReadOnly();

        System.out.println("You have 30 seconds to kill connection id "
            + connectionId + "...");
        Thread.sleep(30000);
        System.out.println("Executing statement on reconnectable connection...");

        try {
            reconnectableConn.createStatement().executeQuery("SELECT 1");
        } catch (SQLException sqlEx) {
            ; // ignore
        }

        reconnectableConn.createStatement().executeQuery("SELECT 1");

        assertTrue(reconnectableConn.isReadOnly() == isReadOnly);
    }

    private Map getCharacterSetsAndCollations() throws Exception {
        Map charsetsToLoad = new HashMap();

        try {
            this.rs = this.stmt.executeQuery("SHOW character set");

            while (rs.next()) {
                charsetsToLoad.put(rs.getString("Charset"),
                    rs.getString("Default collation"));
            }

            //
            // These don't have mappings in Java...
            //
            charsetsToLoad.remove("swe7");
            charsetsToLoad.remove("hp8");
            charsetsToLoad.remove("dec8");
            charsetsToLoad.remove("koi8u");
            charsetsToLoad.remove("keybcs2");
            charsetsToLoad.remove("geostd8");
            charsetsToLoad.remove("armscii8");
        } finally {
            if (this.rs != null) {
                this.rs.close();
            }
        }

        return charsetsToLoad;
    }

    private static void killConnection(Connection adminConn, String threadId)
        throws SQLException {
        adminConn.createStatement().execute("KILL " + threadId);
    }
}