View source for Response from Kay Kasemir regarding analog record problem in RDB Channel Archiver

<pre>
Hello:

As you already figured out, the issue is that EPICS R3.14.10+ IOCs send
NaN for non-initliazed meta data fields,
combined with MySQL and Postgres being unable to store NaN (and +-Inf)
in floating point table columns.

A workaround has been provided in early September.
If you get the latest copy of the sources from the SourceForge
repository, the NumericMetaDataHelper.insert() will look as shown
below.
Please try to update your copy of the sources accordingly.

It's not in the current SNS source code snapshot, because with Oracle
as used at the SNS there is no NaN issue, so we have not updated
SNS to the latest SourceForge version.

Thanks,
Kay



    public static void insert(final RDBUtil rdb, final SQL sql,

            final RDBWriteChannel channel, final INumericMetaData meta) throws Exception

    {

        final Connection connection = rdb.getConnection();

        final PreparedStatement insert = connection.prepareStatement(sql.numeric_meta_insert);

        try

        {

            insert.setInt(1, channel.getId());

            setDoubleOrNull(insert, 2, meta.getDisplayLow());

            setDoubleOrNull(insert, 3, meta.getDisplayHigh());

            setDoubleOrNull(insert, 4, meta.getWarnLow());

            setDoubleOrNull(insert, 5, meta.getWarnHigh());

            setDoubleOrNull(insert, 6, meta.getAlarmLow());

            setDoubleOrNull(insert, 7, meta.getAlarmHigh());

            insert.setInt(8, meta.getPrecision());

            // Oracle schema has NOT NULL units...

            String units = meta.getUnits();

            if (units == null  ||  units.length() < 1)

                units = " "; //$NON-NLS-1$

            insert.setString(9, units);

            insert.executeUpdate();

        }

        finally

        {

            insert.close();

        }

    }


    /** Some dialects like MySQL cannot handle NaN or +-Inf.

     *  Set those numbers as Null in the statement.

     *  @param statement

     *  @param index

     *  @param number

     *  @throws SQLException

     */

private static void setDoubleOrNull(final PreparedStatement statement, final int index,

            final double number) throws SQLException

    {

if (Double.isInfinite(number) || Double.isNaN(number))

statement.setNull(index, Types.DOUBLE);

else

statement.setDouble(index, number);

    }

On 12/1/11 20:03 , "Hovanes Egiyan" <hovanes@jlab.org<mailto:hovanes@jlab.org>> wrote:

Dear Kay,

I work at Jefferson Lab in Newport News, and we are evaluating the
RDB Channel Archiver for one of our new experimental halls.
In particular, we are checking how well it works with MySQL database on
RHEL5 32bit Linux.
I downloaded and compiled the archiver engine and the archiver
configurator in CSS.
I configured a test engine and started an IOC which provides PVs for
that engine. The engine runs successfully when I only archive "bi" and
"bo" record types.
The samples properly show up in the MySQL database tables.
When I add a single "ai" or "ao" to the list of the PVs to be archived
and restart the engine, the engine gives error
messages. Below I pasted the output messages that I see on the terminal
where the engine is running.
The 'NaN'-related messages keep coming and no entries are made in the
database for any PVs.
Although, when IOC is shut off the engine does make an entry about
invalid record. I do not know what these
errors are, and I was hoping you can give me some idea what can be
causing these and how to fix it.
I think I assigned values to the  "metadata" that the archiver needs for
that analog record that I want to archive.
I also pasted the definition of that record and the result I am getting
with "ca_test" for that analog channel, and it
seems that there are indeed two "NaN" values reported by CA, but I do
not know what fields they correspond to.

The version of the EPICS base I use is R1.14.11, and the CSS source code
I got is 3.0.2 from the SNS web site:
hxxp://ics-web.sns.ornl.gov/css/products.html , under "SNS Control Room"
column.

Thanks in advance,
Hovanes Egiyan
Staff Scientist
Jefferson Lab

=================== Error Messages from Archive Engine
=======================================

