ERROR database: postgresql_error: ERROR: operator does not exist: ` integer

[ghost]

Running build 336 just fine, compiles, and runs. Compiled build 337 with no error. After running in same enviromet as build 336 I get the below:

INFO database: Defaulting Reconnect sleep time to 5 second
ERROR database: postgresql_error: ERROR: operator does not exist: integer LINE 1: SELECTref_system_id`, ref_system_name FROM reference_syste...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

[CacheSynchronize()]:, SystemCacheSyncronize() call failed.
ERROR: database [DatabaseInitFinalize()]: CacheSynchronize() call failed ...
Fatal Error, Quitting..
Barnyard2 exiting
database: Closing connection to database "snort"

Moving back to 336 there is no problem...

[nunogt]

Hitting the same issue using latest master (that would be f9186f2). @mesteele101 can't make sense of the build numbers you're referring to, which commit would that be?

[ghost]

This problem is directly related to build 337 of Barnyard2. Build 337 compiles just fine with the latest PostgreSQL source, but running Barnyard2 on a PostgreSQL server fails.

If I compile 336 using the same PostgreSQL source, I have no problems running Barnyard2 and connecting to a PostgreSQL server.

[nunogt]

Where can build 336 be found?

[fathur]

someone find the solution? I got some problem here...

[fathur]

I have the solution:

Edit this file before compile
src/output-plugins/spo_database_cache.h

And replace everything `ref_system_id` (with backtick) into ref_system_id (without backtick).

Then compile it.

I do not know this will be affect to another driver or not. At least it would be help in postgres.

I thought this is a bug for postgres @firnsy

[dan-aksenov]

fathur's solution confirmed.

[ghost]

I just downloaded and the above lines to change are not in the file? Has the latest Barnyard2 been modified to include that change.

Edit this file before compile
src/output-plugins/spo_database_cache.h

And replace everything ref_system_id (with backtick) into ref_system_id (without backtick).

[fathur]

I have checked and it is still there...

https://github.com/firnsy/barnyard2/blob/master/src/output-plugins/spo_database_cache.h

[ghost]

Thanks, I was looking at something different.

Is this problems specific to Barnyard2, or it the problem caused by some other application?

I'm wondering why this fix has yet to be incorporated into Barnyard2?

[fathur]

I think this is a bug in Barnyard2 for postgreSql database. Other applications should not affecting when compile it.

[peter-manton]

This problem is still occurring - is this likely to be fixed any time soon?

Patch below:

--- spo_database_cache.h	2017-06-27 16:13:55.197106291 +0100
+++ spo_database_cache.h.new	2017-06-29 15:52:24.987624409 +0100
@@ -96,9 +96,9 @@
 ** Ref: http://www.postgresql.org/docs/9.1/static/datatype-binary.html
 
 #define PGSQL_SQL_INSERT_SPECIFIC_REFERENCE_SYSTEM "INSERT INTO reference_system (ref_system_name) VALUES (E'%s');"
-#define PGSQL_SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT `ref_system_id` FROM reference_system WHERE ref_system_name = E'%s';"
-#define PGSQL_SQL_INSERT_SPECIFIC_REF  "INSERT INTO reference (`ref_system_id`,ref_tag) VALUES ('%u',E'%s');"
-#define PGSQL_SQL_SELECT_SPECIFIC_REF  "SELECT ref_id FROM reference WHERE `ref_system_id` = '%u' AND ref_tag = E'%s';"
+#define PGSQL_SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT ref_system_id FROM reference_system WHERE ref_system_name = E'%s';"
+#define PGSQL_SQL_INSERT_SPECIFIC_REF  "INSERT INTO reference (ref_system_id,ref_tag) VALUES ('%u',E'%s');"
+#define PGSQL_SQL_SELECT_SPECIFIC_REF  "SELECT ref_id FROM reference WHERE ref_system_id = '%u' AND ref_tag = E'%s';"
 #define PGSQL_SQL_INSERT_CLASSIFICATION "INSERT INTO sig_class (sig_class_name) VALUES (E'%s');"
 #define PGSQL_SQL_SELECT_SPECIFIC_CLASSIFICATION "SELECT sig_class_id FROM sig_class WHERE sig_class_name = E'%s';"
 #define PGSQL_SQL_INSERT_SIGNATURE "INSERT INTO signature (sig_sid, sig_gid, sig_rev, sig_class_id, sig_priority, sig_name) VALUES ('%u','%u','%u','%u','%u',E'%s');"
