ConnectionTest.java
// SPDX-License-Identifier: LGPL-2.1-or-later
// Copyright (c) 2012-2014 Monty Program Ab
// Copyright (c) 2015-2025 MariaDB Corporation Ab
package org.mariadb.jdbc.integration;
import static org.junit.jupiter.api.Assertions.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.Statement;
import java.util.*;
import java.util.concurrent.Executor;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import org.junit.jupiter.api.*;
import org.mariadb.jdbc.*;
import org.mariadb.jdbc.integration.util.SocketFactoryBasicTest;
import org.mariadb.jdbc.integration.util.SocketFactoryTest;
import org.mariadb.jdbc.util.constants.Capabilities;
@DisplayName("Connection Test")
public class ConnectionTest extends Common {
@BeforeAll
public static void beforeAll2() throws SQLException {
createSequenceTables();
}
@Test
public void isValid() throws SQLException {
Connection sharedConn = DriverManager.getConnection(mDefUrl);
assertTrue(sharedConn.isValid(2000));
sharedConn.close();
assertFalse(sharedConn.isValid(2000));
}
@Test
public void tcpKeepAlive() throws SQLException {
try (Connection con = createCon("&tcpKeepAlive=false")) {
con.isValid(1);
}
}
@Test
void isValidWrongValue() {
try {
sharedConn.isValid(-2000);
fail("most have thrown an error");
} catch (SQLException e) {
assertTrue(e.getMessage().contains("the value supplied for timeout is negative"));
}
}
@Test
void missingHost() {
assertThrowsContains(
SQLException.class,
() -> DriverManager.getConnection("jdbc:mariadb:///db"),
"host, pipe or local socket must be set to connect socket");
assertThrowsContains(
SQLException.class,
() -> DriverManager.getConnection("jdbc:mariadb:///db?socketFactory=test"),
"host, pipe or local socket must be set to connect socket");
}
@Test
void socketTimeout() throws SQLException {
Assumptions.assumeTrue(!isMaxscale());
try (Connection con = createCon("&socketTimeout=50")) {
assertEquals(50, con.getNetworkTimeout());
Statement stmt = con.createStatement();
stmt.execute("SELECT 1");
Common.assertThrowsContains(SQLException.class, () -> stmt.execute("SELECT SLEEP(0.1)"), "");
}
try (Connection con = createCon("&socketTimeout=500")) {
assertEquals(500, con.getNetworkTimeout());
Statement stmt = con.createStatement();
stmt.execute("SELECT SLEEP(0.1)");
Common.assertThrowsContains(SQLException.class, () -> stmt.execute("SELECT SLEEP(1)"), "");
}
try (Connection con = createCon("&socketTimeout=0")) {
assertEquals(0, con.getNetworkTimeout());
Statement stmt = con.createStatement();
stmt.execute("SELECT SLEEP(0.5)");
}
}
@Test
public void autoCommit() throws SQLException {
Connection con = DriverManager.getConnection(mDefUrl);
assertTrue(con.getAutoCommit());
con.setAutoCommit(false);
assertFalse(con.getAutoCommit());
con.setAutoCommit(false);
assertFalse(con.getAutoCommit());
con.setAutoCommit(true);
assertTrue(con.getAutoCommit());
con.setAutoCommit(true);
assertTrue(con.getAutoCommit());
Statement stmt = con.createStatement();
stmt.execute("SET autocommit=false");
assertFalse(con.getAutoCommit());
con.close();
}
@Test
public void initSQL() throws SQLException {
try (Connection con = createCon("&initSql=SET @myVar='YourVar'")) {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT @myVar");
assertTrue(rs.next());
assertEquals("YourVar", rs.getString(1));
}
}
@Test
public void nativeSQL() throws SQLException {
String[] inputs =
new String[] {
"select {fn TIMESTAMPDIFF ( SQL_TSI_HOUR, {fn convert('SQL_', SQL_INTEGER)})}, {fn"
+ " TIMESTAMPDIFF (HOUR, {fn convert ('sQL_' , SQL_INTEGER)})}",
"{call foo({fn now()})} //end",
"{call foo({fn '{' now()} /* {test}# \"'\" */) \"\\\"'#\" '\"\\''} #{test2}",
"{ call foo({fn now()})}",
"{\r\ncall foo({fn now()})}",
"{\r\n call foo({fn now()})}",
"{call foo(/*{fn now()}*/)}",
"{CALL foo({fn now() /* -- * */ -- test \n })}",
"{?=call foo({fn now()})}",
"select {fn dayname ({fn abs({fn now()})})}",
"{d '1997-05-24'}",
"{d'1997-05-24'}",
"{\nt'10:30:29'}",
"{t '10:30:29'}",
"{t'10:30:29'}",
"{ts '1997-05-24 10:30:29.123'}",
"{ts'1997-05-24 10:30:29.123'}",
"'{string data with { or } will not be altered'",
"`{string data with { or } will not be altered`",
"-- Also note that you can safely include { and } in comments",
"SELECT * FROM {oj TABLE1 LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930}"
};
String[] outputs =
new String[] {
"select TIMESTAMPDIFF ( HOUR, convert('SQL_', INTEGER)), TIMESTAMPDIFF (HOUR, convert "
+ " ('sQL_' , INTEGER))",
"call foo(now()) //end",
"call foo('{' now() /* {test}# \"'\" */) \"\\\"'#\" '\"\\'' #{test2}",
"call foo(now())",
"call foo(now())",
"call foo(now())",
"call foo(/*{fn now()}*/)",
"CALL foo(now() /* -- * */ -- test \n )",
"?=call foo(now())",
"select dayname (abs(now()))",
"'1997-05-24'",
"'1997-05-24'",
"'10:30:29'",
"'10:30:29'",
"'10:30:29'",
"'1997-05-24 10:30:29.123'",
"'1997-05-24 10:30:29.123'",
"'{string data with { or } will not be altered'",
"`{string data with { or } will not be altered`",
"-- Also note that you can safely include { and } in comments",
"SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON DEPT_NO = 003420930"
};
for (int i = 0; i < inputs.length; i++) {
assertEquals(outputs[i], sharedConn.nativeSQL(inputs[i]));
}
assertEquals(
"INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('abc\\\\', '{\"data\":"
+ " \"test\"}')",
sharedConn.nativeSQL(
"INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('abc\\\\', '{\"data\":"
+ " \"test\"}')"));
try {
sharedConn.nativeSQL("{call foo({fn now())}");
fail("most have thrown an error");
} catch (SQLException e) {
assertTrue(
e.getMessage().contains("Invalid escape sequence , missing closing '}' character in '"));
}
try {
sharedConn.nativeSQL("{call foo({unknown} )}");
fail("most have thrown an error");
} catch (SQLException e) {
assertTrue(e.getMessage().contains("unknown escape sequence {unknown}"));
}
}
@Test
public void nativeSQLNoBackSlash() throws SQLException {
try (Connection con = createCon()) {
java.sql.Statement stmt = con.createStatement();
stmt.execute("SET sql_mode = concat(@@sql_mode,',NO_BACKSLASH_ESCAPES')");
assertEquals("call foo('{' now())", con.nativeSQL("{call foo({fn '{' now()})}"));
}
}
@Test
public void nativeSqlTest() throws SQLException {
String exp;
if (isMariaDBServer() || minVersion(8, 0, 17)) {
exp =
"SELECT convert(foo(a,b,c), SIGNED INTEGER)"
+ ", convert(convert(?, CHAR), SIGNED INTEGER)"
+ ", 1=?"
+ ", 1=?"
+ ", convert(?, SIGNED INTEGER )"
+ ", convert (?, SIGNED INTEGER )"
+ ", convert(?, UNSIGNED INTEGER)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, DOUBLE)"
+ ", convert(?, DOUBLE)"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DATETIME)"
+ ", convert(?, DATETIME)";
} else {
exp =
"SELECT convert(foo(a,b,c), SIGNED INTEGER)"
+ ", convert(convert(?, CHAR), SIGNED INTEGER)"
+ ", 1=?"
+ ", 1=?"
+ ", convert(?, SIGNED INTEGER )"
+ ", convert (?, SIGNED INTEGER )"
+ ", convert(?, UNSIGNED INTEGER)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, BINARY)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", convert(?, CHAR)"
+ ", 0.0+?"
+ ", 0.0+?"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DECIMAL)"
+ ", convert(?, DATETIME)"
+ ", convert(?, DATETIME)";
}
assertEquals(
exp,
sharedConn.nativeSQL(
"SELECT {fn convert(foo(a,b,c), SQL_BIGINT)}"
+ ", {fn convert({fn convert(?, SQL_VARCHAR)}, SQL_BIGINT)}"
+ ", {fn convert(?, SQL_BOOLEAN )}"
+ ", {fn convert(?, BOOLEAN)}"
+ ", {fn convert(?, SMALLINT )}"
+ ", {fn convert (?, TINYINT )}"
+ ", {fn convert(?, SQL_BIT)}"
+ ", {fn convert(?, SQL_BLOB)}"
+ ", {fn convert(?, SQL_VARBINARY)}"
+ ", {fn convert(?, SQL_LONGVARBINARY)}"
+ ", {fn convert(?, SQL_ROWID)}"
+ ", {fn convert(?, SQL_BINARY)}"
+ ", {fn convert(?, SQL_NCHAR)}"
+ ", {fn convert(?, SQL_CLOB)}"
+ ", {fn convert(?, SQL_NCLOB)}"
+ ", {fn convert(?, SQL_DATALINK)}"
+ ", {fn convert(?, SQL_VARCHAR)}"
+ ", {fn convert(?, SQL_NVARCHAR)}"
+ ", {fn convert(?, SQL_LONGVARCHAR)}"
+ ", {fn convert(?, SQL_LONGNVARCHAR)}"
+ ", {fn convert(?, SQL_SQLXML)}"
+ ", {fn convert(?, SQL_LONGNCHAR)}"
+ ", {fn convert(?, SQL_CHAR)}"
+ ", {fn convert(?, SQL_FLOAT)}"
+ ", {fn convert(?, SQL_DOUBLE)}"
+ ", {fn convert(?, SQL_DECIMAL)}"
+ ", {fn convert(?, SQL_REAL)}"
+ ", {fn convert(?, SQL_NUMERIC)}"
+ ", {fn convert(?, SQL_TIMESTAMP)}"
+ ", {fn convert(?, SQL_DATETIME)}"));
}
@Test
public void doubleBackslash() throws SQLException {
java.sql.Statement stmt = sharedConn.createStatement();
stmt.setEscapeProcessing(true);
stmt.execute("DROP TABLE IF EXISTS TEST_SYNTAX_ERROR");
stmt.execute(
"CREATE TABLE TEST_SYNTAX_ERROR("
+ " id INTEGER unsigned NOT NULL AUTO_INCREMENT, "
+ " str_value MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL,"
+ " json_value MEDIUMTEXT CHARACTER SET utf8mb4 NOT NULL, "
+ " PRIMARY KEY ( id ))");
stmt.execute(
"INSERT INTO TEST_SYNTAX_ERROR(str_value, json_value) VALUES ('abc\\\\', '{\"data\":"
+ " \"test\"}')");
}
@Test
public void databaseStateChange() throws SQLException {
Assumptions.assumeTrue(
(isMariaDBServer() && minVersion(10, 2, 0)) || (!isMariaDBServer() && minVersion(5, 7, 0)));
try (Connection connection = createCon()) {
try (Statement stmt = connection.createStatement()) {
stmt.execute("drop database if exists _test_db");
stmt.execute("create database _test_db");
ResultSet rs = stmt.executeQuery("SELECT DATABASE()");
rs.next();
assertEquals(rs.getString(1), connection.getCatalog());
stmt.execute("USE _test_db");
assertEquals("_test_db", connection.getCatalog());
stmt.execute("drop database _test_db");
}
}
}
@Test
public void databaseNoStateChange() throws SQLException {
try (Connection connection = createCon("&disableSessionTracking=true")) {
assertEquals(database, connection.getCatalog());
try (Statement stmt = connection.createStatement()) {
stmt.execute("drop database if exists _test_db");
stmt.execute("create database _test_db");
stmt.execute("USE _test_db");
assertEquals("_test_db", connection.getCatalog());
stmt.execute("drop database _test_db");
}
}
}
@Test
public void catalog() throws SQLException {
Assumptions.assumeTrue(
(isMariaDBServer() && minVersion(10, 2, 0)) || (!isMariaDBServer() && minVersion(5, 7, 0)));
try (Connection connection = createCon()) {
try (Statement stmt = connection.createStatement()) {
stmt.execute("drop database if exists _test_db");
stmt.execute("create database _test_db");
ResultSet rs = stmt.executeQuery("SELECT DATABASE()");
rs.next();
String initialCatalog = connection.getCatalog();
assertEquals(rs.getString(1), initialCatalog);
connection.setCatalog(initialCatalog);
assertEquals(initialCatalog, connection.getCatalog());
connection.setCatalog("_test_db");
assertEquals("_test_db", connection.getCatalog());
stmt.execute("USE _test_db");
assertEquals("_test_db", connection.getCatalog());
connection.setCatalog(null);
assertEquals("_test_db", connection.getCatalog());
connection.setCatalog("_test_db");
assertEquals("_test_db", connection.getCatalog());
stmt.execute("drop database _test_db");
assertTrue(connection.getCatalog() == null || "_test_db".equals(connection.getCatalog()));
}
}
}
@Test
public void checkFixedData() throws SQLException {
sharedConn.unwrap(java.sql.Connection.class);
Common.assertThrowsContains(
SQLException.class,
() -> sharedConn.unwrap(String.class),
"The receiver is not a wrapper for java.lang.String");
assertTrue(sharedConn.createBlob() instanceof Blob);
assertTrue(sharedConn.createClob() instanceof Clob);
assertTrue(sharedConn.createNClob() instanceof NClob);
assertThrows(SQLException.class, () -> sharedConn.createSQLXML());
assertNull(sharedConn.createArrayOf("", null));
assertThrows(SQLException.class, () -> sharedConn.createArrayOf("string", "ddd"));
assertThrows(SQLException.class, () -> sharedConn.createStruct("", null));
assertNull(sharedConn.getSchema());
sharedConn.setSchema("fff");
assertNull(sharedConn.getSchema());
}
@Test
public void clientInfo() throws SQLException {
assertTrue(sharedConn.getClientInfo().isEmpty());
sharedConn.setClientInfo("some", "value");
Properties props = new Properties();
props.put("another", "one");
props.put("and another", "two");
sharedConn.setClientInfo(props);
assertEquals(3, sharedConn.getClientInfo().size());
assertEquals("value", sharedConn.getClientInfo("some"));
assertNull(sharedConn.getClientInfo("some33"));
}
@Test
public void abortTestAlreadyClosed() throws SQLException {
Connection connection = createCon();
connection.close();
Executor executor = Runnable::run;
connection.abort(executor);
}
@Test
public void abortTestNoExecutor() {
try {
sharedConn.abort(null);
} catch (SQLException e) {
assertTrue(e.getMessage().contains("Cannot abort the connection: null executor passed"));
}
}
@Test
public void abortClose() throws Throwable {
Connection connection = createCon();
Statement stmt = connection.createStatement();
stmt.setFetchSize(1);
ResultSet rs =
stmt.executeQuery(
"select * from information_schema.columns as c1, "
+ "information_schema.tables, information_schema.tables as t2");
assertTrue(rs.next());
connection.abort(Runnable::run);
// must still work
Thread.sleep(20);
try {
assertTrue(rs.next());
fail();
} catch (SQLException sqle) {
assertTrue(sqle.getMessage().contains("Operation not permit on a closed resultSet"));
}
}
@Test
public void verificationAbort() throws Throwable {
Timer timer = new Timer();
try (Connection connection = createCon()) {
timer.schedule(
new TimerTask() {
@Override
public void run() {
try {
connection.abort(Runnable::run);
} catch (SQLException sqle) {
fail(sqle.getMessage());
}
}
},
10);
Statement stmt = connection.createStatement();
assertThrows(
SQLException.class,
() ->
stmt.executeQuery(
"select * from information_schema.columns as c1, information_schema.tables,"
+ " information_schema.tables as t2"));
}
}
@Test
public void networkTimeoutTest() throws SQLException {
try (Connection connection = createCon()) {
assertEquals(0, connection.getNetworkTimeout());
int timeout = 1000;
Executor executor = Runnable::run;
connection.setNetworkTimeout(executor, timeout);
connection.isValid(2);
assertEquals(timeout, connection.getNetworkTimeout());
try {
Statement stmt = connection.createStatement();
stmt.execute("select sleep(2)");
fail("Network timeout is " + timeout / 1000 + "sec, but slept for 2 sec");
} catch (SQLException sqlex) {
assertTrue(connection.isClosed());
}
}
}
@Test
public void testConnectionAttributes() throws Exception {
// check if performance_schema is ON
Statement stmt = sharedConn.createStatement();
ResultSet res = stmt.executeQuery("show variables like 'performance_schema'");
if (res.next()) {
Assumptions.assumeFalse(res.getString("Value").equals("OFF"));
try (Connection connection = createCon()) {
Statement attributeStatement = connection.createStatement();
ResultSet result =
attributeStatement.executeQuery(
"select * from performance_schema.session_connect_attrs where"
+ " ATTR_NAME='_server_host' and processlist_id = connection_id()");
while (result.next()) {
String strVal = result.getString("ATTR_VALUE");
assertEquals(Configuration.parse(mDefUrl).addresses().get(0).host, strVal);
}
} catch (SQLException e) {
// eat exception when performance_schema.session_connect_attrs doesn't exists
}
}
}
@Test
public void isolationLevel() throws SQLException {
java.sql.Connection connection = createCon();
int[] levels =
new int[] {
java.sql.Connection.TRANSACTION_READ_UNCOMMITTED,
java.sql.Connection.TRANSACTION_READ_COMMITTED,
java.sql.Connection.TRANSACTION_SERIALIZABLE,
java.sql.Connection.TRANSACTION_REPEATABLE_READ
};
for (int level : levels) {
connection.setTransactionIsolation(level);
assertEquals(level, connection.getTransactionIsolation());
connection.setTransactionIsolation(level);
assertEquals(level, connection.getTransactionIsolation());
}
connection.close();
assertThrows(
SQLException.class,
() -> connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED));
try (java.sql.Connection con2 = createCon()) {
try {
con2.setTransactionIsolation(10_000);
fail();
} catch (SQLException e) {
assertTrue(e.getMessage().contains("Unsupported transaction isolation level"));
}
}
}
@Test
public void confDefaultIsolationLevel() throws SQLException {
int[] levels =
new int[] {
java.sql.Connection.TRANSACTION_READ_UNCOMMITTED,
java.sql.Connection.TRANSACTION_READ_COMMITTED,
java.sql.Connection.TRANSACTION_SERIALIZABLE,
java.sql.Connection.TRANSACTION_REPEATABLE_READ
};
String[] levelStr =
new String[] {"READ-UNCOMMITTED", "READ-COMMITTED", "SERIALIZABLE", "REPEATABLE-READ"};
for (int i = 0; i < levels.length; i++) {
try (Connection connection = createCon("transactionIsolation=" + levelStr[i])) {
assertEquals(levels[i], connection.getTransactionIsolation());
}
}
}
@Test
public void readOnlyConnection() throws SQLException {
String url =
password == null || password.isEmpty()
? String.format(
"jdbc:mariadb://address=(host=%s)(port=%s)(type=replica)/%s?user=%s%s",
hostname, port, database, user, defaultOther)
: String.format(
"jdbc:mariadb://address=(host=%s)(port=%s)(type=replica)/%s?user=%s&password=%s%s",
hostname, port, database, user, password, defaultOther);
try (Connection connection = DriverManager.getConnection(url)) {
Statement stmt = connection.createStatement();
boolean canUseTransactionReadOnly =
(isMariaDBServer()
&& sharedConn.getContext().getVersion().versionGreaterOrEqual(11, 1, 1))
|| (!sharedConn.getContext().getVersion().isMariaDBServer()
&& ((sharedConn.getContext().getVersion().getMajorVersion() >= 8
&& sharedConn.getContext().getVersion().versionGreaterOrEqual(8, 0, 3))
|| (sharedConn.getContext().getVersion().getMajorVersion() < 8
&& sharedConn
.getContext()
.getVersion()
.versionGreaterOrEqual(5, 7, 20))));
ResultSet rs =
stmt.executeQuery(
"SELECT @@" + (canUseTransactionReadOnly ? "transaction_read_only" : "tx_read_only"));
assertTrue(rs.next());
assertTrue(rs.getBoolean(1));
}
}
@Test
public void savepointTest() throws SQLException {
try (Connection con = createCon()) {
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMPORARY TABLE spt(test varchar(10))");
con.setAutoCommit(false);
stmt.executeUpdate("INSERT INTO spt values('hej1')");
stmt.executeUpdate("INSERT INTO spt values('hej2')");
Savepoint savepoint = con.setSavepoint("ye`\\\\`p");
stmt.executeUpdate("INSERT INTO spt values('hej3')");
stmt.executeUpdate("INSERT INTO spt values('hej4')");
assertEquals("ye``\\\\``p", savepoint.getSavepointName());
Common.assertThrowsContains(
SQLException.class,
savepoint::getSavepointId,
"Cannot retrieve savepoint id of a named savepoint");
con.rollback(savepoint);
stmt.executeUpdate("INSERT INTO spt values('hej5')");
stmt.executeUpdate("INSERT INTO spt values('hej6')");
con.commit();
ResultSet rs = stmt.executeQuery("SELECT * FROM spt");
assertTrue(rs.next());
assertEquals("hej1", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej2", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej5", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej6", rs.getString(1));
assertFalse(rs.next());
}
}
@Test
public void netWorkTimeout() throws SQLException {
Connection con = createCon();
Common.assertThrowsContains(
SQLException.class,
() -> con.setNetworkTimeout(Runnable::run, -200),
"Connection.setNetworkTimeout cannot be called with a negative timeout");
con.close();
Common.assertThrowsContains(
SQLException.class,
() -> con.setNetworkTimeout(Runnable::run, 200),
"Connection.setNetworkTimeout cannot be called on a closed connection");
}
@Test
public void savepointUnname() throws SQLException {
try (Connection con = createCon()) {
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMPORARY TABLE spt(test varchar(10))");
con.setAutoCommit(false);
stmt.executeUpdate("INSERT INTO spt values('hej1')");
stmt.executeUpdate("INSERT INTO spt values('hej2')");
Savepoint savepoint = con.setSavepoint();
stmt.executeUpdate("INSERT INTO spt values('hej3')");
stmt.executeUpdate("INSERT INTO spt values('hej4')");
assertTrue(savepoint.getSavepointId() > 0);
Common.assertThrowsContains(
SQLException.class,
savepoint::getSavepointName,
"Cannot retrieve savepoint name of an unnamed savepoint");
con.rollback(savepoint);
Common.assertThrowsContains(
SQLException.class, () -> con.rollback(new MySavepoint()), "Unknown savepoint type");
stmt.executeUpdate("INSERT INTO spt values('hej5')");
stmt.executeUpdate("INSERT INTO spt values('hej6')");
con.commit();
con.commit();
ResultSet rs = stmt.executeQuery("SELECT * FROM spt");
assertTrue(rs.next());
assertEquals("hej1", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej2", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej5", rs.getString(1));
assertTrue(rs.next());
assertEquals("hej6", rs.getString(1));
assertFalse(rs.next());
}
}
@Test
public void releaseSavepoint() throws SQLException {
try (Connection con = createCon()) {
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMPORARY TABLE spt(test varchar(10)) ");
con.setAutoCommit(false);
stmt.executeUpdate("INSERT INTO spt values('hej1')");
stmt.executeUpdate("INSERT INTO spt values('hej2')");
Savepoint savepoint = con.setSavepoint();
stmt.executeUpdate("INSERT INTO spt values('hej3')");
stmt.executeUpdate("INSERT INTO spt values('hej4')");
con.releaseSavepoint(savepoint);
Common.assertThrowsContains(
SQLException.class,
() -> con.releaseSavepoint(new MySavepoint()),
"Unknown savepoint type");
stmt.executeUpdate("INSERT INTO spt values('hej5')");
stmt.executeUpdate("INSERT INTO spt values('hej6')");
con.commit();
ResultSet rs = stmt.executeQuery("SELECT * FROM spt");
for (int i = 1; i < 7; i++) {
assertTrue(rs.next());
assertEquals("hej" + i, rs.getString(1));
}
assertFalse(rs.next());
}
}
@Test
public void various() throws SQLException {
assertThrows(SQLException.class, () -> sharedConn.setTypeMap(null));
assertTrue(sharedConn.getTypeMap().isEmpty());
assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, sharedConn.getHoldability());
sharedConn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, sharedConn.getHoldability());
}
@Test
public void verificationEd25519AuthPlugin() throws Throwable {
Assumptions.assumeTrue(!isMaxscale() && isMariaDBServer() && minVersion(10, 2, 0));
Statement stmt = sharedConn.createStatement();
try {
stmt.execute("INSTALL SONAME 'auth_ed25519'");
} catch (SQLException sqle) {
Assumptions.assumeTrue(false, "server doesn't have ed25519 plugin, cancelling test");
}
try {
stmt.execute("drop user verificationEd25519AuthPlugin" + getHostSuffix());
} catch (SQLException e) {
// eat
}
try {
if (minVersion(10, 4, 0)) {
stmt.execute(
"CREATE USER IF NOT EXISTS verificationEd25519AuthPlugin"
+ getHostSuffix()
+ " IDENTIFIED "
+ "VIA ed25519 USING PASSWORD('MySup8%rPassw@ord')");
} else {
stmt.execute(
"CREATE USER IF NOT EXISTS verificationEd25519AuthPlugin"
+ getHostSuffix()
+ " IDENTIFIED "
+ "VIA ed25519 USING '6aW9C7ENlasUfymtfMvMZZtnkCVlcb1ssxOLJ0kj/AA'");
}
} catch (SQLException sqle) {
// already existing
}
stmt.execute(
"GRANT SELECT on "
+ sharedConn.getCatalog()
+ ".* to verificationEd25519AuthPlugin"
+ getHostSuffix());
try (Connection connection =
createCon("user=verificationEd25519AuthPlugin&password=MySup8%rPassw@ord")) {
// must have succeeded
connection.getCatalog();
}
stmt.execute("drop user verificationEd25519AuthPlugin" + getHostSuffix());
}
@Test
public void parsecAuthPlugin() throws Throwable {
Assumptions.assumeTrue(!isMaxscale() && isMariaDBServer() && minVersion(10, 6, 1));
Statement stmt = sharedConn.createStatement();
try {
stmt.execute("INSTALL SONAME 'auth_parsec'");
} catch (SQLException sqle) {
Assumptions.assumeTrue(false, "server doesn't have auth_parsec plugin, cancelling test");
}
stmt.execute("drop user IF EXISTS verifParsec" + getHostSuffix());
stmt.execute("drop user IF EXISTS verifParsec2" + getHostSuffix());
stmt.execute(
"CREATE USER verifParsec"
+ getHostSuffix()
+ " IDENTIFIED VIA parsec USING PASSWORD('MySup8%rPassw@ord')");
stmt.execute(
"CREATE USER verifParsec2" + getHostSuffix() + " IDENTIFIED VIA parsec USING PASSWORD('')");
stmt.execute("GRANT SELECT on `" + database + "`.* to verifParsec" + getHostSuffix());
stmt.execute("GRANT SELECT on `" + database + "`.* to verifParsec2" + getHostSuffix());
String version = System.getProperty("java.version");
int majorVersion =
(version.indexOf(".") >= 0)
? Integer.parseInt(version.substring(0, version.indexOf(".")))
: Integer.parseInt(version);
if (majorVersion < 15) {
// before java 15, Ed25519 is not supported
// assuming, that BouncyCastle is not on test classpath
assertThrowsContains(
SQLException.class,
() -> createCon("user=verifParsec&password=MySup8%rPassw@ord"),
"Parsec authentication not available. Either use Java 15+ or add BouncyCastle"
+ " dependency");
} else {
try (Connection connection = createCon("user=verifParsec&password=MySup8%rPassw@ord")) {
// must have succeeded
connection.getCatalog();
}
}
assertThrowsContains(
SQLException.class,
() -> createCon("user=verifParsec2&password=MySup8%rPassw@ord&restrictedAuth=dialog"),
"Client restrict authentication plugin to a limited set");
stmt.execute("drop user verifParsec" + getHostSuffix());
stmt.execute("drop user verifParsec2" + getHostSuffix());
}
@Test
public void pamAuthPlugin() throws Throwable {
// https://mariadb.com/kb/en/authentication-plugin-pam/
// only test on travis, because only work on Unix-like operating systems.
// /etc/pam.d/mariadb pam configuration is created beforehand
Assumptions.assumeTrue(isMariaDBServer() && System.getenv("TEST_PAM_USER") != null);
Statement stmt = sharedConn.createStatement();
try {
stmt.execute("INSTALL PLUGIN pam SONAME 'auth_pam'");
} catch (SQLException sqle) {
// might be already set
}
String pamUser = System.getenv("TEST_PAM_USER");
String pamPwd = System.getenv("TEST_PAM_PWD");
try {
stmt.execute("DROP USER '" + pamUser + "'" + getHostSuffix());
} catch (SQLException e) {
// eat
}
stmt.execute("CREATE USER '" + pamUser + "'@'%' IDENTIFIED VIA pam USING 'mariadb'");
stmt.execute(
"GRANT SELECT ON *.* TO '" + pamUser + "'" + getHostSuffix() + " IDENTIFIED VIA pam");
stmt.execute("FLUSH PRIVILEGES");
int testPort = port;
if (System.getenv("TEST_PAM_PORT") != null) {
testPort = Integer.parseInt(System.getenv("TEST_PAM_PORT"));
}
String connStr =
String.format(
"jdbc:mariadb://%s:%s/%s?user=%s&password=%s&%s",
hostname, testPort, database, pamUser, pamPwd, defaultOther);
if ("1".equals(System.getenv("CLEAR_TEXT"))) {
// mysql_clear_password is not permit if not using SSL
assertThrowsContains(
SQLException.class,
() ->
DriverManager.getConnection(connStr + "&restrictedAuth=dialog,mysql_clear_password"),
"Cannot use authentication plugin mysql_clear_password if SSL is not enabled");
} else {
try {
try (Connection connection =
DriverManager.getConnection(connStr + "&restrictedAuth=dialog,mysql_clear_password")) {
// must have succeeded
connection.getCatalog();
}
} catch (SQLException e) {
}
try {
try (Connection connection =
DriverManager.getConnection(connStr + "&restrictedAuth=dialog,mysql_clear_password")) {
// must have succeeded
connection.getCatalog();
}
} catch (SQLException e) {
System.err.println(
"fail with connectionString : "
+ connStr
+ "&restrictedAuth=dialog,mysql_clear_password");
throw e;
}
}
Common.assertThrowsContains(
SQLException.class,
() -> DriverManager.getConnection(connStr + "&restrictedAuth=other"),
"Client restrict authentication plugin to a limited set of authentication");
stmt.execute("drop user " + pamUser + getHostSuffix());
}
@Test
public void testNoUseReadAheadInputConnection() throws Exception {
try (Connection connection = createCon("useReadAheadInput=false")) {
// must have succeeded
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM sequence_1_to_10");
int i = 0;
while (rs.next()) i++;
assertTrue(i > 0);
}
}
@Test
public void useNoDatabase() throws SQLException {
try (Connection con = createCon()) {
con.getCatalog();
Statement stmt = con.createStatement();
stmt.execute("CREATE DATABASE IF NOT EXISTS someDb");
con.setCatalog("someDb");
stmt.execute("DROP DATABASE someDb");
if (minVersion(10, 4, 0) && !isMaxscale()) {
assertNull(con.getCatalog());
}
}
}
@Test
public void windowsNamedPipe() throws SQLException {
ResultSet rs = null;
try {
rs = sharedConn.createStatement().executeQuery("select @@named_pipe,@@socket");
} catch (SQLException sqle) {
// on non Windows system, named_pipe doesn't exist.
}
if (rs != null) {
assertTrue(rs.next());
System.out.println("named_pipe:" + rs.getString(1));
Assumptions.assumeTrue(rs.getBoolean(1));
String namedPipeName = rs.getString(2);
System.out.println("namedPipeName:" + namedPipeName);
if (!isMariaDBServer() && minVersion(8, 0, 14)) {
try {
rs = sharedConn.createStatement().executeQuery("select @@named_pipe_full_access_group");
if (rs != null) {
String namedPipeFullAccess = rs.getString(1);
System.out.println("namedPipeFullAccess:" + namedPipeFullAccess);
Assumptions.assumeTrue(namedPipeFullAccess != null && !namedPipeFullAccess.isEmpty());
}
} catch (SQLException sqle) {
// on non Windows system, named_pipe doesn't exist.
}
}
// skip test if no namedPipeName was obtained because then we do not use a socket connection
Assumptions.assumeTrue(namedPipeName != null);
String connUrl =
password == null || password.isEmpty()
? String.format("jdbc:mariadb:///%s?user=%s%s", database, user, defaultOther)
: String.format(
"jdbc:mariadb:///%s?user=%s&password=%s%s",
database, user, password, defaultOther);
try (Connection connection =
DriverManager.getConnection(connUrl + "&pipe=" + namedPipeName)) {
java.sql.Statement stmt = connection.createStatement();
try (ResultSet rs2 = stmt.executeQuery("SELECT 1")) {
assertTrue(rs2.next());
}
}
// connection without host name
try (java.sql.Connection connection =
DriverManager.getConnection(
String.format(
"jdbc:mariadb:///%s?%s&pipe=%s&tcpAbortiveClose&tcpKeepAlive",
sharedConn.getCatalog(),
mDefUrl.substring(mDefUrl.indexOf("?user=") + 1),
namedPipeName))) {
connection.setNetworkTimeout(null, 300);
java.sql.Statement stmt = connection.createStatement();
try (ResultSet rs2 = stmt.executeQuery("SELECT 1")) {
assertTrue(rs2.next());
}
}
// connection host format host name
try (java.sql.Connection connection =
DriverManager.getConnection(
String.format(
"jdbc:mariadb://address=(pipe=%s)/%s?%s&tcpAbortiveClose&tcpKeepAlive",
namedPipeName,
sharedConn.getCatalog(),
mDefUrl.substring(mDefUrl.indexOf("?user=") + 1)))) {
connection.setNetworkTimeout(null, 300);
java.sql.Statement stmt = connection.createStatement();
try (ResultSet rs2 = stmt.executeQuery("SELECT 1")) {
assertTrue(rs2.next());
}
}
}
}
@Test
public void windowsNamedPipeCancel() throws SQLException {
Assumptions.assumeFalse(isMariaDBServer());
ResultSet rs = null;
try {
rs =
sharedConn
.createStatement()
.executeQuery("select @@named_pipe,@@socket,@@named_pipe_full_access_group");
} catch (SQLException sqle) {
// on non Windows system, named_pipe doesn't exist.
}
if (rs != null) {
assertTrue(rs.next());
System.out.println("named_pipe:" + rs.getString(1));
Assumptions.assumeTrue(rs.getBoolean(1));
String namedPipeName = rs.getString(2);
System.out.println("namedPipeName:" + namedPipeName);
if (!isMariaDBServer() && minVersion(8, 0, 14)) {
String namedPipeFullAccess = rs.getString(3);
System.out.println("namedPipeFullAccess:" + namedPipeFullAccess);
Assumptions.assumeTrue(namedPipeFullAccess != null && !namedPipeFullAccess.isEmpty());
}
// skip test if no namedPipeName was obtained because then we do not use a socket connection
Assumptions.assumeTrue(namedPipeName != null);
String connUrl =
password == null || password.isEmpty()
? String.format("jdbc:mariadb:///%s?user=%s%s", database, user, defaultOther)
: String.format(
"jdbc:mariadb:///%s?user=%s&password=%s%s",
database, user, password, defaultOther);
try (Connection connection =
DriverManager.getConnection(connUrl + "&pipe=" + namedPipeName)) {
Statement stmt = connection.createStatement();
stmt.cancel(); // will do nothing
ExecutorService exec = Executors.newFixedThreadPool(1);
Common.assertThrowsContains(
SQLTimeoutException.class,
() -> {
exec.execute(new StatementTest.CancelThread(stmt));
stmt.execute(
"select * from information_schema.columns as c1, information_schema.tables,"
+ " information_schema.tables as t2");
exec.shutdown();
},
"Query execution was interrupted");
}
}
}
@Test
public void localSocket() throws Exception {
Assumptions.assumeTrue(
System.getenv("local") != null
&& "1".equals(System.getenv("local"))
&& !System.getProperty("os.name").toLowerCase(Locale.ROOT).contains("win"));
Statement stmt = sharedConn.createStatement();
ResultSet rs = stmt.executeQuery("select @@version_compile_os,@@socket");
if (!rs.next() || rs.getString(2) == null) {
return;
}
String path = rs.getString(2);
try {
stmt.execute("DROP USER testSocket");
} catch (SQLException e) {
// eat
}
boolean useOldNotation =
(!isMariaDBServer() || !minVersion(10, 2, 0))
&& (isMariaDBServer() || !minVersion(8, 0, 0));
if (useOldNotation) {
stmt.execute("CREATE USER testSocket IDENTIFIED BY 'heyPassw!��20��rd'");
stmt.execute("GRANT SELECT on *.* to testSocket IDENTIFIED BY 'heyPassw!��20��rd'");
} else {
stmt.execute("CREATE USER testSocket IDENTIFIED BY 'heyPassw!��20��rd'");
stmt.execute("GRANT SELECT on *.* to testSocket");
}
stmt.execute("FLUSH PRIVILEGES");
try (java.sql.Connection connection =
DriverManager.getConnection(
String.format(
"jdbc:mariadb:///%s?user=testSocket&password=heyPassw!��20��rd&localSocket=%s&tcpAbortiveClose&tcpKeepAlive",
sharedConn.getCatalog(), path))) {
connection.setNetworkTimeout(null, 300);
rs = connection.createStatement().executeQuery("select 1");
assertTrue(rs.next());
}
// host format
try (java.sql.Connection connection =
DriverManager.getConnection(
String.format(
"jdbc:mariadb://address=(localSocket=%s)/%s?user=testSocket&password=heyPassw!��20��rd&tcpAbortiveClose&tcpKeepAlive",
path, sharedConn.getCatalog()))) {
connection.setNetworkTimeout(null, 300);
rs = connection.createStatement().executeQuery("select 1");
assertTrue(rs.next());
}
Common.assertThrowsContains(
SQLException.class,
() ->
DriverManager.getConnection(
"jdbc:mariadb:///"
+ sharedConn.getCatalog()
+ "?user=testSocket&password=heyPassw!��20��rd&localSocket=/wrongPath"),
"Socket fail to connect to address=(localSocket=/wrongPath)");
Common.assertThrowsContains(
SQLException.class,
() ->
DriverManager.getConnection(
"jdbc:mariadb://address=(localSocket=/wrongPath)/"
+ sharedConn.getCatalog()
+ "?user=testSocket&password=heyPassw!��20��rd"),
"Socket fail to connect to address=(localSocket=/wrongPath)");
if (haveSsl()) {
String serverCertPath = SslTest.retrieveCertificatePath();
if (serverCertPath != null) {
try (Connection con =
DriverManager.getConnection(
"jdbc:mariadb:///"
+ sharedConn.getCatalog()
+ "?sslMode=verify-full&user=testSocket&password=heyPassw!��20��rd"
+ "&serverSslCert="
+ serverCertPath
+ "&localSocket="
+ path)) {
rs = con.createStatement().executeQuery("select 1");
assertTrue(rs.next());
}
}
}
stmt.execute("DROP USER testSocket");
}
public static int staticTestValue = 0;
@Test
public void socketFactoryTest() throws SQLException {
try (Connection conn = createCon("socketFactory=" + SocketFactoryBasicTest.class.getName())) {
conn.isValid(1);
}
try (Connection conn = createCon("socketFactory=" + SocketFactoryTest.class.getName())) {
conn.isValid(1);
}
Common.assertThrowsContains(
SQLNonTransientConnectionException.class,
() -> createCon("socketFactory=wrongClass"),
"Socket factory failed to initialized with option \"socketFactory\" set to \"wrongClass\"");
assertEquals(0, staticTestValue);
Common.assertThrowsContains(
SQLNonTransientConnectionException.class,
() -> createCon("socketFactory=org.mariadb.jdbc.integration.util.WrongSocketFactoryTest"),
"Socket factory failed to initialized with option \"socketFactory\" set to"
+ " \"org.mariadb.jdbc.integration.util.WrongSocketFactoryTest\"");
assertEquals(0, staticTestValue);
}
@Test
public void socketOption() throws SQLException {
try (Connection con = createCon("tcpKeepAlive=true&tcpAbortiveClose=true")) {
con.isValid(1);
}
}
@Test
public void sslNotSet() throws SQLException {
Assumptions.assumeFalse(!isMariaDBServer() && minVersion(8, 4, 0));
Assumptions.assumeFalse(haveSsl());
Common.assertThrowsContains(
SQLException.class, () -> createCon("sslMode=trust"), "ssl not enabled in the server");
}
@Test
public void localSocketAddress() throws SQLException {
Assumptions.assumeTrue(
System.getenv("local") != null
&& "1".equals(System.getenv("local"))
&& !System.getProperty("os.name").toLowerCase(Locale.ROOT).contains("win"));
Configuration conf = Configuration.parse(mDefUrl);
HostAddress hostAddress = conf.addresses().get(0);
try (Connection con = createCon("localSocketAddress=" + hostAddress.host)) {
con.isValid(1);
}
}
@Test
public void setReadOnly() throws SQLException {
Connection con = createCon();
con.setReadOnly(true);
con.close();
Common.assertThrowsContains(
SQLNonTransientConnectionException.class,
() -> con.setReadOnly(false),
"Connection is closed");
}
@Test
public void timezone() throws SQLException {
try (Connection con = createCon("timezone=GMT-8")) {
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT @@time_zone");
rs.next();
assertEquals("-08:00", rs.getString(1));
}
try (Connection con = createCon("timezone=UTC")) {
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT @@time_zone, @@system_time_zone");
rs.next();
String srvTz = rs.getString(1);
if ("SYSTEM".equals(rs.getString(1))) {
srvTz = rs.getString(2);
}
assertTrue("+00:00".equals(srvTz) || "UTC".equals(srvTz));
}
try (Connection con = createCon("timezone=disable")) {
con.isValid(1);
}
}
@Test
public void createDatabaseIfNotExist() throws SQLException {
// ensure connecting without DB
String connStr =
String.format(
"jdbc:mariadb://%s:%s/?user=%s&password=%s&%s&createDatabaseIfNotExist&allowPublicKeyRetrieval=true",
hostname, port, user, password, defaultOther);
try (Connection con = DriverManager.getConnection(connStr)) {
con.createStatement().executeQuery("SELECT 1");
}
sharedConn.createStatement().execute("DROP DATABASE IF EXISTS `bla``f``l`");
String nonExistentDatabase = "bla`f`l";
connStr =
String.format(
"jdbc:mariadb://%s:%s/%s?user=%s&password=%s&%s&createDatabaseIfNotExist&allowPublicKeyRetrieval=true",
hostname, port, nonExistentDatabase, user, password, defaultOther);
try (Connection con = DriverManager.getConnection(connStr)) {
ResultSet rs = con.createStatement().executeQuery("select DATABASE()");
assertTrue(rs.next());
assertEquals(nonExistentDatabase, rs.getString(1));
}
nonExistentDatabase = "bla`f`l0";
connStr =
String.format(
"jdbc:mariadb:replication://%s:%s,%s:%s/%s?user=%s&password=%s&%s&createDatabaseIfNotExist&allowPublicKeyRetrieval=true",
hostname, port, hostname, port, nonExistentDatabase, user, password, defaultOther);
try (Connection con = DriverManager.getConnection(connStr)) {
ResultSet rs = con.createStatement().executeQuery("select DATABASE()");
assertTrue(rs.next());
assertEquals(nonExistentDatabase, rs.getString(1));
}
sharedConn.createStatement().execute("DROP DATABASE IF EXISTS `bla``f``l`");
sharedConn.createStatement().execute("DROP DATABASE IF EXISTS `bla``f``l0`");
}
@Test
public void loopHost() throws SQLException {
// ensure connecting without DB
String connStr =
String.format(
"jdbc:mariadb://wronghost,%s:%s/%s?user=%s&password=%s&%s",
hostname, port, database, user, password, defaultOther);
try (Connection con = DriverManager.getConnection(connStr)) {
con.createStatement().executeQuery("SELECT 1");
}
}
static class MySavepoint implements Savepoint {
@Override
public int getSavepointId() throws SQLException {
return 0;
}
@Override
public String getSavepointName() throws SQLException {
return null;
}
}
@Nested
@DisplayName("Transaction Test")
class Transaction {
@Test
public void testProperRollback() throws Exception {
java.sql.Statement stmt = sharedConn.createStatement();
try {
stmt.execute("CREATE TABLE tx_prim_key(id int not null primary key) engine=innodb");
stmt.execute(
"CREATE TABLE tx_fore_key (id int not null primary key, id_ref int not null, foreign"
+ " key (id_ref) references tx_prim_key(id) on delete restrict on update restrict) "
+ "engine=innodb");
stmt.executeUpdate("insert into tx_prim_key(id) values(32)");
stmt.executeUpdate("insert into tx_fore_key(id, id_ref) values(42, 32)");
// 2. try to delete entry in Primary table in a transaction - which will fail due
// foreign key.
sharedConn.setAutoCommit(false);
try (java.sql.Statement st = sharedConn.createStatement()) {
st.executeUpdate("delete from tx_prim_key where id = 32");
sharedConn.commit();
fail("Expected SQLException");
} catch (SQLException e) {
// This exception is expected
assertTrue(
e.getMessage().contains("a foreign key constraint fails")
|| e.getMessage().contains("Foreign key constraint violation"));
sharedConn.rollback();
}
try (java.sql.Connection conn2 = createCon();
java.sql.Statement st = conn2.createStatement()) {
st.setQueryTimeout(30000);
st.executeUpdate("delete from tx_fore_key where id = 42");
st.executeUpdate("delete from tx_prim_key where id = 32");
}
} finally {
stmt.execute("drop table if exists tx_fore_key");
stmt.execute("drop table if exists tx_prim_key");
}
}
@Test
public void transactionTest() throws SQLException {
Statement stmt = sharedConn.createStatement();
try {
stmt.execute(
"CREATE TABLE transaction_test "
+ "(id int not null primary key auto_increment, test varchar(20)) "
+ "engine=innodb");
sharedConn.setAutoCommit(false);
stmt.executeUpdate("INSERT INTO transaction_test (test) VALUES ('heja')");
stmt.executeUpdate("INSERT INTO transaction_test (test) VALUES ('japp')");
sharedConn.commit();
ResultSet rs = stmt.executeQuery("SELECT * FROM transaction_test");
assertTrue(rs.next());
assertEquals("heja", rs.getString("test"));
assertTrue(rs.next());
assertEquals("japp", rs.getString("test"));
assertFalse(rs.next());
stmt.executeUpdate(
"INSERT INTO transaction_test (test) VALUES ('rollmeback')",
java.sql.Statement.RETURN_GENERATED_KEYS);
ResultSet rsGen = stmt.getGeneratedKeys();
rsGen.next();
int[] autoInc = setAutoInc();
assertEquals(autoInc[1] + autoInc[0] * 3, rsGen.getInt(1));
sharedConn.rollback();
rs = stmt.executeQuery("SELECT * FROM transaction_test WHERE id=3");
assertFalse(rs.next());
sharedConn.setAutoCommit(true);
} finally {
stmt.execute("DROP TABLE IF EXISTS transaction_test");
}
}
/**
* Get current autoincrement value, since Galera values are automatically set.
*
* @throws SQLException if any error occur.
*/
public int[] setAutoInc() throws SQLException {
return setAutoInc(1, 0);
}
/**
* Get current autoincrement value, since Galera values are automatically set.
*
* @param autoIncInit default increment
* @param autoIncOffsetInit default increment offset
* @throws SQLException if any error occur
* @see <a
* href="https://mariadb.org/auto-increments-in-galera/">https://mariadb.org/auto-increments-in-galera/</a>
*/
public int[] setAutoInc(int autoIncInit, int autoIncOffsetInit) throws SQLException {
// in case of galera
// if (isGalera()) {
// ResultSet rs =
// sharedConn.createStatement().executeQuery("show variables like
// '%auto_increment%'");
// while (rs.next()) {
// if ("auto_increment_increment".equals(rs.getString(1))) {
// autoInc = rs.getInt(2);
// }
// if ("auto_increment_offset".equals(rs.getString(1))) {
// autoIncOffset = rs.getInt(2);
// }
// }
// if (autoInc == 1) {
// // galera with one node only, then offset is not used
// autoIncOffset = 0;
// }
// }
return new int[] {autoIncInit, autoIncOffsetInit};
}
}
@Nested
@DisplayName("Compression Test")
class Compression {
@Test
public void testConnection() throws Exception {
try (Connection connection = createCon("useCompression")) {
// must have succeeded
connection.getCatalog();
}
}
}
@Test
public void expiredPasswordTest() throws SQLException, InterruptedException {
Assumptions.assumeTrue(srvHasCapability(Capabilities.CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS));
boolean forced = false;
Statement stmt = sharedConn.createStatement();
System.out.println("getHostSuffix(): " + getHostSuffix());
try {
stmt.execute("DROP USER IF EXISTS 'expired_pwd_user'" + getHostSuffix());
stmt.execute(
"CREATE USER 'expired_pwd_user'" + getHostSuffix() + " IDENTIFIED by '!Passw0rd3Works'");
stmt.execute("GRANT all on *.* to 'expired_pwd_user'" + getHostSuffix());
String connStr =
String.format(
"jdbc:mariadb://%s:%s/%s?user=%s&password=%s&%s&allowPublicKeyRetrieval=true",
hostname, port, database, "expired_pwd_user", "!Passw0rd3Works", defaultOther);
stmt.execute("ALTER USER 'expired_pwd_user'" + getHostSuffix() + " PASSWORD EXPIRE");
stmt.execute("FLUSH PRIVILEGES");
if (isMariaDBServer()) {
// force
try {
ResultSet rs = stmt.executeQuery("select @@global.disconnect_on_expired_password");
rs.next();
if (rs.getBoolean(1) != true) {
stmt.execute("set @@global.disconnect_on_expired_password=true");
forced = true;
}
} catch (SQLException e) {
// eat
e.printStackTrace();
}
}
assertThrows(SQLException.class, () -> DriverManager.getConnection(connStr));
try (Connection con =
DriverManager.getConnection(connStr + "&disconnectOnExpiredPasswords=false")) {
assertThrows(SQLException.class, () -> con.createStatement().execute("SELECT 1"));
}
try (Connection con =
DriverManager.getConnection(
connStr + "&disconnectOnExpiredPasswords=false&initSql=SELECT 1")) {
assertThrows(SQLException.class, () -> con.createStatement().execute("SELECT 1"));
}
} finally {
stmt.execute("DROP USER IF EXISTS 'expired_pwd_user'");
if (forced) stmt.execute("set @@global.disconnect_on_expired_password=false");
}
}
@Test
public void isClosed() throws SQLException {
Connection con = DriverManager.getConnection(mDefUrl);
Statement stmt = con.createStatement();
PreparedStatement preparedStatement = con.prepareStatement("SELECT ?");
assertFalse(stmt.isClosed());
assertFalse(preparedStatement.isClosed());
stmt.execute("SELECT 1");
preparedStatement.setInt(1, 1);
preparedStatement.execute();
assertFalse(stmt.isClosed());
assertFalse(preparedStatement.isClosed());
con.close();
assertTrue(stmt.isClosed());
assertTrue(preparedStatement.isClosed());
}
}