Issues with Java's JDBC (MySQL) for linux - Crontab job causes connection failure in java program, but when run manually is fine

Question

So as the title states, Ive got an issue with Crontab and my Java program connecting to a remote MySQL database.

The program itself is quite simple: it connects to the database, grabs two tables (users & passwords), then assigns that to a temporary variable and pushes the variable output to a shell script that creates a new user w/ home directory and a password.

The program itself works when run manually, however when I try to run the program through Crontab, it fails to connect to the database. Now, I have tried a variety of different things to get this to work such as assigning the crontab to different times in the day (we have other programs accessing the database as well, so I assigned it a time when the others didnt.), but that didn't work and just gave me the same issue. I have tried running the Java program directly from crontab, through a script assigned to crontab, and through a script that activates the previous script... and each method fails. (The scripts run, but again - no DB connection is made).

I am pulling my hair out at this point trying to figure this out... It works when I manually run it, yet fails when crontab tries to run it... (WTF?)

Furthermore, Crontab seems to run fine as it executes all the items in the list, but when it gets to my Java program, the connection is never made for some reason...

So if anyone has a clue as to what may be happening here, I would greatly appreciate the input.

---

Also, if anyone can tell me how to print the error output of the SQL connection through Java, it may help me / us to diagnose the issue...

Thanks again!

---

Additional information:

Running Ubuntu 4.4.3
Latest version of linux JDK from apt-get
Latest version of linux Java MySQL integration from apt-get
I do *NOT* have MySQL installed locally! everything is remote.

Crontab line used to execute java program:

  30 14 *  *  * java -cp $CLASSPATH:/home/user/java myprogram > /home/user/java/user_logfile.txt

Classpath:

/usr/share/java/mysql-connector-java-5.1.10.jar

---

Updated Information:

Using Jukka's advice about the printStackTrace, I looked up on google for a method to send the stacktrace to the System.out.println function so crontab could more accurately give me a log digest of what was going on. I managed to find:

catch(Exception e)
{
ByteArrayOutputStream file = new ByteArrayOutputStream();
PrintStream stream = new PrintStream(file);
e.printStackTrace(stream);
System.out.println("The result is :"+file.toString().trim());
}

which helped me to find this:

Error:
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:217)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:205)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:321)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:294)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:266)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:186)
        at prog_users.main(prog_users.java:24)

So the main error is that the classpath for crontab cannot find the jdbc driver!

Now... how do I change the CLASSPATH for a process that has no home directory or .rcbash...?

Answer 1

Cron will mail you the output of all jobs it runs, so make sure that on failure your program outputs enough information so that you can diagnose the problem.

When your your Java program fails, print at least the exception stack trace to the standard error stream and exit with a non-zero exit code:

try {
    // your code here
    // ...
} catch (Exception e) {
    e.printStackTrace();
    System.exit(1);
}

Also, check that your mail delivery is set up correctly, so that you will receive the mails that cron sends you.

Answer 2

Solution:

Jukka's hint about the StackTrace tipped me off on the solution, so a good majority of the credit goes to him for helping me find the solution.

---

So basically, I was working on this problem with a windows frame of mind. I figured that since I set the CLASSPATH in Root (which crontab was running from), that the CLASSPATH would be defined. However, for Linux this is not the case - crontab needs to have the CLASSPATH set.

which led me to:

In the java program, I replaced the

}catch(Exception e){
    e.printStackTrace();
}

with

}catch(Exception e){
    ByteArrayOutputStream file = new ByteArrayOutputStream();
    PrintStream stream = new PrintStream(file);
    e.printStackTrace(stream);
    System.out.println("The result is :"+file.toString().trim());
}

so that my log file would actually SHOW the error output in the logfile when run from crontab.

---

So, on to the solution:

Create a script in /home/user/java called script1

script1:

#!/bin/bash
export CLASSPATH=/usr/share/java/mysql-connector-java-5.1.10.jar
java -cp $CLASSPATH:/home/user/java myprogram > /home/user/java/user_logfile.txt

Then used sudo chmod 0755 /home/user/java/script1 to make it executable.

-- -- -- -- --

Then replace the crontab line:

  30 14 *  *  * java -cp $CLASSPATH:/home/user/java myprogram > /home/user/java/user_logfile.txt

with:

  30 14 *  *  * /home/user/java/script1