vertica 9.2 SIGFPE

vcarusi

Hi,
I have in vertica.log :
Received fatal signal SIGFPE

Then, the server started making the db consistent , didn't manage and crashed.
Any idea, what does SIGFPE mean ?

Thank you,
Veronica

Jim_Knicely

The SIGFPE signal is sent to a process when it executes an erroneous arithmetic operation, such as division by zero (the name "FPE", standing for floating-point exception, is a misnomer as the signal covers integer-arithmetic errors as well).

Anyway, can you please open a support case on this issue?

vcarusi

Thank you.
I would like to find the log, also.

Jim_Knicely

The Vertica log is located in the catalog directory on each node.

dbadmin=> SELECT node_name, catalog_path FROM nodes;
     node_name      |                       catalog_path
--------------------+----------------------------------------------------------
 v_test_db_node0001 | /home/dbadmin/test_db/v_test_db_node0001_catalog/Catalog
 v_test_db_node0002 | /home/dbadmin/test_db/v_test_db_node0002_catalog/Catalog
 v_test_db_node0003 | /home/dbadmin/test_db/v_test_db_node0003_catalog/Catalog
(3 rows)

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Monitoring/Vertica/MonitoringLogFiles.htm

vcarusi

I am using a Community Edition version. May I open a support case ?

Jim_Knicely

No, but I can. Can you send me your Vertica log files?

james.knicely@microfocus.com

Please send the logs at the time the SIGFPE signal occurred.

vcarusi

query:

EXPORT TO PARQUET ( directory = '/D/period_view' , size = 1024, fileSizeMB =0) over () AS
(
SELECT dp.zone_dst, dp.prev_time_dst, dp.time_dst
FROM period dp
WHERE (dp.prev_time_dst < dp.time_dst)
UNION ALL
SELECT t.zone_dst, t.prev_time_dst, t.time_dst
FROM period dp,
( SELECT dp.zone_dst, max(dp.time_dst) AS prev_time_dst, zone_dst('utc'::varchar(3), statement_timestamp() ) AS time_dst
FROM period dp
WHERE (dp.prev_time_dst < dp.time_dst)
GROUP BY dp.zone_dst
HAVING (max(dp.time_dst) < zone_dst('utc'::varchar(3), statement_timestamp() ))
) t
WHERE (dp.zone_dst = t.zone_dst) AND (dp.time_dst = t.prev_time_dst)
ORDER BY 1, 2
)

CREATE TABLE period
(
zone_dst varchar(100),
prev_time_dst timestamp,
time_dst timestamp
);

2019-03-28 13:39:05.747 Init Session:7f99bf78f700-a0000000000816 [Session] [PQuery] TX:a0000000000816(v_db_node0001-474837:0x27) query...
2019-03-28 13:39:05.811 Init Session:7f99bf78f700-a0000000000816 [Session] [Query] TX:a0000000000816(v_db_node0001-474837:0x27) query...
2019-03-28 13:39:05.813 Init Session:7f99bf78f700-a0000000000816 @v_db_node0001: 01000/4320: Parameter size was not registered by the function and cannot be coerced to a definite data type
2019-03-28 13:39:05.814 Init Session:7f99bf78f700-a0000000000816 @v_db_node0001: 01000/4320: Parameter size was not registered by the function and cannot be coerced to a definite data type
2019-03-28 13:39:05.869 EEcmdq:7f998ffff700 [Main] Handling signal: 8
2019-03-28 13:39:05.904 EEcmdq:7f998ffff700 [Main] Received fatal signal SIGFPE.
2019-03-28 13:39:05.904 EEcmdq:7f998ffff700 [Main] Info: si_code: 1, si_pid: -936588715, si_uid: 32665, si_addr: 0x7f99c82cca55

2019-03-28 13:41:02.519 INFO New log
2019-03-28 13:41:02.519 nameless:7f7ad7ae1240 [Init] Log /D/vertica/catalog/db/v_db_node0001_catalog/vertica.log opened; #1
2019-03-28 13:41:02.519 nameless:7f7ad7ae1240 [Init] Processing command line: /opt/vertica/bin/vertica -D /D/vertica/catalog/db/v_db_node0001_catalog -C db -n v_db_node0001 -h -p 5433 -P 4803 -Y ipv4 -c
2019-03-28 13:41:02.519 nameless:7f7ad7ae1240 [Init] Starting up Vertica Analytic Database v9.2.0-0

Jim_Knicely

Thanks. What is the zone_dst function? Is this a User Defined SQL function?

vcarusi

zone_dst varchar(100) - is a field in the 'period' table.

Jim_Knicely

Hi,

This part of your SQL has a function reference named ZONE_DST:

HAVING (max(dp.time_dst) < zone_dst('utc'::varchar(3), statement_timestamp() ))

vcarusi

sorry, my fault.
is "timezone"('utc'::varchar(3),statement_timestamp() )

select "timezone"('utc'::varchar(3),statement_timestamp() );
2019-04-08 15:57:24

vcarusi

My workaround was to create a view for the query and to export data using it. It worked.

vcarusi

Thank you for your help.

Jim_Knicely

Thanks for reporting this. This is a known issue with SQL containing a UNION or UNION ALL clause.

A fix is coming very soon in 9.2.1!

Example of the bug:

dbadmin=> EXPORT TO PARQUET ( directory = '/home/dbadmin/D/period_view' , fileSizeMB =0) over () AS
dbadmin-> (
dbadmin(> SELECT 1 a
dbadmin(> UNION
dbadmin(> SELECT 1 a
dbadmin(> );
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Jim_Knicely

@vcarusi -

Vertis 9.2.0-7 contains a "fix" for the DB crashing issue when the EXPORT TO PARQUET sql contains a UNION or UNION ALL.

See:
https://www.vertica.com/docs/ReleaseNotes/9.2.x/Vertica_9.2.x_Release_Notes.htm#9.2.0-7

Example:

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.2.0-7
(1 row)

dbadmin=> EXPORT TO PARQUET ( directory = '/home/dbadmin/D/period_view' , fileSizeMB =0) over () AS

dbadmin-> (SELECT 1 c UNION ALL SELECT 1 c);
ERROR 0:  Only a single outer SELECT statement is supported
HINT:  Please use a subquery for multiple outer SELECT statements

dbadmin=> EXPORT TO PARQUET ( directory = '/home/dbadmin/D/period_view' , fileSizeMB =0) over () AS
dbadmin-> SELECT c FROM (SELECT 1 c UNION ALL SELECT 1 c) foo;
 Rows Exported
---------------
             2
(1 row)

vcarusi

Thank you, @Jim_Knicely