Tagged with MySQL - Visokio Forums http://forums.visokio.com/discussions/tagged/MySQL/feed.rss Mon, 30 Oct 17 16:11:08 -0400 Tagged with MySQL - Visokio Forums en-CA Linux Headless Server/Scheduler - Unable to read data from database http://forums.visokio.com/discussion/2736/linux-headless-serverscheduler-unable-to-read-data-from-database Wed, 01 Oct 2014 14:04:01 -0400 Veaceslav 2736@/discussions Unable to read data from database errors on a headless Omniscope server or scheduler running on Linux.

If you can run Omniscope Desktop on your machine, you don't need to follow these instructions. Instead, you should run Omniscope Desktop, open your file, go to Data Manager and expand your database block. A dialog should be displayed asking you to locate your database driver. Select the jar file, re-load your data and save the file.

Note: Database (jar file) drivers should be downloaded from the official website distributing the driver.

If you cannot run Omniscope Desktop on your machine to configure the driver from the UI (e.g. you have a headless Linux machine) then you should follow the next steps:

1. Check whether you need to specify a driver location
  • If you already know that the driver location is wrong or missing, go to step 2.
  • If you get only 'Error Unable to read data from database' without any additional details, you need to check whether the driver location is the root cause of the problem.

    a. Edit {OMNISCOPE_INSTALL_DIR}/_launch.sh 

        {OMNISCOPE_INSTALL_DIR} is the directory where Omniscope is installed

    Change JAVA_VM_PARS property to include both AppSetting_APPSETTING_REPORT_RECOGNISED_ERRORS_AS_BUGS and AppSetting_APPSETTING_REPORT_DATABASE_ERRORS_AS_BUG, leave all other JVM flags unchanged.

    E.g.
    JAVA_VM_PARS='-Xms64M -Xmx1100m -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=50 -XX:NewRatio=8 -XX:+UseG1GC -DAppSetting_APPSETTING_REPORT_RECOGNISED_ERRORS_AS_BUGS=true -DAppSetting_APPSETTING_REPORT_DATABASE_ERRORS_AS_BUGS=true'

    b. Run your tasks again
    At this point you should get more details about the error and it should look like this:

    ...driver JAR file location not specified. Please reconfigure this legacy file's database connection in the desktop application.


2. Configure Omniscope to use a specific driver
My example shows how to configure Omniscope to use a MySQL driver. Other database types are very similar.
  1. Download MySQL JDBC driver from MySQL website.
    E.g. http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.33.zip
  2. Unzip, get the jar file and put it somewhere on the machine running Omniscope scheduler
    E.g. /home/visokio/visokio-omniscope-2.9-b1200/lib/mysql-connector-java-5.1.33-bin.jar
  3. Edit {OMNISCOPE_INSTALL_DIR}/_launch.sh again
    Modify JAVA_VM_PARS property to include the JDBC driver location and remove the flags added at step 1.(a)
    E.g.
    JAVA_VM_PARS='-Xms64M -Xmx1100m -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=50 -XX:NewRatio=8 -XX:+UseG1GC -DMySqlDriverSearch_driverDir=/home/visokio/visokio-omniscope-b620/lib/mysql-connector-java-5.1.33-bin.jar'

Note: Here are property names for the other databases (make sure you prepend a '-D' in front of the property e.g -DMySqlDriverSearch_driverDir):

IngresSqlDriverSearch_driverDir
MySqlDriverSearch_driverDir
OracleThinDriverSearch_jarFile
PostgreSqlDriverSearch_driverDir
SqlServer2008DriverSearch_jarFile

These steps configured Omniscope scheduler/server to locate and use database drivers when running in headless mode.

Note: If you configure your file on one machine, then use it on another machine, the driver needs to be added manually on the second machine, otherwise you will get the 'Unable to read data from database' error.]]> Data sources: Connecting to Amazon RDS database MySQL using ssl http://forums.visokio.com/discussion/2651/data-sources-connecting-to-amazon-rds-database-mysql-using-ssl Tue, 22 Jul 2014 08:25:53 -0400 donald 2651@/discussions