@@ -117,9 +117,9 @@
 
 
 #define SQL_INSERT_SPECIFIC_REFERENCE_SYSTEM "INSERT INTO reference_system (ref_system_name) VALUES ('%s');"
-#define SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT `ref_system_id` FROM reference_system WHERE ref_system_name = '%s';"
-#define SQL_INSERT_SPECIFIC_REF  "INSERT INTO reference (`ref_system_id`,ref_tag) VALUES ('%u','%s');"
-#define SQL_SELECT_SPECIFIC_REF  "SELECT ref_id FROM reference WHERE `ref_system_id` = '%u' AND ref_tag = '%s';"
+#define SQL_SELECT_SPECIFIC_REFERENCE_SYSTEM "SELECT ref_system_id FROM reference_system WHERE ref_system_name = '%s';"
+#define SQL_INSERT_SPECIFIC_REF  "INSERT INTO reference (ref_system_id,ref_tag) VALUES ('%u','%s');"
+#define SQL_SELECT_SPECIFIC_REF  "SELECT ref_id FROM reference WHERE ref_system_id = '%u' AND ref_tag = '%s';"
 #define SQL_INSERT_CLASSIFICATION "INSERT INTO sig_class (sig_class_name) VALUES ('%s');"
 #define SQL_SELECT_SPECIFIC_CLASSIFICATION "SELECT sig_class_id FROM sig_class WHERE sig_class_name = '%s';"
 #define SQL_INSERT_SIGNATURE "INSERT INTO signature (sig_sid, sig_gid, sig_rev, sig_class_id, sig_priority, sig_name) VALUES ('%u','%u','%u','%u','%u','%s');"
@@ -145,8 +145,8 @@
 #define SQL_SELECT_ALL_SIGREF "SELECT ref_id, sig_id, ref_seq FROM sig_reference ORDER BY sig_id,ref_seq;"
 #define SQL_INSERT_SIGREF "INSERT INTO sig_reference (ref_id,sig_id,ref_seq) VALUES ('%u','%u','%u');"
 #define SQL_SELECT_SPECIFIC_SIGREF "SELECT ref_id FROM sig_reference WHERE (ref_id = '%u') AND (sig_id = '%u') AND (ref_seq='%u');"
-#define SQL_SELECT_ALL_REFERENCE_SYSTEM  "SELECT `ref_system_id`, ref_system_name FROM reference_system;"
-#define SQL_SELECT_ALL_REF "SELECT ref_id, `ref_system_id`, ref_tag FROM reference; "
+#define SQL_SELECT_ALL_REFERENCE_SYSTEM  "SELECT ref_system_id, ref_system_name FROM reference_system;"
+#define SQL_SELECT_ALL_REF "SELECT ref_id, ref_system_id, ref_tag FROM reference; "
 #define SQL_SELECT_ALL_CLASSIFICATION "SELECT sig_class_id, sig_class_name FROM sig_class ORDER BY sig_class_id ASC; "
 #define SQL_SELECT_ALL_SIGNATURE "SELECT sig_id, sig_sid, sig_gid,sig_rev, sig_class_id, sig_priority, sig_name FROM signature;"
 #define SQL_UPDATE_SPECIFIC_SIGNATURE "UPDATE signature SET "		\

[NathanGibbs3]

I think the merging of PR #186 on Apr 12, 2016 caused this issue.