Ship modern MySQL/MariaDB JDBC for Java 8; purge old mysql-connector 5.1.x on upgrade (MySQL 8 auth)

[natechadwick-intsof]

Summary

8.1.x / Java 8 LTS installs (especially DTS) still end up with a legacy MySQL JDBC client on the classpath (com.mysql.jdbc.* / Connector/J 5.1.x). Against MySQL 8+, that fails pool startup with:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool:
  Client does not support authentication protocol requested by server; consider upgrading MySQL client
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
  Client does not support authentication protocol requested by server; consider upgrading MySQL client

MySQL 8 default user plugin is often caching_sha2_password. Old Connector/J 5.1 only supports mysql_native_password.

Field impact

• DTS services that need JDBC (metadata, feeds, comments, forms, membership, etc.) fail to create Spring HikariDataSource beans → webapps broken or partial.
• Cascading CMS symptoms during publish (e.g. admin calls to DTS fail, metadata path errors) while Tomcat itself is “up.”
• Support workaround today: ALTER USER … IDENTIFIED WITH mysql_native_password — works but should not be the only story for MySQL 8 customers.
• Upgrades that leave old mysql-connector-java-5.1*.jar (or mixed 5.1 + 8.x) on Deployment/Server/lib / lib/jdbc (or CMS jetty/base/lib/jdbc) keep reproducing this forever.

Goals

1. Ship a modern connector that works with MySQL 8 auth (caching_sha2_password) on the Java 8 line for CMS and DTS packaging.
2. Upgrade / install must remove obsolete MySQL connectors from known JDBC classpath locations so customers cannot silently keep 5.1.x.
3. Decide product default: ship only MariaDB Connector/J if it fully supports our MySQL use (URL + auth + SSL + installer), or ship mysql-connector-j 8.x (and optionally MariaDB for MariaDB servers), but never leave 5.1.x beside a modern JAR.

Decision needed: MariaDB-only vs dual ship

Option A — Ship only MariaDB Connector/J (preferred if proven)

Pros:

• One JAR for MySQL-compatible and MariaDB servers.
• Already referenced heavily in delivery-tier distribution on main (mariadb-java-client).
• Active maintenance, good MySQL 8 auth support on current 3.x drivers.

Cons / research required:

• MariaDB Connector/J 3.5+ behavior with jdbc:mysql://… URLs (main-line notes: may not accept jdbc:mysql:// by default — see historical #1388 discussion on CMS).
• Installer / perc-datasources / docs historically document jdbc:mysql:// for MySQL customers.
• Need a clear rule:
  • either rewrite / document jdbc:mariadb:// for all MySQL/MariaDB, or
  • enable MariaDB driver acceptance of MySQL scheme if supported and safe.
• Verify: Hikari + our DTS datasources, SSL flags, timezone, allowPublicKeyRetrieval, batch, and service-specific schemas.

Acceptance for A: one integration matrix pass — DTS + CMS against MySQL 8.0/8.4 with product-default URL form and caching_sha2 users (no mysql_native_password workaround).

Option B — Ship mysql-connector-j 8.x (and keep MariaDB for MariaDB if needed)

Pros:

• Drop-in for existing jdbc:mysql:// configs.
• Aligns with main CMS dependency direction (com.mysql:mysql-connector-j:8.4.x on current main).

Cons:

• Two MySQL-family JARs if MariaDB also ships → must still purge 5.1.x and avoid DriverManager ambiguity.
• Java 8 compatibility: pick a connector version that still supports Java 8 if this repo’s runtime is Java 8 (Connector/J 8.0.x/8.4 support matrix must be checked for JDK 8 vs 11+).

Acceptance for B: same MySQL 8 auth matrix; no 5.1.x remaining after upgrade.

Recommendation to validate in this issue

1. Spike MariaDB Connector/J (version pinned for Java 8) with current 8.1.x DTS datasource URLs.
2. If jdbc:mysql:// works cleanly (or a one-line installer rewrite to jdbc:mariadb:// is acceptable), prefer MariaDB-only for MySQL-family on Java 8 LTS to reduce dual-driver mess.
3. If not, ship mysql-connector-j (Java 8–compatible line) as the MySQL driver and MariaDB client only when targeting MariaDB, still delete all mysql-connector-java-5*.jar / old com.mysql.jdbc artifacts on upgrade.

Upgrade must remove old connectors

Installer / update scripts for CMS and DTS should treat known JDBC dirs as managed:

Product	Typical locations (adjust to actual 8.1.x layout)
DTS	Deployment/Server/lib/, Deployment/Server/lib/jdbc/, any Catalina shared lib used for webapps
CMS	jetty/base/lib/jdbc/, distribution tree JDBC drop dir

On install and upgrade:

• Delete / replace: mysql-connector-java-5*.jar, legacy mysql-connector-java-*.jar using com.mysql.jdbc package, duplicate outdated mysql-connector-j-*.jar if replacing with a newer pin.
• Do not leave 5.1.x + 8.x side by side.
• Log what was removed (INFO) so support can see “removed obsolete mysql-connector-java-5.1.32.jar”.
• Fail or WARN loudly if an unknown extra mysql-*.jar remains that isn’t the shipped pin (optional strict mode).

This is as important as shipping the new JAR — field upgrades otherwise keep the broken client forever.

Packaging / docs

• Pin exact version in Java 8 BOM / delivery-tier / distribution POM.
• Document supported URL forms and MySQL 8 auth (no requirement for mysql_native_password when modern driver is present).
• Upgrade notes: “MySQL 8: remove old connectors; if you previously altered users to native password, optional to move back to caching_sha2 after driver upgrade.”
• Cross-check CMS install path (same class of bug as closed main #1388 for 8.2) so Java 8 LTS is not left behind.

Acceptance criteria

• Decision recorded: MariaDB-only or mysql-connector-j (+ optional MariaDB) with rationale and Java 8 compatibility.
• Chosen connector(s) shipped in DTS (and CMS if applicable) distribution for this repo’s release train.
• Upgrade removes all known obsolete MySQL Connector/J 5.1.x (and conflicting older jars) from managed JDBC classpath locations.
• Clean DTS start against MySQL 8 with caching_sha2_password user — no Hikari authentication protocol error.
• No dual registration of com.mysql.jdbc + com.mysql.cj / conflicting drivers in a default install.
• Operator doc / release note for 8.1.x patch.
• Automated or scripted check (install test / smoke) that fails if 5.1.x still present after upgrade.

Workaround (until fixed)

ALTER USER 'dts_user'@'%' IDENTIFIED WITH mysql_native_password BY '…';
FLUSH PRIVILEGES;

Then restart DTS. Prefer product fix over permanent native-password policy.

Related

• Symptom + support context: MySQL 8 + old client on DTS post-upgrade (field).
• Main 8.2 CMS connector history: [8.2] MySQL install requires mysql-connector-j-8.x; current installer doesn't ship a mysql-connector  percussioncms#1388 (closed; modern connector direction).
• Related publish/DTS friction (secondary): Improve logging when DTS rotateKey push fails (HTTP 405 / security key) #3 (rotateKey logging), Metadata extraction fails on unbound prefixes (e.g. gcse:search) during DTS publish #4 (metadata gcse).

[natechadwick-intsof]

Field error (DTS, post-upgrade):

HikariPool$PoolInitializationException: Failed to initialize pool: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Client does not support authentication protocol requested by server; consider upgrading MySQL client

Stack package com.mysql.jdbc.* confirms legacy Connector/J 5.1.x still on DTS classpath against MySQL 8 (caching_sha2_password).

[vijaya-boddipudi]

Need to validate