Bug in aqms-libs/tndB

[Aparna Bhaskaran]

Hi all,

At the SCSN, we found an issue in the tntdb library for Postgresql. The issue is how OTLv4 handles the return value from the database stored function sequence.getNext(). This function returns a string (or VARCHAR in Postgres speak) of sequence ids. By default, the maximum length of string that OTL allows is 254 chars + string terminator(\0) = 255 chars. This is quite a limitation; sequence.getNext() may return a string of length much larger than 255 chars.

The issue is being tracked in this ticket - https://gitlab.com/aqms-swg/aqms-wavearchiver/-/work_items/14

To read how this event manifested itself at SCSN, please see the bottom of the email.

 

After trying various fixes that did not resolve the issue, the latest fix was to add a couple of settings to .odbc.ini. These settings increase the allowed limit for strings for OTL. 

UnknownSizes        = 255  
MaxVarcharSize      = 6144 

A sample modified .odbc.ini is shown below

Description         = PostgreSQL connection to AQMS database
Driver              = PostgreSQL
Database            = database
Servername          = hostname
UserName            = user
Password            = password
Port                = 5432
Protocol            = 14.4
UnknownSizes        = 255  
MaxVarcharSize      = 6144
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ConnSettings        =

This fix is still being tested. No change to AQMS c++ or stored procedure code is expected. 

If anyone has experience or expertise in OTL with Postgres please feel free to chime in. 

As this issue manifested for us in the wavearchiver (repo: aqms-wavearchiver), the database sequences that produced this error were for the filename and waveform tables.

This issue was detected by our analysts. When they would bring up an event in Jiggle, they would see waveforms for only some of the channel components for a station. For .e.g they would see waveforms for CI.CTC.HHE and CI.CTC.HHN but not for the HHZ channel. Investigation found that the wavearchiver had saved waveforms for all three channel components to disk for the event but the database update had failed. Due to truncation of sequence ids by OTL, insufficient sequence ids were retrieved and inserts to filename and waveform tables failed.

regards,

Aparna

[Loren Antolik]

Aparna,

Thanks for the update.  Let us know if we can help test in any way.

I don't think we have seen this error occur in any systems we build or maintain.  Which sequence id values result in lengths greater than 255 chars?

thanks,

Loren

--
You received this message because you are subscribed to the Google Groups "AQMS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to anss-aqms+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/anss-aqms/CAKtPfvxi%3Dwxp165NAK%3DJHW0MZs8A6%3DYxptrpOn%3DQcfPdYLC5dQ%40mail.gmail.com.

To follow ISTI news and updates please subscribe to our newsletter "the isti letter" at  https://www.isti.com/newsletter-sign-up.

[Ellen Yu]

Hi Loren,

We have seen this in our waveform archiver.  We think it is more likely to happen when you have larger sequence numbers and there is also a lot of requests for the sequence happening in quick succession or perhaps concurrently.  Our waveform archiver is multithreaded so this happens frequently, but intermittently.

Ellen

[Aparna Bhaskaran]

Hello all,

A final update on this issue. The corresponding ticket has been updated and closed.

The fix for this issue was to set MaxVarcharSize, in odbc.ini, to a value higher than the default of 255 chars. As the database stored procedure sequence.getNext() already limits the return value to under 6000 characters, MaxVarcharSize = 6144 = 6 * 1024 is sufficient. This was the one and only fix for this issue.

The stored procedure, seqeunce.getNext() and tndb in aqms-libs remain unchanged.

regards,

Aparna

--
You received this message because you are subscribed to the Google Groups "AQMS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to anss-aqms+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/anss-aqms/b06c3975-bb1f-473a-a6eb-014824fc73e3n%40googlegroups.com.

[Paul Friberg]

Thanks Aparna. 

Everyone please make sure you update your .odbc.ini file with this setting, AND restart any C++ programs that interact with the Postgres database. 

Paul

To view this discussion visit https://groups.google.com/d/msgid/anss-aqms/CAKtPfvxqixScLFFetvjc5q75_CD7Gq3%3Dk592TqE%3DYhvcO8mRmQ%40mail.gmail.com.

--

===================================
Paul Friberg   p.fr...@isti.com
CEO/Seismologist
ISTI==Instrumental Software Technologies, Inc.
Phone +1.518.602.0001