Examples with ServerPreparedStatement com.mysql.cj.jdbc.ServerPreparedStatement used on opensource projects

Example 26 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project JavaSegundasQuintas by ecteruel.

the class StatementRegressionTest method testBug81706.

/**
 * Tests fix for Bug#81706 - NullPointerException in driver.
 *
 * @throws Exception
 */
@Test
public void testBug81706() throws Exception {
    boolean useSPS = false;
    boolean cacheRsMd = false;
    boolean readOnly = false;
    do {
        final String testCase = String.format("Case [SPS: %s, CacheRsMd: %s, Read-only: %s]", useSPS ? "Y" : "N", cacheRsMd ? "Y" : "N", readOnly ? "Y" : "N");
        Properties props = new Properties();
        props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), Boolean.toString(useSPS));
        props.setProperty(PropertyKey.cacheResultSetMetadata.getKeyName(), Boolean.toString(cacheRsMd));
        props.setProperty(PropertyKey.queryInterceptors.getKeyName(), TestBug81706QueryInterceptor.class.getName());
        Connection testConn = getConnectionWithProps(props);
        testConn.setReadOnly(readOnly);
        Statement testStmt;
        PreparedStatement testPstmt;
        TestBug81706QueryInterceptor.isActive = true;
        TestBug81706QueryInterceptor.testCase = testCase;
        // Statement.executeQuery();
        testStmt = testConn.createStatement();
        testStmt.setFetchSize(Integer.MIN_VALUE);
        testStmt.executeQuery("/* ping */");
        testStmt.close();
        // Statemente.execute();
        testStmt = testConn.createStatement();
        testStmt.setFetchSize(Integer.MIN_VALUE);
        testStmt.execute("/* ping */");
        testStmt.close();
        // PreparedStatement.executeQuery();
        testPstmt = testConn.prepareStatement("/* ping */");
        assertFalse(testPstmt instanceof ServerPreparedStatement, testCase + ": Not the right Statement type.");
        testPstmt.setFetchSize(Integer.MIN_VALUE);
        testPstmt.executeQuery();
        testPstmt.close();
        // PreparedStatement.execute();
        testPstmt = testConn.prepareStatement("/* ping */");
        assertFalse(testPstmt instanceof ServerPreparedStatement, testCase + ": Not the right Statement type.");
        testPstmt.setFetchSize(Integer.MIN_VALUE);
        testPstmt.execute();
        testPstmt.close();
        TestBug81706QueryInterceptor.isActive = false;
        testConn.close();
    } while (// Cycle through all possible combinations.
    (useSPS = !useSPS) || (cacheRsMd = !cacheRsMd) || (readOnly = !readOnly));
}

Example 27 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class ResultSetRegressionTest method testBug25650385.

/**
 * Tests fix for BUG#25650385, GETBYTE() RETURNS ERROR FOR BINARY() FLD.
 *
 * @throws Exception
 */