2011-12-01 18:25:03.578 INFO [Thread 10]
org . csstudio . archive . engine . Application (start) - Archive Engine 3.0.1
2011-12-01 18:25:03.722 INFO [Thread 10]
org . csstudio . archive . engine . server.EngineServer (<init>) - Engine HTTP
Server port 4,812
2011-12-01 18:25:03.723 INFO [Thread 10]
org . csstudio . archive . engine . Application (start) - Reading configuration
'Test'
2011-12-01 18:25:04.012 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix ca provided
by EPICS PVFactory in org . csstudio . utility . pv . epics
2011-12-01 18:25:04.013 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix epics
provided by EPICS PVFactory in org . csstudio . utility . pv . epics
2011-12-01 18:25:04.016 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix sim provided
by Simulated PV in org . csstudio . utility . pv . simu
2011-12-01 18:25:04.016 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix loc provided
by Local PV in org . csstudio . utility . pv . simu
2011-12-01 18:25:04.018 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix sys provided
by System PV in org . csstudio . utility . pv . simu
2011-12-01 18:25:04.019 CONFIG [Thread 10]
org . csstudio . utility . pv . PVFactory (initialize) - PV prefix const
provided by Constant PV in org . csstudio . utility . pv . simu
2011-12-01 18:25:04.025 INFO [Thread 10]
org . csstudio . archive . engine . Application (start) - Read configuration: 3
channels in 0.3 seconds
2011-12-01 18:25:04.025 INFO [Thread 10]
org . csstudio . archive . engine . Application (start) - Running, CA addr list:
127.0.0.1 129.57.36.125 129.57.167.67
2011-12-01 18:25:04.026 CONFIG [Thread 10]
org . csstudio . utility . pv . epics.PVContext (initJCA) - Initializing JCA (JNI)
2011-12-01 18:25:04.029 INFO [Thread 11]
org . csstudio . archive . engine . model.WriteThread (run) - WriteThread starts
2011-12-01 18:25:04.132 INFO [Thread 12]
org . csstudio . archive . engine . scanner.ScanThread (run) - Scan Thread runs
2011-12-01 18:25:34.073 WARNING [Thread 11]
org . csstudio . archive . engine . model.WriteThread (run) - Error, will try to
reconnect
java . sql . SQLException: 'NaN' is not a valid numeric or approximate
numeric value
         at com . mysql . jdbc . SQLError . createSQLException(SQLError.java:910)
         at
com . mysql . jdbc . ServerPreparedStatement . setDouble(ServerPreparedStatement.java:1742)
         at
org . csstudio . archive . writer . rdb . NumericMetaDataHelper . insert(NumericMetaDataHelper.java:67)
         at
org . csstudio . archive . writer . rdb . RDBArchiveWriter . writeMetaData(RDBArchiveWriter.java:201)
         at
org . csstudio . archive . writer . rdb . RDBArchiveWriter . addSample(RDBArchiveWriter.java:169)
         at
org . csstudio . archive . engine . model . WriteThread . write(WriteThread.java:256)
         at
org . csstudio . archive . engine . model . WriteThread . run(WriteThread.java:180)
         at java . lang . Thread . run(Thread.java:662)

2011-12-01 18:26:04.108 WARNING [Thread 11]
org . csstudio . archive . engine . model.WriteThread (run) - Error, will try to
reconnect
java . sql . SQLException: 'NaN' is not a valid numeric or approximate
numeric value
         at com . mysql . jdbc . SQLError . createSQLException(SQLError.java:910)
         at
com . mysql . jdbc . ServerPreparedStatement . setDouble(ServerPreparedStatement.java:1742)
         at
org . csstudio . archive . writer . rdb . NumericMetaDataHelper . insert(NumericMetaDataHelper.java:67)
         at
org . csstudio . archive . writer . rdb . RDBArchiveWriter . writeMetaData(RDBArchiveWriter.java:201)
         at
org . csstudio . archive . writer . rdb . RDBArchiveWriter . addSample(RDBArchiveWriter.java:169)
         at
