How do I install JDBC drivers (in this case for MS SQL Server) on Knime Server?
I've checked the installation manual, but couldn't find anything related.
Thanks
Tim
Copy the driver's JAR file somewhere on the server. Then adjust the database preferences in a KNIME instance so that they include the JAR file. Export the preferences and put the file into the correct location on the server. This is described in the installation manual on page 9 ("INSTALLATION OF THE OPEN SOURCE KNIME PLATFORM").
I've been trying to get this to work, but my workflow will not execute on the server. The error is always the same:
There are messages for workflow "ConnectionTest 2015-03-20 08.42.00"
Database Reader 2:30 - ERROR: Execute failed: org.knime.core.node.InvalidSettingsException: Could not register database driver "com.microsoft.sqlserver.jdbc.SQLServerDriver", reason: com.microsoft.sqlserver.jdbc.SQLServerDriver
I've create a .preference.epf file, and put it in /srv/knime_server.
vmfpdm01:/srv/knime_server # ls -la total 48 drwxr-xr-x 9 knime users 4096 Mar 20 07:41 . drwxrwxrwx 5 root root 4096 Jan 21 14:51 .. -rw-r--r-- 1 root root 10986 Mar 20 07:41 .preferences.epf drwxr-xr-x 8 knime users 4096 Mar 20 07:41 jobs drwxr-xr-x 2 knime users 4096 Jan 21 14:51 licenses drwxr-xr-x 2 knime users 4096 Jan 21 14:53 locks drwxr-xr-x 4 knime users 4096 Jan 23 21:51 runtime drwxr-xr-x 2 knime users 4096 Mar 20 07:38 temp drwxr-xr-x 3 knime users 4096 Feb 20 14:47 trash drwxr-xr-x 6 knime users 4096 Mar 20 07:40 workflows
Since my Knime instance on my laptop runs under Windows, and the server is a Linux machine, it didn't make sense to me to just copy the preferences file, because it is full of Windows paths on my laptop, and the paths on the server are completely different. So I changed the one line in the preferences file:
/instance/org.knime.workbench.core/database_drivers=/opt/drivers/sqljdbc_4.1/enu/sqljdbc.jar;/opt/drivers/sqljdbc_4.1/enu/sqljdbc4.jar;/opt/drivers/sqljdbc_4.1/enu/sqljdbc41.jar
As you can see, this is the correct location of the driver files:
vmfpdm01:/ # ls -l /opt/drivers/sqljdbc_4.1/enu/ total 1740 drwxr-xr-x 4 fifthplay users 4096 Mar 20 07:24 auth -rw-r--r-- 1 fifthplay users 1178 Mar 20 07:24 install.txt -rw-r--r-- 1 fifthplay users 14286 Mar 20 07:24 license.txt -rw-r--r-- 1 fifthplay users 4299 Mar 20 07:24 release.txt drwxr-xr-x 7 fifthplay users 4096 Mar 20 07:24 samples -rw-r--r-- 1 fifthplay users 563935 Mar 20 07:24 sqljdbc.jar -rw-r--r-- 1 fifthplay users 585020 Mar 20 07:24 sqljdbc4.jar -rw-r--r-- 1 fifthplay users 586192 Mar 20 07:24 sqljdbc41.jar drwxr-xr-x 4 fifthplay users 4096 Mar 20 07:24 xa
I also tried to have only this line in the .preferences file.
None of this seems to work.
What am I doing wrong???
Tim
Having a KNIME Linux server in a Windows Environment is a little bit tricky, especially, when you have a "real" server without any graphical environment (just terminals) so you can even use VNC or similar.
To still access the Linux KNIME and confiure it (including the export of the preference file) and for debugging the workflows on the server, I'm usually forwarding the X session with KNIME from the Linux machine to my Windows client... e.g. by using CYGWIN / startxon the Windows machine, connecting via ssh -Y to the server and starting KNIME there, while the GUI is shown on my Windows client.
JDBC drivers are initialized when KNIME starts (in your case the RMI instance that executed the workflows). If a driver cannot be loaded during startup there is a warning message in the log file "Can't load driver file xyz...". Can you check the message in the RMI's log (in the "runtime" folder) ?
Unfortunately, that's not an option. The server is a headless one, it does not have any GUI related stuff installed on it.
I found a temporary workaround: just put the jar (driver) files in the workflow directory.
Is there any way to have verbose logging about the parsing of the preferences file?
Tim
Thanks for the hint on the log file, and the fact that drivers are initialized when KNIME starts. That did the trick!
I did a restart of glassfish and it looks like it is working now. I no longer have the jar files in the workflow directory, and
In the log file ( .metadata/knime/knime.log ) I saw a warning about the temp dir. It was set to D:\Temp in the preferences file. So I changed the line that sets the temporary directory to something more appropriate:
/instance/org.knime.workbench.core/knime.tempDir=/tmp
With this fixed, I don't see any more warnings, and other workflows can access the db as well.
Mission accomplished.
Tim
Dear Knime-Team,
we tried to follow the post ... up to now without succes.
We have the following setup and like to use the ojdbc7.jar from oracle as database driver on the KNIME-Server
The KNIME-Server runs on a RedHat-Linux without any X. The Tomee, + KNIME Workflows, e.g. the Demo Workflow "Extract System and Environment Variables" works fine.
Locally we however use Windows and develop the Workflows in Windows. There we were able to add the ojdbc7.jar and use it.
How do we get the Database workflows to run on the server? Aka. how do we provide the ojdbc7.jar to the knime_executor on the linux machine?
The steps described above are still valid. The only thing that has changed is the name and location of the preference file on the server. How to provide a preference file is described in the administration manual on page 8 ("Preferences File").
We have a scenario similar to the one described by "twuyts" and "mergen":
1. Our developers run KNIME Analytics platforms on their laptops. The operating system is Microsoft Windows 10.
2. We have installed KNIME Server on a virtual machine running RedHat 7 Linux.
3. We developed a workflow that connects to a Teradata database. The workflow uses two Teradata JDBC drivers. This works great on our laptops.
4. We deployed the workflow to KNIME Server. We're unable to run the workflow there because it cannot find the JDBC drivers.
5. The KNIME Server administration manual states that the solution is to export the preferences from KNIME Analytics Platform and then copy that file to the /config folder on the server that is hosting KNIME Server. This worked for us, but there are a few additonal steps required.
Here are the additional steps:
1. After exporting the file, open it up and modify the line that specifies the location of the Teradata drivers. For example, change
/instance/org.knime.workbench.core/database_drivers=C\:\\software\\JDBC
to
/instance/org.knime.workbench.core/database_drivers=/apps/JDBC/teradata
Important: Replace the paths above with the paths where you keep the drivers.
2. Copy this preferences.epf file to the the server where you are running KNIME Server. For example, if you installed KNIME Server to
/my-apps/knime_server
then copy the prefences.epf file to
/my-apps/knime_server/workflow_repository/config/
3. Do the following on the Linux server where KNIME Server is installed
a. Run "knime_server/apache-tomee-plus-1.7.1/bin/shutdown.sh"
b. Do a "ps -ef | grep knime". Make sure that the knime executor processes are not running before going on to the next step. Once (and once only) we found that the executor processes would not go away. We had to kill the processes.
c. Run "knime_server/apache-tomee-plus-1.7.1/bin/startup.sh".
That is what we had to do in order to get things to work.
Now, I did not like the fact that the prefences.epf file had a lot of lines in there that were very specific to my laptop. I then decided to remove all lines with the exception of the line I wanted (the JDBC line), but my workflow did not execute successfully when I did that. Through trial and error, I managed to trim the preferences.epf file down to three lines. That's how I have it now and things work. I have not found any documentation stating that this is an OK thing to do: all documentation implies I should copy the exported file as is to the server.
This is what my preferences.epf file looks like now:
#Mon Jan 16 09:50:31 CST 2017
file_export_version=3.0
/instance/org.knime.workbench.core/database_drivers=/apps/JDBC/teradata
In the future I would really like to see an INI file for KNIME Server where I could just set a path to the JDBC folders.
3 Likes
Hi Carlos,
Thanks for the detailed description -- it's all correct. Modifying the preferences and tweaking file paths is not easy (and also not well documented). In fact, we are not very happy with that solution either and have planned some changes to this. We'd like to remove the dependency on Eclipse Preferences (this is what it is ... it's something that comes with the underlying framework and we are just using that) and instead have some other sort of configuration option that is easier to maintain and portable (also among test/dev/prod systems).
Thanks again!
Bernd