@Test
public void testBug25650385() throws Exception {
    /*
         * getByte (recommended for TINYINT):
         * TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, BOOLEAN, CHAR , VARCHAR , LONGVARCHAR, ROWID
         */
    createTable("testBug25650385", "(b1 blob(12), c1 char(12), c2 binary(12), i1 int, c3 char(12) CHARACTER SET binary)");
    this.stmt.execute("INSERT INTO testBug25650385 values (10, 'a', 48, 10, 23)");
    Properties props = new Properties();
    props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
    props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
    for (boolean useSSPS : new boolean[] { false, true }) {
        for (boolean jdbcCompliantTruncation : new boolean[] { false, true }) {
            props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "" + useSSPS);
            props.setProperty(PropertyKey.jdbcCompliantTruncation.getKeyName(), "" + jdbcCompliantTruncation);
            Connection c1 = getConnectionWithProps(props);
            this.pstmt = c1.prepareStatement("select * from testBug25650385");
            if (useSSPS) {
                assertTrue(this.pstmt instanceof ServerPreparedStatement);
            }
            ResultSet rs1 = this.pstmt.executeQuery();
            assertTrue(rs1.next());
            // from blob(12)
            assertEquals('1', rs1.getBytes(1)[0]);
            if (jdbcCompliantTruncation) {
                assertThrows(SQLDataException.class, "Value '10' is outside of valid range for type java.lang.Byte", new Callable<Void>() {

                    public Void call() throws Exception {
                        rs1.getByte(1);
                        return null;
                    }
                });
            } else {
                assertEquals('1', rs1.getByte(1));
            }
            assertEquals(10, rs1.getInt(1));
            assertEquals(10L, rs1.getLong(1));
            assertEquals(10, rs1.getShort(1));
            assertEquals("10", rs1.getString(1));
            // from c1 char(12)
            assertEquals('a', rs1.getBytes(2)[0]);
            assertEquals('a', rs1.getByte(2));
            assertThrows(SQLDataException.class, "Cannot determine value type from string 'a'", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getInt(2);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string 'a'", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getLong(2);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string 'a'", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getShort(2);
                    return null;
                }
            });
            assertEquals("a", rs1.getString(2));
            // from c2 binary(12)
            assertEquals('4', rs1.getBytes(3)[0]);
            if (jdbcCompliantTruncation) {
                assertThrows(SQLDataException.class, "Value '48.+ is outside of valid range for type java.lang.Byte", new Callable<Void>() {

                    public Void call() throws Exception {
                        rs1.getByte(3);
                        return null;
                    }
                });
            } else {
                assertEquals('4', rs1.getByte(3));
            }
            assertThrows(SQLDataException.class, "Cannot determine value type from string '48.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getInt(3);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string '48.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getLong(3);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string '48.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getShort(3);
                    return null;
                }
            });
            assertTrue(rs1.getString(3).startsWith("48"));
            // from i1 int
            if (useSSPS) {
                assertEquals(10, rs1.getBytes(4)[0]);
            } else {
                assertEquals('1', rs1.getBytes(4)[0]);
            }
            assertEquals(10, rs1.getByte(4));
            assertEquals(10, rs1.getInt(4));
            assertEquals(10, rs1.getLong(4));
            assertEquals(10, rs1.getShort(4));
            assertEquals("10", rs1.getString(4));
            // from c3 char(12) CHARACTER SET binary
            assertEquals('2', rs1.getBytes(5)[0]);
            if (jdbcCompliantTruncation) {
                assertThrows(SQLDataException.class, "Value '23.+ is outside of valid range for type java.lang.Byte", new Callable<Void>() {

                    public Void call() throws Exception {
                        rs1.getByte(5);
                        return null;
                    }
                });
            } else {
                assertEquals('2', rs1.getByte(5));
            }
            assertThrows(SQLDataException.class, "Cannot determine value type from string '23.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getInt(5);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string '23.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getLong(5);
                    return null;
                }
            });
            assertThrows(SQLDataException.class, "Cannot determine value type from string '23.+", new Callable<Void>() {

                public Void call() throws Exception {
                    rs1.getShort(5);
                    return null;
                }
            });
            assertTrue(rs1.getString(5).startsWith("23"));
        }
    }
}

Example 28 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class StatementRegressionTest method testBug80615.