org . csstudio . archive . engine . model . WriteThread . write(WriteThread.java:256)
         at
org . csstudio . archive . engine . model . WriteThread . run(WriteThread.java:180)
         at java . lang . Thread . run(Thread.java:662)


====================== part of the DB file defining the problematic PV
===================================

record( ai, "bus$(BUS):$(ADDR):CAT")
{
   field( DESC, "Cathode Voltage Readback")
   field( DTYP, "BaseIU")
   field( INP, "@$(BUS):$(ADDR):ADC:CAT")
   field( SCAN, "$(SCAN)")
   field( PREC, "2")
   field( LOPR, "0")
   field( HOPR, "2000")
   field( LOLO, "0")
   field( LOW,  "0")
   field( HIGH, "1700")
   field( HIHI, "1800")
   field( LSV , "NO_ALARM")
   field( LLSV, "NO_ALARM")
   field( HSV,  "MINOR")
   field( HHSV, "MAJOR")
   field( ADEL, "1.0" )
   field( EGU,  "V" )
}


========================== ca_test result for the problem PV
=================================


310 /home/halldrun/epics/app/hvIU/src: ca_test bus0:0x733e06:CAT
name:   bus0:0x733e06:CAT
native type:    DBR_DOUBLE
native count:   1
DBR_STRING      1577.71
DBR_SHORT       1577
DBR_FLOAT       1577.7126
DBR_ENUM        1577
DBR_CHAR        41
DBR_LONG        1577
DBR_DOUBLE      1577.7126
DBR_STS_STRING   0  0   Value: 1577.71
DBR_STS_SHORT    0  0   Value: 1577
DBR_STS_FLOAT    0  0   Value: 1577.7126
DBR_STS_ENUM     0  0   Value: 1577
DBR_STS_CHAR     0  0   Value: 41
DBR_STS_LONG     0  0   Value: 1577
DBR_STS_DOUBLE   0  0   Value: 1577.7126
DBR_TIME_STRING  0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value:
1577.71
DBR_TIME_SHORT   0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value: 1577
DBR_TIME_FLOAT   0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value:
1577.7126
DBR_TIME_ENUM    0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value: 1577
DBR_TIME_CHAR    0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value: 41
DBR_TIME_LONG    0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value: 1577
DBR_TIME_DOUBLE  0  0   TimeStamp: 2011/12/01 19:53:55.458780   Value:
1577.7126
DBR_GR_STRING    0  0   Value: 1577.71
DBR_GR_SHORT     0  0 V
             2000        0     1800     1700        0        0   Value:
1577
DBR_GR_FLOAT     0  0 V   2
         2000.000    0.000 1800.000 1700.000      nan      nan   Value:
1577.7126
DBR_GR_ENUM      0  0   Value: 1577
DBR_GR_CHAR      0  0 V
              208        0        8      164        0        0   Value: 41
DBR_GR_LONG      0  0 V
             2000        0     1800     1700 -2147483648 -2147483648
Value: 1577
DBR_GR_DOUBLE    0  0 V   2
         2000.000    0.000 1800.000 1700.000      nan      nan   Value:
1577.7126
DBR_CTRL_STRING  0  0   Value: 1577.71
DBR_CTRL_SHORT   0  0 V
             2000        0     1800     1700        0        0
2000        0 Value: 1577
DBR_CTRL_FLOAT   0  0 V   2
         2000.000    0.000 1800.000 1700.000      nan      nan
2000.000    0.000 Value: 1577.7126
DBR_CTRL_ENUM    0  0   Value: 1577
DBR_CTRL_CHAR    0  0 V
              208        0        8      164        0        0
208        0 Value:   41
DBR_CTRL_LONG    0  0 V
             2000        0     1800     1700 -2147483648 -2147483648
2000        0   Value: 1577
DBR_CTRL_DOUBLE  0  0 V   2
         2000.000    0.000 1800.000 1700.000      nan      nan
2000.000    0.000 Value: 1577.712646
DBR_STSACK_STRING        0  0  1  2 1577.71
DBR_CLASS_NAME  ai

</pre>