SQL query faults

[BasvanH]

Expected behavior

No SQL errors in gvmd.log

Actual behavior

I've had these SQL errors before, but to elimitate possible mistakes by myself I started with a fresh new DB. And agein fte rrunning for a few weeks, these errors start to popup during scans. If they appear, jobs get stuck.

md manage:WARNING:2020-06-12 07h44.29 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 15627 AND name = 'cpe:/a:jquery:jquery:1.6.2' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 15627        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:jquery:jquery:1.6.2');
md manage:WARNING:2020-06-12 07h44.29 UTC:4435: sql_x_internal: sql_exec_internal failed
md manage:WARNING:2020-06-12 07h44.31 UTC:4435: sql_exec_internal: PQexec failed: ERROR:  more than one row returned by a subquery used as an expression
 (7)
md manage:WARNING:2020-06-12 07h44.31 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 16505 AND name = 'cpe:/a:apache:http_server' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 16505        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:apache:http_server');
md manage:WARNING:2020-06-12 07h44.31 UTC:4435: sql_x_internal: sql_exec_internal failed
md manage:WARNING:2020-06-12 07h44.31 UTC:4435: sql_exec_internal: PQexec failed: ERROR:  more than one row returned by a subquery used as an expression
 (7)
md manage:WARNING:2020-06-12 07h44.31 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 16505 AND name = 'cpe:/a:apache:http_server' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 16505        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:apache:http_server');
md manage:WARNING:2020-06-12 07h44.15 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 15608 AND name = 'cpe:/a:openbsd:openssh:7.4' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 15608        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:openbsd:openssh:7.4');
md manage:WARNING:2020-06-12 07h44.15 UTC:4435: sql_x_internal: sql_exec_internal failed
md manage:WARNING:2020-06-12 07h44.17 UTC:4435: sql_exec_internal: PQexec failed: ERROR:  more than one row returned by a subquery used as an expression
 (7)
md manage:WARNING:2020-06-12 07h44.17 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 15612 AND name = 'cpe:/a:openbsd:openssh:7.4' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 15612        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:openbsd:openssh:7.4');
md manage:WARNING:2020-06-12 07h44.17 UTC:4435: sql_x_internal: sql_exec_internal failed
md manage:WARNING:2020-06-12 07h44.17 UTC:4435: sql_exec_internal: PQexec failed: ERROR:  more than one row returned by a subquery used as an expression
 (7)
md manage:WARNING:2020-06-12 07h44.14 UTC:4435: sql_exec_internal: SQL: SELECT value FROM report_host_details WHERE report_host = 15592 AND name = 'cpe:/a:php:php' AND source_type = 'nvt' AND source_name     = (SELECT source_name FROM report_host_details        WHERE report_host = 15592        AND source_type = 'nvt'        AND name = 'App'        AND value = 'cpe:/a:php:php');
md manage:WARNING:2020-06-12 07h44.14 UTC:4435: sql_x_internal: sql_exec_internal failed
md manage:WARNING:2020-06-12 07h44.14 UTC:4435: sql_exec_internal: PQexec failed: ERROR:  more than one row returned by a subquery used as an expression
 (7)

I have multiple jobs, NVT and CVE's. I have some excludes setup. It's a master slave setup with one master and two slaves. I did not delete any host from assets, as it looks like the query searches for a unknown host.

Steps to reproduce

I think it has something to do with the overrides or master/slave things not going right.

GVM versions

gsa: Greenbone Security Assistant 9.0.1git-a2e4e3cd3-gsa-9.0
gvm: 9.0.1~git-5a976c58-gvmd-9.0
openvas-scanner: OpenVAS 7.0.1
gvm-libs: gvm-libs 11.0.1

Environment

Operating system:
Linux xxxxxxxxx 4.15.0-101-generic 102-Ubuntu SMP Mon May 11 10:07:26 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.4 LTS"

[BasvanH]

This is the actual PSQL error:

ERROR:  more than one row returned by a subquery used as an expression
SQL state: 21000

Looking at the subquery:

	FROM report_host_details
	WHERE report_host = 16692
	AND source_type = 'nvt'
	AND name = 'App'
	AND value = 'cpe:/a:openbsd:openssh:7.4'

This indeed returns three rows:

"1.3.6.1.4.1.25623.1.0.108577"
"1.3.6.1.4.1.25623.1.0.108577"
"1.3.6.1.4.1.25623.1.0.108577"

[BasvanH]

Any update on this matter, Greenbone is not usable this way. Job stale during the SQL failts. If you need any information, please ask. More then willing to provide it to you.

[timopollmeier]

This looks like an issue with products being installed in multiple locations on the same host or maybe detected multiple times due to vhosts sharing an IP address.
We're currently working on improving this in master, but I'll fix the SQL error in gvmd-9.0 soon.

[BasvanH]

It indeed is a master / slave (2) setup. Jobs with the same targets are used on both the slaves. Great to hear it's known issue. I'll be most happy if you can implement a fix so I can run again.

[BasvanH]

The patch has solved those SQL errors, but I get new errors: #1148