/**
 * Tests fix for Bug#80615 - prepared statement leak when rewriteBatchedStatements=true and useServerPrepStmt.
 *
 * There are two bugs here:
 * 1. A server prepared statement leakage by not actually closing the statement on server when .close() is called in the client side. This occurs when
 * setting 'cachePrepStmts=true&useServerPrepStmts=true' and a prepared statement is set as non-poolable ('setPoolable(false)'). By itself this doesn't
 * cause any visible issue because the connector has a fail-safe mechanism that uses client-side prepared statements when server-side prepared statements
 * fail to be prepared. So, the connector ends up using client-side prepared statements after the number of open prepared statements on server hits the
 * value of 'max_prepared_stmt_count'.
 * 2. A prepared statement fails to be prepared when there are too many open prepared statements on server. By setting the options
 * 'rewriteBatchedStatements=true&useServerPrepStmts=true' when a query happens to be rewritten a new (server-side) prepared statement is required but the
 * fail-safe mechanism isn't implemented in this spot, so, since the leakage described above already consumed all available prepared statements on server,
 * this ends up throwing the exception.
 *
 * This test combines three elements:
 * 1. Call .close() on a server prepared statement. This promotes a prepared statement for caching if prepared statements cache is enabled.
 * 2. cachePrepStmts=true|false. Turns on/off the prepared statements cache.
 * 3. Call .setPoolable(true|false) on the prepared statement. This allows canceling the prepared statement caching, on a per statement basis. It has no
 * effect if the prepared statements cache if turned off for the current connection.
 *
 * Expected behavior:
 * - If .close() is not called on server prepared statements then they also can't be promoted for caching. This causes a server prepared statements leak in
 * all remaining combinations.
 * - If .close() is called on server prepared statements and the prepared statements cache is disabled by any form (either per connection or per statement),
 * then the statements is immediately closed on server side too.
 * - If .close() is called on server prepared statements and the prepared statements cache is enabled (both in the connection and in the statement) then the
 * statement is cached and only effectively closed in the server side if and when removed from the cache.
 *
 * @throws Exception
 */
