Skip to content

Instantly share code, notes, and snippets.

@dveeden
Created September 17, 2021 09:49
Show Gist options
  • Save dveeden/176a8023f301f25783e8a6f5e29c4aa5 to your computer and use it in GitHub Desktop.
Save dveeden/176a8023f301f25783e8a6f5e29c4aa5 to your computer and use it in GitHub Desktop.
MariaDB Connector/J testing with TiDB v5.2.0 and Jython
#!/bin/jython
import sys
import java.util.Properties
import java.sql.SQLException
import org.mariadb.jdbc.Driver
# Test user setup:
#
# mysql-sql> create user 'test_native'@'%' identified with 'mysql_native_password' by 'test';
# Query OK, 0 rows affected (0.0778 sec)
# mysql-sql> create user 'test_sha2'@'%' identified with 'caching_sha2_password' by 'test';
# Query OK, 0 rows affected (0.0346 sec)
# mysql-sql> grant select on test.* to 'test_native'@'%';
# Query OK, 0 rows affected (0.0457 sec)
# mysql-sql> grant select on test.* to 'test_sha2'@'%';
# Query OK, 0 rows affected (0.0358 sec)
# export CLASSPATH=/path/to/mariadb-java-client-X.Y.Z.jar
for user in ["test_native", "test_sha2"]:
for ssl in ["false", "true"]:
drv = org.mariadb.jdbc.Driver()
p = java.util.Properties()
driverName = "{} v{}.{}".format(
drv.getClass().canonicalName, drv.majorVersion, drv.minorVersion
)
jdbc_url = "jdbc:mariadb://127.0.0.1:4000/test?user={}&password=test&useSSL={}&trustServerCertificate=true".format(
user, ssl
)
print("======================================================")
try:
conn = drv.connect(jdbc_url, p)
stmt = conn.prepareStatement(
'SELECT CONNECTION_ID() AS "connid", VERSION() AS "version"'
)
res = stmt.executeQuery()
while res.next():
print(
"connection {} to TiDB {}".format(
res.getString("connid"), res.getString("version")
)
)
print("%s %s\nSuccess: %s" % (driverName, jdbc_url, conn))
except java.sql.SQLException as e:
print("%s %s\nFailed: %s" % (driverName, jdbc_url, e))
print("======================================================")
@dveeden
Copy link
Author

dveeden commented Sep 17, 2021

$ for jar in /home/dvaneeden/dev/mariadb-connector-j/target/mariadb-java-client-?.?.?.jar /home/dvaneeden/Downloads/mariadb-java-client-?.?.?.jar; do echo "Testing with $jar"; CLASSPATH=$jar ./mariadb_connector_j_testing.py 2> /dev/null; done
Testing with /home/dvaneeden/dev/mariadb-connector-j/target/mariadb-java-client-2.3.0.jar
======================================================
connection 219 to TiDB 5.7.25-TiDB-v5.2.0
org.mariadb.jdbc.Driver v2.3 jdbc:mariadb://127.0.0.1:4000/test?user=test_native&password=test&useSSL=false&trustServerCertificate=true
Success: org.mariadb.jdbc.MariaDbConnection@63cd2cd2
======================================================
======================================================
connection 221 to TiDB 5.7.25-TiDB-v5.2.0
org.mariadb.jdbc.Driver v2.3 jdbc:mariadb://127.0.0.1:4000/test?user=test_native&password=test&useSSL=true&trustServerCertificate=true
Success: org.mariadb.jdbc.MariaDbConnection@e111c7c
======================================================
======================================================
org.mariadb.jdbc.Driver v2.3 jdbc:mariadb://127.0.0.1:4000/test?user=test_sha2&password=test&useSSL=false&trustServerCertificate=true
Failed: java.sql.SQLNonTransientConnectionException: Client does not support authentication protocol requested by server. Consider upgrading MariaDB client. plugin was = caching_sha2_password
======================================================
======================================================
org.mariadb.jdbc.Driver v2.3 jdbc:mariadb://127.0.0.1:4000/test?user=test_sha2&password=test&useSSL=true&trustServerCertificate=true
Failed: java.sql.SQLNonTransientConnectionException: Client does not support authentication protocol requested by server. Consider upgrading MariaDB client. plugin was = caching_sha2_password
======================================================
Testing with /home/dvaneeden/Downloads/mariadb-java-client-2.7.4.jar
======================================================
connection 227 to TiDB 5.7.25-TiDB-v5.2.0
org.mariadb.jdbc.Driver v2.7 jdbc:mariadb://127.0.0.1:4000/test?user=test_native&password=test&useSSL=false&trustServerCertificate=true
Success: org.mariadb.jdbc.MariaDbConnection@1abebef3
======================================================
======================================================
connection 229 to TiDB 5.7.25-TiDB-v5.2.0
org.mariadb.jdbc.Driver v2.7 jdbc:mariadb://127.0.0.1:4000/test?user=test_native&password=test&useSSL=true&trustServerCertificate=true
Success: org.mariadb.jdbc.MariaDbConnection@79d82f66
======================================================
======================================================
org.mariadb.jdbc.Driver v2.7 jdbc:mariadb://127.0.0.1:4000/test?user=test_sha2&password=test&useSSL=false&trustServerCertificate=true
Failed: java.sql.SQLTransientConnectionException: Could not connect to address=(host=127.0.0.1)(port=4000)(type=master) : RSA public key is not available client side (option serverRsaPublicKeyFile not set)
======================================================
======================================================
connection 233 to TiDB 5.7.25-TiDB-v5.2.0
org.mariadb.jdbc.Driver v2.7 jdbc:mariadb://127.0.0.1:4000/test?user=test_sha2&password=test&useSSL=true&trustServerCertificate=true
Success: org.mariadb.jdbc.MariaDbConnection@3c9c6245
======================================================

@dveeden
Copy link
Author

dveeden commented Sep 17, 2021

So looks like mysql_native_password works in all situations, but caching_sha2_password only works with MariaDB Connector/J 2.7.4 on a TLS connection.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment