Response from Kay Kasemir regarding analog record problem in RDB Channel Archiver
From GlueXWiki
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