@Test
public void testBug80615() throws Exception {
    final int prepStmtCacheSize = 5;
    final int maxPrepStmtCount = 25;
    final int testRepetitions = maxPrepStmtCount + 5;
    int maxPrepStmtCountOri = -1;
    try {
        // Check if it is possible to create a server prepared statement with the current max_prepared_stmt_count.
        Properties props = new Properties();
        props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
        props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true");
        Connection checkConn = getConnectionWithProps(props);
        PreparedStatement checkPstmt = checkConn.prepareStatement("SELECT 1");
        assertTrue(checkPstmt instanceof ServerPreparedStatement, "Failed to create a server prepared statement possibly because there are too many active prepared statements on server already.");
        checkPstmt.close();
        this.rs = this.stmt.executeQuery("SELECT @@GLOBAL.max_prepared_stmt_count");
        this.rs.next();
        maxPrepStmtCountOri = this.rs.getInt(1);
        this.stmt.execute("SET GLOBAL max_prepared_stmt_count = " + maxPrepStmtCount);
        this.stmt.execute("FLUSH STATUS");
        // 2 - The statement that triggers the expelling of the oldest element of the cache to get room for itself.
        for (int i = 1; i <= prepStmtCacheSize + 2; i++) {
            checkPstmt = checkConn.prepareStatement("SELECT " + i);
            assertTrue(checkPstmt instanceof ServerPreparedStatement, "Test ABORTED because the server doesn't allow preparing at least " + (prepStmtCacheSize + 2) + " more statements.");
        }
        // Also closes all prepared statements.
        checkConn.close();
        // Good to go, start the test.
        boolean closeStmt = false;
        boolean useCache = false;
        boolean poolable = false;
        do {
            final String testCase = String.format("Case: [Close STMTs: %s, Use cache: %s, Poolable: %s ]", closeStmt ? "Y" : "N", useCache ? "Y" : "N", poolable ? "Y" : "N");
            System.out.println();
            System.out.println(testCase);
            System.out.println("********************************************************************************");
            createTable("testBug80615", "(id INT)");
            props.setProperty(PropertyKey.rewriteBatchedStatements.getKeyName(), "true");
            props.setProperty(PropertyKey.cachePrepStmts.getKeyName(), Boolean.toString(useCache));
            if (useCache) {
                props.setProperty(PropertyKey.prepStmtCacheSize.getKeyName(), String.valueOf(prepStmtCacheSize));
            }
            final Connection testConn = getConnectionWithProps(props);
            final Statement checkStmt = testConn.createStatement();
            // Prepare a statement to be executed later. This is prepare #1.
            PreparedStatement testPstmt1 = testConn.prepareStatement("INSERT INTO testBug80615 VALUES (?)");
            assertTrue(testPstmt1 instanceof ServerPreparedStatement, testCase);
            // Need to cast, this is a JDBC 4.0 feature.
            ((StatementImpl) testPstmt1).setPoolable(poolable);
            testPstmt1.setInt(1, 100);
            testPstmt1.addBatch();
            testPstmt1.setInt(1, 200);
            testPstmt1.addBatch();
            // One server-side prepared statement already prepared.
            int prepCount = 1;
            int expectedPrepCount = prepCount;
            int expectedExecCount = 0;
            int expectedCloseCount = 0;
            testBug80615CheckComStmtStatus(prepCount, true, testCase, checkStmt, expectedPrepCount, expectedExecCount, expectedCloseCount);
            // Prepare a number of statements higher than the limit set on server. There are at most (*) maxPrepStmtCount - 1 prepares available.
            // This should exhaust the number of allowed prepared statements, forcing the connector to use client-side prepared statements from that point
            // forward unless statements are closed correctly.
            // Under the tested circumstances there where some unexpected server prepared statements leaks (1st bug).
            // (*) There's no canonical way of knowing exactly how many preparing statement slots are available because other sessions may be using them.
            boolean isSPS = true;
            do {
                PreparedStatement testPstmt2 = testConn.prepareStatement("INSERT INTO testBug80615 VALUES (" + prepCount + " + ?)");
                prepCount++;
                isSPS = testPstmt2 instanceof ServerPreparedStatement;
                if (closeStmt) {
                    // Statements are being correctly closed so there is room to create new ones every time.
                    assertTrue(isSPS, testCase);
                } else if (prepCount > maxPrepStmtCount) {
                    // Not closing statements causes a server prepared statements leak on server.
                    // In this iteration (if not before) it should have started failing-over to a client-side prepared statement.
                    assertFalse(isSPS, testCase);
                } else if (prepCount <= prepStmtCacheSize + 2) {
                    // There should be enough room to prepare server-side prepared statements. (This was checked in the beginning.)
                    assertTrue(isSPS, testCase);
                }
                // prepStmtCacheSize + 1 < prepCount <= maxPrepStmtCount --> can't assert anything as there can statements prepared externally.
                // Need to cast, this is a JDBC 4.0 feature.
                ((StatementImpl) testPstmt2).setPoolable(poolable);
                testPstmt2.setInt(1, 0);
                testPstmt2.execute();
                if (isSPS) {
                    expectedPrepCount++;
                    expectedExecCount++;
                }
                if (closeStmt) {
                    testPstmt2.close();
                    if (isSPS) {
                        if (useCache && poolable && (prepCount - 1) > prepStmtCacheSize) {
                            // The first statement isn't cached yet.
                            // A statement (oldest in cache) is effectively closed on server side only after local statements cache is full.
                            expectedCloseCount++;
                        } else if (!useCache || !poolable) {
                            // The statement is closed immediately on server side.
                            expectedCloseCount++;
                        }
                    }
                }
                testBug80615CheckComStmtStatus(prepCount, isSPS, testCase, checkStmt, expectedPrepCount, expectedExecCount, expectedCloseCount);
            } while (prepCount < testRepetitions && isSPS);
            if (closeStmt) {
                assertEquals(testRepetitions, prepCount, testCase);
            } else {
                assertTrue(prepCount > prepStmtCacheSize + 2, testCase);
                assertTrue(prepCount <= maxPrepStmtCount + 1, testCase);
            }
            // Batched statements are being rewritten so this will prepare another statement underneath.
            // It was failing before if the the number of stmt prepares on server was exhausted at this point (2nd Bug).
            testPstmt1.executeBatch();
            testPstmt1.close();
            testConn.close();
        } while ((closeStmt = !closeStmt) || (useCache = !useCache) || (poolable = !poolable));
    } finally {
        if (maxPrepStmtCountOri >= 0) {
            this.stmt.execute("SET GLOBAL max_prepared_stmt_count = " + maxPrepStmtCountOri);
            this.stmt.execute("FLUSH STATUS");
        }
    }
}

