Examples with SessionFactory com.mysql.cj.xdevapi.SessionFactory used on opensource projects

Example 36 with SessionFactory

use of com.mysql.cj.xdevapi.SessionFactory in project aws-mysql-jdbc by awslabs.

the class TableSelectTest method testBug22038729.

/**
 * Tests fix for Bug#22038729, X DEVAPI: ANY API CALL AFTER A FAILED CALL PROC() RESULTS IN HANG
 * and for duplicate Bug#25575010, X DEVAPI: ANY API CALL AFTER A FAILED SELECT RESULTS IN HANG
 *
 * @throws Exception
 */
@Test
public void testBug22038729() throws Exception {
    final Field pf = CoreSession.class.getDeclaredField("protocol");
    pf.setAccessible(true);
    try {
        sqlUpdate("drop table if exists testBug22038729");
        sqlUpdate("create table testBug22038729 (c1 int, c2 int unsigned, id bigint)");
        sqlUpdate("insert into testBug22038729 values(10, 100, -9223372036854775808)");
        sqlUpdate("insert into testBug22038729 values(11, 11, 9223372036854775806)");
        sqlUpdate("drop procedure if exists testBug22038729p");
        sqlUpdate("create procedure testBug22038729p (in p1 int,IN p2 char(20)) begin select -10;select id+1000 from testBug22038729; end;");
        // XProtocol.readRowOrNull()
        Session sess = new SessionFactory().getSession(this.testProperties);
        Table t1 = sess.getDefaultSchema().getTable("testBug22038729");
        RowResult rows = t1.select("c1-c2").orderBy("c1 DESC").execute();
        assertTrue(rows.hasNext());
        Row r = rows.next();
        assertEquals(0, r.getInt(0));
        assertThrows(XProtocolError.class, "ERROR 1690 \\(22003\\) BIGINT UNSIGNED value is out of range .*", () -> rows.hasNext());
        // It was hanging
        sess.close();
        // XProtocol.readRowOrNull()
        sess = new SessionFactory().getSession(this.testProperties);
        SqlResult rs1 = sess.sql("select c1-c2 from testBug22038729 order by c1 desc").execute();
        assertEquals(0, rs1.fetchOne().getInt(0));
        assertThrows(XProtocolError.class, "ERROR 1690 \\(22003\\) BIGINT UNSIGNED value is out of range .*", () -> rs1.fetchOne());
        // It was hanging
        sess.close();
        // XProtocol.drainRows()
        sess = new SessionFactory().getSession(this.testProperties);
        sess.sql("select c1-c2 from testBug22038729 order by c1 desc").execute();
        XProtocol xp = (XProtocol) pf.get(((SessionImpl) sess).getSession());
        assertThrows(XProtocolError.class, "ERROR 1690 \\(22003\\) BIGINT UNSIGNED value is out of range .*", () -> {
            xp.drainRows();
            return xp;
        });
        // It was hanging
        sess.close();
        sess = new SessionFactory().getSession(this.testProperties);
        SqlResult rs2 = sess.sql("call testBug22038729p(?, ?)").bind(10).bind("X").execute();
        assertTrue(rs2.hasData());
        assertTrue(rs2.hasNext());
        r = rs2.next();
        assertEquals(-10, r.getInt(0));
        assertFalse(rs2.hasNext());
        assertTrue(rs2.nextResult());
        assertTrue(rs2.hasData());
        assertTrue(rs2.hasNext());
        r = rs2.next();
        assertEquals(-9223372036854774808L, r.getLong(0));
        assertThrows(XProtocolError.class, "ERROR 1690 \\(22003\\) BIGINT value is out of range .*", () -> rs2.hasNext());
        // It was hanging
        sess.close();
    } finally {
        sqlUpdate("drop table if exists testBug22038729");
        sqlUpdate("drop procedure if exists testBug22038729p");
    }
}

Example 37 with SessionFactory

use of com.mysql.cj.xdevapi.SessionFactory in project aws-mysql-jdbc by awslabs.

the class TableUpdateTest method testPreparedStatements.

