I am using Knime 4.1.2 and I have this weird issue where my MySQL Connector node’s Hostname setting seems to be changed/swapped on-the-fly when executing my workflow on cloud servers specifically (tried with both Amazon AWS and DigitalOcean!).
The workflow works perfectly fine on my Windows workstation and my own Ubuntu18 server, but when I run it on a DigitalOcean droplet or Amazon EC2 server, the Hostname setting gets replaced with the server’s own hostname/IP address, which of course results in a MySQL connection error (java.sql.SQLException: Access denied for user...). I cannot comprehend why this happens!
This is the exact procedure I did on my own Ubuntu18 server where I was able to run the workflow, and the procedure I also applied for cloud servers where I encounter this problem:
- Install Amazon Corretto 8 JDK
- Download the KNIME package and extract it
- Copy my workflow to the server
- Execute my workflow from the command line
Is there any reason why KNIME would replace my node’s parameter at execution when running on a cloud server specifically? I am really looking forward to some guidance on this one. Thanks a lot!
Hello hbCyber,
this is indeed a very strange behavior. I’m pretty certain that the KNIME DB nodes do not change the hostname on the fly. You can double check this by enabling the JDBC logger via the Advanced Tab of the MySQL Connector node. This enables detailed logging of all JDBC related activities and you should find an entry in the KNIME log similar to this one:
connectionController=org.knime.database.connection.UserDBConnectionController=(url=jdbc:mysql://:3306/mysql, authenticationType=USER_PWD, user=<YOUR_DB_USER>, password=true)
The JDBC logger uses DEBUG as log level so you might need to change the KNIME log file or KNIME Console log level to DEBUG as well. For more details about this see the Workbench Guide.
Is it possible that the DNS entries for the hostname point to the wrong IP address? Did you try to ping the db host?
Bye
Tobias
Hi @tobias.koetter ! I need a bit more help to set up logging properly. I am not using the KNIME GUI since this is a Linux server (terminal only). I have tried modifying the log4j3.xml in my knime-workspace/.metadata/ folder to set its level to TRACE, but even with that, I cannot see the JDBC output (although I did activate it in the Advanced Tab of the MySQL Connector node).
I couldn’t find any part of the Workbench guide that tells me the location of another configuration file that allows me to set the desired log level in knime.log
I have also pinged the target host and confirmed that the ping resolves to the correct IP, and therefore it is only the KNIME workflow that chooses the wrong IP. I even tried hardcoding the static IP of the target database in the workflow and it still got swapped during execution.
May I ask that you tell me know how I can enable DEBUG output for the JDBC logger in a terminal? Or where else I could find the JDBC logger output?
Note that I have tried switching to the generic DB Connector node, and the same issue still occurs.
The settings.xml of my DB Connector node show the correct target host:
<config key=“attributes”/>
<entry key=“db_url” type=“xstring” value=“jdbc:mysql://[redacted, but CORRECT]”/>
</config>
And when I run the workflow, with this command:
~/knime_4.1.2/knime -nosplash -application org.knime.product.KNIME_BATCH_APPLICATION -workflowDir=’/root/knime-workspace/[project]/’ -credential=‘mysql_credentials;[redacted];[redacted]’ -reset -nosave -fail onloaderror --launcher.suppressErrors
I get the error as if the workflow was trying to connect to the local server’s IP!
ERROR KNIME-Worker-3-DB Connector 0:26 Node Execute failed: Access denied for user ‘[redacted]’@’[the wrong IP! server’s own IP!]’ (using password: YES)
java.sql.SQLException: Access denied for user ‘[redacted]’@’[the wrong IP! server’s own IP!]’ (using password: YES) at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
Hello hbCyber,
this is a DB configuration problem. You have to grant your db user access to the database from the IP address of the machine KNIME is running on or simply for all remote addresses as described here.
Bye
Tobias
2 Likes
Hi @tobias.koetter – THANK YOU, your answer led me to the solution.
As it turns out, in all my cloud servers where I tried to run KNIME, I had a “floating” IP attached to them, which means they keep that IP even if I reboot them etc. So each server had an initial IP + a floating IP.
When I opened up the database server access, I had granted access to the “floating” IP. But when the server on which KNIME is running was trying to connect, it wasn’t recognized as a connection attempt coming from the “floating” IP, but just the initial IP instead. So all I had to do was to open up access to the non-floating IP instead.
Looking back at it, I see that I misinterpreted the JDBC error message to mean that the user@host was trying to connect to “host” but was actually telling me that the user FROM that host had been denied…
Thanks a million, I really appreciate it!
1 Like
I’m glad it works now.
Bye
Tobias