Example 29 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class SyntaxRegressionTest method testAlterTableAlgorithmLock.

/**
 * ALTER TABLE syntax changed in 5.6GA
 *
 * ALTER TABLE ... , algorithm, concurrency
 *
 * algorithm:
 * | ALGORITHM [=] DEFAULT
 * | ALGORITHM [=] INPLACE
 * | ALGORITHM [=] COPY
 *
 * concurrency:
 * | LOCK [=] DEFAULT
 * | LOCK [=] NONE
 * | LOCK [=] SHARED
 * | LOCK [=] EXCLUSIVE
 *
 * @throws SQLException
 */
@Test
public void testAlterTableAlgorithmLock() throws SQLException {
    assumeTrue(versionMeetsMinimum(5, 6, 6) && !isServerRunningOnWindows(), "The non-Windows MySQL 5.6.6+ is required to run this test.");
    Connection c = null;
    Properties props = new Properties();
    props.setProperty(PropertyKey.sslMode.getKeyName(), "DISABLED");
    props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true");
    try {
        c = getConnectionWithProps(props);
        String[] algs = { "", ", ALGORITHM DEFAULT", ", ALGORITHM = DEFAULT", ", ALGORITHM INPLACE", ", ALGORITHM = INPLACE", ", ALGORITHM COPY", ", ALGORITHM = COPY" };
        String[] lcks = { "", ", LOCK DEFAULT", ", LOCK = DEFAULT", ", LOCK NONE", ", LOCK = NONE", ", LOCK SHARED", ", LOCK = SHARED", ", LOCK EXCLUSIVE", ", LOCK = EXCLUSIVE" };
        createTable("testAlterTableAlgorithmLock", "(x VARCHAR(10) NOT NULL DEFAULT '') CHARSET=latin2");
        int i = 1;
        for (String alg : algs) {
            for (String lck : lcks) {
                i = i ^ 1;
                // We should check if situation change in future
                if (!(lck.contains("NONE") && alg.contains("COPY"))) {
                    String sql = "ALTER TABLE testAlterTableAlgorithmLock CHARSET=latin" + (i + 1) + alg + lck;
                    this.stmt.executeUpdate(sql);
                    this.pstmt = this.conn.prepareStatement("ALTER TABLE testAlterTableAlgorithmLock CHARSET=?" + alg + lck);
                    assertTrue(this.pstmt instanceof ClientPreparedStatement);
                    this.pstmt = c.prepareStatement(sql);
                    assertTrue(this.pstmt instanceof ServerPreparedStatement);
                }
            }
        }
    } finally {
        if (c != null) {
            c.close();
        }
    }
}

Example 30 with ServerPreparedStatement

use of com.mysql.cj.jdbc.ServerPreparedStatement in project ABC by RuiPinto96274.

the class SyntaxRegressionTest method testExplicitPartitions.

/**
 * Test for explicit partition selection syntax
 *
 * @throws Exception
 */
