java.io.FileNotFoundException: jenkins_audit2db.ddl (Permission denied) on doGenerateDdl

[coderfi]

Per installation instructions, I go to the Jenkins Settings, configure the JDBC connection, click on Advanced and hit the Generate DDL

The jenkins log shows a permission denied error.

I am using a standalone Jenkins RPM installation (https://wiki.jenkins-ci.org/display/JENKINS/Installing+Jenkins+on+RedHat+distributions)

I suspect the DDL generator is trying to write in a directory that does not have write permissions for my jenkins user... possibly because an expected environment variable was not defined (such as JENKINS_HOME).

In any case, if you could log the full write path, I could at least hack around the issue and grant write permissions in that directory to the user (this wouldn't help end users, but would help sysadmins solve the issue).

java.io.FileNotFoundException: jenkins_audit2db.ddl (Permission denied) at java.io.FileOutputStream.open(Native Method) at java.io.FileOutputStream.(FileOutputStream.java:221) at java.io.FileOutputStream.(FileOutputStream.java:110) at java.io.FileWriter.(FileWriter.java:63) at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:247) at org.jenkins.plugins.audit2db.internal.data.HibernateUtil.getSchemaDdl(HibernateUtil.java:92) at org.jenkins.plugins.audit2db.internal.DbAuditPublisherDescriptorImpl.doGenerateDdl(DbAuditPublisherDescriptorImpl.java:271) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606)

[tnguyen1]

Here's a workaround that worked for me to generate DDL for MySQL database.

Open a script console in Jenkins on master node @ http://JENKINS_URL/computer/(master)/script and execute the following script. As a result it generates the DDL file in the ${java.io.tmpdir} directory (eg. /tmp on Linux) on Jenkins host.

HTH

import java.io.File;
import java.io.IOException;
import java.util.Properties;
import java.util.Scanner;

import org.hibernate.HibernateException;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

final String dialect = "org.hibernate.dialect.MySQLDialect";

final Properties props = new Properties();
props.put("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
props.put("hibernate.connection.url", "jdbc:mysql://my_host:3306/my_database_name");
props.put("hibernate.connection.username", "my_user");
props.put("hibernate.connection.password", "my_password");
props.put("hibernate.dialect", dialect);

final Configuration config = new AnnotationConfiguration().configure();
config.addProperties(props);

final SchemaExport generator = new SchemaExport(config);
final File tempDdlFile = File.createTempFile("jenkins_audit2db", ".ddl");
generator.setOutputFile(tempDdlFile.getPath());
generator.setFormat(true);
generator.setDelimiter(";");
generator.execute(true, false, false, true);

println "Generated DDL at [" + tempDdlFile.getPath() + "]";

[dshvedchenko]

it tries to crate temp file in / folder , so you can temporary grant chmod a+w . , then generate your ddl , and then revoke

[bojjac]

I am also facing the same issue as i configured mysql as DB. As tnguyen1 mentioned, it worked for me.

[ip2k]

Thanks @dshvedchenko , I chmod'd / and /tmp to 777 temporarily to generate the DDL and that worked. In case anyone needs the stuff the DDL generates for mysql, here is the SQL to set it up (assuming you've already created a database and it's active when you're running this query):

create table JENKINS_BUILD_DETAILS (
        id varchar(255) not null unique,
        duration bigint,
        endDate datetime,
        fullName varchar(255) not null,
        name varchar(255) not null,
        result varchar(255),
        startDate datetime not null,
        userId varchar(255),
        userName varchar(255),
        node_url varchar(255),
        primary key (id)
    );

    create table JENKINS_BUILD_NODE (
        url varchar(255) not null unique,
        description varchar(255),
        displayName varchar(255) not null,
        label varchar(255),
        masterAddress varchar(255) not null,
        masterHostName varchar(255) not null,
        name varchar(255) not null,
        primary key (url)
    );

    create table JENKINS_BUILD_PARAMS (
        id varchar(255) not null unique,
        name varchar(255) not null,
        value varchar(255),
        buildDetails_id varchar(255) not null,
        primary key (id)
    );

    alter table JENKINS_BUILD_DETAILS 
        add index FK7EF9B948198EB4F5 (node_url), 
        add constraint FK7EF9B948198EB4F5 
        foreign key (node_url) 
        references JENKINS_BUILD_NODE (url);

    alter table JENKINS_BUILD_PARAMS 
        add index FK3961B5A06C847107 (buildDetails_id), 
        add constraint FK3961B5A06C847107 
        foreign key (buildDetails_id) 
        references JENKINS_BUILD_DETAILS (id);

(make sure to create a user and grant them permissions on the DB this stuff is in)

Here's the JDBC driver for mysql: http://dev.mysql.com/downloads/connector/j/ Download and extract this, then put the mysql-connector-java-5.1.39-bin.jar (yours might be a different version number) in `/var/lib/jenkins/plugins/audit2db/WEB-INF/lib, make sure it's readable by the jenkins user, and restart Jenkins to pick it up.

Driver class is com.mysql.jdbc.Driver and the JDBC URL is like jdbc:mysql://your-mysql-hostname-or-ip/db-name?user=db-user&password=db-user-password

Make sure to enable the post-build step to log the build to the database for a project, build it (and wait for it to finish), then see if SELECT * FROM audit.JENKINS_BUILD_DETAILS; gets you anything.

Hopefully this saves someone a few hours :)

[youssefboudaya]

@ip2k hey can you provide the DDL generated for postgres ?