Mysql Question posted in
The official MySQL documentation can be found here.

How to programmatically interact with both a MySQL 5 database and a MySQL 8 database from a single project?

My goal is to periodically sync data between two databases. One is a "new" database which runs MySQL 8, the other is an "old" database that hasn’t been fully decommissioned yet which runs MySQL 5. I want to sync in both directions, meaning for some tables I’m syncing from old to new, and others I’m syncing from new to old.

My goal is to do this from a single Java project, using JDBC. I’m using MySQL Connector/J. The latest version 8.4.0 which only supports MySQL 8 and up. Conversely, connector version 5.1.49 does not work with MySQL 8.

I have tried using multiple modules, but this feels hacky. Assuming module A depends on module B:

  • If A depends on connector 5.1.49 and B on connector 8.4.0, everything works.
  • If A depends on connector 8.4.0 and B on connector 5.1.49, queries to a MySQL 5 database from module B behave as if module B is using connector 8.4.0.

I have tried manually setting the driver class name to the MySQL 5 one, but that doesn’t work.

Tags:

2

Answers


  1. 0

    The problem you have is that you need two versions of the same library, and you do not know how to embedd them both on the classpath. Only the first of the two will be effective.

    What you can do however is to arrange your application in three parts. One of them is common, one of them is MySQL 5 related, and one of them is MySQL 8 related. The common part allows to control the other two and exchange data. The other two parts take over the communication, which mainly happens via the JDBC driver.

    The trick is to have these different parts on separate classloaders, which will allow you to run both JDBC drivers simulaneously within one JVM. It works the same way as several webapps in one servlet container – and each of them come with their own version of some library.

    There are a number of resources out there how to deal with your own classloader. Here is one of them:
    https://docs.oracle.com/javase/tutorial/ext/basics/index.html

    Likely you do not even have to care about creating the jar. The JDBC drivers are jar files already, and all you need is the classloader and some controlling logic (the common part I mentioned earlier).

    Login or Signup to reply.
  2. 0

    As you requested, below is the example Java code I tested. It practically couldn’t be simpler.

    import java.sql.*;

class MysqlMain
{
  public static void main(String[] argv)
  {
    Connection mysql57 = null;
    Connection mysql84 = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {
      mysql57 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?user=root");
      stmt = mysql57.createStatement();
      rs = stmt.executeQuery("SELECT VERSION()");
      while (rs.next()) {
        String version = rs.getString(1);
        System.out.println(version);
      }

      mysql84 = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3307/test?user=root");
      stmt = mysql84.createStatement();
      rs = stmt.executeQuery("SELECT VERSION()");
      while (rs.next()) {
        String version = rs.getString(1);
        System.out.println(version);
      }
    } catch (SQLException e) {
      System.err.println(e.getMessage());
      System.err.println(e.getSQLState());
      System.err.println(e.getErrorCode());
      System.exit(1);
    } finally {
      if (rs != null) {
        try {
        rs.close();
        } catch (SQLException e) { }
        rs = null;
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) { }
        stmt = null;
      }
    }
  }
}

    Here’s how I compiled and ran it:

    javac -cp mysql-connector-j-8.0.33.jar MysqlMain.java
java -cp mysql-connector-j-8.0.33.jar:. MysqlMain

    Output:

    5.7.44
8.4.0

    Also tested the most recent Connector/J:

    javac -cp mysql-connector-j-8.4.0.jar MysqlMain.java
java -cp mysql-connector-j-8.4.0.jar:. MysqlMain

    Output is the same:

    5.7.44
8.4.0

    My version of Java:

    java -version
java version "1.8.0_331"
Java(TM) SE Runtime Environment (build 1.8.0_331-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.331-b09, mixed mode)

    MacOS version:

    uname -a
Darwin <hostname> 23.4.0 Darwin Kernel Version 23.4.0: Fri Mar 15 00:12:41 PDT 2024; root:xnu-10063.101.17~1/RELEASE_ARM64_T8103 arm64
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search