/* * * MariaDB Client for Java * * Copyright (c) 2012-2014 Monty Program Ab. * Copyright (c) 2015-2020 MariaDB Corporation Ab. * * This library is free software; you can redistribute it and/or modify it under * the terms of the GNU Lesser General Public License as published by the Free * Software Foundation; either version 2.1 of the License, or (at your option) * any later version. * * This library is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or * FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License * for more details. * * You should have received a copy of the GNU Lesser General Public License along * with this library; if not, write to Monty Program Ab info@montyprogram.com. * * This particular MariaDB Client for Java file is work * derived from a Drizzle-JDBC. Drizzle-JDBC file which is covered by subject to * the following copyright and notice provisions: * * Copyright (c) 2009-2011, Marcus Eriksson * * Redistribution and use in source and binary forms, with or without modification, * are permitted provided that the following conditions are met: * Redistributions of source code must retain the above copyright notice, this list * of conditions and the following disclaimer. * * Redistributions in binary form must reproduce the above copyright notice, this * list of conditions and the following disclaimer in the documentation and/or * other materials provided with the distribution. * * Neither the name of the driver nor the names of its contributors may not be * used to endorse or promote products derived from this software without specific * prior written permission. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. * IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY * OF SUCH DAMAGE. * */ package org.mariadb.jdbc; import static org.junit.Assert.*; import com.sun.jna.Platform; import java.math.BigDecimal; import java.sql.*; import java.sql.Date; import java.util.*; import java.util.concurrent.ExecutorService; import java.util.concurrent.Executors; import java.util.concurrent.TimeUnit; import org.junit.*; import org.mariadb.jdbc.internal.util.DeRegister; import org.mariadb.jdbc.internal.util.constant.HaMode; import org.mariadb.jdbc.util.DefaultOptions; public class DriverTest extends BaseTest { @BeforeClass() public static void initClass() throws SQLException { afterClass(); try (Statement stmt = sharedConnection.createStatement()) { stmt.execute("CREATE TABLE tt1(id int , name varchar(20))"); stmt.execute("CREATE TABLE tt2(id int , name varchar(20))"); stmt.execute( "CREATE TABLE Drivert2(id int not null primary key auto_increment, test varchar(10))"); stmt.execute( "CREATE TABLE utente(id int not null primary key auto_increment, test varchar(10))"); stmt.execute( "CREATE TABLE Drivert3(id int not null primary key auto_increment, test varchar(10))"); stmt.execute( "CREATE TABLE Drivert30(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE Drivert4(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE Drivert5(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE Drivert6(id int not null primary key auto_increment, test varchar(20))"); stmt.execute("CREATE TABLE test_float(id int not null primary key auto_increment, a float)"); stmt.execute( "CREATE TABLE test_big_autoinc2(id int not null primary key auto_increment, test varchar(10))"); stmt.execute("CREATE TABLE test_big_update(id int primary key not null, updateme int)"); stmt.execute("CREATE TABLE sharedConnection(id int)"); stmt.execute("CREATE TABLE extest(id int not null primary key)"); stmt.execute( "CREATE TABLE commentPreparedStatements(id int not null primary key auto_increment, a varchar(10))"); stmt.execute( "CREATE TABLE quotesPreparedStatements(id int not null primary key auto_increment, a varchar(10) , b varchar(10))"); stmt.execute("CREATE TABLE ressetpos(i int not null primary key)"); stmt.execute("CREATE TABLE streamingressetpos(i int not null primary key)"); stmt.execute("CREATE TABLE streamingtest(val varchar(20))"); stmt.execute("CREATE TABLE testString2(a varchar(10))"); stmt.execute("CREATE TABLE testBlob2(a blob)"); stmt.execute("CREATE TABLE unsignedtest(a int unsigned)"); stmt.execute("CREATE TABLE conj25(a VARCHAR(1024))"); stmt.execute( "CREATE TABLE DriverTestt1(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE DriverTestt2(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE DriverTestt3(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE DriverTestt4(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE DriverTestt5(id int not null primary key auto_increment, test varchar(20))"); stmt.execute("CREATE TABLE conj275(a VARCHAR(10))"); stmt.execute("CREATE TABLE testLongEscapes(t1 longtext)"); stmt.execute( "CREATE TABLE testRollbackOnClose(id int not null primary key auto_increment, test varchar(20))"); stmt.execute( "CREATE TABLE testAutoCommit(id int not null primary key auto_increment, test varchar(20))"); stmt.execute("CREATE TABLE escapeTest(t varchar(10))"); stmt.execute("CREATE PROCEDURE foo() BEGIN SELECT 1; END"); stmt.execute("CREATE PROCEDURE multiUpdateCount() BEGIN SELECT 1; SELECT 2; END"); stmt.execute("FLUSH TABLES"); } } @AfterClass public static void afterClass() throws SQLException { try (Statement stmt = sharedConnection.createStatement()) { stmt.execute("DROP TABLE IF EXISTS tt1"); stmt.execute("DROP TABLE IF EXISTS tt2"); stmt.execute("DROP TABLE IF EXISTS Drivert2"); stmt.execute("DROP TABLE IF EXISTS utente"); stmt.execute("DROP TABLE IF EXISTS Drivert3"); stmt.execute("DROP TABLE IF EXISTS Drivert30"); stmt.execute("DROP TABLE IF EXISTS Drivert4"); stmt.execute("DROP TABLE IF EXISTS Drivert5"); stmt.execute("DROP TABLE IF EXISTS Drivert6"); stmt.execute("DROP TABLE IF EXISTS test_float"); stmt.execute("DROP TABLE IF EXISTS test_big_autoinc2"); stmt.execute("DROP TABLE IF EXISTS test_big_update"); stmt.execute("DROP TABLE IF EXISTS sharedConnection"); stmt.execute("DROP TABLE IF EXISTS extest"); stmt.execute("DROP TABLE IF EXISTS commentPreparedStatements"); stmt.execute("DROP TABLE IF EXISTS quotesPreparedStatements"); stmt.execute("DROP TABLE IF EXISTS ressetpos"); stmt.execute("DROP TABLE IF EXISTS streamingressetpos"); stmt.execute("DROP TABLE IF EXISTS streamingtest"); stmt.execute("DROP TABLE IF EXISTS testString2"); stmt.execute("DROP TABLE IF EXISTS testBlob2"); stmt.execute("DROP TABLE IF EXISTS unsignedtest"); stmt.execute("DROP TABLE IF EXISTS conj25"); stmt.execute("DROP TABLE IF EXISTS DriverTestt1"); stmt.execute("DROP TABLE IF EXISTS DriverTestt2"); stmt.execute("DROP TABLE IF EXISTS DriverTestt3"); stmt.execute("DROP TABLE IF EXISTS DriverTestt4"); stmt.execute("DROP TABLE IF EXISTS DriverTestt5"); stmt.execute("DROP TABLE IF EXISTS conj275"); stmt.execute("DROP TABLE IF EXISTS testLongEscapes"); stmt.execute("DROP TABLE IF EXISTS testRollbackOnClose"); stmt.execute("DROP TABLE IF EXISTS testAutoCommit"); stmt.execute("DROP TABLE IF EXISTS escapeTest"); stmt.execute("DROP PROCEDURE IF EXISTS foo"); stmt.execute("DROP PROCEDURE IF EXISTS multiUpdateCount"); } } private static int namedPipeBusyTestError = 0; @Test public void doQuery() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into DriverTestt1 (test) values ('hej1')"); stmt.execute("insert into DriverTestt1 (test) values ('hej2')"); stmt.execute("insert into DriverTestt1 (test) values ('hej3')"); stmt.execute("insert into DriverTestt1 (test) values (null)"); ResultSet rs = stmt.executeQuery("select * from DriverTestt1"); int[] autoInc = setAutoInc(); for (int i = 1; i < 4; i++) { assertTrue(rs.next()); assertEquals(String.valueOf(autoInc[1] + autoInc[0] * i), rs.getString(1)); assertEquals("hej" + i, rs.getString("test")); } assertTrue(rs.next()); assertEquals(null, rs.getString("test")); } @Test(expected = SQLException.class) public void askForBadColumnTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into DriverTestt2 (test) values ('hej1')"); stmt.execute("insert into DriverTestt2 (test) values ('hej2')"); stmt.execute("insert into DriverTestt2 (test) values ('hej3')"); stmt.execute("insert into DriverTestt2 (test) values (null)"); ResultSet rs = stmt.executeQuery("select * from DriverTestt2"); if (rs.next()) { rs.getInt("non_existing_column"); } else { fail(); } } @Test(expected = SQLException.class) public void askForBadColumnIndexTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into DriverTestt3 (test) values ('hej1')"); stmt.execute("insert into DriverTestt3 (test) values ('hej2')"); stmt.execute("insert into DriverTestt3 (test) values ('hej3')"); stmt.execute("insert into DriverTestt3 (test) values (null)"); ResultSet rs = stmt.executeQuery("select * from DriverTestt3"); assertTrue(rs.next()); rs.getInt(102); } @Test /* Accessing result set using table.column */ public void tableDotColumnInResultSet() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into tt1 values(1, 'one')"); stmt.execute("insert into tt2 values(1, 'two')"); ResultSet rs = stmt.executeQuery("select tt1.*, tt2.* from tt1, tt2 where tt1.id = tt2.id"); assertTrue(rs.next()); assertEquals(1, rs.getInt("tt1.id")); assertEquals(1, rs.getInt("tt2.id")); assertEquals("one", rs.getString("tt1.name")); assertEquals("two", rs.getString("tt2.name")); } @Test(expected = SQLException.class) public void badQuery() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.executeQuery("whraoaooa"); } @Test public void preparedTest() throws SQLException { try (Statement stmt = sharedConnection.createStatement()) { stmt.execute("insert into DriverTestt4 (test) values ('hej1')"); } int[] autoInc = setAutoInc(); String query = "SELECT * FROM DriverTestt4 WHERE test = ? and id = ?"; PreparedStatement prepStmt = sharedConnection.prepareStatement(query); prepStmt.setString(1, "hej1"); prepStmt.setInt(2, autoInc[0] + autoInc[1]); ResultSet results = prepStmt.executeQuery(); String res = ""; while (results.next()) { res = results.getString("test"); } assertEquals("hej1", res); assertEquals(2, prepStmt.getParameterMetaData().getParameterCount()); } @Test public void parameterMetaDataTypeNotAvailable() throws SQLException { String query = "SELECT * FROM DriverTestt4 WHERE test = ? and id = ?"; try (PreparedStatement prepStmt = sharedConnection.prepareStatement(query)) { ParameterMetaData parameterMetaData = prepStmt.getParameterMetaData(); assertEquals(2, parameterMetaData.getParameterCount()); try { parameterMetaData.getParameterType(1); fail("must have thrown an error"); } catch (SQLException sqle) { assertTrue(sqle instanceof SQLFeatureNotSupportedException); } } } @Test public void parameterMetaDataNotPreparable() throws SQLException { Assume.assumeTrue(System.getenv("SKYSQL") == null && System.getenv("SKYSQL_HA") == null); Assume.assumeFalse(sharedUsePrepare()); Statement stmt = sharedConnection.createStatement(); Map initValues = loadVariables(stmt); // statement that cannot be prepared try (PreparedStatement pstmt = sharedConnection.prepareStatement("select TMP.field1 from (select ? from dual) TMP")) { ParameterMetaData meta = pstmt.getParameterMetaData(); assertEquals(1, meta.getParameterCount()); } Map endingValues = loadVariables(stmt); assertEquals(initValues.get("Prepared_stmt_count"), endingValues.get("Prepared_stmt_count")); assertEquals( (Integer) (initValues.get("Com_stmt_prepare") + 1), endingValues.get("Com_stmt_prepare")); assertEquals(initValues.get("Com_stmt_close"), endingValues.get("Com_stmt_close")); } @Test public void parameterMetaDataReturnException() throws SQLException { // statement that cannot be prepared try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("selec1t 2 from dual")) { ParameterMetaData meta = preparedStatement.getParameterMetaData(); assertEquals(0, meta.getParameterCount()); } } private Map loadVariables(Statement stmt) throws SQLException { Map variables = new HashMap<>(); ResultSet rs = stmt.executeQuery( "SHOW SESSION STATUS WHERE Variable_name in ('Prepared_stmt_count','Com_stmt_prepare', 'Com_stmt_close')"); assertTrue(rs.next()); variables.put(rs.getString(1), rs.getInt(2)); assertTrue(rs.next()); variables.put(rs.getString(1), rs.getInt(2)); assertTrue(rs.next()); variables.put(rs.getString(1), rs.getInt(2)); return variables; } @Test public void parameterMetaDataPreparable() throws SQLException { Assume.assumeTrue(System.getenv("SKYSQL") == null && System.getenv("SKYSQL_HA") == null); Assume.assumeFalse(sharedUsePrepare()); Statement stmt = sharedConnection.createStatement(); Map initValues = loadVariables(stmt); // statement that cannot be prepared try (PreparedStatement pstmt = sharedConnection.prepareStatement("select ?")) { ParameterMetaData parameterMetaData = pstmt.getParameterMetaData(); parameterMetaData.getParameterCount(); } Map endingValues = loadVariables(stmt); assertEquals(initValues.get("Prepared_stmt_count"), endingValues.get("Prepared_stmt_count")); assertEquals( (Integer) (initValues.get("Com_stmt_prepare") + 1), endingValues.get("Com_stmt_prepare")); assertEquals( (Integer) (initValues.get("Com_stmt_close") + 1), endingValues.get("Com_stmt_close")); } @Test public void streamingResultSet() throws Exception { Statement stmt = sharedConnection.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs = stmt.executeQuery("SELECT 1"); assertTrue(rs.isBeforeFirst()); try { rs.first(); fail("should not get there"); } catch (SQLException sqle) { assertTrue(sqle.getMessage().toLowerCase().contains("invalid operation")); } } @Test public void updateTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("insert into DriverTestt5 (test) values ('hej1')"); stmt.execute("insert into DriverTestt5 (test) values ('hej2')"); stmt.execute("insert into DriverTestt5 (test) values ('hej3')"); stmt.execute("insert into DriverTestt5 (test) values (null)"); int[] autoInc = setAutoInc(); String query = "UPDATE DriverTestt5 SET test = ? where id = ?"; PreparedStatement prepStmt = sharedConnection.prepareStatement(query); prepStmt.setString(1, "updated"); prepStmt.setInt(2, autoInc[1] + 3 * autoInc[0]); int updateCount = prepStmt.executeUpdate(); assertEquals(1, updateCount); String query2 = "SELECT * FROM DriverTestt5 WHERE id=?"; prepStmt = sharedConnection.prepareStatement(query2); prepStmt.setInt(1, autoInc[1] + 3 * autoInc[0]); ResultSet results = prepStmt.executeQuery(); String result = ""; while (results.next()) { result = results.getString("test"); } assertEquals("updated", result); } @Test public void ralfTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); for (int i = 0; i < 10; i++) { stmt.execute("INSERT INTO Drivert2 (test) VALUES ('aßa" + i + "')"); } PreparedStatement ps = sharedConnection.prepareStatement("SELECT * FROM Drivert2 where test like'%ß%' limit ?"); ps.setInt(1, 5); ps.addBatch(); ResultSet rs = ps.executeQuery(); int result = 0; while (rs.next()) { result++; } assertEquals(result, 5); } @Test public void autoIncTest() throws SQLException { Assume.assumeFalse(isGalera()); Statement stmt = sharedConnection.createStatement(); stmt.execute("INSERT INTO Drivert3 (test) VALUES ('aa')", Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertEquals(1, rs.getInt("insert_id")); stmt.execute("INSERT INTO Drivert3 (test) VALUES ('aa')", Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertEquals(2, rs.getInt("insert_id")); /* multi-row inserts */ stmt.execute( "INSERT INTO Drivert3 (test) VALUES ('bb'),('cc'),('dd')", Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); requireMinimumVersion(5, 0); /* non-standard autoIncrementIncrement */ try (Connection connection = setConnection("&sessionVariables=auto_increment_increment=2&allowMultiQueries=true")) { stmt = connection.createStatement(); stmt.execute( "INSERT INTO Drivert3 (test) values ('bb'),('cc');INSERT INTO Drivert3 (test) values ('dd'),('ee')", Statement.RETURN_GENERATED_KEYS); rs = stmt.getGeneratedKeys(); assertTrue(rs.next()); assertEquals(7, rs.getInt(1)); assertTrue(rs.next()); assertEquals(9, rs.getInt(1)); assertFalse(rs.next()); stmt.getMoreResults(); rs = stmt.getGeneratedKeys(); assertTrue(rs.next()); assertEquals(11, rs.getInt(1)); assertTrue(rs.next()); assertEquals(13, rs.getInt(1)); assertFalse(rs.next()); } } @Test public void autoInc2Test() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("ALTER TABLE `utente` AUTO_INCREMENT=1", Statement.RETURN_GENERATED_KEYS); } @Test public void transactionTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); sharedConnection.setAutoCommit(false); stmt.executeUpdate("INSERT INTO Drivert30 (test) VALUES ('heja')"); stmt.executeUpdate("INSERT INTO Drivert30 (test) VALUES ('japp')"); sharedConnection.commit(); ResultSet rs = stmt.executeQuery("SELECT * FROM Drivert30"); assertEquals(true, rs.next()); assertEquals("heja", rs.getString("test")); assertEquals(true, rs.next()); assertEquals("japp", rs.getString("test")); assertEquals(false, rs.next()); stmt.executeUpdate( "INSERT INTO Drivert30 (test) VALUES ('rollmeback')", Statement.RETURN_GENERATED_KEYS); ResultSet rsGen = stmt.getGeneratedKeys(); rsGen.next(); int[] autoInc = setAutoInc(); assertEquals(autoInc[1] + autoInc[0] * 3, rsGen.getInt(1)); sharedConnection.rollback(); rs = stmt.executeQuery("SELECT * FROM Drivert30 WHERE id=3"); assertEquals(false, rs.next()); sharedConnection.setAutoCommit(true); } @Test public void savepointTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); sharedConnection.setAutoCommit(false); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej1')"); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej2')"); Savepoint savepoint = sharedConnection.setSavepoint("yep"); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej3')"); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej4')"); sharedConnection.rollback(savepoint); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej5')"); stmt.executeUpdate("INSERT INTO Drivert4 (test) values('hej6')"); sharedConnection.commit(); ResultSet rs = stmt.executeQuery("SELECT * FROM Drivert4"); assertTrue(rs.next()); assertEquals("hej1", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej2", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej5", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej6", rs.getString(2)); assertFalse(rs.next()); sharedConnection.setAutoCommit(true); } @Test public void savepointUnname() throws SQLException { Statement stmt = sharedConnection.createStatement(); sharedConnection.setAutoCommit(false); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej1')"); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej2')"); Savepoint savepoint = sharedConnection.setSavepoint(); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej3')"); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej4')"); sharedConnection.rollback(savepoint); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej5')"); stmt.executeUpdate("INSERT INTO Drivert5 (test) values('hej6')"); sharedConnection.commit(); ResultSet rs = stmt.executeQuery("SELECT * FROM Drivert5"); assertTrue(rs.next()); assertEquals("hej1", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej2", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej5", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej6", rs.getString(2)); assertFalse(rs.next()); sharedConnection.setAutoCommit(true); } @Test public void releaseSavepoint() throws SQLException { Statement stmt = sharedConnection.createStatement(); sharedConnection.setAutoCommit(false); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej1')"); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej2')"); Savepoint savepoint = sharedConnection.setSavepoint(); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej3')"); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej4')"); sharedConnection.releaseSavepoint(savepoint); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej5')"); stmt.executeUpdate("INSERT INTO Drivert6 (test) values('hej6')"); sharedConnection.commit(); ResultSet rs = stmt.executeQuery("SELECT * FROM Drivert6"); assertTrue(rs.next()); assertEquals("hej1", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej2", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej3", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej4", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej5", rs.getString(2)); assertTrue(rs.next()); assertEquals("hej6", rs.getString(2)); assertFalse(rs.next()); sharedConnection.setAutoCommit(true); } @Test public void isolationLevel() throws SQLException { try (Connection connection = setConnection()) { int[] levels = new int[] { Connection.TRANSACTION_READ_UNCOMMITTED, Connection.TRANSACTION_READ_COMMITTED, Connection.TRANSACTION_SERIALIZABLE, Connection.TRANSACTION_REPEATABLE_READ }; for (int level : levels) { connection.setTransactionIsolation(level); assertEquals(level, connection.getTransactionIsolation()); } } } @Test public void wrongIsolationLevel() throws SQLException { try (Connection connection = setConnection()) { try { connection.setTransactionIsolation(10_000); fail(); } catch (SQLException e) { assertTrue(e.getMessage().contains("Unsupported transaction isolation level")); } } } @Test public void isValidTest() throws SQLException { assertEquals(true, sharedConnection.isValid(0)); } @Test public void testConnectorJurl() throws SQLException { UrlParser url = UrlParser.parse("jdbc:mariadb://localhost/test"); assertEquals("localhost", url.getHostAddresses().get(0).host); assertEquals("test", url.getDatabase()); assertEquals("jdbc:mariadb://localhost/test", url.getInitialUrl()); assertEquals(3306, url.getHostAddresses().get(0).port); url = UrlParser.parse("jdbc:mariadb://localhost:3307/test"); assertEquals("localhost", url.getHostAddresses().get(0).host); assertEquals("test", url.getDatabase()); assertEquals("jdbc:mariadb://localhost:3307/test", url.getInitialUrl()); assertEquals(3307, url.getHostAddresses().get(0).port); url = UrlParser.parse("jdbc:mariadb://localhost:3307/test", new Properties()); assertEquals("localhost", url.getHostAddresses().get(0).host); assertEquals("test", url.getDatabase()); assertEquals("jdbc:mariadb://localhost:3307/test", url.getInitialUrl()); assertEquals(3307, url.getHostAddresses().get(0).port); Properties props = new Properties(); props.setProperty("useServerPrepStmts", "true"); url = UrlParser.parse("jdbc:mariadb://localhost:3307/test", props); assertEquals("localhost", url.getHostAddresses().get(0).host); assertEquals("test", url.getDatabase()); assertEquals("jdbc:mariadb://localhost:3307/test", url.getInitialUrl()); assertEquals(3307, url.getHostAddresses().get(0).port); } @SuppressWarnings("deprecation") @Test public void metadataUrl() throws SQLException { String testUrl = System.getProperty("dbUrl", mDefUrl); // + "&pool=true&maxPoolSize=2&minPoolSize=1"; // ensure that metadata URL correspond to initial URL assertEquals(sharedConnection.getMetaData().getURL(), testUrl); MariaDbDataSource datasource = new MariaDbDataSource(); datasource.setUrl(testUrl); try (Connection conn = datasource.getConnection()) { assertEquals(conn.getMetaData().getURL(), testUrl); } // specific case for Datasource, using deprecated historical setProperties() method, URL can be // changed, URL is then reconstructed MariaDbDataSource datasource2 = new MariaDbDataSource(hostname, port, database); datasource2.setProperties( "user=" + username + ((password != null) ? "&password=" + password : "") + ((options.useSsl != null) ? "&useSsl=" + options.useSsl : "") + ((options.serverSslCert != null) ? "&serverSslCert=" + options.serverSslCert : "") + "&useServerPrepStmts=true"); try (Connection conn = datasource2.getConnection()) { assertEquals( "jdbc:mariadb://address=(host=" + hostname + ")(port=" + port + ")(type=master)/" + database + "?user=" + username + ((password != null) ? "&password=" + password : "") + ((options.useSsl != null) ? "&useSsl=" + options.useSsl : "") + ((options.serverSslCert != null) ? "&serverSslCert=" + options.serverSslCert : "") + "&useServerPrepStmts=true", conn.getMetaData().getURL()); } assertNotEquals( datasource.getUrlParser().getInitialUrl(), datasource2.getUrlParser().getInitialUrl()); } @Test public void testAliasReplication() throws SQLException { UrlParser url = UrlParser.parse("jdbc:mysql:replication://localhost/test"); UrlParser url2 = UrlParser.parse("jdbc:mariadb:replication://localhost/test"); assertEquals(url.getDatabase(), url2.getDatabase()); assertEquals(url.getOptions(), url2.getOptions()); assertEquals(url.getHostAddresses(), url2.getHostAddresses()); assertEquals(url.getHaMode(), url2.getHaMode()); } @Test public void testAliasDataSource() throws SQLException { ArrayList hostAddresses = new ArrayList<>(); hostAddresses.add(new HostAddress(hostname, port)); UrlParser urlParser = new UrlParser( database, hostAddresses, DefaultOptions.defaultValues(HaMode.NONE), HaMode.NONE); UrlParser urlParser2 = new UrlParser( database, hostAddresses, DefaultOptions.defaultValues(HaMode.NONE), HaMode.NONE); urlParser.parseUrl("jdbc:mysql:replication://localhost/test"); urlParser2.parseUrl("jdbc:mariadb:replication://localhost/test"); assertEquals(urlParser.getDatabase(), urlParser2.getDatabase()); assertEquals(urlParser.getOptions(), urlParser2.getOptions()); assertEquals(urlParser.getHostAddresses(), urlParser2.getHostAddresses()); assertEquals(urlParser.getHaMode(), urlParser2.getHaMode()); } @Test public void testEscapes() throws SQLException { String query = "select ?"; PreparedStatement stmt = sharedConnection.prepareStatement(query); stmt.setString(1, "hej\""); ResultSet rs = stmt.executeQuery(); assertEquals(true, rs.next()); assertEquals(rs.getString(1), "hej\""); } @Test public void escapeTest() throws SQLException { String param = "a'\""; param += String.valueOf(10); param += String.valueOf(13); param += String.valueOf(26); try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("INSERT INTO escapeTest VALUES (?)")) { preparedStatement.setString(1, param); preparedStatement.execute(); } Statement stmt = sharedConnection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM escapeTest"); Assert.assertTrue(rs.next()); Assert.assertEquals(param, rs.getString(1)); } @Test public void testPreparedWithNull() throws SQLException { String query = "select ? as test"; PreparedStatement pstmt = sharedConnection.prepareStatement(query); pstmt.setNull(1, 1); ResultSet rs = pstmt.executeQuery(); assertEquals(true, rs.next()); assertEquals(null, rs.getString("test")); assertEquals(true, rs.wasNull()); } @Test @SuppressWarnings("deprecation") public void connectFailover() throws SQLException { Assume.assumeTrue(hostname != null); String hosts = hostname + ":" + port + "," + hostname + ":" + (port + 1); String url = "jdbc:mariadb://" + hosts + "/" + database + "?user=" + username + (password != null && !"".equals(password) ? "&password=" + password : "") + ((options.useSsl != null) ? "&useSsl=" + options.useSsl : "") + ((options.serverSslCert != null) ? "&serverSslCert=" + options.serverSslCert : ""); try (Connection connection = openNewConnection(url)) { MariaDbConnection my = (MariaDbConnection) connection; assertTrue(my.getPort() == port); ResultSet rs = connection.createStatement().executeQuery("select 1"); if (rs.next()) { assertEquals(rs.getInt(1), 1); } else { fail(); } } } @Test public void floatingNumbersTest() throws SQLException { PreparedStatement ps = sharedConnection.prepareStatement("insert into test_float (a) values (?)"); ps.setDouble(1, 3.99); ps.executeUpdate(); ResultSet rs = sharedConnection.createStatement().executeQuery("select a from test_float"); assertEquals(true, rs.next()); assertEquals((float) 3.99, rs.getFloat(1), 0.00001); assertEquals((float) 3.99, rs.getFloat("a"), 0.00001); assertEquals(false, rs.next()); } @Test public void manyColumnsTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); try { stmt.execute("drop table if exists test_many_columns"); StringBuilder query = new StringBuilder("create table test_many_columns (a0 int primary key not null"); for (int i = 1; i < 1000; i++) { query.append(",a").append(i).append(" int"); } query.append(")"); stmt.execute(query.toString()); stmt.execute("FLUSH TABLES"); query = new StringBuilder("insert into test_many_columns values (0"); for (int i = 1; i < 1000; i++) { query.append(",").append(i); } query.append(")"); stmt.execute(query.toString()); ResultSet rs = stmt.executeQuery("select * from test_many_columns"); assertEquals(true, rs.next()); for (int i = 0; i < 1000; i++) { assertEquals(rs.getInt("a" + i), i); } } finally { stmt.execute("drop table if exists test_many_columns"); } } @Test public void bigAutoIncTest() throws SQLException { Assume.assumeFalse(isGalera()); Statement stmt = sharedConnection.createStatement(); stmt.execute("alter table test_big_autoinc2 auto_increment = 1000"); stmt.execute( "insert into test_big_autoinc2 values (null, 'hej')", Statement.RETURN_GENERATED_KEYS); ResultSet rsGen = stmt.getGeneratedKeys(); assertEquals(true, rsGen.next()); assertEquals(1000, rsGen.getInt(1)); stmt.execute("alter table test_big_autoinc2 auto_increment = " + Short.MAX_VALUE); stmt.execute( "insert into test_big_autoinc2 values (null, 'hej')", Statement.RETURN_GENERATED_KEYS); rsGen = stmt.getGeneratedKeys(); assertEquals(true, rsGen.next()); assertEquals(Short.MAX_VALUE, rsGen.getInt(1)); stmt.execute("alter table test_big_autoinc2 auto_increment = " + Integer.MAX_VALUE); stmt.execute( "insert into test_big_autoinc2 values (null, 'hej')", Statement.RETURN_GENERATED_KEYS); rsGen = stmt.getGeneratedKeys(); assertEquals(true, rsGen.next()); assertEquals(Integer.MAX_VALUE, rsGen.getInt(1)); } @Test public void bigUpdateCountTest() throws SQLException { Statement stmt = sharedConnection.createStatement(); for (int i = 0; i < 4; i++) { stmt.execute("insert into test_big_update values (" + i + "," + i + ")"); } ResultSet rs = stmt.executeQuery("select count(*) from test_big_update"); assertEquals(true, rs.next()); assertEquals(4, rs.getInt(1)); int updateCount = stmt.executeUpdate("update test_big_update set updateme=updateme+1"); assertEquals(4, updateCount); } @Test(expected = SQLIntegrityConstraintViolationException.class) public void testException1() throws SQLException { sharedConnection.createStatement().execute("insert into extest values (1)"); sharedConnection.createStatement().execute("insert into extest values (1)"); } @Test public void testExceptionDivByZero() throws SQLException { ResultSet rs = sharedConnection.createStatement().executeQuery("select 1/0"); assertEquals(rs.next(), true); assertEquals(null, rs.getString(1)); } @Test(expected = SQLSyntaxErrorException.class) public void testSyntaxError() throws SQLException { sharedConnection.createStatement().executeQuery("create asdf b"); } @Test public void testPreparedStatementsWithComments() throws SQLException { String query = "INSERT INTO commentPreparedStatements (a) VALUES (?) # ?"; PreparedStatement pstmt = sharedConnection.prepareStatement(query); pstmt.setString(1, "yeah"); pstmt.execute(); } @Test public void testPreparedStatementsWithQuotes() throws SQLException { String query = "INSERT INTO quotesPreparedStatements (a,b) VALUES ('hellooo?', ?) # ?"; PreparedStatement pstmt = sharedConnection.prepareStatement(query); pstmt.setString(1, "ff"); pstmt.execute(); } @SuppressWarnings("StatementWithEmptyBody") @Test public void testResultSetPositions() throws SQLException { sharedConnection.createStatement().execute("insert into ressetpos values (1),(2),(3),(4)"); Statement stmt = sharedConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("select * from ressetpos"); assertTrue(rs.isBeforeFirst()); assertTrue(rs.next()); assertFalse(rs.isBeforeFirst()); assertTrue(rs.isFirst()); rs.beforeFirst(); assertTrue(rs.isBeforeFirst()); while (rs.next()) { // just load datas. } assertTrue(rs.isAfterLast()); rs.absolute(4); assertFalse(rs.isAfterLast()); rs.absolute(2); assertEquals(2, rs.getInt(1)); rs.relative(2); assertEquals(4, rs.getInt(1)); assertFalse(rs.next()); rs.previous(); assertEquals(4, rs.getInt(1)); rs.relative(-3); assertEquals(1, rs.getInt(1)); assertEquals(false, rs.relative(-1)); rs.next(); assertEquals(1, rs.getInt(1)); rs.last(); assertEquals(4, rs.getInt(1)); assertEquals(4, rs.getRow()); assertTrue(rs.isLast()); rs.first(); assertEquals(1, rs.getInt(1)); assertEquals(1, rs.getRow()); rs.absolute(-1); assertEquals(4, rs.getRow()); assertEquals(4, rs.getInt(1)); } @Test public void streamingResultSetPositions() throws SQLException { Statement stmt = sharedConnection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.execute("TRUNCATE streamingressetpos"); stmt.execute("INSERT INTO streamingressetpos VALUES (1), (2), (3), (4)"); stmt.setFetchSize(Integer.MIN_VALUE); ResultSet rs = stmt.executeQuery("SELECT * FROM streamingressetpos"); assertTrue(rs.absolute(2)); assertEquals(2, rs.getRow()); assertTrue(rs.relative(-1)); assertEquals(1, rs.getRow()); rs.afterLast(); assertEquals(5, rs.getRow()); rs.beforeFirst(); assertEquals(0, rs.getRow()); assertTrue(rs.next()); assertEquals(1, rs.getRow()); assertTrue(rs.next()); assertEquals(2, rs.getRow()); } @Test public void streamingResultSetPositionsForward() throws SQLException { Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.execute("TRUNCATE streamingressetpos"); stmt.execute("INSERT INTO streamingressetpos VALUES (1), (2), (3), (4), (5), (6)"); stmt.setFetchSize(2); ResultSet rs = stmt.executeQuery("SELECT * FROM streamingressetpos"); for (int i = 1; i <= 6; i++) { assertTrue(rs.next()); assertEquals(i, rs.getRow()); } assertFalse(rs.next()); assertEquals(7, rs.getRow()); } @Test(expected = SQLException.class) public void findColumnTest() throws SQLException { ResultSet rs = sharedConnection.createStatement().executeQuery("select 1 as 'hej'"); assertEquals(1, rs.findColumn("hej")); rs.findColumn("nope"); } @Test public void getStatementTest() throws SQLException { Statement stmt1 = sharedConnection.createStatement(); ResultSet rs = stmt1.executeQuery("select 1 as 'hej'"); assertEquals(stmt1, rs.getStatement()); } @Test public void testAutocommit() throws SQLException { assertTrue(sharedConnection.getAutoCommit()); sharedConnection.setAutoCommit(false); assertFalse(sharedConnection.getAutoCommit()); /* Check that autocommit value "false" , that driver derives from server status flags * remains the same when EOF, ERROR or OK stream were received. */ sharedConnection.createStatement().executeQuery("select 1"); assertFalse(sharedConnection.getAutoCommit()); sharedConnection.createStatement().execute("set @a=1"); assertFalse(sharedConnection.getAutoCommit()); try { sharedConnection.createStatement().execute("insert into nosuchtable values(1)"); } catch (Exception e) { // eat exception } assertFalse(sharedConnection.getAutoCommit()); ResultSet rs = sharedConnection.createStatement().executeQuery("select @@autocommit"); assertTrue(rs.next()); assertEquals(0, rs.getInt(1)); sharedConnection.setAutoCommit(true); /* Check that autocommit value "true" , that driver derives from server status flags * remains the same when EOF, ERROR or OK stream were received. */ assertTrue(sharedConnection.getAutoCommit()); sharedConnection.createStatement().execute("set @a=1"); assertTrue(sharedConnection.getAutoCommit()); try { sharedConnection.createStatement().execute("insert into nosuchtable values(1)"); } catch (Exception e) { // eat exception } assertTrue(sharedConnection.getAutoCommit()); rs = sharedConnection.createStatement().executeQuery("select @@autocommit"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); /* Set autocommit value using Statement.execute */ sharedConnection.createStatement().execute("set @@autocommit=0"); assertFalse(sharedConnection.getAutoCommit()); sharedConnection.createStatement().execute("set @@autocommit=1"); assertTrue(sharedConnection.getAutoCommit()); /* Use session variable to set autocommit to 0 */ try (Connection connection = setConnection("&sessionVariables=autocommit=0")) { assertFalse(connection.getAutoCommit()); sharedConnection.setAutoCommit(true); } } @Test public void testUpdateCountSingle() throws SQLException { Statement stmt = sharedConnection.createStatement(); stmt.execute("select 1"); assertTrue(-1 == stmt.getUpdateCount()); } @Test public void testUpdateCountMulti() throws SQLException { try (Connection connection = setConnection("&allowMultiQueries=true")) { Statement stmt = connection.createStatement(); stmt.execute("select 1;select 1"); assertTrue(-1 == stmt.getUpdateCount()); stmt.getMoreResults(); assertTrue(-1 == stmt.getUpdateCount()); } } /** * CONJ-385 - stored procedure update count regression. * * @throws SQLException if connection error occur. */ @Test public void testUpdateCountProcedure() throws SQLException { CallableStatement callableStatement = sharedConnection.prepareCall("{call multiUpdateCount()}"); callableStatement.execute(); assertTrue(-1 == callableStatement.getUpdateCount()); callableStatement.getMoreResults(); assertTrue(-1 == callableStatement.getUpdateCount()); } @Test public void testConnectWithDb() throws SQLException { Assume.assumeTrue(System.getenv("SKYSQL") == null && System.getenv("SKYSQL_HA") == null); requireMinimumVersion(5, 0); try { sharedConnection.createStatement().executeUpdate("drop database test_testdrop"); } catch (Exception e) { // eat exception } try (Connection connection = setConnection("&createDatabaseIfNotExist=true&profileSql=true", "test_testdrop")) { DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getCatalogs(); boolean foundDb = false; while (rs.next()) { if (rs.getString("table_cat").equals("test_testdrop")) { foundDb = true; } } assertTrue(foundDb); sharedConnection.createStatement().executeUpdate("drop database test_testdrop"); } } @Test public void streamingResult() throws SQLException { Statement st = sharedConnection.createStatement(); for (int i = 0; i < 100; i++) { st.execute("insert into streamingtest values('aaaaaaaaaaaaaaaaaa')"); } st.setFetchSize(Integer.MIN_VALUE); try (ResultSet rs = st.executeQuery("select * from streamingtest")) { assertTrue(rs.next()); Statement st2 = sharedConnection.createStatement(); try (ResultSet rs2 = st2.executeQuery("select * from streamingtest")) { rs2.next(); } } } // Test if driver works with sql_mode= NO_BACKSLASH_ESCAPES @Test public void noBackslashEscapes() throws SQLException { requireMinimumVersion(5, 0); // super privilege is needed for this test Assume.assumeTrue(hasSuperPrivilege("NoBackslashEscapes")); Statement st = sharedConnection.createStatement(); ResultSet rs = st.executeQuery("select @@global.sql_mode"); assertTrue(rs.next()); String originalSqlMode = rs.getString(1); st.execute("set @@global.sql_mode = '" + originalSqlMode + ",NO_BACKSLASH_ESCAPES'"); st.execute("TRUNCATE testBlob2"); try { try (Connection connection = setConnection("&profileSql=true")) { PreparedStatement preparedStatement = connection.prepareStatement("insert into testBlob2(a) values(?)"); byte[] bytes = new byte[255]; for (byte i = -128; i < 127; i++) { bytes[i + 128] = i; } MariaDbBlob blob = new MariaDbBlob(bytes); preparedStatement.setBlob(1, blob); int affectedRows = preparedStatement.executeUpdate(); assertEquals(affectedRows, 1); } } finally { st.execute("set @@global.sql_mode='" + originalSqlMode + "'"); } } // Test if driver works with sql_mode= NO_BACKSLASH_ESCAPES @Test public void noBackslashEscapes2() throws SQLException { requireMinimumVersion(5, 0); // super privilege is needed for this test Assume.assumeTrue(hasSuperPrivilege("NoBackslashEscapes2")); Statement st = sharedConnection.createStatement(); ResultSet rs = st.executeQuery("select @@global.sql_mode"); assertTrue(rs.next()); String originalSqlMode = rs.getString(1); st.execute("set @@global.sql_mode = '" + originalSqlMode + ",NO_BACKSLASH_ESCAPES'"); try { try (Connection connection = setConnection("&profileSql=true")) { try (PreparedStatement preparedStatement = connection.prepareStatement("insert into testString2(a) values(?)")) { preparedStatement.setString(1, "'\\"); int affectedRows = preparedStatement.executeUpdate(); assertEquals(affectedRows, 1); } try (PreparedStatement preparedStatement = connection.prepareStatement("select * from testString2")) { rs = preparedStatement.executeQuery(); assertTrue(rs.next()); String out = rs.getString(1); assertEquals(out, "'\\"); Statement st2 = connection.createStatement(); rs = st2.executeQuery("select 'a\\b\\c'"); assertTrue(rs.next()); assertEquals("a\\b\\c", rs.getString(1)); } } } finally { st.execute("set @@global.sql_mode='" + originalSqlMode + "'"); } } // Test if driver works with sql_mode= ANSI_QUOTES @Test public void ansiQuotes() throws SQLException { // super privilege is needed for this test Assume.assumeTrue(hasSuperPrivilege("AnsiQuotes")); Statement st = sharedConnection.createStatement(); ResultSet rs = st.executeQuery("select @@global.sql_mode"); assertTrue(rs.next()); String originalSqlMode = rs.getString(1); st.execute("set @@global.sql_mode = '" + originalSqlMode + ",ANSI_QUOTES'"); st.execute("TRUNCATE testBlob2"); try { try (Connection connection = setConnection("&profileSql=true")) { PreparedStatement preparedStatement = connection.prepareStatement("insert into testBlob2(a) values(?)"); byte[] bytes = new byte[255]; for (byte i = -128; i < 127; i++) { bytes[i + 128] = i; } MariaDbBlob blob = new MariaDbBlob(bytes); preparedStatement.setBlob(1, blob); int affectedRows = preparedStatement.executeUpdate(); assertEquals(affectedRows, 1); } } finally { st.execute("set @@global.sql_mode='" + originalSqlMode + "'"); } } @Test public void unsignedTest() throws Exception { Statement st = sharedConnection.createStatement(); st.execute("insert into unsignedtest values(4294967295)"); ResultSet rs = st.executeQuery("select * from unsignedtest"); assertTrue(rs.next()); assertEquals(4294967295L, rs.getLong("unsignedtest.a")); } @Test // Bug in URL parser public void mdev3916() throws Exception { try { setConnection("&password="); } catch (SQLException ex) { // SQLException is ok because we might get for example an access denied exception if (!(ex.getMessage().contains("Could not connect: Access denied"))) { throw ex; } } } @Test public void conj1() throws Exception { Assume.assumeTrue(System.getenv("SKYSQL") == null && System.getenv("SKYSQL_HA") == null); requireMinimumVersion(5, 0); try (Connection connection = setConnection("&profileSql=true")) { Statement st = connection.createStatement(); st.setQueryTimeout(1); st.execute("select sleep(0.5)"); try { st.execute( "select * from information_schema.columns as c1, information_schema.tables, information_schema.tables as t2"); fail("must be exception here"); } catch (Exception e) { // normal exception } Statement st2 = connection.createStatement(); assertEquals(st2.getQueryTimeout(), 0); // no exception ResultSet rs = st2.executeQuery("select sleep(1.5)"); assertTrue(rs.next()); assertEquals(0, rs.getInt(1)); Statement st3 = connection.createStatement(); st3.setQueryTimeout(1); rs = st3.executeQuery("select sleep(0.1)"); assertTrue(rs.next()); assertEquals(0, rs.getInt(1)); assertEquals(st3.getQueryTimeout(), 1); } } /* Check that exception contains SQL statement, for queries with syntax errors */ @Test public void dumpQueryOnSyntaxException() { String syntacticallyWrongQuery = "banana"; try (Connection connection = setConnection("&dumpQueriesOnException")) { Statement st = connection.createStatement(); st.execute(syntacticallyWrongQuery); } catch (SQLException sqle) { assertTrue(sqle.getMessage().contains("Query is: " + syntacticallyWrongQuery)); } } /* Check that query contains SQL statement, if dumpQueryOnException is true */ @Test public void dumpQueryOnException() throws Exception { try (Connection connection = setConnection("&profileSql=true&dumpQueriesOnException=true")) { String selectFromNonExistingTable = "select * from banana"; try { Statement st = connection.createStatement(); st.execute(selectFromNonExistingTable); } catch (SQLException sqle) { assertTrue(sqle.getMessage().contains("Query is: " + selectFromNonExistingTable)); } } } /* Check that query does not contains SQL statement by default */ @Test public void shouldNotDumpQueryOnExceptionByDefault() { String selectFromNonExistingTable = "select * from mango"; try { Statement st = sharedConnection.createStatement(); st.execute(selectFromNonExistingTable); } catch (SQLException sqle) { assertFalse(sqle.getCause().getMessage().contains("Query is: " + selectFromNonExistingTable)); } } /* CONJ-14 * getUpdateCount(), getResultSet() should indicate "no more results" with * (getUpdateCount() == -1 && getResultSet() == null) */ @Test public void conj14() throws Exception { Statement st = sharedConnection.createStatement(); /* 1. Test update statement */ st.execute("use " + database); assertEquals(0, st.getUpdateCount()); /* No more results */ assertFalse(st.getMoreResults()); assertEquals(-1, st.getUpdateCount()); assertEquals(null, st.getResultSet()); /* 2. Test select statement */ st.execute("select 1"); assertEquals(-1, st.getUpdateCount()); assertTrue(st.getResultSet() != null); /* No More results */ assertFalse(st.getMoreResults()); assertEquals(-1, st.getUpdateCount()); assertEquals(null, st.getResultSet()); /* Test batch */ try (Connection connection = setConnection("&profileSql=true&allowMultiQueries=true")) { st = connection.createStatement(); /* 3. Batch with two SELECTs */ st.execute("select 1;select 2"); /* First result (select)*/ assertEquals(-1, st.getUpdateCount()); assertTrue(st.getResultSet() != null); /* has more results */ assertTrue(st.getMoreResults()); /* Second result (select) */ assertEquals(-1, st.getUpdateCount()); assertTrue(st.getResultSet() != null); /* no more results */ assertFalse(st.getMoreResults()); assertEquals(-1, st.getUpdateCount()); assertEquals(null, st.getResultSet()); /* 4. Batch with a SELECT and non-SELECT */ st.execute("select 1; use " + database); /* First result (select)*/ assertEquals(-1, st.getUpdateCount()); assertTrue(st.getResultSet() != null); /* Next result is no ResultSet */ assertFalse(st.getMoreResults()); assertNull(st.getResultSet()); assertEquals(0, st.getUpdateCount()); /* no more results */ assertFalse(st.getMoreResults()); assertEquals(-1, st.getUpdateCount()); assertEquals(null, st.getResultSet()); } } @Test public void conj25() throws Exception { try (Statement stmt = sharedConnection.createStatement()) { StringBuilder st = new StringBuilder("INSERT INTO conj25 VALUES (REPEAT('a',1024))"); for (int i = 1; i <= 100; i++) { st.append(",(REPEAT('a',1024))"); } stmt.setFetchSize(Integer.MIN_VALUE); stmt.execute(st.toString()); stmt.executeQuery("SELECT * FROM conj25 a, conj25 b"); } } @Test public void namedPipe() { try (ResultSet rs = sharedConnection.createStatement().executeQuery("select @@named_pipe,@@socket")) { assertTrue(rs.next()); if (rs.getBoolean(1)) { String namedPipeName = rs.getString(2); // skip test if no namedPipeName was obtained because then we do not use a socket connection Assume.assumeTrue(namedPipeName != null); try (Connection connection = setConnection("&pipe=" + namedPipeName)) { Statement stmt = connection.createStatement(); try (ResultSet rs2 = stmt.executeQuery("SELECT 1")) { assertTrue(rs2.next()); } } } } catch (SQLException e) { // not on windows } } /** * CONJ-435 : "All pipe instances are busy" exception on multiple connections to the same named * pipe. * * @throws Exception if any error occur. */ @Test public void namedPipeBusyTest() throws Exception { Assume.assumeFalse(!isMariadbServer() && minVersion(8, 0, 0)); try { ResultSet rs = sharedConnection.createStatement().executeQuery("select @@named_pipe,@@socket"); assertTrue(rs.next()); if (rs.getBoolean(1)) { String namedPipeName = rs.getString(2); // skip test if no namedPipeName was obtained because then we do not use a socket connection Assume.assumeTrue(namedPipeName != null); ExecutorService exec = Executors.newFixedThreadPool(100); // check blacklist shared for (int i = 0; i < 100; i++) { Thread.sleep(2); exec.execute( new ConnectWithPipeThread( "jdbc:mariadb:///testj?user=" + username + "&pipe=" + namedPipeName + "&connectTimeout=500")); } // wait for thread endings exec.shutdown(); exec.awaitTermination(30, TimeUnit.SECONDS); assertEquals(namedPipeBusyTestError, 0); } } catch (SQLException e) { assertTrue(e.getMessage(), e.getMessage().contains("Unknown system variable 'named_pipe'")); } } /** CONJ-293 : permit connection to named pipe when no host is defined. */ @Test public void namedPipeWithoutHost() { try (ResultSet rs = sharedConnection.createStatement().executeQuery("select @@named_pipe,@@socket")) { assertTrue(rs.next()); if (rs.getBoolean(1)) { String namedPipeName = rs.getString(2); // skip test if no namedPipeName was obtained because then we do not use a socket connection Assume.assumeTrue(namedPipeName != null); try (Connection connection = DriverManager.getConnection( "jdbc:mariadb:///testj?user=" + username + "&pipe=" + namedPipeName)) { Statement stmt = connection.createStatement(); try (ResultSet rs2 = stmt.executeQuery("SELECT 1")) { assertTrue(rs2.next()); } } } } catch (SQLException e) { // not on windows } } @Test public void localSocket() throws Exception { requireMinimumVersion(5, 1); Assume.assumeTrue(System.getenv("TRAVIS") == null && System.getenv("DOCKER_SOCKET") == null); Assume.assumeTrue(isLocalConnection("localSocket")); Statement st = sharedConnection.createStatement(); ResultSet rs = st.executeQuery("select @@version_compile_os,@@socket"); if (!rs.next()) { return; } System.out.println("os:" + rs.getString(1) + " path:" + rs.getString(2)); String os = rs.getString(1); if (os.toLowerCase().startsWith("win") || Platform.isWindows()) { return; } String path = rs.getString(2); st.execute("CREATE USER testSocket@'localhost' IDENTIFIED BY 'MySup5%rPassw@ord'"); st.execute("GRANT SELECT on *.* to testSocket@'localhost' IDENTIFIED BY 'MySup5%rPassw@ord'"); st.execute("FLUSH PRIVILEGES"); String connString = connU + "?user=testSocket&password=MySup5%rPassw@ord&localSocket=" + path; try (Connection connection = openConnection(connString, null)) { rs = connection.createStatement().executeQuery("select 1"); assertTrue(rs.next()); } st.execute("DROP user testSocket@'localhost'"); } @Test public void sharedMemory() throws Exception { requireMinimumVersion(5, 1); Assume.assumeFalse(!isMariadbServer() && minVersion(8, 0, 0)); Statement st = sharedConnection.createStatement(); ResultSet rs = st.executeQuery("select @@version_compile_os"); if (!rs.next()) { return; } String os = rs.getString(1); if (!os.toLowerCase().startsWith("win")) { return; // skip test on non-Windows } try { rs = st.executeQuery("select @@shared_memory,@@shared_memory_base_name"); if (!rs.next()) { return; } if (!rs.getString(1).equals("1")) { return; } } catch (SQLException e) { return; } String shmBaseName = rs.getString(2); try (Connection connection = setConnection("&sharedMemory=" + shmBaseName + "&profileSql=true")) { rs = connection.createStatement().executeQuery("select repeat('a',100000)"); assertTrue(rs.next()); assertEquals(100000, rs.getString(1).length()); char[] arr = new char[100000]; Arrays.fill(arr, 'a'); rs = connection.createStatement().executeQuery("select '" + new String(arr) + "'"); assertTrue(rs.next()); assertEquals(100000, rs.getString(1).length()); } } @Test public void preparedStatementToString() throws Exception { try (PreparedStatement ps = sharedConnection.prepareStatement("SELECT ?,?,?,?,?,?,?")) { ps.setInt(1, 1); ps.setBigDecimal(2, new BigDecimal("1")); ps.setString(3, "one"); ps.setBoolean(4, true); Calendar calendar = new GregorianCalendar(1972, 3, 22); ps.setDate(5, new Date(calendar.getTime().getTime())); ps.setDouble(6, 1.5); ps.setByte(7, (byte) 0xfe); assertEquals( "sql : 'SELECT ?,?,?,?,?,?,?', parameters : [1,1,'one',true,'1972-04-22',1.5,-2]", ps.toString()); } } /* Test that CLOSE_CURSORS_ON_COMMIT is silently ignored, and HOLD_CURSORS_OVER_COMMIT is actually used*/ @Test public void resultSetHoldability() throws Exception { Statement st = sharedConnection.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, st.getResultSetHoldability()); PreparedStatement ps = sharedConnection.prepareStatement( "SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, ps.getResultSetHoldability()); ResultSet rs = ps.executeQuery(); assertEquals(rs.getHoldability(), ResultSet.HOLD_CURSORS_OVER_COMMIT); CallableStatement cs = sharedConnection.prepareCall( "{CALL foo}", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT); assertEquals(cs.getResultSetHoldability(), ResultSet.HOLD_CURSORS_OVER_COMMIT); } @Test public void emptyBatch() throws Exception { Statement st = sharedConnection.createStatement(); st.executeBatch(); } @Test public void createDbWithSpacesTest() throws SQLException { try (Connection connection = setConnection("&createDatabaseIfNotExist=true&profileSql=true", "test with spaces")) { DatabaseMetaData dbmd = connection.getMetaData(); ResultSet rs = dbmd.getCatalogs(); boolean foundDb = false; while (rs.next()) { if (rs.getString("table_cat").equals("test with spaces")) { foundDb = true; } } assertTrue("database \"test with spaces\" not created !?", foundDb); connection.createStatement().execute("drop database `test with spaces`"); } } /** * CONJ-275 : Streaming resultSet with no result must not have a next() value to true. * * @throws Exception exception */ @Test public void checkStreamingWithoutResult() throws Exception { PreparedStatement pstmt = sharedConnection.prepareStatement("SELECT * FROM conj275 where a = ?"); pstmt.setFetchSize(10); pstmt.setString(1, "no result"); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { fail("must not have result value"); } } /** * CONJ-497 - Long escapable string. * * @throws SQLException exception */ @Test public void testLongEscapes() throws SQLException { // 40m, because escaping will double the send byte numbers Assume.assumeTrue(checkMaxAllowedPacketMore40m("testLongEscapes")); try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("INSERT into testLongEscapes values (?)")) { byte[] arr = new byte[20_000_000]; Arrays.fill(arr, (byte) '\''); preparedStatement.setBytes(1, arr); preparedStatement.execute(); Arrays.fill(arr, (byte) '\"'); preparedStatement.setBytes(1, arr); preparedStatement.execute(); } Statement stmt = sharedConnection.createStatement(); try (ResultSet rs = stmt.executeQuery("select length(t1) from testLongEscapes")) { assertTrue(rs.next()); assertEquals(20_000_000, rs.getInt(1)); assertTrue(rs.next()); assertEquals(20_000_000, rs.getInt(1)); assertFalse(rs.next()); } } @Test public void testRollbackOnClose() throws SQLException { try (Connection connection = setConnection()) { Statement stmt = connection.createStatement(); stmt.executeUpdate("INSERT INTO testRollbackOnClose (test) VALUES ('heja')"); connection.setAutoCommit(false); stmt.executeUpdate("INSERT INTO testRollbackOnClose (test) VALUES ('japp')"); } try (Connection connection = setConnection()) { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) FROM testRollbackOnClose"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); } } @Test public void testAutoCommit() throws SQLException { try (Connection connection = setConnection()) { assertTrue(connection.getAutoCommit()); Statement stmt = connection.createStatement(); stmt.executeUpdate("INSERT INTO testAutoCommit (test) VALUES ('heja')"); } try (Connection connection = setConnection("&autocommit=false")) { assertFalse(connection.getAutoCommit()); Statement stmt = connection.createStatement(); stmt.executeUpdate("INSERT INTO testAutoCommit (test) VALUES ('japp')"); ResultSet rs = stmt.executeQuery("SELECT count(*) FROM testAutoCommit"); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); } try (Connection connection = setConnection()) { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT count(*) FROM testAutoCommit"); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); } } @Test public void databaseType() throws SQLException { Assume.assumeTrue(System.getenv("SKYSQL") == null && System.getenv("SKYSQL_HA") == null); Assume.assumeTrue(System.getenv("TRAVIS") != null); boolean isMysql = System.getenv("AURORA") != null || System.getenv("DB").contains("mysql"); assertEquals( isMysql ? "MySQL" : "MariaDB", sharedConnection.getMetaData().getDatabaseProductName()); if (!isMysql) { try (Connection connection = setConnection("&useMysqlMetadata=true")) { assertEquals("MySQL", connection.getMetaData().getDatabaseProductName()); } } } @Test public void deregisterDriver() throws Throwable { try (Connection conn = setConnection()) { Statement stmt = conn.createStatement(); stmt.execute("CREATE TEMPORARY TABLE forcePoolClose(id int)"); // force use (and launch) of pipeline thread pool try (PreparedStatement p = conn.prepareStatement("INSERT INTO forcePoolClose(id) VALUES (?)")) { p.setInt(1, 1); p.addBatch(); p.setInt(1, 2); p.addBatch(); p.executeBatch(); } new Thread( () -> { try { stmt.setQueryTimeout(1); stmt.execute("select sleep(0.5)"); } catch (SQLException sqle) { // eat } }) .start(); } // force de-registration for (java.sql.Driver drv : Collections.list(DriverManager.getDrivers())) { if (drv.acceptsURL("jdbc:mariadb:")) { DriverManager.deregisterDriver(drv); Thread.sleep(1000); Iterator it = Thread.getAllStackTraces().keySet().iterator(); Thread thread; while (it.hasNext()) { thread = it.next(); if (thread.getName().contains("MariaDb-bulk-")) { for (StackTraceElement ste : thread.getStackTrace()) { System.out.println(ste); } assertFalse(thread.isAlive()); } } } } // force registration DriverManager.registerDriver(new org.mariadb.jdbc.Driver(), new DeRegister()); // ensure registration is ok and new thread pool is created if needed try (Connection conn = setConnection()) { Statement stmt = conn.createStatement(); stmt.execute("CREATE TEMPORARY TABLE forcePoolClose(id int)"); // force use (and launch) of pipeline thread pool try (PreparedStatement p = conn.prepareStatement("INSERT INTO forcePoolClose(id) VALUES (?)")) { p.setInt(1, 1); p.addBatch(); p.setInt(1, 2); p.addBatch(); p.executeBatch(); } } } private static class ConnectWithPipeThread implements Runnable { private final String url; public ConnectWithPipeThread(String url) { this.url = url; } @Override public void run() { Connection connection = null; try { connection = DriverManager.getConnection(url); Thread.sleep(1000); } catch (SQLException | InterruptedException e) { namedPipeBusyTestError += 1; e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } } }