MySQL to PostgreSQL migration: mysql connector

Question

I am trying to migrate from MySQL to PostgreSQL and I have a Java-related problem that I am not able to fix. Full disclosure: I know little or nothing about Java, but the migration uses a Java-based script, so for me it becomes a configuration problem.

Short version of the problem:

The migration tool throws this exception:

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

mysql-connector-java-5.0.8-bin.jar is already in the "JAVA_HOME\jre\lib\ext" directory, and I don't know how to solve this depencency problem.

Long version of the problem:

I was trying to migrate from MySQL to PostgreSQL. I checked the official postgresql documentation and I chose the free tool from entreprisedb (that can be downloaded here) to start the migration.

From the installation readme, they tell you that the mysql connector is not installed by default, but they also tell you the steps to solve this problem:

To enable MySQL connectivity, download MySQL's freely available JDBC driver from: http://www.enterprisedb.com/downloads/third-party-jdbc-drivers

Place the mysql-connector-java-5.0.8-bin.jar file in the "JAVA_HOME\jre\lib\ext" directory (in my case: "C:\Program Files\Java\jre1.8.0_60\lib\ext\mysql-connector-java-5.0.8-bin.jar").

After configuring the tool properly and executing the .bat, this is the error I get:

Connecting with source MySQL database server...
MTK-11009: Error Connecting Database "MySQL Server"
DB-null: java.sql.SQLException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
Stack Trace:
com.edb.MTKException: MTK-11009: Error Connecting Database "MySQL Server"
    at com.edb.dbhandler.mysql.MySQLConnection.<init>(MySQLConnection.java:48)
    at com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:250)
    at com.edb.MigrationToolkit.createNewSourceConnection(MigrationToolkit.java:5982)
    at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:3346)
    at com.edb.MigrationToolkit.main(MigrationToolkit.java:1700)
Caused by: java.sql.SQLException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at com.edb.Utility.processException(Utility.java:327)
    at com.edb.dbhandler.mysql.MySQLConnection.<init>(MySQLConnection.java:47)
    ... 4 more

...which, to my understanding, probably means that mysql-connector-java-5.0.8-bin.jar is not found.

All the links I've found online regarding the error are specific for Eclipse or other IDEs, so I have not yet been able to solve this dependency problem.

the connector must be placed in your project not in JRE

Mohamed Nabli
– Mohamed Nabli

2016-07-25 12:41:35 +00:00
Commented Jul 25, 2016 at 12:41 — Mohamed Nabli
– Mohamed Nabli, Commented Jul 25, 2016 at 12:41

Xavier Peña · Accepted Answer · 2016-07-25 14:27:30Z

1

SOLUTION

With the help of a friend that masters Java, this is the solution he achieved:

To start looking for the problem, we opened the runMTK.bat. The execution line reads:

cscript //nologo "..\etc\sysconfig\runJavaApplication.vbs" "..\etc\sysconfig\edbmtk-49.config" "-Dprop=..\etc\toolkit.properties -classpath -jar edb-migrationtoolkit.jar %*"

So then we opened this runJavaApplication.vbs, and in order to know the JAVA_EXECUTABLE_PATH that the program was using, we add this line to the script:

Wscript.Echo "JAVA_EXECUTABLE_PATH = " & JAVA_EXECUTABLE_PATH

With that info, we discover that the script is using the Java folder under C:\Program Files (x86), instead of the one under C:\Program Files (where I dropped the mysql jar). So we copy the mysql-connector-java-5.0.8-bin.jar in the \ext folder of the x86, and now the script works.

Word of advice: the script is throwing errors in half of the exported tables, so all the hassle may not be worth it. BUT if anyone is interested in making this migration script work from A to Z (which has been quite a challenge), here are the details:

HOW TO

Free tool (from entreprisedb): http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Extract the files from the zip and fun the installer (ppasmeta-9.5.0.5-windows-x64.exe) as administrator.

To enable MySQL connectivity, download MySQL's freely available JDBC driver from:
http://www.enterprisedb.com/downloads/third-party-jdbc-drivers

Place the mysql-connector-java-5.0.8-bin.jar file in the "JAVA_HOME\jre\lib\ext" directory (in my case: "C:\Program Files\Java\jre1.8.0_60\lib\ext\mysql-connector-java-5.0.8-bin.jar").

The Migration Toolkit documentation can be found:

here (online doc): https://www.enterprisedb.com/docs/en/9.4/migrate/toc.html
or here (pdf doc): http://get.enterprisedb.com/docs/Postgres_Plus_Migration_Guide_v9.5.pdf

First: modify C:\Program Files\PostgresPlus\edbmtk\etc\toolkit.properties (Info here):

SRC_DB_URL=jdbc:mysql://SOURCE-HOST-NAME/SOURCE-DB-NAME
SRC_DB_USER=********
SRC_DB_PASSWORD=********

TARGET_DB_URL=jdbc:edb://localhost:5444/DESTINATION-DB-NAME
TARGET_DB_USER=enterprisedb
TARGET_DB_PASSWORD=********

Then: execute C:\Program Files\PostgresPlus\edbmtk\bin\runMTK.bat (Info here).

runMTK.bat -sourcedbtype mysql -targetdbtype enterprisedb -allTables YOUR_DB_SCHEMA

// ...or with a limited subset of tables:

runMTK.bat -sourcedbtype mysql -targetdbtype enterprisedb -tables TABLE1,TABLE2,TABLE3 YOUR_DB_SCHEMA

In order to get this subset of tables from MySQL:

SELECT 
    GROUP_CONCAT(TABLE_NAME)
FROM
    information_schema.tables
WHERE
    TABLE_SCHEMA = 'your_db_name'

answered Jul 25, 2016 at 14:27

Xavier Peña

7,9899 gold badges66 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yoav.str Over a year ago

I am tryig to work with your solution but keep on getting DB-null: java.sql.SQLException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 2017-06-18 17:07:00 Stack Trace: com.edb.MTKException: MTK-11009: Error Connecting Database "MySQL Server" at com.edb.dbhandler.mysql.MySQLConnection.<init>(MySQLConnection.java:48) at com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:263) at com.edb.MigrationToolkit.createNewSourceConnection(MigrationToolkit.java:6357)

yoav.str Over a year ago

where did u add this line Wscript.Echo "JAVA_EXECUTABLE_PATH = " & JAVA_EXECUTABLE_PATH ?

Collectives™ on Stack Overflow

MySQL to PostgreSQL migration: mysql connector

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related