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

Example 36 with Row

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

the class TableSelectTest method testTableRowLockOptions.

@Test
public void testTableRowLockOptions() throws Exception {
    assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5")), "MySQL 8.0.5+ is required to run this test.");
    Function<RowResult, List<String>> asStringList = rr -> rr.fetchAll().stream().map(r -> r.getString(0)).collect(Collectors.toList());
    sqlUpdate("DROP TABLE IF EXISTS testTableRowLockOptions");
    sqlUpdate("CREATE TABLE testTableRowLockOptions (_id VARCHAR(32), a VARCHAR(20))");
    // index is required to enable row locking
    sqlUpdate("CREATE UNIQUE INDEX myIndex ON testTableRowLockOptions (_id)");
    sqlUpdate("INSERT INTO testTableRowLockOptions VALUES ('1', '1'), ('2', '1'), ('3', '1')");
    Session session1 = null;
    Session session2 = null;
    try {
        session1 = new SessionFactory().getSession(this.testProperties);
        Table table1 = session1.getDefaultSchema().getTable("testTableRowLockOptions");
        session2 = new SessionFactory().getSession(this.testProperties);
        Table table2 = session2.getDefaultSchema().getTable("testTableRowLockOptions");
        RowResult res;
        CompletableFuture<RowResult> futRes;
        /*
             * 1. Shared Lock in both sessions.
             */
        // session2.lockShared() returns data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockShared().execute();
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared().executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session1.rollback();
        // session2.lockShared(NOWAIT) returns data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute();
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session1.rollback();
        // session2.lockShared(SKIP_LOCK) returns data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        session1.rollback();
        /*
             * 2. Shared Lock in first session and exclusive lock in second.
             */
        // session2.lockExclusive() blocks until session1 ends.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        // session2.startTransaction();
        // res = table2.select("_id").where("_id < '3'").lockExclusive().execute(); (Can't test)
        // session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive().executeAsync();
        final CompletableFuture<RowResult> fr1 = futRes;
        assertThrows(TimeoutException.class, () -> fr1.get(3, TimeUnit.SECONDS));
        // Unlocks session2.
        session1.rollback();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        // session2.lockExclusive(NOWAIT) should return locking error.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        session2.startTransaction();
        assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
        final CompletableFuture<RowResult> fr2 = futRes;
        assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr2.get(3, TimeUnit.SECONDS));
        session2.rollback();
        session1.rollback();
        // session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockShared().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session1.rollback();
        /*
             * 3. Exclusive Lock in first session and shared lock in second.
             */
        // session2.lockShared() blocks until session1 ends.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        // session2.startTransaction();
        // res = table2.select("_id").where("_id < '3'").lockShared().execute(); (Can't test)
        // session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared().executeAsync();
        final CompletableFuture<RowResult> fr3 = futRes;
        assertThrows(TimeoutException.class, () -> fr3.get(3, TimeUnit.SECONDS));
        // Unlocks session2.
        session1.rollback();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        // session2.lockShared(NOWAIT) should return locking error.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        session2.startTransaction();
        assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute());
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
        final CompletableFuture<RowResult> fr4 = futRes;
        assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr4.get(3, TimeUnit.SECONDS));
        session2.rollback();
        session1.rollback();
        // session2.lockShared(SKIP_LOCK) should return (unlocked) data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session1.rollback();
        /*
             * 4. Exclusive Lock in both sessions.
             */
        // session2.lockExclusive() blocks until session1 ends.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        // session2.startTransaction();
        // res = table2.select("_id").where("_id < '3'").lockExclusive().execute(); (Can't test)
        // session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive().executeAsync();
        final CompletableFuture<RowResult> fr5 = futRes;
        assertThrows(TimeoutException.class, () -> fr5.get(3, TimeUnit.SECONDS));
        // Unlocks session2.
        session1.rollback();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(2, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("1", "2"));
        session2.rollback();
        // session2.lockExclusive(NOWAIT) should return locking error.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        session2.startTransaction();
        assertThrows(XProtocolError.class, "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
        final CompletableFuture<RowResult> fr6 = futRes;
        assertThrows(ExecutionException.class, ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.", () -> fr6.get(3, TimeUnit.SECONDS));
        session2.rollback();
        session1.rollback();
        // session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
        session1.startTransaction();
        table1.select("_id").where("_id = '1'").lockExclusive().execute();
        session2.startTransaction();
        res = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session2.startTransaction();
        futRes = table2.select("_id").where("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
        res = futRes.get(3, TimeUnit.SECONDS);
        assertTrue(futRes.isDone());
        assertEquals(1, asStringList.apply(res).size());
        assertThat(asStringList.apply(res), hasItems("2"));
        session2.rollback();
        session1.rollback();
    } finally {
        if (session1 != null) {
            session1.close();
        }
        if (session2 != null) {
            session2.close();
        }
        sqlUpdate("DROP TABLE IF EXISTS testTableRowLockOptions");
    }
}

Example 37 with Row

use of com.mysql.cj.xdevapi.Row 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 38 with Row

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

the class TableSelectTest method basicViewQuery.

@Test
public void basicViewQuery() {
    try {
        sqlUpdate("drop table if exists basicTable1");
        sqlUpdate("drop table if exists basicTable2");
        sqlUpdate("drop view if exists basicView");
        sqlUpdate("create table basicTable1 (_id varchar(32), name varchar(20))");
        sqlUpdate("create table basicTable2 (_id varchar(32), birthday date, age int)");
        sqlUpdate("create view basicView as select basicTable1._id, name, birthday, age from basicTable1 join basicTable2 on basicTable1._id=basicTable2._id");
        sqlUpdate("insert into basicTable1 values ('some long UUID', 'Sakila')");
        sqlUpdate("insert into basicTable2 values ('some long UUID', '2000-05-27', 14)");
        Table view = this.schema.getTable("basicView");
        Map<String, Object> params = new HashMap<>();
        params.put("name", "Saki%");
        params.put("age", 20);
        RowResult rows = view.select("birthday, `_id`, name").where("name like :name AND age < :age").bind(params).execute();
        // verify metadata
        List<String> columnNames = rows.getColumnNames();
        assertEquals("birthday", columnNames.get(0));
        assertEquals("_id", columnNames.get(1));
        assertEquals("name", columnNames.get(2));
        Row row = rows.next();
        assertEquals("2000-05-27", row.getString(0));
        assertEquals("2000-05-27", row.getString("birthday"));
        assertEquals("Sakila", row.getString(2));
        assertEquals("Sakila", row.getString("name"));
        assertEquals("some long UUID", row.getString(1));
        assertEquals("some long UUID", row.getString("_id"));
    } finally {
        sqlUpdate("drop table if exists basicTable1");
        sqlUpdate("drop table if exists basicTable2");
        sqlUpdate("drop view if exists basicView");
    }
}

Example 39 with Row

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

the class TableTest method testAsyncBind.

@Test
public void testAsyncBind() throws Exception {
    try {
        sqlUpdate("drop table if exists testAsyncBind");
        sqlUpdate("create table testAsyncBind(a int,b bigint,c double,d blob)");
        CompletableFuture<SqlResult> asyncSqlRes = null;
        SqlResult sqlRes = null;
        Row r = null;
        // execute without bind()
        assertThrows(ExecutionException.class, ".*You have an error in your SQL syntax.*", () -> this.session.sql("insert into testAsyncBind values(?,?,?,?)").executeAsync().get());
        // execute with more bind()
        assertThrows(ExecutionException.class, ".*Too many arguments.*", () -> this.session.sql("insert into testAsyncBind values(?,?,?,?)").bind(1, 2, 3, 4, 5).executeAsync().get());
        // execute with less bind()
        assertThrows(ExecutionException.class, ".*You have an error in your SQL syntax.*", () -> this.session.sql("insert into testAsyncBind values(?,?,?,?)").bind(1, 2, 3).executeAsync().get());
        // Success
        asyncSqlRes = this.session.sql("insert into testAsyncBind values(?,?,?,?)").bind(10, 2).bind(3, "S").executeAsync();
        sqlRes = asyncSqlRes.get();
        asyncSqlRes = this.session.sql("select * from testAsyncBind where a=?").bind(10).executeAsync();
        sqlRes = asyncSqlRes.get();
        r = sqlRes.next();
        assertTrue(r.getBoolean(0));
        assertEquals(10, r.getInt(0));
        assertEquals(2, r.getLong(1));
        assertEquals(3.0, r.getDouble(2), 1);
        assertEquals("S", r.getString(3));
        assertFalse(sqlRes.hasNext());
        // bind in where and having
        asyncSqlRes = this.session.sql("select b+? as Temp,a as Temp1 from testAsyncBind where a=?+? having a>?").bind(100, 9, 1, 0).executeAsync();
        sqlRes = asyncSqlRes.get();
        r = sqlRes.next();
        assertTrue(r.getBoolean(0));
        assertEquals(102, r.getInt("Temp"));
        assertFalse(sqlRes.hasNext());
    } finally {
        sqlUpdate("drop table if exists testAsyncBind");
    }
}

Example 40 with Row

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

the class TableTest method testBug25650912.

@Test
public void testBug25650912() throws Exception {
    try {
        sqlUpdate("drop table if exists testBug25650912");
        sqlUpdate("create table testBug25650912 (x bigint,y char(220))");
        Table table = this.schema.getTable("testBug25650912", false);
        table.insert("x", "y").values(1, 'a').executeAsync().get();
        RowResult rows = table.select("x, y").execute();
        Row row = rows.next();
        assertEquals(1, row.getInt("x"));
        assertEquals("a", row.getString("y"));
        assertThrows(XProtocolError.class, "ERROR 1366 \\(HY000\\) Incorrect integer value: 's' for column 'x' at row 1", new Callable<Void>() {

            public Void call() throws Exception {
                table.update().set("x", 's').execute();
                return null;
            }
        });
        table.update().set("x", (byte) 2).set("y", 's').execute();
        rows = table.select("x, y").execute();
        row = rows.next();
        assertEquals(2, row.getInt("x"));
        assertEquals("s", row.getString("y"));
        table.update().set("x", BigInteger.valueOf(3)).execute();
        rows = table.select("x").execute();
        row = rows.next();
        assertEquals(3, row.getInt("x"));
        table.update().set("x", BigDecimal.valueOf(4.123)).execute();
        rows = table.select("x").execute();
        row = rows.next();
        assertEquals(4, row.getInt("x"));
    } finally {
        sqlUpdate("drop table if exists testBug25650912");
    }
}