0

Goal is to connect Cloud SQL mysql or postgreSQL instances using Cloud Data Fusion.

  • created Cloud SQL instances with MySQL and postgreSQL
  • created Cloud Data Fusion instance
  • From wrangler > Add connection > Cloud SQL MySQL
  • Added Data Fusion instance as member in IAM and added permissions to following Cloud SQL Client Cloud Data Fusion API Service Agent
  • In add connection used jdbc url as jdbc:mysql://google/mysql?cloudSqlInstance=&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false
  • Added driver for mySQL: mysql-connector-java-5.1.39-bin.jar
  • Added MySQL socket factory jar as library.

While testing connection, it fails with error: com.mysql.jdbc.Driver

Expecting testing of the connection with Cloud SQL MySQL to be successful so that the data fusion pipeline can be built.

deba
  • 1
  • Possible duplicate of [Can't connect Cloud Data Fusion with Google Cloud SQL for PostgreSQL](https://stackoverflow.com/questions/55880595/cant-connect-cloud-data-fusion-with-google-cloud-sql-for-postgresql) – kurtisvg Aug 01 '19 at 16:32

2 Answers2

2

The issue is that you need to provide a jar that has both the driver and the connector included. You can find instructions on how to build a uberjar (also called a farjar) using the driver and JDBC Socket Factory from this post here.

Edit: Here is a POM for MySQL:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>com.example</groupId>
   <artifactId>mysql-with-cloud-sql-socket-factory</artifactId>
   <packaging>pom</packaging>
   <version>0.0.1</version>

   <dependencies>
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.17</version>
      </dependency>
      <dependency>
         <groupId>com.google.cloud.sql</groupId>
         <artifactId>mysql-socket-factory-connector-j-8</artifactId>
         <version>1.0.14</version>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-assembly-plugin</artifactId>
            <version>2.4.1</version>
            <configuration>
               <!-- get all project dependencies -->
               <descriptorRefs>
                  <descriptorRef>jar-with-dependencies</descriptorRef>
               </descriptorRefs>
            </configuration>
            <executions>
               <execution>
                  <id>make-assembly</id>
                  <!-- bind to the packaging phase -->
                  <phase>package</phase>
                  <goals>
                     <goal>single</goal>
                  </goals>
               </execution>
            </executions>
         </plugin>
      </plugins>
   </build>
</project>
kurtisvg
  • 3,412
  • 1
  • 8
  • 24
  • Thanks @kurtisvg for your reply. I have gone through the post but I am not sure how to make this fat jar. Pardon my java skills. I have driver jars for both mySQL and postgreSQL which possibly needs to be embedded with socket factory jar. Is there any already avaiable version of uber jar / fat jar? Or any more detailed steps how to make it? Could you please help? – deba Aug 02 '19 at 06:41
  • @deba - I've added a pom for MySQL in the original answer. You can put this POM.xml in an empty folder and use the `mvn package` command to create the jar locally. – kurtisvg Aug 02 '19 at 18:11
  • Hi @kurtisvg ! Thanks a lot for your reply. I created the jar as you suggested for mysql with a small change in version of connector in pom file that you provided. I used both 5.1.32 and 8.0.17. In both the occasions I got following error: "Caused by: java.lang.NoClassDefFoundError: com/mysql/cj/protocol/SocketFactory" do you know what exactly I am missing? The goal remains the same: connection mysql cloud sql instance from Cloud Data Fusion. Best regards, Deba – deba Aug 04 '19 at 22:20
  • Both the versions of connector with mysql-socket-factory-connector-j-8 1.0.14. – deba Aug 04 '19 at 22:30
  • @deba - You should match the version of the connector to the version of the driver you are using. 8.0.X is strongly preferred over 5.x, so you want to make sure to use `mysql-connector-java:8.0.17` with `mysql-socket-factory-connector-j-8:1.0.14`. Verify your version numbers, and then try `mvn clean package`. – kurtisvg Aug 05 '19 at 15:34
  • Thanks again, for your reply! I will try it out soon. Just so that you know, I tried the similar for postgreSQL as per the pom you provided in other post. And it worked!! very happy! Now i just need this mySQL connector to work. fingers crossed! – deba Aug 06 '19 at 10:19
  • Hi @kurtisvg! I am happy to inform that even MySQL connectivity is working now with newly rebuilt jar! Many thanks once again for all the support and vital guidelines / information that you provided. Best regards. – deba Aug 07 '19 at 12:17
  • @deba - Glad you got it figured out. Please make sure you upvote/accept the answer that helped you solve the issue, so other users know it is valid. – kurtisvg Aug 08 '19 at 16:03
  • Hi @kurtisvg I tried to upvote the post but it says: "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score. " – deba Aug 27 '19 at 07:48
  • Hi @kurtisvg : Thanks, This answer helped me to create the fat jar build. But when I used this on google cloud to connect with instance, it is throwing me error "Could not create socket factory 'com.google.cloud.sql.mysql.SocketFactory' due to underlying exception." Do you have any idea on this? – NitinM Sep 20 '19 at 12:44
  • @NitinM It's hard to say without know what the underlying exception is. I would suggest make sure you your instance has a public IP, you have the `Cloud SQL Admin API` enabled, and that the service account you are using has the correct IAM permissions (`Cloud SQL Connect` or higher). – kurtisvg Sep 20 '19 at 16:36
  • @kurtisvg: I have public IP instance and I have enabled Admin API as well and I have given all cloud sql permissions also but still it is showing me the same error. – NitinM Sep 20 '19 at 17:14
  • @NitinM - Do you see any more info on that the exception might be - maybe it is logged to stack-driver somewhere? – kurtisvg Sep 20 '19 at 19:35
0

It is a bit annoying to see "error: com.mysql.jdbc.Driver not found" and nothing more that can point to the root cause. Nevertheless, the error message in Data Fusion it is correct because the mysql Driver class is not included when the package is manually built. The issue is coming from this line:

<scope>provided</scope>

in the cloud-sql-jdbc-socket-factory/connector-j-8/pom.xml file which means that the target package won't include the mysql-connector-java jar file, the one that contains com.mysql.jdbc.Driver and others. The error can be resolved if you construct the jar connector without the aforementioned line.

rsantiago
  • 2,054
  • 8
  • 17