Experimental Physics and
Industrial Control System

1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 <2023> 2024	Index	1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 <2023> 2024
<== Date ==>		<== Thread ==>

Subject:	Re: Question about fetching history data from MySQL by using phoebus
From:	neu lzf via Tech-talk <tech-talk at aps.anl.gov>
To:	"Kasemir, Kay" <kasemirk at ornl.gov>, CAOUEN Loic <loic.caouen at cea.fr>
Cc:	"tech-talk at aps.anl.gov" <tech-talk at aps.anl.gov>
Date:	Fri, 30 Jun 2023 05:19:23 +0000

Hi Kay and Loic,

Thank you all for the reply and sorry to interrupt again. The problem still exists. I think maybe the problem is MySQL library version.

I use MySQL 5.7.12 on server side (192.168.1.230) and CSS OPI on client side (192.168.1.231). I have done all the MySQL configurations before and CSS are successful to connect to MySQL, while for Phoebus I am still struggling to find the reason.

The configurations for Phoebus are:

org.csstudio.trends.databrowser3/urls=jdbc:mysql://192.168.1.230/archive

org.csstudio.trends.databrowser3/archives=jdbc:mysql://192.168.1.230/archive

Maybe the problem is MySQL library in Phoebus are not suitable to MySQL 5.7.12, I download the mysql-connector-java-5.1.49 from website https://downloads.mysql.com/archives/c-j/, and try to directly replace mysql-connector-java-8.0.30.jar with mysql-connector-java-5.1.49 in Phoebus folder, the path is product-sns-4.7.3-SNAPSHOT/lib/, however the problem still exists. I wonder if the simply replacement is right or maybe java version is the issue.

CSS 4.5.10-SNAPSHOT community edition could connect to MySQL 5.7.12 successfully to read the historic data on the following java environment:

openjdk version "1.8.0_102"

OpenJDK Runtime Environment (build 1.8.0_102-b14)

OpenJDK 64-Bit Server VM (build 25.102-b14, mixed mode)

while, If running Phoebus, I have to change to the java environment below (update-alternatives --config java) and thus failed to connect to MySQL 5.7.12 even if the configuration are the same as CSS for the address of archive Data Server URLs and Archive Data sources, user name and passwords:

java version "16.0.1" 2021-04-20

Java(TM) SE Runtime Environment (build 16.0.1+9-24)

Java HotSpot(TM) 64-Bit Server VM (build 16.0.1+9-24, mixed mode, sharing)

I wonder if java version is the issue and if simply replace mysql-connector-java-8.0.30.jar with mysql-connector-java-5.1.49 is right. Thanks again !

The error messages are as follows:

=======================================================================================================================

URL: jdbc:mysql://192.168.1.230/archive

Error:

java.lang.Exception: Cannot connect to jdbc:mysql://192.168.1.230/archive as root

at org.phoebus.framework.rdb.RDBInfo.connect(RDBInfo.java:134)

at org.phoebus.framework.rdb.RDBConnectionPool.getConnection(RDBConnectionPool.java:137)

at org.phoebus.archive.reader.rdb.RDBArchiveReader.getStatusValues(RDBArchiveReader.java:116)

at org.phoebus.archive.reader.rdb.RDBArchiveReader.<init>(RDBArchiveReader.java:70)

at org.phoebus.archive.reader.rdb.RDBArchiveReaderFactory.createReader(RDBArchiveReaderFactory.java:28)

at org.phoebus.archive.reader.ArchiveReaders.createReader(ArchiveReaders.java:50)

at org.csstudio.trends.databrowser3.ui.search.ArchiveListPane.lambda$showArchiveInfo$3(ArchiveListPane.java:76)

at org.phoebus.framework.jobs.Job.execute(Job.java:50)

at org.phoebus.framework.jobs.JobManager.execute(JobManager.java:54)

at org.phoebus.framework.jobs.JobManager.lambda$schedule$0(JobManager.java:45)

at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)

at java.base/java.lang.Thread.run(Thread.java:831)

Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://192.168.1.230/archive

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)

at org.phoebus.framework.rdb.RDBInfo.connect(RDBInfo.java:130)

... 13 more

==========================================================================================================================

From: Kasemir, Kay <kasemirk at ornl.gov>
Sent: 29 June 2023 17:40
To: neu lzf <lzfneu at live.com>
Cc: tech-talk at aps.anl.gov <tech-talk at aps.anl.gov>
Subject: Re: Question about fetching history data from MySQL by using phoebus

> I try to use binary SNS version of Phoebus and I checked the product/lib folder and found that the file mysql-connector-java-8.0.30.jar is present.

Right, should look something like this :

/jdk/…

/lib/…

/lib/product-sns-4.7.3-SNAPSHOT/lib/mysql-connector-java-8.0.30.jar

When you start it, it’s using the jdk/… that’s included in the download, so the menu Help, About shows java version 17, which should work with that mysql jar file.

When I then try to read data from say jdbc:mysql://localhost/archive, that either works, or it fails with a meaningful error message because there is a connection issue (network, no MySQL running, …) like this :

WARNING [org.csstudio.trends.databrowser3] No archived data for xxx

…

java.lang.Exception: Cannot connect to jdbc:mysql://localhost/archive as archive

…

Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

What you see is this :

the connection issue: Cannot connect to jdbc://mysql://192.168.1.230/archive as root
Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://192.168.1.230/archive

Check the exact connection string. You mention both

jdbc://mysql://192.168.1.230/archive

and

jdbc:mysql://192.168.1.230/archive

The correct syntax is something like jdbc:mysql://localhost/archive

If I try jdbc://mysql://localhost/archive then the extra // indeed turns it into something that’s not recognized as a MySQL connection string, and then “no suitable driver” makes sense for an error.

Replies:: Re: Re: Question about fetching history data from MySQL by using phoebus lzfneu--- via Tech-talk

References:: Question about fetching history data from MySQL by using phoebus neu lzf via Tech-talk; Re: Question about fetching history data from MySQL by using phoebus Kasemir, Kay via Tech-talk; Re: Question about fetching history data from MySQL by using phoebus neu lzf via Tech-talk; RE: Question about fetching history data from MySQL by using phoebus CAOUEN Loic via Tech-talk; Re: Question about fetching history data from MySQL by using phoebus Kasemir, Kay via Tech-talk

Navigate by Date:: Prev: Re: Phoebus Increased Slider Sensitivity? Tynan Ford via Tech-talk; Next: Help using newest OPC UA Modules/IOCs Marco A. Barra Montevechi Filho via Tech-talk; Index: 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 <2023> 2024
Navigate by Thread:: Prev: Re: Question about fetching history data from MySQL by using phoebus Kasemir, Kay via Tech-talk; Next: Re: Re: Question about fetching history data from MySQL by using phoebus lzfneu--- via Tech-talk; Index: 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 <2023> 2024

ANJ, 30 Jun 2023

· Home · News · About · Base · Modules · Extensions · Distributions · Download ·
· Search · EPICS V4 · IRMIS · Talk · Bugs · Documents · Links · Licensing ·

Experimental Physics and Industrial Control System

Experimental Physics and
Industrial Control System