@Test
public void testPreparedStatements() {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.14")), "MySQL 8.0.14+ is required to run this test.");
    try {
        // Prepare test data.
        testPreparedStatementsResetData();
        SessionFactory sf = new SessionFactory();
        /*
             * Test common usage.
             */
        Session testSession = sf.getSession(this.testProperties);
        int sessionThreadId = getThreadId(testSession);
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        Table testTbl1 = testSession.getDefaultSchema().getTable("testPrepareUpdate_1");
        Table testTbl2 = testSession.getDefaultSchema().getTable("testPrepareUpdate_2");
        Table testTbl3 = testSession.getDefaultSchema().getTable("testPrepareUpdate_3");
        Table testTbl4 = testSession.getDefaultSchema().getTable("testPrepareUpdate_4");
        // Initialize several UpdateStatement objects.
        // Update all.
        UpdateStatement testUpdate1 = testTbl1.update().where("true").set("ord", expr("ord * 10"));
        // Criteria with one placeholder.
        UpdateStatement testUpdate2 = testTbl2.update().where("ord >= :n").set("ord", expr("ord * 10"));
        // Criteria with same placeholder repeated.
        UpdateStatement testupdate3 = testTbl3.update().where("ord >= :n AND ord <= :n + 1").set("ord", expr("ord * 10"));
        // Criteria with multiple placeholders.
        UpdateStatement testUpdate4 = testTbl4.update().where("ord >= :n AND ord <= :m").set("ord", expr("ord * 10"));
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate1, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate2, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testupdate3, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate4, 0, -1);
        // A. Set binds: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate1, 0, -1);
        assertTestPreparedStatementsResult(testUpdate2.bind("n", 2).execute(), 3, testTbl2.getName(), 1, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate2, 0, -1);
        assertTestPreparedStatementsResult(testupdate3.bind("n", 2).execute(), 2, testTbl3.getName(), 1, 20, 30, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testupdate3, 0, -1);
        assertTestPreparedStatementsResult(testUpdate4.bind("n", 2).bind("m", 3).execute(), 2, testTbl4.getName(), 1, 20, 30, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        testPreparedStatementsResetData();
        // B. Set orderBy resets execution count: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testUpdate1.orderBy("id").execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate1, 0, -1);
        assertTestPreparedStatementsResult(testUpdate2.orderBy("id").execute(), 3, testTbl2.getName(), 1, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate2, 0, -1);
        assertTestPreparedStatementsResult(testupdate3.orderBy("id").execute(), 2, testTbl3.getName(), 1, 20, 30, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testupdate3, 0, -1);
        assertTestPreparedStatementsResult(testUpdate4.orderBy("id").execute(), 2, testTbl4.getName(), 1, 20, 30, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
        testPreparedStatementsResetData();
        // C. Set binds reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 1, testUpdate1, 1, 1);
        assertTestPreparedStatementsResult(testUpdate2.bind("n", 3).execute(), 2, testTbl2.getName(), 1, 2, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 2, testUpdate2, 2, 1);
        assertTestPreparedStatementsResult(testupdate3.bind("n", 3).execute(), 2, testTbl3.getName(), 1, 2, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 3, testupdate3, 3, 1);
        assertTestPreparedStatementsResult(testUpdate4.bind("m", 4).execute(), 3, testTbl4.getName(), 1, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 4, 4, 0);
        testPreparedStatementsResetData();
        // D. Set binds reuse statement: 3rd execute -> execute.
        assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate1, 1, 2);
        assertTestPreparedStatementsResult(testUpdate2.bind("n", 4).execute(), 1, testTbl2.getName(), 1, 2, 3, 40);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate2, 2, 2);
        assertTestPreparedStatementsResult(testupdate3.bind("n", 1).execute(), 2, testTbl3.getName(), 10, 20, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testupdate3, 3, 2);
        assertTestPreparedStatementsResult(testUpdate4.bind("m", 2).execute(), 1, testTbl4.getName(), 1, 20, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 2);
        assertPreparedStatementsStatusCounts(testSession, 4, 8, 0);
        testPreparedStatementsResetData();
        // E. Set new values deallocates and resets execution count: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testUpdate1.set("ord", expr("ord * 100")).execute(), 4, testTbl1.getName(), 100, 200, 300, 400);
        assertPreparedStatementsCountsAndId(testSession, 3, testUpdate1, 0, -1);
        assertTestPreparedStatementsResult(testUpdate2.set("ord", expr("ord * 100")).execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 2, testUpdate2, 0, -1);
        assertTestPreparedStatementsResult(testupdate3.set("ord", expr("ord * 100")).execute(), 2, testTbl3.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 1, testupdate3, 0, -1);
        assertTestPreparedStatementsResult(testUpdate4.set("ord", expr("ord * 100")).execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 4, 8, 4);
        testPreparedStatementsResetData();
        // F. No Changes: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 100, 200, 300, 400);
        assertPreparedStatementsCountsAndId(testSession, 1, testUpdate1, 1, 1);
        assertTestPreparedStatementsResult(testUpdate2.execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 2, testUpdate2, 2, 1);
        assertTestPreparedStatementsResult(testupdate3.execute(), 2, testTbl3.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 3, testupdate3, 3, 1);
        assertTestPreparedStatementsResult(testUpdate4.execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 8, 12, 4);
        testPreparedStatementsResetData();
        // G. Set limit for the first time deallocates and re-prepares: 1st execute -> re-prepare + execute.
        assertTestPreparedStatementsResult(testUpdate1.limit(1).execute(), 1, testTbl1.getName(), 100, 2, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate1, 1, 1);
        assertTestPreparedStatementsResult(testUpdate2.limit(1).execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate2, 2, 1);
        assertTestPreparedStatementsResult(testupdate3.limit(1).execute(), 1, testTbl3.getName(), 100, 2, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testupdate3, 3, 1);
        assertTestPreparedStatementsResult(testUpdate4.limit(1).execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 12, 16, 8);
        testPreparedStatementsResetData();
        // H. Set limit reuse prepared statement: 2nd execute -> execute.
        assertTestPreparedStatementsResult(testUpdate1.limit(2).execute(), 2, testTbl1.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate1, 1, 2);
        assertTestPreparedStatementsResult(testUpdate2.limit(2).execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate2, 2, 2);
        assertTestPreparedStatementsResult(testupdate3.limit(2).execute(), 2, testTbl3.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testupdate3, 3, 2);
        assertTestPreparedStatementsResult(testUpdate4.limit(2).execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 2);
        assertPreparedStatementsStatusCounts(testSession, 12, 20, 8);
        testPreparedStatementsResetData();
        // I. Set orderBy deallocates and resets execution count, set limit has no effect: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testUpdate1.orderBy("id").limit(1).execute(), 1, testTbl1.getName(), 100, 2, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 3, testUpdate1, 0, -1);
        assertTestPreparedStatementsResult(testUpdate2.orderBy("id").limit(1).execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 2, testUpdate2, 0, -1);
        assertTestPreparedStatementsResult(testupdate3.orderBy("id").limit(1).execute(), 1, testTbl3.getName(), 100, 2, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 1, testupdate3, 0, -1);
        assertTestPreparedStatementsResult(testUpdate4.orderBy("id").limit(1).execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 0, testUpdate4, 0, -1);
        assertPreparedStatementsStatusCounts(testSession, 12, 20, 12);
        testPreparedStatementsResetData();
        // J. Set limit reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testUpdate1.limit(2).execute(), 2, testTbl1.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 1, testUpdate1, 1, 1);
        assertTestPreparedStatementsResult(testUpdate2.limit(2).execute(), 1, testTbl2.getName(), 1, 2, 3, 400);
        assertPreparedStatementsCountsAndId(testSession, 2, testUpdate2, 2, 1);
        assertTestPreparedStatementsResult(testupdate3.limit(2).execute(), 2, testTbl3.getName(), 100, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 3, testupdate3, 3, 1);
        assertTestPreparedStatementsResult(testUpdate4.limit(2).execute(), 1, testTbl4.getName(), 1, 200, 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testUpdate4, 4, 1);
        assertPreparedStatementsStatusCounts(testSession, 16, 24, 12);
        testPreparedStatementsResetData();
        testSession.close();
        // Prepared statements won't live past the closing of the session.
        assertPreparedStatementsCount(sessionThreadId, 0, 10);
        /*
             * Test falling back onto non-prepared statements.
             */
        testSession = sf.getSession(this.testProperties);
        int origMaxPrepStmtCount = this.session.sql("SELECT @@max_prepared_stmt_count").execute().fetchOne().getInt(0);
        try {
            // Allow preparing only one more statement.
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(getPreparedStatementsCount() + 1).execute();
            sessionThreadId = getThreadId(testSession);
            assertPreparedStatementsCount(sessionThreadId, 0, 1);
            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
            testTbl1 = testSession.getDefaultSchema().getTable("testPrepareUpdate_1");
            testTbl2 = testSession.getDefaultSchema().getTable("testPrepareUpdate_2");
            testUpdate1 = testTbl1.update().where("true").set("ord", expr("ord * 10"));
            testUpdate2 = testTbl2.update().where("true").set("ord", expr("ord * 10"));
            // 1st execute -> don't prepare.
            assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 0, testUpdate1, 0, -1);
            assertTestPreparedStatementsResult(testUpdate2.execute(), 4, testTbl2.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 0, testUpdate2, 0, -1);
            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);
            testPreparedStatementsResetData();
            // 2nd execute -> prepare + execute.
            assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 1, testUpdate1, 1, 1);
            // Fails preparing, execute as non-prepared.
            assertTestPreparedStatementsResult(testUpdate2.execute(), 4, testTbl2.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 1, testUpdate2, 0, -1);
            // Failed prepare also counts.
            assertPreparedStatementsStatusCounts(testSession, 2, 1, 0);
            testPreparedStatementsResetData();
            // 3rd execute -> execute.
            assertTestPreparedStatementsResult(testUpdate1.execute(), 4, testTbl1.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 1, testUpdate1, 1, 2);
            // Execute as non-prepared.
            assertTestPreparedStatementsResult(testUpdate2.execute(), 4, testTbl2.getName(), 10, 20, 30, 40);
            assertPreparedStatementsCountsAndId(testSession, 1, testUpdate2, 0, -1);
            assertPreparedStatementsStatusCounts(testSession, 2, 2, 0);
            testPreparedStatementsResetData();
            testSession.close();
            // Prepared statements won't live past the closing of the session.
            assertPreparedStatementsCount(sessionThreadId, 0, 10);
        } finally {
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(origMaxPrepStmtCount).execute();
        }
    } finally {
        for (int i = 0; i < 4; i++) {
            sqlUpdate("DROP TABLE IF EXISTS testPrepareUpdate_" + (i + 1));
        }
    }
}