1. Download the public key for Amazon RDS.
http://s3.amazonaws.com/rds-downloads/mysql-ssl-ca-cert.pem

2. Add the key to the java keystore for use with Omniscope.

You will need to install the latest Java SE Development Kit 7 to use the keytool. We will also be using the root certificates file provided with the java runtime.

Java SE Development Kit 7 Downloads

http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html

Create a directory for working with the certificates.
Copy the Amazon RDS public key file that you downloaded into it.
Copy the cacerts authority file from the jdk jre security library directory.

mkdir C:\Users\USERNAME\omniscope-server\security
cd C:\Users\USERNAME\omniscope-server\security
copy "C:\Users\USERNAME\Downloads\mysql-ssl-ca-cert.pem” "C:\Users\USERNAME\omniscope-server\security\”
copy "C:\Program Files\Java\jdk1.7.0_65\jre\lib\security\cacerts” "C:\Users\USERNAME\omniscope-server\security\”

Import the Amazon RDS public key into the authority file.

"C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -import -v -alias awsrdsmysql -file mysql-ssl-ca-cert.pem -keystore cacerts

You will be prompted for the password which is "changeit”

You will be prompted to Trust this certificate”, type yes

Certificate was added to keystore
[Storing cacerts]

You can view the certificates to check using the following command
"C:\Program Files\Java\jdk1.7.0_65\bin\keytool.exe” -list -v -keystore cacerts

You now have a file with the default root certificates and the Amazon RDS mysql public key.

3. Set the Java keystore file for Omniscope to this file.
Edit the installconfig.properties file for your Omniscope installation.

http://www.visokio.com/kb/installconfig

Uncomment the ADDITIONAL_JVM_ARGS parameter if commented out and add the following , the value for -Djavax.net.ssl.trustStore must be the full path and filename of the keystore file.

-Djavax.net.ssl.trustStore=”C:\Users\USERNAME\omniscope-server\security\cacerts"

ADDITIONAL_JVM_ARGS=-Djavax.net.ssl.trustStore="
C:\Users\USERNAME\omniscope-server\security\cacerts"

4. Download and install the latest official JDBC driver for MySQL - MySQL Connector/J.
http://dev.mysql.com/downloads/connector/j/

5. Start Omniscope and connect to the database using the Database table source or Database table output.

Connection details
Select database: JDBC (Advanced)
Driver jar path: path to the MySQL Connector/J JDBC driver you installed earlier
e.g.
C:\Program Files (x86)\MySQL\MySQL Connector J\mysql-connector-java-5.1.31-bin.jar

Driver Class: com.mysql.jdbc.Driver
JDBC URL: jdbc:mysql://HOSTNAME:PORT/DATABASENAME?useSSL=true

The HOSTNAME:PORT is the authorized Endpoint listed in the RDS Dashboard for the database instance. You need to append the useSSL=true parameter to the url.
See http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html for the JDBC URL Format.

e.g.
jdbc:mysql://visokiodbinstance.cnyz7m0qcuin.eu-west-1.rds.amazonaws.com:3306/visokiomysqldb?useSSL=true



image

image]]> MySQL over SSH on a Mac http://forums.visokio.com/discussion/774/mysql-over-ssh-on-a-mac Tue, 17 May 2011 05:42:10 -0400 aknotts 774@/discussions
Thanks

Andy]]> Publish to mySQL error http://forums.visokio.com/discussion/1375/publish-to-mysql-error Fri, 09 Mar 2012 03:14:24 -0500 sherriff 1375@/discussions
Database Error

An error occurred connecting to or querying the database.

Data truncation: Incorrect time value: '1970-01-01' for column [ETA] at row 1

The receiving table has been setup in mySQL and the field [ETA] has been give the format TIME which expects hh:mm:ss
The data to be output is in the format: hh:mm:ss

The first row is 00:00:00

But we cannot figure out what might be the cause of the error.]]>