@Test
public void testExplicitPartitions() throws Exception {
    assumeTrue(versionMeetsMinimum(5, 6, 5) && isMysqlRunningLocally(), "Locally running MySQL 5.6.5+ is required to perform this test.");
    String datadir = null;
    this.rs = this.stmt.executeQuery("SHOW VARIABLES WHERE Variable_name='datadir'");
    this.rs.next();
    datadir = this.rs.getString(2);
    if (datadir != null) {
        datadir = new File(datadir).getCanonicalPath();
    }
    this.rs = this.stmt.executeQuery("SHOW VARIABLES WHERE Variable_name='secure_file_priv'");
    this.rs.next();
    String fileprivdir = this.rs.getString(2);
    assumeFalse("NULL".equalsIgnoreCase(this.rs.getString(2)), "To run this test the server needs to be started with the option\"--secure-file-priv=\"");
    if (fileprivdir.length() > 0) {
        fileprivdir = new File(fileprivdir).getCanonicalPath();
        assumeTrue(datadir.equals(fileprivdir), "To run this test the server option\"--secure-file-priv=\" needs to be empty or to match the server's data directory.");
    }
    Properties props = getPropertiesFromTestsuiteUrl();
    String dbname = props.getProperty(PropertyKey.DBNAME.getKeyName());
    props = new Properties();
    props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true");
    Connection c = null;
    try {
        this.stmt.executeUpdate("SET @old_default_storage_engine = @@default_storage_engine");
        this.stmt.executeUpdate("SET @@default_storage_engine = 'InnoDB'");
        c = getConnectionWithProps(props);
        createTable("testExplicitPartitions", "(a INT NOT NULL, b varchar (64), INDEX (b,a), PRIMARY KEY (a)) ENGINE = InnoDB" + " PARTITION BY RANGE (a) SUBPARTITION BY HASH (a) SUBPARTITIONS 2" + " (PARTITION pNeg VALUES LESS THAN (0) (SUBPARTITION subp0, SUBPARTITION subp1)," + " PARTITION `p0-9` VALUES LESS THAN (10) (SUBPARTITION subp2, SUBPARTITION subp3)," + " PARTITION `p10-99` VALUES LESS THAN (100) (SUBPARTITION subp4, SUBPARTITION subp5)," + " PARTITION `p100-99999` VALUES LESS THAN (100000) (SUBPARTITION subp6, SUBPARTITION subp7))");
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions PARTITION (pNeg, pNeg) VALUES (-1, \"pNeg(-subp1)\")");
        this.pstmt = this.conn.prepareStatement("INSERT INTO testExplicitPartitions PARTITION (pNeg, subp0) VALUES (-3, \"pNeg(-subp1)\")");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("INSERT INTO testExplicitPartitions PARTITION (pNeg, subp0) VALUES (-2, \"(pNeg-)subp0\")");
        assertTrue(this.pstmt instanceof com.mysql.cj.jdbc.ServerPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("INSERT INTO testExplicitPartitions PARTITION (`p100-99999`) VALUES (100, \"`p100-99999`(-subp6)\"), (101, \"`p100-99999`(-subp7)\"), (1000, \"`p100-99999`(-subp6)\")");
        assertTrue(this.pstmt instanceof com.mysql.cj.jdbc.ServerPreparedStatement);
        this.pstmt.execute();
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions PARTITION(`p10-99`,subp3) VALUES (1, \"subp3\"), (10, \"p10-99\")");
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions PARTITION(subp3) VALUES (3, \"subp3\")");
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions PARTITION(`p0-9`) VALUES (5, \"p0-9:subp3\")");
        this.stmt.executeUpdate("FLUSH STATUS");
        this.stmt.execute("SELECT * FROM testExplicitPartitions PARTITION (subp2)");
        this.pstmt = this.conn.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (subp2,pNeg) AS TableAlias");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (subp2,pNeg) AS TableAlias");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt = this.conn.prepareStatement("LOCK TABLE testExplicitPartitions READ, testExplicitPartitions as TableAlias READ");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("LOCK TABLE testExplicitPartitions READ, testExplicitPartitions as TableAlias READ");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = this.conn.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (subp3) AS TableAlias");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("SELECT COUNT(*) FROM testExplicitPartitions PARTITION (`p10-99`)");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        this.pstmt = this.conn.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (pNeg) WHERE a = 100");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (pNeg) WHERE a = 100");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        this.stmt.executeUpdate("UNLOCK TABLES");
        // Test LOAD
        assertNotNull(dbname, "No database selected");
        File f = new File(datadir + File.separator + dbname + File.separator + "loadtestExplicitPartitions.txt");
        if (f.exists()) {
            f.delete();
        }
        this.pstmt = this.conn.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtestExplicitPartitions.txt'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("SELECT * FROM testExplicitPartitions PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtestExplicitPartitions.txt'");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.execute("SELECT * FROM testExplicitPartitions PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtestExplicitPartitions.txt'");
        this.pstmt = this.conn.prepareStatement("ALTER TABLE testExplicitPartitions TRUNCATE PARTITION pNeg, `p10-99`");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("ALTER TABLE testExplicitPartitions TRUNCATE PARTITION pNeg, `p10-99`");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.executeUpdate("ALTER TABLE testExplicitPartitions TRUNCATE PARTITION pNeg, `p10-99`");
        this.stmt.executeUpdate("FLUSH STATUS");
        this.pstmt = this.conn.prepareStatement("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, subp4, subp5)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, subp4, subp5)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.stmt.executeUpdate("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, subp4, subp5)");
        this.stmt.executeUpdate("ALTER TABLE testExplicitPartitions TRUNCATE PARTITION pNeg, `p10-99`");
        this.stmt.executeUpdate("FLUSH STATUS");
        this.pstmt = this.conn.prepareStatement("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, `p10-99`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, `p10-99`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.stmt.executeUpdate("LOCK TABLE testExplicitPartitions WRITE");
        this.stmt.executeUpdate("LOAD DATA INFILE 'loadtestExplicitPartitions.txt' INTO TABLE testExplicitPartitions PARTITION (pNeg, `p10-99`)");
        this.stmt.executeUpdate("UNLOCK TABLES");
        // Test UPDATE
        this.stmt.executeUpdate("UPDATE testExplicitPartitions PARTITION(subp0) SET b = concat(b, ', Updated')");
        this.pstmt = this.conn.prepareStatement("UPDATE testExplicitPartitions PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("UPDATE testExplicitPartitions PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2') WHERE a = -2");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        this.stmt.executeUpdate("UPDATE testExplicitPartitions PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100");
        this.stmt.executeUpdate("UPDATE testExplicitPartitions PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100') WHERE a = 100");
        this.pstmt = this.conn.prepareStatement("UPDATE testExplicitPartitions PARTITION(`p100-99999`, pNeg) SET a = -222, b = concat(b, ', Updated from a = 100') WHERE a = 100");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("UPDATE testExplicitPartitions SET b = concat(b, ', Updated2') WHERE a = 1000000");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        // Test DELETE
        this.stmt.executeUpdate("DELETE FROM testExplicitPartitions PARTITION (pNeg) WHERE a = -1");
        this.pstmt = this.conn.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (pNeg) WHERE a = -1");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (pNeg) WHERE a = -1");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        this.stmt.executeUpdate("DELETE FROM testExplicitPartitions PARTITION (subp1) WHERE b like '%subp1%'");
        this.pstmt = this.conn.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (subp1) WHERE b like '%subp1%'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt.execute();
        this.pstmt = c.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (subp1) WHERE b like '%subp1%'");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.pstmt.execute();
        this.stmt.executeUpdate("FLUSH STATUS");
        this.stmt.executeUpdate("LOCK TABLE testExplicitPartitions WRITE");
        this.stmt.executeUpdate("DELETE FROM testExplicitPartitions PARTITION (subp1) WHERE b = 'p0-9:subp3'");
        this.pstmt = this.conn.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (subp1) WHERE b = 'p0-9:subp3'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.stmt.executeUpdate("DELETE FROM testExplicitPartitions PARTITION (`p0-9`) WHERE b = 'p0-9:subp3'");
        this.pstmt = this.conn.prepareStatement("DELETE FROM testExplicitPartitions PARTITION (`p0-9`) WHERE b = 'p0-9:subp3'");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.stmt.executeUpdate("UNLOCK TABLES");
        // Test multi-table DELETE
        this.stmt.executeUpdate("CREATE TABLE testExplicitPartitions2 LIKE testExplicitPartitions");
        this.pstmt = this.conn.prepareStatement("INSERT INTO testExplicitPartitions2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("INSERT INTO testExplicitPartitions2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        this.stmt.executeUpdate("ALTER TABLE testExplicitPartitions2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`");
        this.pstmt = this.conn.prepareStatement("INSERT IGNORE INTO testExplicitPartitions2 PARTITION (subp3) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("INSERT IGNORE INTO testExplicitPartitions2 PARTITION (subp3) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.executeUpdate("INSERT IGNORE INTO testExplicitPartitions2 PARTITION (subp3) SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        this.stmt.executeUpdate("TRUNCATE TABLE testExplicitPartitions2");
        this.stmt.executeUpdate("INSERT INTO testExplicitPartitions2 SELECT * FROM testExplicitPartitions PARTITION (subp3, `p10-99`, `p100-99999`)");
        this.pstmt = this.conn.prepareStatement("CREATE TABLE testExplicitPartitions3 SELECT * FROM testExplicitPartitions PARTITION (pNeg,subp3,`p100-99999`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("CREATE TABLE testExplicitPartitions3 SELECT * FROM testExplicitPartitions PARTITION (pNeg,subp3,`p100-99999`)");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.stmt.executeUpdate("CREATE TABLE testExplicitPartitions3 SELECT * FROM testExplicitPartitions PARTITION (pNeg,subp3,`p100-99999`)");
        this.pstmt = this.conn.prepareStatement("DELETE testExplicitPartitions, testExplicitPartitions2 FROM testExplicitPartitions PARTITION (pNeg), testExplicitPartitions3, testExplicitPartitions2 PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions3.a = testExplicitPartitions2.a");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("DELETE testExplicitPartitions, testExplicitPartitions2 FROM testExplicitPartitions PARTITION (pNeg), testExplicitPartitions3, testExplicitPartitions2 PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions3.a = testExplicitPartitions2.a");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.executeUpdate("DELETE testExplicitPartitions, testExplicitPartitions2 FROM testExplicitPartitions PARTITION (pNeg), testExplicitPartitions3, testExplicitPartitions2 PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions3.a = testExplicitPartitions2.a");
        this.pstmt = this.conn.prepareStatement("DELETE FROM testExplicitPartitions2, testExplicitPartitions3 USING testExplicitPartitions2 PARTITION (`p0-9`), testExplicitPartitions3, testExplicitPartitions PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions2.a = testExplicitPartitions.a");
        assertTrue(this.pstmt instanceof ClientPreparedStatement);
        this.pstmt = c.prepareStatement("DELETE FROM testExplicitPartitions2, testExplicitPartitions3 USING testExplicitPartitions2 PARTITION (`p0-9`), testExplicitPartitions3, testExplicitPartitions PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions2.a = testExplicitPartitions.a");
        assertTrue(this.pstmt instanceof ServerPreparedStatement);
        this.stmt.executeUpdate("DELETE FROM testExplicitPartitions2, testExplicitPartitions3 USING testExplicitPartitions2 PARTITION (`p0-9`), testExplicitPartitions3, testExplicitPartitions PARTITION (subp3) WHERE testExplicitPartitions.a = testExplicitPartitions3.a AND testExplicitPartitions3.b = 'subp3' AND testExplicitPartitions2.a = testExplicitPartitions.a");
        this.stmt.executeUpdate("SET @@default_storage_engine = @old_default_storage_engine");
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testExplicitPartitions, testExplicitPartitions2, testExplicitPartitions3");
        if (c != null) {
            c.close();
        }
        if (datadir != null) {
            File f = new File(datadir + File.separator + dbname + File.separator + "loadtestExplicitPartitions.txt");
            if (f.exists()) {
                f.deleteOnExit();
            }
        }
    }
}