10

I want to create a database within a pipeline script to be used by the deployed app. But first I started testing the connection. I got this problem:

java.sql.SQLException: No suitable driver found for jdbc:mysql://mysql:3306/test_db

I have the database plugin and the MySQL database plugin installed.

How do I get the JDBC driver?

import groovy.sql.Sql
node{

    def sql = Sql.newInstance("jdbc:mysql://mysql:3306/test_db", "user","passwd", "com.mysql.jdbc.Driver")
    def rows = sql.execute "select count(*) from test_table;"
    echo rows.dump()
}

Update after albciff answer:

My versions of:

Jenkins = 2.19.1

Database plugin = 1.5

Mysql database plugin = 1.1

The latest test script.

import groovy.sql.Sql

Class.forName("com.mysql.jdbc.Driver")

Which throws:

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
Mikael Svensson
  • 692
  • 1
  • 7
  • 20

3 Answers3

3

From the MySQL DataBase Plugin documentation you can see that jdbc drivers for MySQL are included:

Note that MySQL JDBC driver is under GPLv2 with FOSS exception. This plugin by itself qualifies under the FOSS exception, but if you are redistributing this plugin, please do check the license terms. Drizzle(+MySQL) Database Plugin is available as an alternative to this plugin, and that one is under the BSD license.

More concretely the actual last version (1.1) for this plugin contains connector version 5.1.38:

Version 1.1 (May 21, 2016) mysql-connector version 5.1.38

So probably in order to have the driver available you have to force the driver to be registered.

To do so use Class.forName("com.mysql.jdbc.Driver") before instantiate the connection in your code:

import groovy.sql.Sql
node{
    Class.forName("com.mysql.jdbc.Driver")
    def sql = Sql.newInstance("jdbc:mysql://mysql:3306/test_db", "user","passwd", "com.mysql.jdbc.Driver")
    def rows = sql.execute "select count(*) from test_table;"
    echo rows.dump()
}

UPDATE:

In order to has the JDBC connector classes available in the Jenkins pipeline groovy scripts you need to update the DataBase plugin to last currently version:

Version 1.5 (May 30, 2016) Pipeline Support

albciff
  • 18,112
  • 4
  • 64
  • 89
  • 2
    Thanks for confirming that it contains mysql-connector. Tested your code and got: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver – Mikael Svensson Oct 13 '16 at 13:40
  • @MikaelSvensson but if you're receiving `ClassNotFoundException` for `com.mysql.jdbc.Driver` then seems that in your groovy context the mysql-connector is not available. – albciff Oct 13 '16 at 13:49
  • @MikaelSvensson I'm not a jenkins expert but checking the [DataBase plugin](https://wiki.jenkins-ci.org/display/JENKINS/Database+Plugin) seems that to use `Pipeline Support` you need the last version (1.5) of this plugin. Can you check if you've this version of the plugin? – albciff Oct 13 '16 at 13:52
  • @MikaelSvensson I install DataBase plugin 1.5 and mySql DataBase plugin 1.1 in my jenkins, and `Class.forName("com.mysql.jdbc.Driver")` works as expected – albciff Oct 13 '16 at 14:05
  • 1
    I can confirm that i have DB plugin 1.5 and mysql db plugin 1.1 installed. And it does not work. I tried to reinstall the mysql db plugin also. I still get ClassNotFoundException. – Mikael Svensson Oct 13 '16 at 14:36
  • @MikaelSvensson strange it works correctly for me as a groovy pipline with this plugin versions, then I don't know what could be wrong... I'm not sure but can you try to restart your jenkins (`http://yourServer/jenkins/safeRestart`) ? – albciff Oct 13 '16 at 14:46
  • 1
    I have updated jenkins to 2.19.1 and all other plugins to the newest version and restarted jenkins with safeRestart. But the same result. I have to fall back to command line mysql which is not optimal. – Mikael Svensson Oct 14 '16 at 06:20
  • You could use the code in http://stackoverflow.com/a/27131375/1168698 to check that the mysql jar is indeed in the classpath when the script runs. If it isn't (which is probably the case) copy it where it'll be found. – sensei Oct 14 '16 at 17:25
  • I am trying to use jenkins pipeline to get data from Oracle database. i have tried to setupo database plugins. However I am not sure how to connet to Oracle DB using jenkins pipeline, is there any advice? – Joey Trang Aug 15 '17 at 08:27
0

You can simply add the java connector in the java class path.

If jenkins is running java < 9 you probably will find the right place inside something like that: <java_home>/jre/lib/ext

If jenkins is running java >= 9 you probably will find the right place inside something like that: /usr/share/jenkins/jenkins.war

To find your paths you can check:

  • http://your.jenkins.host/systemInfo (or navigate system info path by GUI) and search for java.ext.dirs or java.class.path
  • http://your.jenkins.host/script (running console script such as System.getProperty("java.ext.dirs") or System.getProperty("java.class.path"))

This snippet can help you with the jenkins.war thing when running inside docker:

#adding extra jars to default jenkins java classpath (/usr/share/jenkins/jenkins.war)
RUN sudo mkdir -p /usr/share/jenkins/WEB-INF/lib/
RUN whereis jar #just to find full jar command classpath to use with sudo
COPY ./jar-ext/groovy/mysql-connector-java-8.0.21.jar /usr/share/jenkins/WEB-INF/lib/
RUN cd /usr/share/jenkins && sudo /opt/java/openjdk/bin/jar -uvf jenkins.war ./WEB-INF/lib/mysql-connector-java-8.0.21.jar
Marco Hackuno Guassone
  • 51
  • 6
0

For Jenkins running on Java >= 9 add the jdbc drivers under ${JENKINS_HOME}/war/WEB-INF/lib and under the --webroot directory.

Stelios
  • 1,294
  • 3
  • 12
  • 28