Search code examples
mysqljenkins-pipeline

connecting to mysql db from jenkins pipeline not wroking

I have Jenkins v2.219 installed (running in docker if it matters) and iv'e installed:

Database plugin v1.5

MySQL Database Plugin v1.3

I've created the simplest pipeline to check the db connection and it doesn't work. My pipeline is:

import groovy.sql.Sql
node { 
    Class.forName("com.mysql.jdbc.Driver")
    def sql = Sql.newInstance("jdbc:mysql://<mysql_db_host>:3306/<db_name>", "myuser","mypass", "com.mysql.jdbc.Driver")
    sql.execute "SELECT * FROM table"   
}

And when I run it I get:

Rebuilds build #47
Running in Durability level: MAX_SURVIVABILITY
Running on Jenkins in /var/jenkins_home/workspace/pipeline_playground
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:419)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
    at org.eclipse.jetty.webapp.WebAppClassLoader.loadClass(WebAppClassLoader.java:543)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
    at org.codehaus.groovy.runtime.callsite.CallSiteClassLoader.loadClass(CallSiteClassLoader.java:54)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:352)
    at org.codehaus.groovy.reflection.ClassLoaderForClassArtifacts.loadClass(ClassLoaderForClassArtifacts.java:60)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at java_lang_Class$forName$1.callStatic(Unknown Source)
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCallStatic(CallSiteArray.java:56)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callStatic(AbstractCallSite.java:194)
    at org.kohsuke.groovy.sandbox.impl.Checker$2.call(Checker.java:191)
    at org.kohsuke.groovy.sandbox.GroovyInterceptor.onStaticCall(GroovyInterceptor.java:35)
    at org.jenkinsci.plugins.scriptsecurity.sandbox.groovy.SandboxInterceptor.onStaticCall(SandboxInterceptor.java:186)
    at org.kohsuke.groovy.sandbox.impl.Checker$2.call(Checker.java:189)
    at org.kohsuke.groovy.sandbox.impl.Checker.checkedStaticCall(Checker.java:193)
    at org.kohsuke.groovy.sandbox.impl.Checker.checkedCall(Checker.java:98)
    at com.cloudbees.groovy.cps.sandbox.SandboxInvoker.methodCall(SandboxInvoker.java:17)
    at WorkflowScript.run(WorkflowScript:3)
    at ___cps.transform___(Native Method)
    at com.cloudbees.groovy.cps.impl.ContinuationGroup.methodCall(ContinuationGroup.java:86)
    at com.cloudbees.groovy.cps.impl.FunctionCallBlock$ContinuationImpl.dispatchOrArg(FunctionCallBlock.java:113)
    at com.cloudbees.groovy.cps.impl.FunctionCallBlock$ContinuationImpl.fixArg(FunctionCallBlock.java:83)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at com.cloudbees.groovy.cps.impl.ContinuationPtr$ContinuationImpl.receive(ContinuationPtr.java:72)
    at com.cloudbees.groovy.cps.impl.ConstantBlock.eval(ConstantBlock.java:21)
    at com.cloudbees.groovy.cps.Next.step(Next.java:83)
    at com.cloudbees.groovy.cps.Continuable$1.call(Continuable.java:174)
    at com.cloudbees.groovy.cps.Continuable$1.call(Continuable.java:163)
    at org.codehaus.groovy.runtime.GroovyCategorySupport$ThreadCategoryInfo.use(GroovyCategorySupport.java:129)
    at org.codehaus.groovy.runtime.GroovyCategorySupport.use(GroovyCategorySupport.java:268)
    at com.cloudbees.groovy.cps.Continuable.run0(Continuable.java:163)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable.access$001(SandboxContinuable.java:18)
    at org.jenkinsci.plugins.workflow.cps.SandboxContinuable.run0(SandboxContinuable.java:51)
    at org.jenkinsci.plugins.workflow.cps.CpsThread.runNextChunk(CpsThread.java:185)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup.run(CpsThreadGroup.java:405)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup.access$400(CpsThreadGroup.java:96)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup$2.call(CpsThreadGroup.java:317)
    at org.jenkinsci.plugins.workflow.cps.CpsThreadGroup$2.call(CpsThreadGroup.java:281)
    at org.jenkinsci.plugins.workflow.cps.CpsVmExecutorService$2.call(CpsVmExecutorService.java:67)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at hudson.remoting.SingleLaneExecutorService$1.run(SingleLaneExecutorService.java:131)
    at jenkins.util.ContextResettingExecutorService$1.run(ContextResettingExecutorService.java:28)
    at jenkins.security.ImpersonatingExecutorService$1.run(ImpersonatingExecutorService.java:59)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Finished: FAILURE

I've also tried running it without: "Class.forName("com.mysql.jdbc.Driver")" that gave me "java.sql.SQLException: No suitable driver found for ..."

UPDATE:

btw, i do see:

database-mysql.jar, mysql-connector-java-8.0.13.jar, protobuf-java-3.6.1.jar

at: /plugins/database-mysql/WEB-INF/lib

Solution

  • I had the same problem and this was how I fixed it. First link the database jar files to your jenkins system. To find out where jenkins is looking for jar files go to the script console of your jenkins server and run this code

    println System.getProperty("java.ext.dirs")

    Result

    /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.232.b09-0.fc29.x86_64/jre/lib/ext:/usr/java/packages/lib/ext

    Next go to one of those directories and create symlinks to the jar files of the plugins. On a linux system...

    cd /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.232.b09-0.fc29.x86_64/jre/lib/ext

ln -s /var/lib/jenkins/plugins/database-mysql/WEB-INF/lib/database-mysql.jar 
ln -s /var/lib/jenkins/plugins/database-mysql/WEB-INF/lib/mysql-connector-java-8.0.13.jar

    Restart jenkins so that the jar files are loaded.

    Now you can query your MySQL database from your jenkins pipeline

    import groovy.sql.Sql
node{
    def conn = Sql.newInstance("jdbc:mysql://localhost:3306/cms", "fred", "fredpassword", "com.mysql.jdbc.Driver")
    def rows = conn.rows("select username from users LIMIT 10")
    assert rows.size() == 10
    println rows.join('\n')
}